Kill every MySQL SELECT older than X seconds

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

Advertisements

One thought on “Kill every MySQL SELECT older than X seconds

  1. Script to Kill the queries running more than 10 mins ( ~ 700 secs ) :-
    ————————————————————————————

    #!/bin/bash
    > /home/srinivas/MySQL_Long_Query_Kill.log
    RECIPIENT=srin@udbco.com,santhu@udbco.com
    RECIPIENTCC=saj@udbco.com
    RECIPIENTBCC=srinivas@gmail.com,santhoshudb@gmail.com
    SEC=700
    IFS=’|’
    if [[ $SEC -lt 700 ]]; then
    echo “Usage: All the MySQL queries are less than $SEC seconds, So no MySQL process to Kill ….”
    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 “Hi Team,” > /home/srinivas/MySQL_Long_Query_Kill.log
    echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
    echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
    echo “Killing the MySQL query with PID :$qid which has been running for $qsec seconds .” >> /home/srinivas/MySQL_Long_Query_Kill.log
    mysqladmin kill $qid
    echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
    echo “Below is the Query that has been killed : ” >> /home/srinivas/MySQL_Long_Query_Kill.logs
    echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
    echo ” $query ” >> /home/srinivas/MySQL_Long_Query_Kill.log
    echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
    echo “Query killed Time : `date +”%Y-%m-%d %H:%M:%S”`” >> /home/srinivas/MySQL_Long_Query_Kill.log
    echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
    echo ” ” >> /home/srinivas/MySQL_Long_Query_Kill.log
    echo “Regards, ” >> /home/srinivas/MySQL_Long_Query_Kill.log
    echo “IT Team, ” >> /home/srinivas/MySQL_Long_Query_Kill.log
    mail -s “HIGH: MySQL Long running Queries are Killed as on `date +”%Y-%d-%m %H:%M:%S”`.” $RECIPIENT -c $RECIPIENTCC -b $RECIPIENTBCC — -f ITteam@udbco.com < /home/srinivas/MySQL_Long_Query_Kill.log

    fi
    done

    Best,
    Srinivas Mutyala

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s