0

The load average of my service is high, always around 15.

And the reason for that is the Mysql is overloaded.

I'm wondering How Can I find out which specific query/script consume the most Mysql resources?

Thanks.

David Liu
  • 313
  • 1
  • 3
  • 6

2 Answers2

0

If you're looking for troublesome queries, you'll need to check the slow query log, which can probably be found in <datadir>/<hostname>-slow.log but the logging is specfied by variables:

mysql> show variables like '%slow_quer%';

This will show you slow queries (defined as taking longer than the variable 'long_query_time' and which (mysql) user executed them, but not which script they came from.

You might also want to look into the command 'EXPLAIN' and search the web for hints about SQL query optimization'. If you need more help after that, come back with more information.

dotplus
  • 1,220
  • 7
  • 12
  • These is not a file "/-slow.log" on my server. "mysql> show variables like '%slow_quer%' returns "log_slow_queries" OFF. – David Liu Oct 12 '10 at 15:34
  • I've corrected my filename description. Anyway, you should probably turn it on:) mysql> set global slow_query_log=ON; – dotplus Oct 12 '10 at 15:45
  • I have run "mysql> set global slow_query_log=ON", but it returns ERROR 1193 (HY000): Unknown system variable 'slow_query_log'. – David Liu Oct 13 '10 at 01:53
0

If you have long-running queries, you might be able to catch them in the act via watch:

watch --interval=1 "mysql -e 'SHOW PROCESSLIST'"

You should be able to see the queries in the process list. If you can't see the entire query, just replace "SHOW PROCESSLIST" with "SHOW FULL PROCESSLIST". You should see the username who called the query and the database that they're running queries against. From there, you could use grep to check for the query within your code.

If you're using a framework like rails/django, it might be a little more difficult since the database ORM's built into those frameworks tuck away the actual raw queries from your view. ;-)

majorhayden
  • 576
  • 3
  • 5
  • ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'watch --interval=1 "mysql -e 'SHOW PROCESSLIST'"' at line 1 – David Liu Oct 13 '10 at 02:21
  • I just ran it on my server with MySQL 5.0 and it worked like a charm. Can you try it without watch and see what you get? You would just run: mysql -e 'SHOW PROCESSLIST' – majorhayden Oct 13 '10 at 02:40