monitor and kill long running mysql select statements

Below script checks and kills any sql SELECT statements that has been running for more than 60 seconds. The script can be added to a cron task to be run periodically. Make sure to check the log file it creates to keep an eye on the long running sql statements and tune it.



echo "####" `date` "####" >>${LOG}
PIDS=$(mysql -t -e 'show full processlist' | awk -F'|' -v seconds="$SECONDS" '$7 > seconds  && toupper($9) ~ /^ SELECT/ {print $0}' | tee -a $LOG | awk -F'|' '{print $2}')
[ -n "$PIDS" ] && mysqladmin kill `echo $PIDS | tr ' ' ','`

Prior to running it, setup the root login in “~/.my.cnf” file with the login credentials:

Bookmark/Search this post with