Review slow MySQL queries

You've enabled slow query logging and now have a log to review. If you haven't enabled logging, you can find steps here.

Analyzing the log

Log location:

/var/log/slow-queries.log

Format:

# Time: 210905  6:33:11
# User@Host: dbuser[dbname] @ hostname [1.2.3.4]
# Query_time: 12.116250  Lock_time: 0.000035 Rows_sent: 0  Rows_examined: 201565
use dbname;
SET timestamp=1409898791;
...SLOW QUERY HERE...

The log can be overwhelming when you have dozens if not hundreds of entries in the format above. To make the log more readable, use mysqldumpslow to parse the log.

mysqldumpslow -r -a /var/log/mysql/mysqld.slow.log

The output will contain important info like time, rows, and query.

Count: 2  Time=20.54s (41s)  Lock=0.02s (0s)  Rows=12547802 (0), dbuser[dbuser]@localhost
  SELECT * FROM db_exp_table WHERE site_user_id='214380' ORDER BY site_id DESC LIMIT 0,2

Count: 1  Time=21.74s (21s)  Lock=0.01s (0s)  Rows=12547802 (0), dbuser[dbuser]@localhost
  SELECT * FROM db_exp_table WHERE site_user_id='214380' ORDER BY site_id DESC LIMIT 0,2

Count: 1  Time=23.86s (23s)  Lock=0.20s (0s) Rows=12547802 (0), dbuser[dbuser]@localhost
  SELECT * FROM db_exp_table WHERE site_user_id='214380' ORDER BY site_id DESC LIMIT 0,2

Looking at the example above, we have 1 query that has taken 20-24 seconds to run. This causes issues with your site ranging from slow load times to timeout errors (endless spinning). The query is examining 12,547,802 rows. A large query (ie. select *) run on a table with over 12 million rows will likely result in a poor user experience. Optimizing the query (ie. select "coffee tables") or reducing the rows examined (ie. archiving old data) will speed up the query.

If the log file is large, run mysqldumpslow and save the output to a file. This will make it easier to review with other file manipulation tools like grep or find.

mysqldumpslow -r -a /var/log/slow-queries.log > slow.log.parsed

Next Steps

After reviewing the slow query log, you should have a good idea of problematic queries that need to be fixed. If your site is still running slowly with MySQL using high resources, you may need to optimize MySQL to better utilize your server's resources.

Once you are finished with the log, make sure to disable MySQL slow query logging. Leaving logging enabled will drain MySQL resources unnecessarily and potentially fill your disk (with bad queries).

GoDaddy Hosting Services

Our server experts can enable/disable MySQL slow query logging and optimize MySQL. To learn more about this service, our GoDaddy Guides are here to help.

More Info

To learn more about the hosting services GoDaddy offers, see our Hosting Services Menu.