Review slow MySQL queriesYou'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 logLog location:
# Time: 210905 6:33:11 # User@Host: dbuser[dbname] @ hostname [126.96.36.199] # 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.logThe 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/mysql/mysqld.slow.log > slow.log.parsed
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.