2

How to find the MySQL query which take too long to execute (more than 5 sec)?

I tried using PROCESSLIST but its showing minimum info. I need the full query currently running in the server so i can try optimizing the queries to decrease my server load.

Is there any terminal command available to find the currently processing query?

luchaninov
  • 6,792
  • 6
  • 60
  • 75
yoganand yoganand
  • 48
  • 4
  • Did you try [`EXPLAIN`](http://dev.mysql.com/doc/refman/5.0/en/explain.html)? – inhan Dec 07 '12 at 09:40
  • http://stackoverflow.com/questions/11606972/how-can-i-enable-slow-query-log-on-my-server?rq=1 – John Dvorak Dec 07 '12 at 09:45
  • thanks Jan Dvorak. i tried it . but it shows all queries which executed through mysql . but i need to isolate the queries which are running more than a sec – yoganand yoganand Dec 07 '12 at 10:03
  • thanks inhan . am expecting like a terminal command which will show the current processing queries which is taking more than a sec . explain will not be useful in my case – yoganand yoganand Dec 07 '12 at 10:13
  • if you log slow queries as suggested, you can set minimum time of slow query with parameter long_query_time. If you set value to 5, then it should log all queries which take more than 5 seconds to execute – TCFDS Dec 07 '12 at 10:21
  • Possible duplicate of [How to see full query from SHOW PROCESSLIST](http://stackoverflow.com/questions/3638689/how-to-see-full-query-from-show-processlist) – luchaninov May 06 '16 at 20:25

1 Answers1

2

If you are using MySQL 5.1+, the slow query log can be used. Here and here is documentation. The configuration and running options are related to MySQL version. But I believe you can achieve your goals following the instructions related to your environment.

Paul Schreiber
  • 12,531
  • 4
  • 41
  • 63
Jose Areas
  • 719
  • 3
  • 11
  • This seems to be the best way to go as you can specify your requirements. I personally use [Monyog](https://www.webyog.com/product/monyog) due to its ease of use and amount of data it provides. If you are not looking to buy a tool. You can download a 14 day trial to sort this particular issue. –  Apr 27 '17 at 11:09