MySQL 5.7 read-write benchmarks

In this post, we’ll look at the results from some MySQL 5.7 read-write benchmarks.

In my past blogs I’ve posted benchmarks on MySQL 5.5 / 5.6 / 5.7 in OLTP read-only workloads. For example:

Now, it is time to test some read-write transactional workloads. I will again use sysbench, and my scripts and configs are available here: https://github.com/Percona-Lab-results/201605-OLTP-RW.

A short description of the setup:

  • The client (sysbench) and server are on different servers, connected via 10Gb network
  • CPU: 56 logical CPU threads servers Intel(R) Xeon(R) CPU E5-2683 v3 @ 2.00GHz
  • sysbench ten tables x 10mln rows, Pareto distribution
  • OS: Ubuntu 15.10 (Wily Werewolf)
  • Kernel 4.2.0-30-generic
  • The storage device is Samsung SM863 SATA SSD, single device, with ext4 filesystem
  • MySQL versions: 5.7.12 , 5.6.30 and 5.5.48

InnoDB holds all data in memory, and InnoDB log files are big enough, so there are only IO writes (which happen in the background) and there is no pressure from InnoDB on the IO subsystem.

The results looked like the following:

MySQL 5.7 read-write benchmarks

The vertical line shows the variability of the throughput (standard deviation).

To show the difference for a lower numbers of threads, here is chart with relative performance normalized by MySQL 5.7 (MySQL 5.7 = 1 in the following chart):

MySQL 5.7 read-write benchmarks

So we can finally see significant improvements in MySQL 5.7, it scales much better in read-write workloads than previous versions.

In the lower numbers of threads, however, MySQL 5.7 throughput is still behind MySQL 5.5 and MySQL 5.6, this is where slower single thread performance in MySQL 5.7 and longer execution paths show themselves. The problem with low threads read-write performance is replication. I wonder how a slave 5.7 performs comparing to 5.6 slave – I am going to run this benchmark soon.

Another point to keep in mind is that we still see a “bell shape,” even for MySQL 5.7. After 430 threads, the throughput drops off a cliff. Despite Oracle’s claims that there is no need for a thread pool anymore, this is not the case – I am not able to prevent a throughput drop using magic tuning with innodb_thread_concurrency and innodb_spin_wait_delay. No matter what, MySQL 5.7 is not able to maintain throughput on a high amount of threads (1000+) for this workload.

What can be done in this case? I have two solutions: Percona Server with thread pool functionality, or ProxySQL with connection multiplexing. I will show these results in the next post.

 

 

 

Share this post

Comments (7)

  • Peter Zaitsev

    Vadim,

    Generally the drop of performance with high amount of threads can come from internal contention (which can be fixed by internal optimizations) as well as by row locks, which mainly depends on what transactions you have in flight. Have you looked into how much row lock waits is happening for this workload ? With Pareto distribution I would expect fair amount.

    Managing transaction flow efficiently in such case is an interesting problem on its own as suspending thread while it hold row level rocks is a bad idea. I wonder if

    – Does innodb_thread_concurrency prioritize threads which have row level locks ?
    – What do Percona Thread Pool (or any other) does in this case ?
    – How does ProxySQL handle it ?

    May 17, 2016 at 1:36 pm
  • David Wrench

    Would partitioning the tables and setting transaction isolation to serializable help, as long as there were no cross partition accesses?

    May 17, 2016 at 5:03 pm
    • Vadim Tkachenko

      David,

      As this is pareto distribution (80% of requests hit 20% of the same data), I do not think partitioning will help, as there is contention for the “hot” data.

      May 19, 2016 at 6:49 pm
  • jk

    why use SM863? as I konw, SM863 is good at seq read & write, and PM863 is good at random read & write.

    May 22, 2016 at 11:48 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.