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