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?

3 Answers
3

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

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.

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.