Transaction locks on new cluster

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

I moved from xtradbcluter 5.7 to xtradbcluster 8.0.26.
After the switch I have been getting a lot of deadlock errors.

[client]
socket=/var/run/mysqld/mysqld.sock
ssl-ca=/etc/mysql/percona-cert/ca.pem
ssl-cert=/etc/mysql/percona-cert/server-cert.pem
ssl-key=/etc/mysql/percona-cert/server-key.pem

[mysqld]
server-id=5
user=mysql
tmpdir=/db3/tmp
datadir=/db1
log-error=/var/log/mysql/error.log

default_storage_engine=InnoDB
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 8
innodb_lock_wait_timeout = 3600000
wait_timeout = 86400
interactive_timeout = 86400

max_heap_table_size = 96M
sort_buffer_size = 16M
join_buffer_size = 16M
tmp_table_size = 96M
max_sort_length = 8K
max_allowed_packet = 16M
group_concat_max_len = 16M
max_connections = 200
max_connect_errors = 10

max_binlog_size = 128M
log-bin=binlog
log_slave_updates
binlog_expire_logs_seconds=604800
wsrep_provider=/usr/lib/galera4/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.2.61 #node 1
binlog_format=ROW

# Slave thread to use
wsrep_slave_threads=8
#wsrep_causal_reads=ON
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.4.71
wsrep_cluster_name=WebDB-cluster
wsrep_node_name=DB2
pxc_strict_mode=PERMISSIVE
wsrep_sst_donor=DB1
ssl-ca=/etc/mysql/percona-cert/ca.pem
ssl-cert=/etc/mysql/percona-cert/server-cert.pem
ssl-key=/etc/mysql/percona-cert/server-key.pem

[sst]
# options: SERVER,TRANSACTION,STREAMING,CLIENT
#wsrep_debug=
tmpdir=/db3/tmp
sst_idle_timeout=0
sst-idle-timeout=0
encrypt=4
ssl-ca=/etc/mysql/percona-cert/ca.pem
ssl-cert=/etc/mysql/percona-cert/server-cert.pem

These values seem to be set extremely high. However I wasn’t getting there locks on the 5.7 cluster.

innodb_lock_wait_timeout = 3600000
wait_timeout = 86400
interactive_timeout = 86400

From the error log I am also getting

2022-02-16T00:45:02.016076Z 2314661 [Note] [MY-000000] [WSREP] Wsrep_rollback_local
2022-02-16T00:45:02.018868Z 1 [Note] [MY-000000] [WSREP] --------- CONFLICT DETECTED --------
2022-02-16T00:45:02.018909Z 1 [Note] [MY-000000] [WSREP] cluster conflict due to high priority abort for threads:

2022-02-16T00:45:02.018941Z 1 [Note] [MY-000000] [WSREP] Winning thread:
   THD: 1, mode: high priority, state: exec, conflict: executing, seqno: 378164
   SQL: (null)

2022-02-16T00:45:02.018994Z 1 [Note] [MY-000000] [WSREP] Victim thread:
   THD: 2314661, mode: local, state: idle, conflict: aborting, seqno: -1
   SQL: (null)

2022-02-16T00:45:28.000528Z 2272746 [Note] [MY-000000] [WSREP] MDL conflict db=data table=filed ticket=10 solved by abort
2022-02-16T00:46:33.265208Z 2294833 [Note] [MY-000000] [WSREP] MDL conflict db=data table=files ticket=10 solved by abort
2022-02-16T00:49:31.414275Z 2193507 [Note] [MY-000000] [WSREP] MDL conflict db=data table=files ticket=10 solved by abort

data.files is a temporary table in a stored procedure.

1 Like