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.
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…
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)
Awesome to see how Drizzle is performing.
Pingback: A (prototype) lower impact slow query log