By Carsten | February 5, 2008
Following up on yesterday’s post on the MySQL slow query log, I’ve been doing quite a bit of analysis of our slow query log entries.
To make sure we catch everything, we’ve also turned on log_queries_not_using_indexes. This gives us a couple of problems
- Partly because we’re at an early stage of deployment, some tables are so small that even though the correct indexes are defined, they’re not used.
- Certain tables are updated frequently, with lots of inserts and deletes taking place. Most of the time, the tables in question contain relatively few rows, again causing the optimizer to ignore the defined indexes and the slow query log to be updated.
In other words, we’re having a boatload of false positives — just a quick analysis reveals more than 100k of the 144k queries picked up so far by the slow query log are of the type described. I haven’t had time to look at most of the remaining 44k queries yet; I suspect the percentage is going to be much higher. Needless to say, this makes it pretty hard to track down other, more troublesome queries which appear less often, but may have a greater impact.
It would be great to have the option to turn off logging to the slow query log on a per-statement basis. MySQL provides the SQL_CACHE and SQL_NO_CACHE modifiers for fine-tuning the usage of the query cache.Alas, there’s no DO_NOT_GENERATE_A_SLOW_LOG_ENTRY_AND_YES_I_REALLY_MEAN_IT modifier.
Of course, it never hurts to ask.
Topics: MySQL |