EmergencyEMERGENCY? Get 24/7 Help Now!

MySQL 5.7 sysbench OLTP read-only results: is MySQL 5.7 really faster?

 | April 7, 2016 |  Posted In: MySQL

PREVIOUS POST
NEXT POST

MySQL 5.7 sysbench OLTP read-only resultsThis blog will look at MySQL 5.7 sysbench OLTP read-only results to determine if they are faster than previous versions.

As promised in my previous post, I have checked MySQL 5.7 performance against previous versions in a different workload. This time, I will use sysbench OLTP read-only transactions (read-write transactions are part of future research, as there is more tuning required to get the best performance in write workloads).

One important thing to mention is that MySQL 5.6 and 5.7 have special optimizations for READ-ONLY transactions. In MySQL 5.6, however,  you need to start a transaction with "START TRANSACTION READ ONLY" to get the optimization benefit. MySQL 5.7 automatically detects read-only transactions.

I’ve modified the sysbench oltp.lua script to use "START TRANSACTION READ ONLY" for MySQL 5.6. This optimization is not available in MySQL 5.5.

I also tried two different setups:

  • Local connections: the client (sysbench) and the server (mysqld) are running on the same server
  • Network connection: the client and server are connected by a 10GB network

Other details

  • CPU: 56 logical CPU threads servers, Intel® Xeon® CPU E5-2683 v3 @ 2.00GHz
  • sysbench 10 tables x 10 million rows, Pareto distribution
  • OS: Ubuntu 15.10 (Wily Werewolf)
  • Kernel 4.2.0-30-generic

More details with scripts and config files are available on our github.

Summary results can also be found here:

This post covers the most interesting highlights. First, the results on the local connections:

Looking at these results, I was as surprised as you probably are. On a high number of threads and by a significantly visible margin, MySQL 5.7 is actually slower than MySQL 5.6.

Let me show you the relative performance of MySQL 5.5 and MySQL 5.6 (having MySQL 5.7 as a baseline = 1.0):

With a lower number of threads, MySQL 5.5 outperforms MySQL 5.7 by 8-15%, and on a higher number of threads MySQL 5.6 is better by 6-7%.

To validate these findings, we can check the results on a remote connection. Here is a chart:

This gives us a similar picture, with a couple of key differences. MySQL 5.6 encounters scalability problems sooner, and the throughput declines. The fix for that is using innodb-thread-concurrency=64.

Here are the results:

In this round, I did not test scenarios over 1000 threads. But gauging from the results above it seems that MySQL 5.7 has problems. It is interesting to consider how it will affect replication performance – and I will test this after my read-write benchmarks.

 

PREVIOUS POST
NEXT POST
Vadim Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.

6 Comments

  • The overhead in MySQL 5.6 is code that isn’t efficient with concurrency. The overhead in MySQL 5.7 is a longer code path — which is most obvious at low concurrency, but also means you run out of CPU sooner at high concurrency. Your graphs show that the improvements in 5.7 don’t always outweigh the costs.

    • Having results above, and in this post https://www.percona.com/blog/2016/03/17/percona-server-5-7-performance-improvements/ , it seems that using innodb_thread_concurrency in range [N;N*2], where N is number of logical CPU threads is beneficial for vanilla MySQL 5.6 and even 5.7

Leave a Reply