What is running right now?

Question

I know I can use "show processlist" to see what is running when I'm in the office. How can I see what is running when I'm not there.

Something is running on my server at 3am every morning that is generating a IO Rate Alert and a CPU Usage Alert, but I don't know what it is. Is there a logging system that I should be using or a script I can write that will report what is currently running against the database at that time.

The alerts come in within the same rough time-frame every night, about 0300 - 0330.

I'm not even sure if this is a DBA question or more a Sys Admin question?

I appreciate any help.

Answer 1

You have three(3) options to go about looking at what is running in MySQL

OPTION #1 : General Log

You could simply activate the general log and look for the timestamp between 3:00-3:30 AM. You could either have the general log as a text file or a MyISAM table. Here are my posts on using the general log:

• How to enable MySQL general log? (Jan 08, 2012)
• How to keep MySQL general_log after I restart my computer? (Dec 10, 2011)
• How do I output MySQL logs to syslog? (Jun 30, 2011)

OPTION #2 : Slow Log

Same setup procedure as the General Log. Here are my past posts on this:

• MySQL slow log configuration (Mar 30, 2012)
• Create a custom log table in MySQL (Nov 18, 2011)

OPTION #3 : Query Digest

The problem with Options 1 and 2 is that they record only completed queries. If you are trying to catch long running queries in the act of running long between 3:00-3:30AM, even before they get recorded in the slow log or general log, you have to go with setting up a crontab.

I usually use MAATKIT's mk-query-digest (most prefer the most current pt-query-digest). Here are my posts on how to script a crontabed Query Digest:

• Investigate peak in MySQL throughput (Apr 24, 2012)
• MySQL general query log performance effects (Nov 24, 2011)

Answer 2

You could home brew a bash script to fire off and log the info. Something like:

#/bin/bash
USER=monuser
PASS=monpassword
LOGFILE=/path/to/info.log

for i in {1..25}; do 
   mysql -u $USER -p$PASS -h localhost -e "SHOW FULL PROCESSLIST\G" >> $LOGFILE ;
   sleep 1 ; 
done

Then just set a crontab to fire off the script say, every 3 min when the hour is 2 or 3am.

*/3 2,3 * * * /path/to/script.sh 2>&1