Archives

The List

« ERROR 1553 (HY000): You can’t use locks with log tables. — Now what? | Main | Installing MySQL 5.1 on Kubuntu, Ubuntu or Asus EEE »

Controlling the MySQL Slow Log (Another Item for the Wish List)

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

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.

For more MySQL-related information om this site, see:
MySQL Pop Quizzes || MySQL Function of the Day
MySQL DATE_FORMAT() and PHP DATE() tool
Other MySQL blog entries

Topics: MySQL |

9 Responses to “Controlling the MySQL Slow Log (Another Item for the Wish List)”

  1. Roland Bouman Says:
    February 5th, 2008 at 6:16 pm

    Hi Carsten!

    “It would be great to have the option to turn off logging to the slow query log on a per-statement basis.”

    Mm, maybe I am missing something. But that would imply that you already know which ones are worth looking after.

    Anyway - It seems to me you can actually do something close to what you want. It would just involve modifying the functions from which you call those statements rather than the SQL itself, and wrap

    SET slow_query_log := 1;

    SET slow_query_log := 0;

    around those queries. I think the popular APIs nowadays allow you to send several statements in a batch, so this would be pretty close to a query modifier you mean.

    Or you could use MySQL proxy, and have that wrap the log enabling statements around the actual queries.

  2. Carsten Says:
    February 5th, 2008 at 10:22 pm

    Yes, I do indeed know what I’m looking for … in some cases, I know this based on my knowledge of the application — e.g. that the table in question will never contain more than 50 rows, and that it will always be faster to do a full scan than an index lookup. In other cases, I might realize it by looking at my slow query log :-)

    As for turning off the slow query log by wrapping it in SET statements: unless I’ve missed something, this cannot be done on a per-session level.

  3. Morgan Tocker Says:
    February 6th, 2008 at 1:04 am

    Carsten,

    In 5.1 there’s a new feature that can help you with your first problem: min_examined_row_limit.

    It’s only half made it into the manual (mentioned on http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_min-examined-row-limit, but not on http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html).

  4. Eric Bergen Says:
    February 6th, 2008 at 10:25 am

    I wrote a patch for this functionality some time ago. It adds set sql_log_slow that works similar to set sql_log_bin It also updates mysqldump to wrap inserts in set sql_log_slow=0 so on import the bulk insert statements don’t get written to the slow query log.

    I can update the patch for 5.0.newest and submit it if you’re interested.

    You can view the patch here: http://ebergen.net/patches/sql_log_slow.patch

  5. Carsten Says:
    February 6th, 2008 at 10:46 am

    Eric,

    Thanks, but we’re deploying on 5.1 — and on Windows at that (sob); I’ve happily forgot all about how to compile MySQL on this platform.

    You may want to add it as a note on the feature request I submitted; could perhaps speed things up a bit.

  6. Carsten Says:
    February 6th, 2008 at 3:09 pm

    Morgan: Not exactly what I was looking for, but it gets me more than 80% of the way toward the perfect solution. Thanks!

  7. Sheeri Cabral Says:
    February 7th, 2008 at 6:26 am

    Why not analyze your slow query logs with something like mysqlsla? That will give you one section for each query, and then you don’t have to worry how many times you see it….

    (www.hackmysql.com has mysqlsla)

  8. Carsten Says:
    February 7th, 2008 at 11:45 am

    Sheeri, thanks to the pointer to mysqlsla .. I remember running into it earlier on, but had forgotten about about it again.

    That said, I do indeed have to worry about how many times a “slow” query makes it into the slow query log. Some of these queries are run 10,000+ times per day, and since I already know that I’m not ever going to be worrying about them, I do want to filter them out to improve the signal/noise ratio.

  9. Log Buffer #83: a Carnival of the Vanities for DBAs Says:
    February 8th, 2008 at 8:41 pm

    [...] Random Ramblings has a wish-list item for MySQL — controlling the MySQL slow log. “It would be great to have the option to turn off logging to the slow query log on a [...]

Comments