{"id":3959,"date":"2015-04-28T14:41:46","date_gmt":"2015-04-28T04:41:46","guid":{"rendered":"https:\/\/www.flamingspork.com\/blog\/?p=3959"},"modified":"2015-04-28T14:41:46","modified_gmt":"2015-04-28T04:41:46","slug":"going-beyond-1-3-million-sql-queriessecond","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2015\/04\/28\/going-beyond-1-3-million-sql-queriessecond\/","title":{"rendered":"Going beyond 1.3 MILLION SQL Queries\/second"},"content":{"rendered":"

So, on a large IBM POWER8 system I was recently running the newly coined “yesmark<\/a>” benchmark, which is best translated as this:<\/p>\n

Benchmark (N for concurrency): for i in {1..N}; do yes \"DO 0;\" | mysql > \/dev\/null & done\r\nLive results: mysqladmin -ri 1 extended-status | grep Questions\r\n<\/code><\/pre>\n
Which sounds all fun until you realize that it’s *amazingly* close in results to a sysbench point select benchmark these days (well, with MySQL 5.7.7).<\/p>\n
Since yesmark doesn’t use InnoDB though, MariaDB is back in the game.<\/p>\n
I don’t think it matters between MariaDB and MySQL at this point for yesbench. With MySQL in a KVM guest on a shared 2 socket POWER8 I could get 754kQPS and on a larger system, I could get 1.3 million \/ sec.<\/p>\n
1.3 Million queries \/ sec is probably the highest number anybody has ever seen out of MySQL or MariaDB, so that’s fairly impressive in itself.<\/p>\n
What’s also impressive is that on this workload, mysqld was still only using 50% of CPU in the system. The mysql command line client was really heavy user.<\/p>\n
Other users are: 8% completely idle, another 12% in linux scheduler (alarmingly high really). So out of all execution time, only about 44% spent in mysqld, 29% in mysql client.<\/p>\n
It seems that the current issues scaling to two socked POWER8 machines are the same as with scaling to other large systems, when we go beyond about 20 POWER8 cores (SMT8), we start to find new and interesting challenges.<\/p>\n","protected":false},"excerpt":{"rendered":"
So, on a large IBM POWER8 system I was recently running the newly coined “yesmark” benchmark, which is best translated as this: Benchmark (N for concurrency): for i in {1..N}; do yes “DO 0;” | mysql > \/dev\/null & done … 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":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[570,8,14],"tags":[],"class_list":["post-3959","post","type-post","status-publish","format-standard","hentry","category-ibm-work-et-al","category-linux-kernel","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-11R","jetpack-related-posts":[{"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":3959,"position":0},"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 "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":[]},{"id":4019,"url":"https:\/\/www.flamingspork.com\/blog\/2015\/12\/18\/power8-accelerated-crc32-merged-in-mariadb-10-1\/","url_meta":{"origin":3959,"position":1},"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":[]},{"id":3795,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/10\/08\/mariadb-10-0-on-power\/","url_meta":{"origin":3959,"position":2},"title":"MariaDB 10.0 on POWER","author":"Stewart Smith","date":"2014-10-08","format":false,"excerpt":"Good news for those wanting to run MariaDB on POWER systems, the latest 10.0 bzr tree (as of a couple of weeks ago) builds and runs well! I recently pulled the latest MariaDB 10.0 from BZR and built it on a POWER8 system in the lab to run some quick\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":[]},{"id":3901,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/11\/12\/preliminary-mysql-cluster-benchmark-results-on-power8\/","url_meta":{"origin":3959,"position":3},"title":"Preliminary MySQL Cluster benchmark results on POWER8","author":"Stewart Smith","date":"2014-11-12","format":false,"excerpt":"Yesterday, I got the basics going for MySQL Cluster on POWER. Today, I finished up a couple more patches to improve performance and ran some benchmarks. This is on a 3.7Ghz POWER8 machine with non-balanced memory (only 2 of the 4 NUMA nodes have memory, so we have less total\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":3755,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-6-performance-on-power8\/","url_meta":{"origin":3959,"position":4},"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 "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":[]},{"id":3956,"url":"https:\/\/www.flamingspork.com\/blog\/2015\/04\/10\/beyond-one-million-qps\/","url_meta":{"origin":3959,"position":5},"title":"Towards (and beyond) ONE MILLION queries per second","author":"Stewart Smith","date":"2015-04-10","format":false,"excerpt":"At Percona Live MySQL Conference 2015 next week I'll be presenting on \"Towards One MILLION queries per second\" on 14th April at 4:50pm in Ballroom A. This is the story of work I've been doing to get MySQL executing ONE MILLION SQL queries per second. It involves tales of 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":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3959","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=3959"}],"version-history":[{"count":1,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3959\/revisions"}],"predecessor-version":[{"id":3960,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3959\/revisions\/3960"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=3959"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=3959"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=3959"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}