Deadlock found when trying to get lock with Magento

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!

I wonder what the status of the cluster is, can you provide cluster status?

mysql -e “show status like ‘wsrep%’” | egrep “cluster|local”

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

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…