monitor and kill long running mysql select statements
Fri, 12/21/2012 - 17:29 — sandipBelow 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.
#!/bin/bash
LOG=/tmp/kill_mysql.log
SECONDS=60
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:
[client]
user=root
password=xxxxxxxx- sandip's blog
- Login or register to post comments
- Read more