My SQL Dump

MySQL musings by a self professed MySQL Geek

Previous Entry Share Next Entry
Using the MySQL event scheduler to rotate the slow query log
swanhart
If you are like me, then you like to record all of your query traffic into the slow query log with long_query_time=0. Since I'm usually using Percona Server, I usually set log_slow_verbosity="microtime,query_plan,innodb,profiling' to get as much data as possible into the slow query log for analysis with pt-query-digest.

Generating a lot of data in the slow query log makes it more difficult to work with as it grows larger. It is important to rotate the log regularly to ensure it does not grow too large to work with. This can be accomplished with a cron job, but it can be done in MySQL directly using the event scheduler.

SET GLOBAL event_scheduler=ON;
DROP EVENT if exists rotate_slow_log_event;
CREATE DEFINER=root@localhost EVENT rotate_slow_log_event
ON SCHEDULE
EVERY 1 HOUR
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Rotate slow query log hourly'
DO
set global slow_query_log_file=CONCAT('slow-', unix_timestamp(), '.log');

This is very close to what I want to do, thanks!

What do you do about all the slow logs in the directory? Do you gzip/mv/archive them off manually, or with a separate process?

The one gotcha I am considering is that, if this is done outside the mysql event system, there's a chance of ending up without a slow log file, which can cause an error.

You are viewing swanhart