The MySQL Slow Query Log is a required tool in the database administrator’s toolbox. It’s great for troubleshooting specific issues, but it’s also great for some rainy day application tuning.
My slow query log is in /var/lib/mysqld/db-001-slow.log
and records any queries that take longer than 10 seconds (the default value for long_query_time)
. I can get information out of this log using mysqldumpslow
.
Running `mysqldumpslow db-001-slow.log`
prints out slow queries sorted by descending execution time. But that’s not useful to me, because any query can get delayed by a blip in the system.
I like running `mysqldumpslow -s c db-001-slow.log`
which prints out the slow queries sorted by descending count of times that query occurred. Optimizing a query that takes 10 seconds to execute but occurs a dozen times every minute will be more beneficial than optimizing the query that takes 140 seconds to execute but rarely occurs.
The first time I tried this exercise, I revealed the following 3 types of slow queries (can’t remember the exact order now):
- Queries with lots of logic and joins returning infrequently-changing data.
- Queries using the
curdate()
function, which are not query cacheable. - Queries to insert/update a stats table for content view counts.
For #1, I used an in-memory cache to cache the query results. For #2, I replaced the curdate()
function with the PHP date()
function everywhere I could find it. For #3, I noticed an extraneous index on the stats table, and indexes slow down inserts and updates, so I removed it. For more on handling these types of queries, see my next post.