Log MySQL slow query in real-time

Question

I would like to know if it is possible to log (or detect in another way) a query that is taking too much time to execute as soon as it reached the long_query_time. Because MySQL only logs slow queries when they finished executing.

I know I can see slow queries before they finished by checking the "Server Connections" tab of MySQL Administrator, but I would like to be automatically notified when it happens (it's easier with logs).

Short of setting up some kind of scheduled task to poll the same information you see in the "Server Connections" tab within MySQL admin, realistically you have it with slow queries. If you're expecting something to be slow surely you should be monitoring it to be able to properly optimise/adjust? — Simon at The Access Group, Jun 10 '13 at 21:57

score 1 · Accepted Answer · answered Jun 10 '13 at 22:43

1

If you are willing to poll the database to check if a query is running for longer than x seconds, you can use show processlist, or go directly to the process list table.

select * from INFORMATION_SCHEMA.PROCESSLIST where time > TIME_IN_SECS

This will add more load to the database, so i would not recommend doing it every second. But that would mean that you wouldn't get the information "as soon as it reached the long_query_time".

answered Jun 10 '13 at 22:43

jvilhena

1,131
8
21

Well, my need is not quite as I described, I just need to be aware of a long query that could block one of my thread in my Java application. – Raphael Royer-Rivard Jun 10 '13 at 23:19

Log MySQL slow query in real-time

1 Answers1