How to manage a growing MySQL slow_log table?

Question

I see MySQL supports storing slow_log as a table (internally using mysql/slow_log.CSV file), and this appears convenient, but is it a feature I should use in production? I found online articles on the importance of temporarily disabling slow query logging while rotating the file log, but what's the best practice for managing slow_log.CSV? eg. copy and truncate table or rotate the slow_log.CSV just like db-slow.log?

Answer 1

First of all, I discourage you from using the slow log with TABLE output, in production. The reason is that writes are locking, which limits the concurrency of your workload.

Moreover, CSV stored engine does not support indexes. Any non-trivial query will be very slow, so I don't see any advantages in doing this. Of course you could use MyISAM and add indexes, but then writes will become more expensive.

That said, the correct way to "rotate" the table is copying it and than truncate it. You will probably lose some queries every time: the ones ran after the copy but before the TRUNCATE TABLE statement.

Answer 2

The Slow Query Log can be handled for CSV and MyISAM

CSV (RDS)

For those who used MySQL RDS and enable the slow log, Amazon will use CSV only. The RDS environment can auto-rotate at a specific time every night. If you using MySQL RDS and wish to use slow logs, let RDS do its thing.

CSV (Non-RDS)

If you wish to manage you own slow log rotations (CSV or MyISAM)

Run one of these at 11:59 PM every night

Log Rotate One Day

SET GLOBAL slow_query_log = 'OFF';
SET sql_log_bin = 0;
USE mysql
DROP TABLE IF EXISTS slow_log_new;
DROP TABLE IF EXISTS slow_log_old;
CREATE TABLE slow_log_old LIKE slow_log;
ALTER TABLE slow_log RENAME slow_log_old;
ALTER TABLE slow_log_new RENAME slow_log;
SET GLOBAL slow_query_log = 'ON';
SET sql_log_bin = 1;

Log Rotate With Date in Log Name

SET GLOBAL slow_query_log = 'OFF';
SET sql_log_bin = 0;
USE mysql
SET @ymd = DATE_FORMAT(NOW(),'%Y%m%d');
DROP TABLE IF EXISTS slow_log_old;
ALTER TABLE slow_log RENAME slow_log_old;
CREATE TABLE slow_log LIKE slow_log_old;
SET @sql = CONCAT('ALTER TABLE slow_log_old RENAME slow_log_',@ymd);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;        
SET GLOBAL slow_query_log = 'ON';
SET sql_log_bin = 1;

MyISAM

See my old posts for MyISAM-based slow logs

• Mar 30, 2012 : MySQL slow log configuration
• Jan 08, 2012 : How to enable MySQL general log?

Final Thoughts

In a high-read, low write environment, doing writes to the slow would be faster to a CSV file that to MyISAM any other engine. Amazon uses it without hesitation. As far as doing queries against a CSV, it's just a full table scan every time