If you’re managing large MySQL installations where lot of queries (written by lot of people) are executed, this is going to sound familiar. Sometimes due to some faulty query or to heavy traffic, your MySQL server could be overloaded and queries start to stall there, making your PROCESS LIST grow and grow and grow…
So, here it is a quick script that kills every SELECT (we don’t want to kill other query types) older than a certain amount of second and that can help you when you find yourself in one of these situations.
It’s rather quick’n'dirty, but for it’s small use case, it works. It expects GNU userland
#!/bin/bash
SEC=$1
IFS='|'
if [[ $SEC -lt 1 ]]; then
echo "Usage: $0 SECONDS"
exit 1
fi
mysqladmin proc -v|grep Query|grep -Evi "delete|update|insert|alter table" |while read dummy qid qusr qhost qdb qstat qsec qstat2 query
do
if [ $qsec -gt $SEC ]; then
echo "Killing query $qid..."
mysqladmin kill $qid
fi
done
