How to selectively kill queries in MySQL?

For as long as it is only about a few of them, it is as simple as looking at the SHOW PROCESSLIST output for thread identifiers to kill. They can be found in the first column called Id. These values can be passed to KILL thread_id command in MySQL. The problems appear with more complex scenarios. What if one needs to terminate all queries running longer than ten seconds? Doing copy&paste; repetitively could take a lot of time with twenty or so candidate threads. This can be done much more efficiently.

INFORMATION_SCHEMA to the rescue!

Earlier today we showed "Anohter way to work with MySQL process list". That post presents how PROCESSLIST table can be used to retrieve any information about connected threads or running queries.

In order to perform a mass-killing of queries or connections, a list of identifiers of the threads matching certain criteria is necessary. For the sake of this example let it be based on query execution time. How to identify all threads running a query for more that 10 seconds? Just read it from INFORMATION_SCHEMA.

SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND = 'Query' AND TIME > 10

Such query returns the list of identifiers that can be used for killing. However usually some threads should not be killed regardless of whether they match the filter or not. These could for example be replication threads, or the current session:

SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND = 'Query' AND TIME > 10 AND ID != CONNECTION_ID() AND USER != 'system user' AND COMMAND != 'Binlog Dump' AND STATE NOT REGEXP '(slave|relay|event)'

The alternative way would be specifying the application users directly instead of filtering out the replication threads. This needs to be very precise as otherwise it may cause some damage if inappropriate connections are terminated. The next step is automating killing based on the output from such query. KILL command takes only one parameter, so there will have to be multiple calls. First, build the command string:

mysql> SELECT CONCAT('KILL ',ID,';')
FROM   INFORMATION_SCHEMA.PROCESSLIST
WHERE  USER = 'webs'
       AND COMMAND = 'Query'
       AND TIME > 10;
...
Query OK, 56 rows affected (0.10 sec)

The result of CONCAT('KILL ',ID,';') called on every row is a complete list of commands needed for execution. Of course, it could be copy-pasted one by one, but this is not something I'd like to present in this post. Less interactive approach can save a lot of time, especially with fifty or a hundred of items on the kill list.

MySQL cannot execute these generated commands directly, so the output has to be written down into some external file.

mysql> SELECT CONCAT('KILL ',ID,';')
FROM   INFORMATION_SCHEMA.PROCESSLIST
WHERE  USER = 'webs'
       AND COMMAND = 'Query'
       AND TIME > 10
INTO   OUTFILE '/tmp/kill_list.txt';

Query OK, 56 rows affected (0.10 sec)

Now, as we have a list of SQL queries already in place, it can be executed:

mysql> \. /tmp/kill_list.txt
Query OK, 0 rows affected (0.00 sec)

Done.

Automate with a script

If the situation where you need to kill many queries occurs more frequently, you may want to learn how to use pt-kill script from Percona Toolkit. It largely automates the work described above, but using it can be much more tricky than it appears. It has a lot of options looking similarly, but which produce quite different actions and I know it can be very confusing at first.

Blog