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

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

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

Share this post

Comments (6)

  • Mark Callaghan Reply

    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.

    April 7, 2016 at 11:12 am
  • shamim Reply

    The innodb_thread_concurrency=64 was for your test scenario or are you suggesting that as a general setting

    April 7, 2016 at 11:34 am
  • Denis Raguzov Reply

    Make bench also with Percona 5.6/5.7

    April 8, 2016 at 5:43 pm
  • Andy Reply

    Does Percona Server 5.7 have the same problem as MySQL 5.7? Does it even make sense to upgrade to Percona 5.7?

    April 9, 2016 at 6:02 am
    • Mark Callaghan Reply

      Slightly worse performance on sysbench read-only is something for the community to improve in MySQL 5.7 and 5.8. But this shouldn’t keep you from upgrading.

      April 9, 2016 at 12:59 pm

Leave a Reply