MySQL Performance: use the InnoDBMemcached plug-in MySQL5.7 to implement 100_MySQL per second

In memcache last week, Tomas announced the version of MySQL5.7 in MySQLPerconaLiveConferenceinLondon. in the Read-Only test environment, InnoDB's Memcachedplugin version can process 1,000,000 queries per second. This article confirms this statement. In fact, I have not provided an accurate statement so far.

Last week, Tomas announced the MySQL 5.7 version in MySQL Percona Live Conference in London-in the Read-Only test environment and the Memcached plugin version of InnoDB, it can process 1,000,000 queries per second. This article confirms this statement.

As a matter of fact, I have not provided an accurate statement so far. how accurate is scalability and how many performance limits are there .. we can get the maximum performance improvement in the latest MySQL 5.7, and make it easy to "normal) in a Read-Only workload environment, SQL is tested to 500 KB QPS. Next, more performance improvements are possible in the InnoDB Memcached Plugin code. However, everything is so natural. Especially for the Facebook team, they broke through and showed great performance points. Of course, Facebook provides us with a test case, and we also use it to successfully improve our code. In the end, the same test case will be displayed in the following test score results ;-)

This test is performed in standalone mode. (Including servers and clients running on them ). So we use the largest HW box in our lab-a 48-core machine. This server can quickly point out an existing or potential performance bottleneck (most of them are on the memcached code ). However, QPS depends on memory and CPU. Therefore, this server only has 2 GHz CPU cores. if there is other faster HW, you may get better points :-)

Now, it's better-the best QPS results are as follows:

I once put MySQL5.6 on the altar and tagged it with "the best result in history".-) -- because some Memcached code improves the performance of MySQL5.6, therefore, we also hope that it can run well in the next version of 5.6. In fact, you only need MySQL5.7 to reach a very high level ......

At my on-site seminar at Percona in London, I once presented the following charts-Memcached QPS is in line with InnoDB's "dml_read/sec" status:

These charts represent four Memcached load tests performed on the "last Version" MySQL:

• #1 running on a 48-core machine ...... -- We encountered a server conflict with MVCC (fixed in MySQL5.7 of the latest version)
• #2 restrict MySQL services to run on 16-core machines to reduce this conflict ...... -- Then, we encountered a transaction conflict (which was also fixed in the latest MySQL5.7)
• #3 adjust Memcached plugin and keep some read operations in an independent transaction-God, help me, and I have encountered some other conflicts ......
• #4 restrict the MySQL service to run on an 8-core machine to see if it will reduce the conflict. The fact shows that the maximum QPS is increased (in the case of 32 users ), but the overall performance is worse ......

On the contrary, in the latest version of MySQL5.7, the situation is indeed completely different:

These figures represent two tests:

• #1-running on a 48-core machine (do not comment too much ;-))
• #2-adjusted some parameters to maintain some read operations in an independent internal transaction. The result is only slightly better at the QPS peak, and there is no difference in other aspects.

Next we will put these test results in a chart to better understand the QPS gap:

You can see the differences between the two.

• The left half curve of the chart shows that the QPS level is obtained on MySQL5.6/5.7 of the "old version.
• Then the curve in the right half is obtained on the latest version of MySQL5.7.

This work is still in progress. for the great progress we have made in this latest MySQL 5.7 version, I will ask Sunny and Jimmy to provide you with all the in-depth details.

I don't know where the performance limit is. It may only exist at the HW (HardWare) layer. I don't know if there is enough HW to observe it;-) -- now through a separate 1Gbit network connection, we have observed the performance of over 700 KB of QPS, when this peak value comes from a single network link, the main trouble is that it comes from the client processing program rather than the server-so it looks like compared to the "original" Memcached itself, memcached @ InnoDB has better scalability;-) -- When several network connections are enabled (or just use a faster NIC) what kind of performance is there? there are still many things to explore! In addition, RW (ReadWrite) workload performance is another challenge ;-)

Thanks to Sunny and Jimmy! Special thanks to yoshinori (Facebook )! -I think this is a typical example!

Want to know