How to extract the number (count) of queries logged in the mysql slow query log for a 10 minute interval

Question

As per my research I thought of using the mysqldumpslow utility to parse the log and extract the results, but not able to figure out how to use it. I want to get the count of number of queries logged in the slow query log for an interval of 10 minutes, so that the values can be compared for analysis.

Thanks

Answer 1

You could use logrotate to create a new slow.log every 10 minutes and analyze them one after another. Implying you are using Linux. Be aware that your example shows that your mysql instance is configured to "log-queries-not-using-indexes" hence you will also get those SELECT's that dont use an index in your log file too.

Update :

Since i still dont know what OS you are using, a more general aproach to your problem would be redirecting the slow log into mysql itself following the mysql docs and get all records from the slow log table like :

SELECT COUNT(*) FROM slow_log;

Which gives you the total amount of Querys logged. Follwed by a :

TRUNCATE TABLE slow_log;

Having a script in place doing this every 10 minutes would output the desired information.