Often times, when you are monitoring the performance of a Drupal site, you need to see what queries are running, and how long they take.
There are several tools to monitor MySQL queries, including mtop and mytop, as well as running SHOW PROCESSLIST from the command line.
However, you sometimes need to filter information from PROCESSLIST's output, such as sleeping threads and such. You also want to see other aspects of the system, for example memory and swapping as well as CPU utilization.
The following simple shell script does that: it 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
Do you have other tricks and hints that would be useful for others? Post them as a comment below.
Most Comments