mysql slow query log

Question

I am trying to write a script to parse the MySQL slow query log. I have seen 1 or 2 parser. does anyone know how to extract the information from that log? I mean does anyone know the structure of the file so I can work with that and if anyone know a good parser for this log file?

thanks

Answer 1

This might help you: http://dev.mysql.com/doc/refman/5.1/en/mysqldumpslow.html

Answer 2

Hey, I got suggestion for this one going forward.

Convert the slow query log to a MyISAM table. This is possible in MySQL 5.1/5.5

Here is how you do it:

Step 1) Add this line to /etc/my.cnf

log-output=TABLE

Step 2) service mysql restart

This will create the table mysql.slow_log but as storage engine CSV.

SHOW CREATE TABLE mysql.slow_log\G

3) Convert mysql.slow_log to MyISAM and index the start_time column

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
SET GLOBAL slow_query_log = @old_log_state;

Optionally, you could also add FULLTEXT index against the sql_text column, but I have never tried. IMHO, It might slow things down. Steps 1-3 should suffice.

Give it a Try !!!