{"id":3770,"date":"2014-06-05T12:03:17","date_gmt":"2014-06-05T02:03:17","guid":{"rendered":"https:\/\/www.flamingspork.com\/blog\/?p=3770"},"modified":"2014-06-05T12:03:17","modified_gmt":"2014-06-05T02:03:17","slug":"performance-impact-of-mysql-query-cache-on-modern-hardware","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2014\/06\/05\/performance-impact-of-mysql-query-cache-on-modern-hardware\/","title":{"rendered":"Performance impact of MySQL query cache on modern hardware"},"content":{"rendered":"

Recently, Morgan<\/a> has been writing on deprecating some MySQL feature<\/a>s and inspired by that while working on MySQL on POWER<\/a>, I wondered “What is the impact of the MySQL query cache on modern hardware?”<\/p>\n

We’ve known for over six years (since before we started Drizzle<\/a>) that the query cache hurt performance. It was for that reason that the query cache was one of the early things to be removed from Drizzle, it just didn’t scale on multi core systems that\u00c2\u00a0 we were targeting.<\/p>\n

So what about modern hardware? While working on MySQL 5.6 on POWER8, I enabled the query cache and ran a benchmark. Enabling the query cache reduced performance by an order of magnitude<\/strong>. I suspect the performance impact is even higher on MySQL 5.7.<\/p>\n

My vote? Deprecate it. Deprecate it now, print a giant warning if it’s enabled and at some point just remove it. A single global mutex just doesn’t scale to 4 cores, let alone 24 cores at 8 threads per core.<\/p>\n","protected":false},"excerpt":{"rendered":"

Recently, Morgan has been writing on deprecating some MySQL features and inspired by that while working on MySQL on POWER, I wondered “What is the impact of the MySQL query cache on modern hardware?” We’ve known for over six years … 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_post_was_ever_published":false,"_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}},"categories":[76,570,14],"tags":[70,628,568,583],"class_list":["post-3770","post","type-post","status-publish","format-standard","hentry","category-code","category-ibm-work-et-al","category-mysql","tag-drizzle","tag-mysql","tag-power8","tag-query-cache"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-YO","jetpack-related-posts":[{"id":3160,"url":"https:\/\/www.flamingspork.com\/blog\/2012\/09\/20\/impact-of-mysql-slow-query-log\/","url_meta":{"origin":3770,"position":0},"title":"Impact of MySQL slow query log","author":"Stewart Smith","date":"2012-09-20","format":false,"excerpt":"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:\u00c2\u00a0Intel(R) Core(TM) i7-2620M CPU @ 2.70GHz The benchmark\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":3801,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/09\/19\/mysql-architecture\/","url_meta":{"origin":3770,"position":1},"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":1736,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/10\/13\/random-query-generator-added-to-drizzle-automation\/","url_meta":{"origin":3770,"position":2},"title":"Random Query Generator added to Drizzle Automation","author":"Stewart Smith","date":"2009-10-13","format":false,"excerpt":"As Lee announced, we have the Random Query Generator added to Drizzle Automation. It always amazed me that we were lacking such a fundamental testing tool for MySQL for all that time. I always found the similar (NDB API) tools for MySQL Cluster (NDB) to be really, really useful when\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"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":3770,"position":3},"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":1383,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/02\/02\/row-id-in-mysql-and-drizzle-and-the-engines\/","url_meta":{"origin":3770,"position":4},"title":"row id in MySQL and Drizzle (and the engines)","author":"Stewart Smith","date":"2009-02-02","format":false,"excerpt":"Some database engines have a fundamental concept of a row id. The row id is everything you need to know to locate a row. Common uses include secondary indexes (key is what's indexed, value is rowid which you then use to lookup the row). One design is the InnoDB method\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":4019,"url":"https:\/\/www.flamingspork.com\/blog\/2015\/12\/18\/power8-accelerated-crc32-merged-in-mariadb-10-1\/","url_meta":{"origin":3770,"position":5},"title":"POWER8 Accelerated CRC32 merged in MariaDB 10.1","author":"Stewart Smith","date":"2015-12-18","format":false,"excerpt":"Earlier on in benchmarking MySQL and MariaDB on POWER8, we noticed that on write workloads (or read workloads involving a lot of IO) we were spending a bunch of time computing InnoDB page checksums. This is a relatively well known MySQL problem and has existed for many years and Percona\u2026","rel":"","context":"In "IBM"","block_context":{"text":"IBM","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/ibm-work-et-al\/"},"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\/3770","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=3770"}],"version-history":[{"count":1,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3770\/revisions"}],"predecessor-version":[{"id":3771,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3770\/revisions\/3771"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=3770"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=3770"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=3770"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}