{"id":3758,"date":"2014-06-03T16:05:05","date_gmt":"2014-06-03T06:05:05","guid":{"rendered":"https:\/\/www.flamingspork.com\/blog\/?p=3758"},"modified":"2014-06-03T16:05:05","modified_gmt":"2014-06-03T06:05:05","slug":"mysql-5-7-on-power","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-7-on-power\/","title":{"rendered":"MySQL 5.7 on POWER"},"content":{"rendered":"<p>In a previous post, I covered <a href=\"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-6-on-power-patch-available\/\">porting MySQL 5.6 to POWER<\/a> and subsequently, some new <a href=\"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-6-performance-on-power8\/\">record performance numbers with MySQL 5.6.17 on POWER8<\/a>.<\/p>\n<p>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 the mutex contention that we saw with MySQL 5.6. The postings on this site are my own and don\u00e2\u20ac\u2122t necessarily represent IBM\u00e2\u20ac\u2122s positions, strategies or opinions.<\/p>\n<p>In looking at MySQL performance on POWER, it&#8217;s inevitable that I should look at MySQL 5.7 and what&#8217;s coming up in the next stable release of MySQL.<\/p>\n<p>Surprisingly, a bunch of the core code in InnoDB and MySQL dealing with mutexes has changed in MySQL 5.7 when compared to MySQL 5.6. Enough that I actually had to post a few bug reports about the changes that apply to any CPU architecture:<\/p>\n<ul>\n<li><a href=\"http:\/\/bugs.mysql.com\/bug.php?id=72805\">Bug 72805<\/a>: mutex_delay() creating excess memory traffic, GCC mem barrier needed\n<ul>\n<li>This is now more generic mutex code, so it&#8217;s even more important to get it right. There&#8217;s a bunch of tricks that have been learned in other places (e.g. Linux kernel) in getting these things right. We need to get them right in MySQL too.<\/li>\n<li>One of these tricks is in ensuring that the compiler doesn&#8217;t compile down spinloops to nothing.<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"http:\/\/bugs.mysql.com\/bug.php?id=72806\">Bug 72806<\/a>: mutex_delay() missing x86 pause instruction optimization\n<ul>\n<li>This is actually a regression over 5.6.<\/li>\n<li>On x86, there is an instruction (PAUSE) that tells the CPU that you&#8217;re in a spin loop and that it should yield resources in the CPU core to other threads (or thread, as HT CPUs only have 2 threads per core).<\/li>\n<li>We have a different way of doing things on POWER, and I&#8217;ve got a patch for that too.<\/li>\n<li>What&#8217;s interesting is reading the Intel CPU manual about the PAUSE instruction and how even if you went and benchmarked it, it depends on the CPU on if this is a NO-OP or not.<\/li>\n<li>I suspect that with this bug fixed, performance on Hyper Threaded Intel systems will improve.<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"http:\/\/bugs.mysql.com\/bug.php?id=72807\">Bug 72807<\/a>: Set thread priority in my_pthread_fastmutex_lock\n<ul>\n<li>This is the POWER equivalent of the x86 PAUSE instruction.<\/li>\n<li>I&#8217;ve found this patch to have a quite decent positive impact on sysbench point select performance.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>There were also the bugs I mentioned in my <a href=\"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-6-on-power-patch-available\/\">MySQL 5.6 on POWER<\/a> blog post. Notably, I had to port Yasufumi&#8217;s memory barrier patch from 5.6 to 5.7. My port is incomplete (I can still crash mysqld without too much trying) but I&#8217;ve deemed it currently &#8220;good enough for benchmarking&#8221; and it&#8217;s attached to <a href=\"http:\/\/bugs.mysql.com\/bug.php?id=47213\">bug 47213<\/a> (I hope to spend some time fixing it up soon too). I don&#8217;t think I&#8217;m missing anything that&#8217;s going to have a major performance impact &#8211; so while not suitable for production use, it&#8217;s good enough to poke some benchmarks at.<\/p>\n<p>So&#8230; I&#8217;m close to the point where I&#8217;ll share my patch for MySQL 5.7, but I&#8217;m really wanting to solve the last couple of issues before doing so. The majority of patches are attached to bug reports and get 99% of the way.<\/p>\n<p>Amazingly enough, MySQL 5.7 works fairly well on POWER &#8220;out of the box&#8221;, and with sysbench point selects, I could quite easily get 320kQPS on a 24 core POWER8 with SMT8 mode without changing a single line of code or doing anything special. This alone is an impressive result when compared to the previous record on both POWER and other CPU architectures with MySQL 5.6 that had been optimized for POWER (while out-of-the-box MySQL 5.7 has not)<br \/>\nFor my benchmarks, I&#8217;m doing the same procedure, workload and basic my.cnf settings that <a href=\"http:\/\/dimitrik.free.fr\/blog\/archives\/2013\/10\/mysql-performance-the-road-to-500k-qps-with-mysql-57.html\">Dimitri has used and written about<\/a>, so I won&#8217;t repeat that here.With my preliminary patch for MySQL 5.7.4-m14 to have it work well on POWER, on the same system I was using for my MySQL 5.6 benchmarks, I could easily match and indeed exceed the previous published maximum sysbench point select results (I got ~<strong>630kQPS<\/strong>). Consider this number a bit preliminary as my patch isn&#8217;t completely solid, but it does mean that we&#8217;re in the right ballpark for MySQL 5.7 performance, which is great news!So, you might just say &#8220;Mission Accomplished&#8221; and be done with it. Well&#8230; there was one issue:\u00c2\u00a0 with the maximum numbers I was getting there was still <strong>30-40% idle CPU on the POWER 8 machine<\/strong>.Now&#8230; you could just use that idle 30-40% of total CPU to do other things (solving Sudoku in SQL for example) but that&#8217;s no fun.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &hellip; <a href=\"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-7-on-power\/\">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_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":[581,628,580,562,568],"class_list":["post-3758","post","type-post","status-publish","format-standard","hentry","category-code","category-ibm-work-et-al","category-mysql","tag-5-7","tag-mysql","tag-mysql-5-7","tag-power","tag-power8"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-YC","jetpack-related-posts":[{"id":3755,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-6-performance-on-power8\/","url_meta":{"origin":3758,"position":0},"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":3765,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/1-million-sql-queries-per-second-mysql-5-7-on-power8\/","url_meta":{"origin":3758,"position":1},"title":"1 million SQL Queries Per Second: MySQL 5.7 on POWER8","author":"Stewart Smith","date":"2014-06-03","format":false,"excerpt":"I'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 or opinions. Also, these numbers should be considered preliminary, but\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":3770,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/06\/05\/performance-impact-of-mysql-query-cache-on-modern-hardware\/","url_meta":{"origin":3758,"position":2},"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":3738,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/05\/26\/awesome-mysql-5-7-improvements\/","url_meta":{"origin":3758,"position":3},"title":"Awesome MySQL 5.7 improvements","author":"Stewart Smith","date":"2014-05-26","format":false,"excerpt":"Recently, I've had reason to poke at MySQL performance on some pretty cool hardware. Comparing MySQL 5.6 to MySQL 5.7 is a pretty interesting thing to do when you have many CPU cores. The improvements to creating read views in InnoDB is absolutely huge for small statements with large concurrency\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":4173,"url":"https:\/\/www.flamingspork.com\/blog\/2016\/09\/23\/first-look-at-mysql-8-0-0-milestone\/","url_meta":{"origin":3758,"position":4},"title":"First look at MySQL 8.0.0 Milestone","author":"Stewart Smith","date":"2016-09-23","format":false,"excerpt":"So, about ten days ago the MySQL Server Team released MySQL 8.0.0 Milestone to the world. One of the most unfortunate things about MySQL development is that it's done behind closed doors, with the only hints of what's to come arriving in maybe a note on a bug or such\u2026","rel":"","context":"In &quot;mysql&quot;","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":3778,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/07\/17\/update-on-mysql-on-power8\/","url_meta":{"origin":3758,"position":5},"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":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3758","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=3758"}],"version-history":[{"count":1,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3758\/revisions"}],"predecessor-version":[{"id":3764,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3758\/revisions\/3764"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=3758"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=3758"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=3758"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}