{"id":3160,"date":"2012-09-20T15:42:27","date_gmt":"2012-09-20T05:42:27","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=3160"},"modified":"2012-09-20T15:42:27","modified_gmt":"2012-09-20T05:42:27","slug":"impact-of-mysql-slow-query-log","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2012\/09\/20\/impact-of-mysql-slow-query-log\/","title":{"rendered":"Impact of MySQL slow query log"},"content":{"rendered":"

So, what impact does enabling the slow query log have on MySQL?<\/p>\n

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:\u00c2\u00a0Intel(R) Core(TM) i7-2620M CPU @ 2.70GHz<\/p>\n

The benchmark is going to be:
\nmysqlslap -u root test -S var\/tmp\/mysqld.1.sock -q 'select 1;'\u00c2\u00a0--number-of-queries=1000000 --concurrency=64 --create-schema=test<\/code><\/p>\n

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

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:
\neatmydata .\/mysql-test-run.pl --start-and-exit --mysqld=--slow-query-log --mysqld=--long-query-time=0<\/code><\/p>\n

The results? It took 18 seconds.<\/p>\n

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.<\/p>\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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. … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[76,75,14,423],"tags":[],"class_list":["post-3160","post","type-post","status-publish","format-standard","hentry","category-code","category-drizzle-work-et-al","category-mysql","category-percona"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-OY","jetpack-related-posts":[{"id":1199,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/09\/08\/building-mysql-on-windows-mysql-forge-wiki\/","url_meta":{"origin":3160,"position":0},"title":"Building MySQL on Windows – MySQL Forge Wiki","author":"Stewart Smith","date":"2008-09-08","format":false,"excerpt":"Building MySQL on Windows - MySQL Forge Wiki This one covers running mysqld in the VisualStudio debugger, which can be useful. I have no special ndb_mgmd.exe or ndbd.exe in debugger instructions or wisdom (running them from mysql-test-run.pl at least). I've attached debugger to already running (started by mysql-test-run.pl) ndb processes,\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3705,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/03\/17\/ghosts-of-mysql-past-part-11-why-are-you-happy-about-this\/","url_meta":{"origin":3160,"position":1},"title":"Ghosts of MySQL Past, part 11: Why are you happy about this?","author":"Stewart Smith","date":"2014-03-17","format":false,"excerpt":"This is part 11 in what's shaping up to be the best part of a 6 week series (Part 1, 2, 3, 4, 5, 6, 7, 7.1, 8, 8.1, 9 and 10) on various history bits of MySQL, somewhat following my LCA2014 talk (video here). One of my favorite MySQL\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3801,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/09\/19\/mysql-architecture\/","url_meta":{"origin":3160,"position":2},"title":"Some current MySQL Architecture writings","author":"Stewart Smith","date":"2014-09-19","format":false,"excerpt":"So, I've been looking around for a while (and a few times now) for any good resources that cover a bunch of MySQL architecture and technical details aimed towards the technically proficient but not MySQL literate audience. I haven't really found anything. I mean, there's the (huge and very detailed)\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3745,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/05\/26\/efficiently-writing-to-a-log-file-from-multiple-threads\/","url_meta":{"origin":3160,"position":3},"title":"Efficiently writing to a log file from multiple threads","author":"Stewart Smith","date":"2014-05-26","format":false,"excerpt":"There's a pattern I keep seeing in threaded programs (or indeed multiple processes) writing to a common log file. This is more of an antipattern than a pattern, and is often found in code that has existed for years. Basically, it's having a mutex to control concurrent writing to the\u2026","rel":"","context":"In "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3345,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/05\/23\/mysql-vs-drizzle-plugin-api\/","url_meta":{"origin":3160,"position":4},"title":"MySQL vs Drizzle plugin APIs","author":"Stewart Smith","date":"2013-05-23","format":false,"excerpt":"There's a big difference in how plugins are treated in MySQL and how they are treated in Drizzle. The MySQL way has been to create a C API in front of the C++-like (I call it C- as it manages to take the worst of both worlds) internal \"API\". The\u2026","rel":"","context":"In "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":800,"url":"https:\/\/www.flamingspork.com\/blog\/2007\/03\/10\/code-size-of-an-engine-versus-test-suite\/","url_meta":{"origin":3160,"position":5},"title":"Code size of an engine versus test suite","author":"Stewart Smith","date":"2007-03-10","format":false,"excerpt":"If you count the lines of code in the MySQL Cluster (NDB) test suite (mysql-5.1\/storage\/ndb\/test - and exclude the old ODBC stuff) you come up with about 104000 lines of code. This is in contrast to the approximate other 350,000 lines of code for the NDB engine (excluding the handler,\u2026","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=3160"}],"version-history":[{"count":1,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3160\/revisions"}],"predecessor-version":[{"id":3161,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3160\/revisions\/3161"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=3160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=3160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=3160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}