I have a 5 node cluster. I am running a backup on one of the nodes. On that node it has Waiting for table backup lock while copying tables. However when I try to do an insert on my write node I am getting lock errors. Looking on the write node i see
---TRANSACTION 856629448, ACTIVE 1251 sec
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
MySQL thread id 12826031, OS thread handle 140640433661696, query id 527779691 hawk.domain.dev 192.168.5.103 data_user wsrep: replicating and certifying write set(-1)
commit
Does backup lock all nodes on the cluster if so is this a change from version (xtradb cluster 5.7). I recently upgraded (xtradb cluster 8.0.x) and did not see this issue in the previous version.
2 Likes
Hi @danarashad , thanks for posting to the forums!
What type of backup are you trying to run? PXB doesn’t do any cluster level locking, but if you’re using mysqldump or others you may inadvertently be locking tables depending on the options you’re passing.
Certainly a commit taking 1,200 seconds is unusual.
1 Like
I am doing a full backup and streaming that to a different server.
When the backup starts it locks the write db. I have taken 3 back ups and it happens every time the backup starts.
Would it be possible to remove one node. Run the backup. Then ass the node back to the cluster.
If so how do I remove a single node then re-add the node back to the cluster
1 Like
Hello @danarashad,
As Michael said, Percona XtraBackup does not do any cluster level locking and the behavior you are experiencing is not normal/expected. Can you please provide the exact command you are executing to take the backup? Have you also verified that every table in your entire cluster is InnoDB?
1 Like
I believe the issue here might have to do with temp tables. If you need a quick fix, you might want to consider changing internal_tmp_disk_storage_engine to revert to the previous value which is to use myisam for temp tables, although be aware of the implications.
1 Like