Cluster: 3-nodes at 1 server(no LAN/WAN lag)
OS: Debian Stretch
Mysql version: 5.7.21-20 (xtradb-cluster)
Average qps: 50q/s
Using this script for testing cluster(php7.0 + pdo plugin):
It creates table and then just use random queries like “INSERT”, “UPDATE”, “DELETE”. :
CREATE TABLE IF NOT EXISTS test ( id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, field_int int(10) NOT NULL, field_float float NOT NULL, field_varchar varchar(100) NOT NULL, field_text longtext NOT NULL, field_datetime datetime NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;
When running one copy of script on singe mysql, percona server, mariadb - all is ok
When running multiple copies of script(parallel): on singe mysql, percona server, mariadb - all is ok
When running one copy of script on pxc and to one single node - all is ok
When running multiple multiple copies of script(parallel) on pxc and more and they connect to different nodes - deadlock on INSERT or UPDATE. (no problem we can use “single-node write mode”, but)
When running multiple multiple copies of script on pxc and more and they connect to one node(!) - deadlock on INSERT or UPDATE.
PXC can’t even hold multiple parallel “write” queries even on single node?
Tested with different options:
wsrep_retry_autocommit - from 0 to 10
wsrep_sync_wait - from 0 to 7 (7 is “helping”, but speed is greatly reduced, so I think it just harder to emulate deadlock)