Not the answer you need?
Register and ask your own question!

Deadlock found when trying to get lock with Magento

aeriallsaerialls EntrantInactive User Role Beginner
Hello!

We have some deadlock problems with our new XtraDB Cluster. We have a 3 nodes cluster (2 Percona XtraDB Cluster in version 5.6.24-72.2-56 and a third arbitrer with garbd) hosting a Magento website.

The old platform used a standard asynchonous MySQL replication with master/slave and we decided to migrate to a much robust solution witth XtraDB Cluster. Since the migration, we notices some deadlock errors in Magento:

a:5:{i:0;s:111:"SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction";i:1;s:7362:"#0 /var/[...]Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)

Those errors was not present before the migration. We tried to tweak the wsrep configuration but without success. We have HAProxy on each frontend servers (3 servers hosting the magento source code) to redirect ALL requests (read and writes) to the single node of the cluster.

Our HAProxy configuration is simple:

frontend FE-BDD-RW
bind 127.0.0.1:3306
mode tcp
default_backend BE-BDD-RW

backend BE-BDD-RW
mode tcp
option httpchk
server BDD01 192.168.10.1:3306 check port 9200 inter 12000 rise 3 fall 3
server BDD02 192.168.10.2:3306 check port 9200 inter 12000 rise 3 fall 3 backup

The wsrep configuration has been tweaked from several sources to help the deadlock issue but without success. Right now, our wsrep configuration is like that:

wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_cluster_address = gcomm://192.168.10.1,192.168.10.2,192.168.10.3
wsrep_cluster_name = CLUSTER01
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = xtrabackup:mypassword
wsrep_provider_options = "gcs.fc_limit = 256; gcs.fc_factor = 0.99; gcs.fc_master_slave = yes"
wsrep_retry_autocommit = 4
binlog_format = ROW
default_storage_engine = InnoDB
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2

Is that possible to Percona to understand that we are in a master/slave situation to avoid deadlock issues that are related to galera? Right now, we don't know how to tweak more the Percona configuration.

Thanks!

Comments

  • jriverajrivera Percona Support Engineer Percona Staff Role
    I wonder what the status of the cluster is, can you provide cluster status?

    mysql -e "show status like 'wsrep%'" | egrep "cluster|local"
  • aeriallsaerialls Entrant Inactive User Role Beginner
    Hello!

    Thanks for the answer.

    You can find the values below:

    On the first node:

    wsrep_local_state_uuid df7d0407-1419-11e5-9c09-b2761f45777f
    wsrep_local_commits 233119
    wsrep_local_cert_failures 0
    wsrep_local_replays 0
    wsrep_local_send_queue 0
    wsrep_local_send_queue_max 2
    wsrep_local_send_queue_min 0
    wsrep_local_send_queue_avg 0.000102
    wsrep_local_recv_queue 0
    wsrep_local_recv_queue_max 1
    wsrep_local_recv_queue_min 0
    wsrep_local_recv_queue_avg 0.000000
    wsrep_local_cached_downto 1391316
    wsrep_local_state 4
    wsrep_local_state_comment Synced
    wsrep_cluster_conf_id 39
    wsrep_cluster_size 3
    wsrep_cluster_state_uuid df7d0407-1419-11e5-9c09-b2761f45777f
    wsrep_cluster_status Primary
    wsrep_local_bf_aborts 25
    wsrep_local_index 2

    On the second node:

    wsrep_local_state_uuid df7d0407-1419-11e5-9c09-b2761f45777f
    wsrep_local_commits 142
    wsrep_local_cert_failures 0
    wsrep_local_replays 0
    wsrep_local_send_queue 0
    wsrep_local_send_queue_max 2
    wsrep_local_send_queue_min 0
    wsrep_local_send_queue_avg 0.000393
    wsrep_local_recv_queue 0
    wsrep_local_recv_queue_max 62
    wsrep_local_recv_queue_min 0
    wsrep_local_recv_queue_avg 0.142113
    wsrep_local_cached_downto 1395415
    wsrep_local_state 4
    wsrep_local_state_comment Synced
    wsrep_cluster_conf_id 39
    wsrep_cluster_size 3
    wsrep_cluster_state_uuid df7d0407-1419-11e5-9c09-b2761f45777f
    wsrep_cluster_status Primary
    wsrep_local_bf_aborts 0
    wsrep_local_index 1
  • aeriallsaerialls Entrant Inactive User Role Beginner
    When we shutdown the second node and the arbiter, the problem is still here (so with only one node). We need to disable the wsrep configuration in the my.cnf file to have a workaround. I think I missing something..
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.