List:General Discussion« Previous MessageNext Message »
From:Tim Gustafson Date:March 10 2011 6:32pm
Subject:Pruning the slow_log Table
View as plain text  
Hi,

I've enabled slow query logging to the mysql.slow_log table, like this:

log_output = TABLE
general_log = 0
slow_query_log = 1
long_query_time = 5

I'm getting queries in the mysql.slow_log table now, which is awesome.  But my question is
this: how does one "prune" that log file, without doing a "truncate table slow_log"?  I
want to just delete records that were added more than 30 days ago only.  Is that
possible?  I see that the table is a CSV file by default, and I've switched it to a
MyISAM table but that did not help; I still get the following error when tying to prune
the table:

mysql> delete from slow_log where start_time < date_sub(now(), interval 30 day);
ERROR 1556 (HY000): You can't use locks with log tables.

Is it possible to print this table this way?

Also, a suggestion: it would be more helpful (I think) if the user_host column was broken
up into two separate columns, "user" and "host", and if those values would match exactly
the data in the mysql.user table so that the two tables are easier to correlate.

Thanks!

Tim Gustafson
Baskin School of Engineering
UC Santa Cruz
tjg@stripped
831-459-5354


Thread
Pruning the slow_log TableTim Gustafson10 Mar