Home / server / Questions / 190129
In Process
David Liu
Asked: 2010-10-13 07:02:33 +0800 CST

Is there a way to find out the detailed statistics of the querys that processed by Mysql?

  • 772

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.

linux mysql query

2 Answers

  1. 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.

    • 0

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

    • 0