Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are you running MySQL ? –  RolandoMySQLDBA Jul 26 '12 at 16:48
    
Yes. MySQL 5.5.22 –  DBA_Noob Jul 26 '12 at 17:26

3 Answers 3

up vote 2 down vote accepted

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:

OPTION #2 : Slow Log

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

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:

share|improve this answer
    
This is awesome Rolando, thank you. –  DBA_Noob Jul 26 '12 at 17:23

You can use WayBack Machine feature of MONyog- MySQL Monitor and Advisor enter image description here

It uses inbuilt sniffer(stores queries executed on MySQL server). Chart plotted by WayBack Machine is zoomable by selecting a sub-interval with the mouse. Along with aggregated sniffer and changed variables you can also get point-in-time information by clicking on a point in the graph.

share|improve this answer

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 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.