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:
- MySQL 5.7 sysbench OLTP read-only results: is MySQL 5.7 really faster?
- MySQL 5.7 primary key lookup results: is it really faster?
- ProxySQL versus MaxScale for OLTP RO workloads
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:
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):
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.