Killing a query taking too long in cluster

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hello,

I came around a scenario where a delete query was taking too long to execute in my 3 node xtradb-5.7 cluster. I just killed the query but was not sure if it is the right way to kill the delete query in cluster. Can you please help to find out the standard steps that should be followed in this scenario ?

Thanks.

1 Like
2

Hello @shahidbashir7861 ,
There is only 1 way to kill a query in MySQL/PXC, using KILL. You need to check the other nodes and make sure the query did not complete on the other nodes due to race condition. Also, check your mysql error logs for any possible information as to why the query was taking a long time. Did you run SHOW ENGINE INNODB STATUS to look for locking issues?

1 Like
3

Hey Matthew,

I did not check for the “show engine innodb status” but I could see the query was using one CPU core completely and there was not high activity on disk state. The query was in ‘sending data’ state. On the other machines the query was not stuck like that and did not show up in “show full processlist”. I have killed the query now and i checked the table on cluster machines and it has the same rows on all the machines. Hope you could tell me something from this info what actually happened.

Thanks.

1 Like
4

Unfortunately I don’t have the gift of magic deduction, so without seeing show processlist, engine innodb status, and the query with explain plan, I can’t tell you what happened. Since this is a PXC, I would also need to see show processlist from all nodes.

1 Like