Link to home
Start Free TrialLog in
Avatar of Jim Youmans
Jim YoumansFlag for United States of America

asked on

Slow Query Log Parser for MySQL 5.7

I am trying to find a decent program to parse the mysqld_slow_queries.log but am having a hard time.  I can't install anything on the mysql server but I can copy the log file to my windows desktop.  I am looking at a parser written in Ruby called SlowQueryLogParser.rb which looks like it would be exactly what I am looking for but I can't seem to figure out what it needs to run.  I have installed Ruby and when I run it like so

SlowQueryLogParser.rb mysqld_slow_queries.log

I get an invalid date error.

Can anyone point me towards a program that will parse the slow query log and give me some output like this?

Here is an example of what the parsed log looks like


```
1 Queries
Taking 4 seconds to complete
Locking for 0 seconds
Average time: 4, Median time 4
Average lock: 0, Median lock 0


DELETE FROM blah WHERE blah1 >= XXX AND blah2<= XXX;
################################################################################


22 Queries
Taking 3 3 seconds to complete
Locking for 0 0 seconds
Average time: 3, Median time 3
Average lock: 0, Median lock 0


select * from table1 WHERE table1.something = table.something and table1.x = XXX;
################################################################################

Open in new window


This parser was inspired by the perl mysql_slow_log_parser written by Nathanial Hendler.
Thank you.
Jim
Avatar of HainKurt
HainKurt
Flag of Canada image

what is exact error message?
what is the code for SlowQueryLogParser.rb?
on which line do you get this error?

+ post a sample from your log file, not a demo, that part, including some datetimes that script is complaining...

ASKER CERTIFIED SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You should use pt-query-digest.
@Jim,

Regardless of what you chose as a solution to this question, did you try pt-query-digest? It is by far the most sophisticated cli tool I have used to analyze slow queries and it is way too simple.
wget percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest slow-query-log-file > output.digest

Open in new window

You get much more than what you are asking here. It has to be a solution unless you really need exact that output.