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.