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.