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

Answer 4

I would not try bash solutions if you like uptime!

If you have access to the code you can actually set the maximum execution time on SELECT statements using the method outlined here :

SELECT 
MAX_EXECUTION_TIME = 1000 --in milliseconds
* 
FROM table;

Otherwise, on the server:

https://stackoverflow.com/questions/415905/how-to-set-a-maximum-execution-time-for-a-mysql-query

Install pt-kill:

$ wget percona.com/get/pt-kill

Take an snapshot of your processlist:

$ mysql -u root -B -pmyreallyimportantpassword -e "show processlist;" > processlist.txt

Test pt-kill on the snapshot:

$ ./pt-kill --test-matching processlist.txt --busy-time 45 --kill-busy-commands 'Execute' --victims all --print
# 2019-02-25T17:34:37 KILL 45710302 (Execute 374 sec) SELECT\n\tCOUNT(DISTINCT(LP.sessionId))\nFROM lp_traffic LP\nINNER JOIN orders O ON O.orderId = LP.order
# 2019-02-25T17:34:37 KILL 45713515 (Execute 67 sec) SELECT \n\tCOUNT(DISTINCT(CASE WHEN T.response = 'SUCCESS' AND T.isVoid = 0 AND (T.txnType IN

Make sure the match rules suit your case. These above will kill all Execute statements over 45 seconds. Once you are sure then modify and run this command to execute the statement at a 10 secoond interval:

$ ./pt-kill -u root -p myreallyimportantpassword --busy-time 45 --kill-busy-commands 'Execute' --victims all --interval 10 --kill

Answer 5

MySQL 5.7 onwards you can use max_execution_time variable to get this done automatically for all "SELECT" read queries .