Percona XtraDB Cluster - Lock wait timeout exceeded

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi, i’am doing some tests on my VMs - i installed Percona XtraDB Cluster (3 nodes). Configured it via official manual, cretead database “Percona” and table “mytable”.

Everything is synchronized:


wsrep_local_state | 4 |
wsrep_local_state_comment | Synced

There is a wsrep config:

[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://192.168.77.10,192.168.77.20,192.168.77.30

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# Slave thread to use
wsrep_slave_threads= 8

wsrep_log_conflicts

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node IP address
wsrep_node_address=192.168.77.10
# Cluster name
wsrep_cluster_name=pxc-cluster

#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-1

#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING

# SST method
wsrep_sst_method=xtrabackup-v2

#Authentication for SST method
wsrep_sst_auth=sstuser:passw0rd

wsrep_sync_wait=1
innodb_lock_wait_timeout=1000

Now, what’s the problem? I’ve created a procedure which inserts a 1Milion records into “my table”. I’am using DBeaver Client 6.2.0.

When i call my procedure with AUTO COMMIT OFF it runs about 35 seconds. Then i explicitly run commit. Commit is running about 5 seconds and then on all nodes in the same time i run a command “select count(*) from mytable;” and on 2 nodes it ends with error. On node 1 it shows right number of rows.

[URL]https://i.imgur.com/bAVa8o1.png[/URL]

When i run the same commands after 30 seconds, it shows on all nodes same values. Why transactions fail on node 2 and node 3 when parameter innodb_lock_wait_timeout is set really high?

On Galera Cluster this is working well, without problems.

Sorry for my bad english, i hope you understand me. Thanks

2

Hi when you say it ends with error, do you have the error log please? And to save me asking later, if we could have the my.cnf for each node?
And to confirm, this is a brand new install on these VMs?
Thanks.

3

Open screen, there is a error message [url]https://i.imgur.com/bAVa8o1.png[/url]

This is mysqld config, it’s the same on all nodes. Yea all nodes are fresh installed (Centos 7.6)

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

bAVa8o1.png
bAVa8o1.png1559×984 38.8 KB

4

OK, thanks. Unfortunately those errors don’t really tell much about what’s going on under the hood.

When the issue happens, what does this command show?

show status like ‘wsrep%’;

You might find that pt-stalk from Percona toolkit (free, open source software) will help you track down what’s happening at a system level [URL]https://www.percona.com/doc/percona-toolkit/LATEST/pt-stalk.html[/URL] and there are a few other blog posts and forum posts that suggest what might be causing the issue if it’s in the environment.

In the meanwhile I will also check if the tech team have anything to add given the config etc you have sent through in case there is something there that is easily identifiable as causing the problem, especially if you are not experiencing the same issue with galera cluster.