Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
These is not a file "/-slow.log" on my server. "mysql> show variables like '%slow_quer%' returns "log_slow_queries" OFF. –  DavidLiu 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'. –  DavidLiu Oct 13 '10 at 1:53
add comment

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. ;-)

share|improve this answer
    
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 –  DavidLiu Oct 13 '10 at 2: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 2:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.