How to find slow mysql queries?

By Justin Silverton

It has happened to all of us running a website or application using mysql as its back-end database. Performance is suddenly very sluggish and you have no idea what is causing it. Now there may be other factors that are causing the issue (overloaded CPU, harddrive running out of space, or a lack of bandwidth), but it could also be a query that is not optimized and/or is taking much longer than it should to return.

How do you know which queries are taking the longest to execute? Mysql has built-in functionality for checking this through the slow query log.

To enable (do one of the following):

1) add this to /etc/my.cnf

log-slow-queries=/tmp/slow_queries.log
long_query_time=10

2) call mysqld with –log-slow-queries[=/tmp/slow_queries.log]

long_query_time is the maximum amount of seconds a query can take before it will be logged to the slow query log.

other related options:

–log-slow-admin-statements

Log slow administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE to the slow query log.

–log-queries-not-using-indexes

If you are using this option with –log-slow-queries, queries that do not use indexes are logged to the slow query log.

If slow query logging has been enabled successfully, you will see “ON” in the VALUE field for “log_slow_queries” (shown above).

Note: Queries handled by the query cache are not added to the slow query log, nor are queries that would not benefit from the presence of an index because the table has zero rows or one row.

You may also run into the case where a query is slow at one time (such as when you are logging it) but not another (if you execute it manually):

* A table may be locked, causing the query to wait. the lock_time indicates how long the query waited for locks to be released
* none of the data or indexes have been cached in memory. This is common when MySQL first starts or your tables have not been optimizied
* a background process was running, making disk I/O considerably slower
* The server may have been overloaded with other unrelated queries at the same time, and there wasn’t enough CPU power to do the job efficiently

Log analysis

MySQL also comes with mysqldumpslow, a perl script that can summarize the slow query log and provide a better idea of how often each slow query executes.