2008-06-12

How To Perform a Connection Massacre

Occasionally, you find yourself in the need to kill problematic connections to the database.
Usually if it's only one or two connections, you can use the combination of "SHOW PROCESSLIST" command to identify the problematic connection ID, and run a "KILL ID" command.

What do you do if you need to kill 10 connections? Or 56? I wouldn't want to type in all those kill commands, it's just dirty work. What we need is a more neat manner to perform those kills. Mass kill, if you wish.

Alternative way: use the INFORMATION_SCHEMA's PROCESSLIST table, to construct the kill statements semi-automatically.

SELECT CONCAT('kill ',id,';') AS kill_list
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE command='Sleep';

This select will return something like this (when using the command line client):

+-----------+
| kill_list |
+-----------+
| kill 28; |
| kill 1; |
+-----------+
2 rows in set (0.04 sec)

You can also use GROUP_CONCAT() to get the kill commands in the same line, which may be more useful when you can't copy&paste easily:

SELECT GROUP_CONCAT('kill ',id SEPARATOR '; ') AS kill_list
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE command='Sleep';

Returns:

+------------------+
| kill_list |
+------------------+
| kill 28;,kill 1; |
+------------------+
1 row in set (0.03 sec)

Note, that you can use any filter in the WHERE clause, such as "WHERE db IN('dbname1','dbname2')", "WHERE user = 'appuser'" or "WHERE time>600". If you got any more clever uses, feel free to post them in the comments.

Now, all needed is to copy&paste those kill commands into the mysql command console (whichever you're using: GUI, command line, etc) and you're done.

Since most programs are kind enough to format the output in some way that prevents convenient copying/pasting, here's another tip. The mysql command line client can be asked to strip some of the output by using the "-s" optional parameter, which stands for "silent". You can use it once, and remove the ASCII art, timing and row count, and if you use it again (-s -s), you only get the actual rows.

An example doing just this (copy&paste friendly!):

mysql -s -s -e "SELECT CONCAT('kill ',id,';') AS kill_list FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command='Sleep';"

Gets you this (with different numbers of course):

kill 28;
kill 1;

Another way to do this, is to use SQLyog or any other GUI for MySQL, which can output the results in text. Most of them have this as a configurable option (Ctrl-L keyboard shortcut in SQLyog).

Hope this is will save someone some precious time.

Update:
I didn't include a suggestion on doing this automatically, since I personally prefer to run kill commands manually most of the time. Plus, some of the comments gave me an idea. Why not just pipe the stripped kill_list into mysql?
mysql -s -s -e "SELECT GROUP_CONCAT('kill ',id SEPARATOR '; ') AS kill_list FROM INFORMATION_SCHEMA.PROCESSLIST WHERE command='Sleep';" | mysql

9 comments:

  1. Is that exclusive to 5.1? I'm not seeing it in 5.0.51a.

    mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
    ERROR 1109 (42S02): Unknown table 'PROCESSLIST' in information_schema

    ReplyDelete
  2. For older systems, you can always use the commandline, combining mysql -e "show processlist" with grep for filtering and cut for getting the processid. I've done this often, with a shell script like:

    for PID in `mysql -u user -ppass -e "show processlist" | grep Sleep | cut -f1`
    do
    mysql -u user -ppass -e "kill $PID"
    done

    ReplyDelete
  3. (that example is for killing queries in the Sleep mode)

    ReplyDelete
  4. The sleeping connections is just a bad example. It means either persistent connections have been used where they shouldn't have, or the app is behaving silly. Easy MySQL-side workaround for this is
    SET GLOBAL wait_timeout=10;

    No need to mess with scripts of any kind, anyway ;-)

    ReplyDelete
  5. Anonymous: Yes, it actually is a new addition in 5.1. Look at this manual page, in both 5.0 and 5.1 versions:
    http://dev.mysql.com/doc/refman/5.1/en/information-schema.html
    http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
    There are a lot more tables in the new soon-to-be-GA-nearly-almost-no-critical-bugs version.

    Sheeri: Well, nothing beats a good linux command line :) Unfortunately, If you're working with windows as your client side, most of these tools are not available by default.

    Arjen: Yes, I agree. I've added the "Sleep" condition as just an example to emphasize the fact this is a regular table which can be queried against - maybe not the best example at that.

    ReplyDelete
  6. It's not pretty and it's only for the 'nix-minded, but I've found this useful when caught in the precarious position of needing to kill a number of uncooperative selects (in the case below batches of longest running 100, running >=30s)...

    mysql -B -e "SHOW FULL PROCESSLIST;"| tee /tmp/mysqlprocesslist`date "+%Y-%m-%dT%H:%M:%S"` | grep -Ewv "Sleep|INSERT|UPDATE|REPLACE|Binlog Dump|NULL|Command" | grep SELECT | sort -n -r -k 6 | head -n 100 | awk -F \\t '{print $1,$6}' | grep -v [1-2][0-9]$ | grep -v " [0-9]"$ | xargs mysqladmin kill

    ReplyDelete
  7. was not only ugly, but wrong... corrected:

    mysql -B -e "SHOW FULL PROCESSLIST;"| tee /tmp/mysqlprocesslist`date "+%Y-%m-%dT%H:%M:%S"` | grep -Ewv "Sleep|INSERT|UPDATE|REPLACE|Binlog Dump|NULL|Command" | grep SELECT | sort -n -r -k 6 | head -n 100 | awk -F \\t '{print $1,$6}' | grep -v " [1-2][0-9]"$ | grep -v " [0-9]"$ | awk '{print $1}' | xargs | sed 's/ /,/g' | xargs mysqladmin kill

    ReplyDelete
  8. There is a snippet on the Forge site that uses a Stored Proc & Cursor to kill long running queries (contributed by Matt Montegomery). To make this a fully automated query killer all you need to do is setup an Event :)

    http://forge.mysql.com/tools/tool.php?id=106

    ReplyDelete
  9. Thank you very much for this command line :

    for PID in `mysql -u user -ppass -e "show processlist" | grep Sleep | cut -f1`
    do
    mysql -u user -ppass -e "kill $PID"
    done

    Before I did all manually, thank you !!

    Fatiha

    ReplyDelete