Impact of MySQL slow query log

So, what impact does enabling the slow query log have on MySQL?

I decided to run some numbers. I’m using my laptop, as we all know the currently most-deployed database servers have mulitple cores, SSDs and many GB of RAM. For the curious: Intel(R) Core(TM) i7-2620M CPU @ 2.70GHz

The benchmark is going to be:
mysqlslap -u root test -S var/tmp/mysqld.1.sock -q 'select 1;' --number-of-queries=1000000 --concurrency=64 --create-schema=test

Which is pretty much “run a whole bunch of nothing, excluding all the overhead of storage engines, optimizer… and focus on logging”.

My first run was going to be with the slow query log on. I’ll start the server with mysql-test-run.pl as it’s just easy:
eatmydata ./mysql-test-run.pl --start-and-exit --mysqld=--slow-query-log --mysqld=--long-query-time=0

The results? It took 18 seconds.

How long without the slow query log (starting with mysql-test-run.pl again, but this time without any of the extra mysqld options)? 13 seconds.

How does this compare to a Drizzle baseline? On a freshly build Drizzle trunk, using the same mysqlslap binary I used above, connecting via UNIX socket: 8 seconds.

4 thoughts on “Impact of MySQL slow query log

  1. MySQL version? It doesn’t make any sense to run benchmarks and publish the results, if one doesn’t know the versions you were using…

  2. This was my 5.6.6 tree, which differs slightly from mainline MySQL 5.6.6 but nothing that should affect this (I’m also not really aware of any codepath here that would have changed much since 5.5… or even that much since 5.1 I think)

  3. Pingback: A (prototype) lower impact slow query log

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.