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.
-
What version of MySQL are you running ??? – RolandoMySQLDBA Jun 25 '12 at 15:46
-
the mysql version is 5.5 – alfish Jun 25 '12 at 19:06
5 Answers
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.
![](../../users/profiles/2413.webp)
- 29,561
- 5
- 64
- 106
-
-
6pt-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 Jun 25 '12 at 22:10
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.
![](../../users/profiles/69271.webp)
- 16,364
- 3
- 47
- 80
-
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 Jun 25 '12 at 19:05
-
-
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 – alfish Jun 25 '12 at 20:47
-
-
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? – alfish Jun 25 '12 at 21:14
-
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 http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html ) – RolandoMySQLDBA Jun 25 '12 at 22:08
-
-
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. – RolandoMySQLDBA Jun 25 '12 at 23:20
-
Thanks for this @RolandoMySQLDBA. pt-kill just refuses to work as its supposed to, I'd prefer a more straight forward approach like this. – Christian Aug 06 '16 at 09:09
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
![](../../users/profiles/77281.webp)
- 7,657
- 3
- 31
- 71
-
-
@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. – Nils Jun 25 '12 at 21:24
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
![](../../users/profiles/481138.webp)
- 11
- 3
MySQL 5.7 onwards you can use max_execution_time variable to get this done automatically for all "SELECT" read queries .
![](../../users/profiles/597750.webp)
- 3
- 2
![](../../users/profiles/526648.webp)
- 11
- 1