mysql.slow_log table related query

MySQL & MariaDB Other MySQL® Questions
1

We have had slow query threshold set to 5 seconds and over years this table has grown up to be very large and I see slow_log.CSV size to be 120GB in mysql data directory.

I planning to run a truncate slow_log to clear this table but concern is this table is huge would there be any issue doing so ?

is it safe to do so?

has any one tried this before ?

any other alternate option ?

I am using MySQL 5.1.52

Thanks
Ishaque

2

Hi,

I’m bit confused. What do you mean by this sentence. “this table has grown up to be very large and I see slow_log.CSV size to be 120GB in mysql data directory”
Are you taking about table or file? If table, are you keeping slow log information in table? (Table with CSV storage engine)? Please give some specific information.

3

Hi Ishaque,

Yes, you can truncate mysql.slow_log table, however I never tried this on that big one (may be slow).
You may also rotate the table and analyse/drop/archive later like this:

CREATE TABLE mysql.slow_log2 LIKE mysql.slow_log;RENAME TABLE mysql.slow_log TO mysql.slow_log_backup, mysql.slow_log2 TO mysql.slow_log;

Also if it’s so big with 5s threshold it means you have a lot of bad queries :slight_smile: