How to parse a mysql slow query log into something useful?

Question

I have an extensive slow query log which was running for a few weeks. I would like to parse it, which would put the highest occurring queries at the top (with number of executions and average times of execution), and it goes from there in descending order.

What tool/command can I use to accomplish that?

Answer 1

You could use a spreadsheet if you can parse the data into individual fields, then sum by query and sort by count of executions and average. Y'know... Normal data analysis stuff.

If you need to, write a regex based application to split the log up into CSV. While you're at it, you could even write a concordance engine for the log names, check averages, and make the tool you're after.

Of course, someone is likely to come in and say "Duh, use 'analysisMySQLProDemon40005K+!'" after I post this.

Answer 2

Check out Maatkit:

mk-query-digest - Parses logs and more. Analyze, transform, filter, review and report on queries.