How to automatically kill slow MySQL queries after N seconds?

Question

I am looking for a well tested bash script (or alternative solution) to do so, in order to avoid max_connection to be exhausted. I know that it is fighting the symptoms, but really need such script as a short term solution.

Answer 1

check out pt-kill command from the percona toolkit.

and.. do start monitoring your system - munin, cacti with better cacti templates for mysql, anything so you get some idea what's going on. logging mysql slow queries will be a good idea too.

Answer 2

If you have MySQL 5.1 where the processlist is in the INFORMATION_SCHEMA, you can do this to generate the KILL QUERY commands in bulk from within the mysql client for query running longer than 20 minutes (1200 seconds):

SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery
FROM information_schema.processlist WHERE user<>'system user'
AND time >= 1200\G

You can do WHERE clauses against the INFO field to look for a specific query, the TIME field against long running queries, or the DB field against a specific database.

If you are root@localhost, you should have full privileges to run this as follows

SECONDS_TOO_LONG=1200
KILLPROC_SQLSTMT="SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"
mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" | mysql -uroot -ppassword

You can crontab this as follows:

SECONDS_TOO_LONG=1200
QUERIES_RUNNING_TOO_LONG=`mysql -uroot -ppassword -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"`
if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"
    mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" | mysql -uroot -ppassword
fi

Here is another variation:

SECONDS_TOO_LONG=1200
QUERIES_RUNNING_TOO_LONG=`mysql -uroot -ppassword -ANe"SELECT COUNT(1) FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"`
if [ ${QUERIES_RUNNING_TOO_LONG} -gt 0 ]
then
    KILLPROC_SQLSTMT="SELECT CONCAT('KILL QUERY ',id,';') KillQuery FROM information_schema.processlist WHERE user<>'system user' AND time >= ${SECONDS_TOO_LONG}"
    mysql -uroot -ppassword -ANe"${KILLPROC_SQLSTMT}" > /tmp/kill_log_queries.sql
    mysql -uroot -ppassword < /tmp/kill_log_queries.sql
fi

BTW You do not have specify a myDB since I explicit read from information_schema.processlist as a fully qualified tablename.

Here is a demonstration of what you should see. For this example, I will echo the KILL command of all processes whose time > 20000 seconds:

[root@***** ~]# mysql `lwdba_connect` -ANe"SELECT GROUP_CONCAT('KILL ',id,'; ' SEPARATOR ' ') FROM information_schema.processlist WHERE time > 25000 AND user<>'system user';"
+----------------------------------------------------+
| KILL 180186;  KILL 180141;  KILL 176419;  KILL 3;  |
+----------------------------------------------------+
[root@***** ~]#

I have been doing this technique for the last 5 years. In fact, I submitted this answer to the DBA StackExchange last year and it got accepted.

Answer 3

I found the following code-snipped here:

Update 2013-01-14: There was an anonymous hint that this is potentially dangerous and can kill replication processes as well. So use at your own risk:

mysql -e 'show processlist\G' |\
egrep -b5 'Time: [0-9]{2,}' |\
grep 'Id:' |\
cut -d':' -f2 |\
sed 's/^ //' |\
while read id
do
  mysql -e "kill $id;"
done