This 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:
- https://github.com/Percona-Lab/benchmark-results/blob/201603-mysql55-56-57-RO/local-OLTP/summary/local-OLTP.md
- https://github.com/Percona-Lab/benchmark-results/blob/201603-mysql55-56-57-RO/remote-OLTP/summary/remote-OLTP.md
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.
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.
The innodb_thread_concurrency=64 was for your test scenario or are you suggesting that as a general setting
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
Make bench also with Percona 5.6/5.7
Does Percona Server 5.7 have the same problem as MySQL 5.7? Does it even make sense to upgrade to Percona 5.7?
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.