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!