We need some help with xtradb cluster, proxysql and sysbench

Hello,

We are preparing to migrate from Mysql 5.7 to Percona xtradb cluster. At the moment we have mounted a test environment and we are testing to certify that all the configuration is well done.

We have 3 nodes: db1, db2 and db3, in 3 different AZs within the same AWS region.
The configuration of the cluster is as follows:

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
bind-address = 0.0.0.0
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /vol/mysql
tmpdir = /vol/tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

max_connections = 100
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP

query_cache_limit = 1M
query_cache_size = 16M

log_error = /vol/mysql/error.log
slow_query_log_file = /vol/mysql/mysql-slow.log
slow_query_log = 1
long_query_time = 15
log_queries_not_using_indexes
expire_logs_days = 10
max_binlog_size = 100M

innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 3G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 12G
innodb_buffer_pool_instances = 12
thread_pool_size = 16

Cluster config

wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_name=db-cluster
wsrep_cluster_address=gcomm://10.0.3.194,10.0.1.59,10.0.1.74
wsrep_node_name=db1
wsrep_node_address=db1
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd
wsrep_notify_cmd=/usr/share/galeranotify/galeranotify.py
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

We have configured ProxySQL using proxysql-admin with the following config file:

proxysql admin interface credentials.

export PROXYSQL_DATADIR=‘/var/lib/proxysql’
export PROXYSQL_USERNAME=‘admin’
export PROXYSQL_PASSWORD=‘admin’
export PROXYSQL_HOSTNAME=‘localhost’
export PROXYSQL_PORT=‘6032’

PXC admin credentials for connecting to pxc-cluster-node.

export CLUSTER_USERNAME=‘username’
export CLUSTER_PASSWORD=‘passw0rd’
export CLUSTER_HOSTNAME=‘db3’
export CLUSTER_PORT=‘3306’

proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.

export MONITOR_USERNAME=‘proxysqlmon’
export MONITOR_PASSWORD=‘monit0r’

Application user to connect to pxc-node through proxysql

export CLUSTER_APP_USERNAME=‘username’
export CLUSTER_APP_PASSWORD=‘passw0rd’

ProxySQL read/write hostgroup

export WRITE_HOSTGROUP_ID=‘20’
export READ_HOSTGROUP_ID=‘21’

ProxySQL read/write configuration mode.

export MODE=“loadbal”

Writer-is-reader configuration

export WRITER_IS_READER=“ondemand”

If we try to launch a test with sysbench we have the following errors:

sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=127.0.0.1 --mysql-user=username --mysql-password=passw0rd --mysql-port=6033 --mysql-db=percona --threads=16 --events=0 --time=500 --tables=10 --report-interval=5 --mysql-ignore-errors run
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 16
Report intermediate results every 5 second(s)
Initializing random number generator from current time

Initializing worker threads…

Threads started!

FATAL: mysql_stmt_execute() returned error 1213 (Deadlock found when trying to get lock; try restarting transaction) for query ‘COMMIT’
FATAL: thread_run' function failed: /usr/share/sysbench/oltp_common.lua:409: SQL error, errno = 1213, state = '40001': Deadlock found when trying to get lock; try restarting transaction FATAL: mysql_stmt_execute() returned error 1213 (Deadlock found when trying to get lock; try restarting transaction) for query 'COMMIT' FATAL: thread_run’ function failed: /usr/share/sysbench/oltp_common.lua:409: SQL error, errno = 1213, state = ‘40001’: Deadlock found when trying to get lock; try restarting transaction
Error in my_thread_global_end(): 2 threads didn’t exit

On the error log of one of the cluster nodes this line appears:
018-11-21T15:50:24.431264Z 5455 [Note] Aborted connection 5455 to db: ‘percona’ user: ‘username’ host: xxxxxxxx.compute.amazonaws.com’ (Got an error reading communication packets)

What are we doing wrong?

If we configure ProxySQL in “singlewrite” mode, everything seems to work correctly.

Deadlocks are expected if you run on multiple nodes.
You can ignore deadlocks - try to remove --mysql-ignore-errors from sysbench command line, or use --mysql-ignore-errors=1213

I’ve tried both options with the same results.

I’ve managed to complete the tests using --mysql-ignore-errors=all

Thanks!