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.

Solution 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.

Solution 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.

Solution 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

Solution 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

Solution 5

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

View more solutions

Comments

[RolandoMySQLDBA]

What version of MySQL are you running ???

[alfish]

the mysql version is 5.5

[alfish]

Thanks about the suggestions, but really look for a "one-off" script.

[alfish]

Roland, could you please clarify these: Is this command persistent, or need to be run frequently? What should be substituted in the command assuming that the mysql user is 'root' and the database name is myDB? Thanks

[alfish]

What is the time constant in the snippet above?

[RolandoMySQLDBA]

I updated my answer.

[alfish]

Thanks, but after turning your last recipe into a bash script, I get: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

[RolandoMySQLDBA]

Which SQL command generated that error ???

[alfish]

Roland SF is not a sandbox. Better to test before you suggest something as solution. In addition, when all the connections are saturated, how mysql is going to run your procedure, assuming that it is not faulty?

[Nils]

@alfish I am not the author - but I would say this is a regular expression that matches all time values that have at least two digits. So the assumtion here is that 10 is too long.

[RolandoMySQLDBA]

The only way my solution can work, given a saturation of DB Connections, is if you connect with the privilege SUPER and all the other saturated connections are connected without SUPER. ( See dev.mysql.com/doc/refman/5.5/en/too-many-connections.html )

[Aaron Brown]

pt-kill is very well tested and solves your exact problem. It takes like 10 minutes to figure out the command line parameters and get it running. What more do you want?

[Aaron Brown]

Why not use pt-kill?

[RolandoMySQLDBA]

I know pt-kill it's better because it's tried, test and fully automated. It's just laziness on my part to fully use it.

[Chris22]

Thanks for this @RolandoMySQLDBA. pt-kill just refuses to work as its supposed to, I'd prefer a more straight forward approach like this.