<MySQL Performance: over 1M QPS with InnoDB Memcached Plugin in MySQL 5.7>MySQL的高性能：MySQL 5.7

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..

在上周， Tomas 在 MySQL Percona Live Conference in London ，宣布了MySQL 5.7的版本--在只读的（Read-Only）测试环境，InnoDB 的 Memcached plugin的版本中，可以处理 每秒 1,000,000 次的查询。这个文章就是证实这个说法的。

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 ;-)

实际上，我至今也没有准确说法，到底可拓展性有多么的准确和有多少的性能限制在这里面..我们可以在最新的MySQL 5.7 中可以得到最大的性能提升，并且，并且可以让我们轻松的在“普通的”（normal）SQL 在只读的环境中（Read-Only workload）  测试到 500K QPS 。接下来，更多的性能提升在InnoDB Memcached Plugin代码中变为可能，然而，一切都是那么自然。尤其在Facebook团队，他们突破并展现出巨大的性能点。当然，Facebook给我们提供了一个测试case，我们也用它来成功的提高了我们代码。最终，同样的测试案例会在下面的测试评分结果中展示出来;-）

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 ;-)

这个测试是在”独立“（standalone）模式上测试的。（包括服务器，客户端都在上面运行）。所以，我们使用我们实验室最大的HW box - 一个48核的机器。这个服务器能够迅速的给我们指出一个存在的，或者潜在的性能瓶颈（最有趣的是，大部分他们都是在memcached代码上面）。然而，QPS 依赖于内存和CPU。因此，这台服务器是只有2Ghz的CPU 核，如果有存在其他更快的HW，你可能能得到更好的积分统计 ：-）
Now, comparing best-to-best QPS results obtained on this server we have the following :

and for people who prefer 2D charts :

大家更喜欢这种2D的表格统计：

 
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..

我还是把”MySQL 5.6“标记成一个传奇，这是理所当然的”那是我保存的最好的结果，直到现在“；-）因为一些Memcached code 也移植到MySQL 5.6，所以，我们也期望在下一个5.6的版本中也能跑到这么高的分数。然而，只有MySQL 5.7才可以跑到这么高的分数。

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 :

在我在 Percona Live in London 对话中，我依然保留了下图-Memcached QPS 这这里对应InnoDB的“dml_reads/sec" 

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

这这图标中，这有4个测试。”先前的“MySQL代码运行在Memcached 工作区中：

• #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)..
• #1 - 也运行在48核的机器上面 .. - 我们得到了少数的竞争，基于MVCC。(我们在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)..
• #2 - 限制MySQL服务器跑在16核的机子上面，降低竞争。。--然而，得到更少的事务之间的竞争（我们也在5.7中得到修复）
• #3 - tune memcached plugin to keep several reads within a single internal transaction -- helps, but hitting other contentions..
• #3 - 打开Memcached plugin 来保证在单个间隔事务中读，--有帮助-确实没有资源争用
• #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..
• #4 - 限制MySQL 服务器跑在8核的机器上，去看看竞争是否可能会降低 -- 确实，最大的peak QPS变得更高（32个用户），但是负载更为严重。

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

而在最新的MySQL 5.7的事情看起来完全不同：

There are 2 tests on these graphs:

在这图标中，有两个测试：

• #1 - is running on 48cores as it (no comments ;-))
• #1 - 跑在48核上面（没有评论；-）
• #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..
• #2 - 用”打开“（tuning）选项去保持单一间隔事务的少量读-稍微在QPS高峰期最好，否则，就没有显著的差异了

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

感受一下增加QPS gap的不同，我把他们放在一个图表中。

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..
• 左边的曲线是先前的MySQL 5.6/5.7 
• then, the last curves on the right part - with the latest MySQL 5.7 code..
• 最后一个曲线（右边）是MySQL 5.7的最新版本

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!

所以，工作测试还要继续，我让Sunny和Jimmy 给你提供更为详细的步骤在最新的MySQL 5.7版本中！

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 ;-)

我不知道这还什么性能限制在这里面。。可能也只有HW等级了吧。所以，我不知道我们应该是否要一台足够大的HW去测试；-）--当前，只通过单个1Gbit的带宽，我们能得到700K QPS，并且限制也只来源于单一网络，主要的问题就来自与客户端而非服务器。。- 所以，更像Memcached @InnoDB 缩放的背影，更好的比较“原始的”Memcached 本身：-）--

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!..

感谢Sunny和Jimmy！还要特殊感谢yoshinori（Facebook）！-我认为这是一个很典型的例子！

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.. ;-))

想要了解更多关于Memecached Plugin 的设计-你可以follow this link :https://blogs.oracle.com/MySQL/entry/nosql_memcached_api_for_mysql 。-然后，你把结果都印在心里面，我现在让你现在想象一下”如果是数据是直接通过“本地”（native）InnoDB API 访问的，然后通过Memcached 层面传输的“，你所期待是哪种性能？

Rgds,
-Dimitri