{"id":3765,"date":"2014-06-03T16:37:47","date_gmt":"2014-06-03T06:37:47","guid":{"rendered":"https:\/\/www.flamingspork.com\/blog\/?p=3765"},"modified":"2014-06-03T16:43:19","modified_gmt":"2014-06-03T06:43:19","slug":"1-million-sql-queries-per-second-mysql-5-7-on-power8","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/1-million-sql-queries-per-second-mysql-5-7-on-power8\/","title":{"rendered":"1 million SQL Queries Per Second: MySQL 5.7 on POWER8"},"content":{"rendered":"<p>I&#8217;ve previously covered <a href=\"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-6-on-power-patch-available\/\">MySQL 5.6 on POWER<\/a> (with <a href=\"https:\/\/flamingspork.com\/mysql\/mysql-5.6.17-POWER.patch\">patch<\/a>), <a href=\"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-6-performance-on-power8\/\">MySQL 5.6 Performance on POWER8<\/a> (spoiler: new performance record) and <a href=\"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-7-on-power\/\">MySQL 5.7 on POWER<\/a>.<\/p>\n<p>Of course, The postings on this site are my own and don\u00e2\u20ac\u2122t necessarily represent IBM\u00e2\u20ac\u2122s positions, strategies or opinions. Also, these numbers should be considered preliminary, but trust me &#8211; I did get them and it&#8217;s not April 1st.<\/p>\n<p>From my last post, you saw that with my preliminary patch for MySQL 5.7 to work on POWER, we could easily match the previous record for sysbench point select queries per second (i.e. key lookups). In fact, we could exceed the published record by a little bit which is kind of nice. At around 630kQPS, one could be rather happy.<\/p>\n<p>But we still had 30-40% idle CPU on POWER8. This led me to file the following bug report:<\/p>\n<ul>\n<li><a href=\"http:\/\/bugs.mysql.com\/bug.php?id=72829\">Bug 72829<\/a>: LOCK_grant is major contention point, leaves 30-40% idle CPU.<\/li>\n<\/ul>\n<p>What&#8217;s going on is that there&#8217;s a rwlock in the MySQL Server that ensures that writers don&#8217;t collide with readers to the data structures describing the GRANTs (i.e. who has access to what). If you run a GRANT statement, it gets a writer lock, and nobody can read (i.e. check permissions) while everything is being updated. If you run a normal SQL statement, you get a read lock (non-exclusive) and can check permissions appropriately.<\/p>\n<p>It&#8217;s been known for a long time that LOCK_grant was a bottleneck. Typically, some people have run with skip-grant-tables to help shorten the time the lock as held (as in MySQL you still take the mutex even though you&#8217;ve started the server with skip-grant-tables).<\/p>\n<p>In Drizzle, we fixed that &#8211; moving authentication and authorization completely behind plugin APIs and if you didn&#8217;t load plugins for them, you executed near enough to zero instructions that it didn&#8217;t matter.<\/p>\n<p>In my experiments, enabling skip-grant-tables actually <strong>hurt<\/strong> performance rather than helped. More investigation is needed, but it seems that simply the act of acquiring and releasing the rdlock is now a <strong>major<\/strong> <strong>bottleneck<\/strong> in some benchmarks (such as sysbench point select).<\/p>\n<p>It turns out that this is a well known problem in other pieces of software (e.g. Linux kernel) and is pretty much what RCU (Read Copy Update) is best at. As far back as 2006 I remember attempting to get my head around RCU so that one day we could use it in MySQL or MySQL Cluster.<\/p>\n<p>Another simpler method is simply splitting the mutex, with readers able to acquire any one of N mutexes and writers needing to acquire them all. This penalizes writers, but unless you&#8217;re executing a lot of GRANTs, you&#8217;re probably safe.<\/p>\n<p>So&#8230; what is the theoretical maximum performance if this bottleneck went away?<\/p>\n<p>I wrote a quick patch that just commented out the rdlock acquisition of LOCK_grant in the hot codepath of sysbench point selects. I wasn&#8217;t running GRANT statements at runtime so this was &#8220;safe&#8221;.<\/p>\n<p>This patch is <strong>not production ready<\/strong>, it&#8217;s merely useful for <strong>demonstrating where we could be with MySQL 5.7 on POWER8 <\/strong>if one last bottleneck is fixed.<\/p>\n<p>My results? Slightly over <strong>ONE MILLION QUERIES PER SECOND!<\/strong><\/p>\n<p>This is roughly <strong>twice the previous record.<\/strong><\/p>\n<p>This is with a dual socket 24 core POWER8 with SMT8 and DSCR=1 on 8 tables with sysbench 0.4.8. Sysbench itself is using a non-trivial amount of CPU and I could probably decently beat this number if I rewrote sysbench using the nonblocking API in libdrizzle (back when me made the Drizzle performance regression tests use a libdrizzle-ified sysbench we got double digit percentage improvement in our sysbench numbers).<\/p>\n<p>There&#8217;s still around 7-10% idle CPU time&#8230; so there&#8217;s more room to grow.<\/p>\n<p>Lacking a physical gauntlet to throw down, I&#8217;ll just have to submit a conference paper somewhere so that I can do that in person.<\/p>\n<p>I really hope that we&#8217;re able to fix this bottleneck in MySQL 5.7 so that MySQL 5.7 will ship being able to do over a million queries per second. From SQL.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve previously covered MySQL 5.6 on POWER (with patch), MySQL 5.6 Performance on POWER8 (spoiler: new performance record) and MySQL 5.7 on POWER. Of course, The postings on this site are my own and don\u00e2\u20ac\u2122t necessarily represent IBM\u00e2\u20ac\u2122s positions, strategies &hellip; <a href=\"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/1-million-sql-queries-per-second-mysql-5-7-on-power8\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/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_feature_clip_id":0,"_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,570,14],"tags":[582,70,139,628,562,568,109],"class_list":["post-3765","post","type-post","status-publish","format-standard","hentry","category-code","category-drizzle-work-et-al","category-ibm-work-et-al","category-mysql","tag-bottleneck","tag-drizzle","tag-mutex","tag-mysql","tag-power","tag-power8","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-YJ","jetpack-related-posts":[{"id":3758,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-7-on-power\/","url_meta":{"origin":3765,"position":0},"title":"MySQL 5.7 on POWER","author":"Stewart Smith","date":"2014-06-03","format":false,"excerpt":"In a previous post, I covered porting MySQL 5.6 to POWER and subsequently, some new record performance numbers with MySQL 5.6.17 on POWER8. Well, those following at home will be aware that not only is the next sentence sponsored by IBM Legal, but that MySQL 5.7 alleviates a bunch of\u2026","rel":"","context":"In &quot;code&quot;","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3755,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-6-performance-on-power8\/","url_meta":{"origin":3765,"position":1},"title":"MySQL 5.6 Performance on POWER8","author":"Stewart Smith","date":"2014-06-03","format":false,"excerpt":"The following sentence is brought to you by IBM Legal: The postings on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. My previous post covered the work needed to get MySQL 5.6.17 running reliably on modern POWER systems. The patch to MySQL 5.6.17 that's\u2026","rel":"","context":"In &quot;IBM&quot;","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":[]},{"id":4003,"url":"https:\/\/www.flamingspork.com\/blog\/2015\/10\/19\/1-million-sql-queries-per-second-ga-mariadb-10-1-on-power8\/","url_meta":{"origin":3765,"position":2},"title":"1 Million SQL Queries per second: GA MariaDB 10.1 on POWER8","author":"Stewart Smith","date":"2015-10-19","format":false,"excerpt":"A couple of days ago, MariaDB announced that MariaDB 10.1 is stable GA - around 19 months since the GA of MariaDB 10.0. With MariaDB 10.1 comes some important scalabiity improvements, especially for POWER8 systems. On POWER, we're a bit unique in that we're on the higher end of CPUs,\u2026","rel":"","context":"In &quot;IBM&quot;","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":[]},{"id":3770,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/06\/05\/performance-impact-of-mysql-query-cache-on-modern-hardware\/","url_meta":{"origin":3765,"position":3},"title":"Performance impact of MySQL query cache on modern hardware","author":"Stewart Smith","date":"2014-06-05","format":false,"excerpt":"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 (since before we started Drizzle) that the query cache hurt\u2026","rel":"","context":"In &quot;code&quot;","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3778,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/07\/17\/update-on-mysql-on-power8\/","url_meta":{"origin":3765,"position":4},"title":"Update on MySQL on POWER8","author":"Stewart Smith","date":"2014-07-17","format":false,"excerpt":"About 1.5 months ago I blogged on MySQL 5.6 on POWER andtalked about what I had to poke at to make modern MySQL versions run and run well on shiny POWER8 systems. One of those bugs, MySQL bug 47213 (InnoDB mutex\/rw_lock should be conscious of memory ordering other than Intel)\u2026","rel":"","context":"In &quot;code&quot;","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3899,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/11\/11\/mysql-cluster-on-power8\/","url_meta":{"origin":3765,"position":5},"title":"MySQL Cluster on POWER8","author":"Stewart Smith","date":"2014-11-11","format":false,"excerpt":"So, I've written previously on MySQL on POWER, and today is a quick bit of news about MySQL Cluster on POWER - specifically MySQL Cluster 7.3.7. I ran into three main issues in getting some flexAsync benchmark results. One of them was the fact that I wanted to do this\u2026","rel":"","context":"In &quot;code&quot;","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"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\/3765","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=3765"}],"version-history":[{"count":2,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3765\/revisions"}],"predecessor-version":[{"id":3767,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3765\/revisions\/3767"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=3765"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=3765"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=3765"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}