Board index Linux Mysql

Moderator: chandranjoy

Monitoring MySQL queries using PROCESSLIST

Postby chandranjoy » Thu Jun 23, 2011 6:56 pm

Monitoring MySQL queries using PROCESSLIST along with system resource utilization

Often times, when you are monitoring the performance of a Drupal site(or any CMS), 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.

# Variables
# Main loop
while true
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

Reference: ... ation.html
Site Admin
Posts: 283
Joined: Fri Oct 23, 2009 11:19 pm

Return to Mysql

Who is online

Users browsing this forum: No registered users and 1 guest