Monitoring MySQL queries using PROCESSLIST

Submitted by Janak on Mon, 06/01/2009 - 14:54

Trying to debug the sluggishness of this website, I came across this is really handy tip from Khalid @ 2bits.com:

The following simple shell script displays the process list, filtering out the "noise", as well as display the relevant vmstat line. The output is all the real queries, their type, and the time it took each (in seconds), as well as the system resources below the queries.

#!/bin/sh
# Variables
DBUSER=someone
DBPASS=something
SECONDS=15
# Main loop
while true
do
mysqladmin -u$DBUSER -p$DBPASS processlist |
egrep -vw 'Sleep|processlist|Binlog Dump' |
awk -F'|' '{print $6, $7, $8, $9}'
# First line of vmstat is historical, so take the second
vmstat 1 2 | tail -1
# Sleep for a while
sleep $SECONDS
done