0

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

Sahil Kakroo
  • 3
  • 3
  • sample output of log file: SELECT * FROM pets; # Time: 160726 13:13:32 # User@Host: root[root] @ localhost [] Id: 1477608 # Query_time: 0.001048 Lock_time: 0.000807 Rows_sent: 1 Rows_examined: 1 SET timestamp=1469553212; SELECT COUNT(*) FROM information_schema.PROCESSLIST; # Time: 160726 13:18:49 # User@Host: root[root] @ localhost [] Id: 1477685 # Query_time: 0.000486 Lock_time: 0.000155 Rows_sent: 1 Rows_examined: 8 SET timestamp=1469553529; – Sahil Kakroo Jul 28 '16 at 19:37

1 Answers1

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.

stewe
  • 68
  • 1
  • 11
  • I don't need the entire log information. I just need the total number of queries for consecutive 10 minute intervals. Do you have any recommendation to handle that? – Sahil Kakroo Jul 28 '16 at 20:20
  • I would appreciate if you mark my answer as correct – stewe Jul 29 '16 at 20:34