November 2013

MySQL Performance: over 1M QPS with InnoDB Memcached Plugin in MySQL 5.7

Last week, during Tomas' keynote at MySQL Percona Live Conference in London we announced as one of "previews" of the following MySQL 5.7 release(s) -- an over 1,000,000 Query/sec result obtained with InnoDB Memcached plugin on a Read-Only workload. This article here is just to confirm the announced results without going too much in details..

In fact we have no idea yet for today what are exactly the scalability and performance limits for this solution.. The huge gain in performance was possible here due initial overall speed-up made recently in MySQL 5.7 and letting us reach 500K QPS in a "normal" SQL Read-Only workload. Then yet more improvement in the InnoDB Memcached Plugin code were possible and came just naturally. Specially since Facebook Team challenged us here pretty well by expressing all performance limitations they are hitting in their workloads. As well Facebook provided us a test case workload which we successfully used to improve even more our code. And finally the same test case was used to obtain the following benchmark results ;-)

The test was executed in "standalone" mode (both server and client are running on the same server). So, we used our biggest HW box we have in the LAB - a 48cores machine. This server was able very quickly to point us into any existing or potential performance issues and bottlenecks (and what is interesting that most of them were now on the memcached code itself). However, Query/sec rate (QPS) is depending a lot here of memory latency and CPU frequency, while this server is having 2Ghz CPU cores only, so on a faster HW you may expect even better results ;-)

Now, comparing best-to-best QPS results obtained on this server we have the following :

and for people who prefer 2D charts :

I've placed in legend "MySQL 5.6", while a true label should be rather "the best result we observed until now" ;-)) -- because some part of Memcached code improvement will be back-ported to MySQL 5.6 as well, so we may expect to see next 5.6 releases running here better too. However - only with MySQL 5.7 code base you'll be able to go really high..

During my talk at Percona Live in London I've also presented the following graphs - the Memcached QPS is corresponding here to the InnoDB "dml_reads/sec" stats :

There are 4 tests on these graphs representing "previous" MySQL code running on Memcached workload :

• #1 - running on 48cores as it.. - we're hitting a severe contentions related to the MVCC code (which was fixed in the latest MySQL 5.7)..
• #2 - limiting MySQL server to run on 16cores only to lower this contention.. - and then hitting transaction related contentions (which was also fixed in the latest MySQL 5.7 code)..
• #3 - tune memcached plugin to keep several reads within a single internal transaction -- helps, but hitting other contentions..
• #4 - limiting MySQL server to run on 8cores to see if contentions may be lowered -- indeed, the max peak QPS becomes higher (on 32 users), but overall performance is worse..

While on the latest MySQL 5.7 code things are looking completely differently :

There are 2 tests on these graphs:

• #1 - is running on 48cores as it (no comments ;-))
• #2 - is using "tuning" option to keep several reads within a single internal transaction - just slightly better on a peak max QPS, otherwise no significant difference anymore..

And to really feel the difference in obtained QPS gap, let's bring them all together to the same graph :

As you can see, the difference is more than impressive ;-))

• all the curves on the left parts of graph representing QPS levels obtained on the "previous" MySQL 5.6 / 5.7 code..
• then, the last curves on the right part - with the latest MySQL 5.7 code..

So, work is still in progress, and I let Sunny and Jimmy provide you all deep details about this huge step forward we made in the latest MySQL 5.7 release!

I don't know what will be the performance limit here.. Probably only HW level.. And don't know if we'll have a big enough HW to see it ;-) -- currently via a single 1Gbit network link we already observed over 700K QPS performance, and while the limitation is coming here from a single network link, the main troubles are coming from clients processing rather server.. - so, seems like Memcached @InnoDB is scaling now way better comparing to the "original" Memcached itself ;-) -- then, what kind of performance may be expected when several network links are used (or simply more fast network cards are used) -- there is still a lot to discover! and RW workload performance will be yet another challenge as well ;-)

Kudos to Sunny and Jimmy! And my special thanks to Yoshinori (Facebook)! - I think this is an excellent example where a common work on a given problem provides a fantastic final result for all MySQL users!..

If you need some details about Memcached Plugin design - you may start your reading from here: https://blogs.oracle.com/MySQL/entry/nosql_memcached_api_for_mysql - while then, keeping in mind all presented here results, I let you imagine now what kind of performance you may expect if data will be accessed directly via "native" InnoDB API and by-passing the Memcached level.. ;-))

Rgds,
-Dimitri

Speaking at Percona Live London 2013

I'm speaking next week at Percona Live in in London. My talk will be almost about MySQL Performance (of course), covering major MySQL 5.6 improvements and latest findings made in MySQL 5.7 for today. Percona Server 5.6 and MariaDB 10 will not be missed as well.. - we're living very interesting times, and performance topic is amazing today as never ;-) As usually, I'll tell you "one more thing" about MySQL Performance latest news that you cannot read or find anywhere else.. - so, stay tuned ;-) Rgds, -Dimitri