Kill long running MySQL processes

It’s all too common for me to come across a box that is hopelessly overloaded with Apache requests and MySQL queries. Sometimes, it takes some brute force to keep the whole box from going under. When all else fails, I use the following one-liner to kill off all of those queries that have been running for too long.

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

This goes with the assumption that you’re MySQL authentication credentials are in ~/.my.cnf. You aren’t running this as root with no root password set, are you?

Tags: , , ,

This entry was posted on Thursday, February 19th, 2009 at 22:39 and is filed under One-Liners. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply