tl;dr
- MySQL 4.1 and 5.5 weren't great releases for performance. I skipped both in production.
- The biggest drop in QPS between releases is from 5.6 to 5.7 and that drop often exceeds the drop from 5.0 to 5.6. What happened? Bug 86215 is open for this.
Configuration
I tested MySQL using upstream 5.0.96, 5.1.72, 5.5.51, 5.6.35, 5.7.17 and 8.0.1. For 8.0.1 I used the latin1 charset and latin1_swedish_ci collation. My servers are described here but I only share results for the i5 NUC.
I was able to compile and run MySQL 4.1.22 on the same server but have yet to share the results. The results weren't good and that matches my memory of 4.1 not being a great release. MySQL 4.0 was an awesome release but I have yet to get it running on Ubuntu 16.04 with gcc 4.7 or 4.8. There are segfaults soon after startup.
My usage of sysbench, including the tests run, and the my.cnf used for each database engine is described here for MySQL 5.6, 5.7 and 8. The my.cnf files I share were for the i3 NUC. For the i5 NUC the InnoDB buffer pool and IO capacity options were increased using these values. The my.cnf files are here for 5.0, 5.1 and 5.5. I use the same server for mysqld and the sysbench clients. The binlog is enabled but sync-on-commit is disabled. Sysbench is run with 4 tables and 1M rows per table for 1, 2 and 4 concurrent clients. The database fits in the InnoDB buffer pool.
Results
The QPS for all tests is here. Charts for some of the tests are below.
The table below lists the QPS relative to MySQL 5.0 for each test. When the value is 0.53 (see update-index for MySQL 8) then MySQL 8 gets 53% of the QPS compared to MySQL 5.0 and 5.0 is almost 2X faster. As reported in previous posts, the regression from 5.6 to 5.7 is large. Fortunately that performance loss hasn't been repeated from 5.7 to 8.
This gives me hope. At first I thought the problem was a steady loss of performance in each major release as features are added and code paths get longer. But now it looks like most of the problem arrived with MySQL 5.7. Maybe we can fix this.
5.0 5.1 5.5 5.6 5.7 8 release/test
---- ---- ---- ---- ---- ----
1.00 0.89 0.73 0.73 0.49 0.53 update-index
1.00 0.89 0.59 0.72 0.41 0.44 update-nonindex
1.00 0.92 0.99 1.04 0.62 0.60 read-write.range100
1.00 0.95 0.92 0.85 0.50 0.49 read-write.range10000
1.00 0.92 0.89 0.92 0.56 0.52 read-only.range10
1.00 0.95 0.92 0.84 0.50 0.50 read-only.range10000
1.00 0.85 0.77 0.76 0.45 0.42 point-query
1.00 0.95 0.93 1.22 0.84 0.82 insert
Charts
For update-index the biggest drop in QPS is from 5.6 to 5.7.
I know I shouldn't think like this, but a small part of me always wonders whether Oracle is attempting to sabotage MySQL Community Edition.
ReplyDeleteOracle has been the best corporate owner of MySQL -- better than Sun, better than when MySQL was standalone. Releases are regular, features arrive and usually in good shape, bugs get fixed. It wasn't until Oracle took over that we finally got improvements to replication and without those improvements MySQL would have been done.
DeleteI've seen the same trend on a variety of hardware and OS configurations and agree there are very evident performance issues in 5.7 which are not related to tuning.
ReplyDeleteTo be fair, we should also consider the minor version numbers of all the releases benchmarked. MySQL 5.7 is only at minor version 17 while the older versions are at 96 (5.0), 72 (5.1), 51 (5.5) & 35 (5.6). If we compared older versions of 5.0 / 5.1 / 5.5 I'm quite confident we'd see a performance drop of sorts.
5.7 introduces a fair share of new features and as features are added the code that handles them can become more complex leading to performance issues. I'm quite confident that these will be addressed in time however currently I feel that although 5.7 is GA & stable its not quite reached the level of maturity needed.
You are more optimistic than I am about the potential for performance improvements in minor releases. I assume any improvements will come in the current development release (8.x) so we have to move fast on bug 86215 before it reaches GA.
DeleteReaders should consider this is talking about a single threaded workload. MySQL 5.7 is far superior to previous releases if you have a highly concurrent workload. In other words, if you are new to MySQL, don't let this article discourage you from installing later releases.
ReplyDeleteI agree this shouldn't prevent someone from trying MySQL 5.7 -- as a new user or an upgrade but not because it does so much better on highly concurrent workloads. The great numbers we see in the official presentations from upstream are for levels of concurrency that don't matter to most users. I assume that even this CPU regression doesn't matter to most users. But I also expect this CPU regression to be a big problem for web-scale MySQL users.
DeleteThe code changed quite a lot since 5.0.x, much more object oriented. Have you investigated where the time is spent with something like perf/flamegraph? That could help explaining the differences.
ReplyDeleteNot yet, first I will take a refresher course from http://www.brendangregg.com/ and then I will be ready.
DeleteIt is very easy, ping me if you have issues.
DeleteCan you verify the results are not from the default innodb file format change from antelope to barracuda? The default change took place in 5.7
ReplyDeleteThis post has a link to http://smalldatum.blogspot.com/2017/02/using-modern-sysbench-to-compare.html which has links to my.cnf for MySQL 5.6, 5.7 and 8. I used barracuda for 5.6, 5.7 and 8 so that doesn't explain regression from 5.6 to 8.
DeleteI used built-in InnoDB for 5.0, 5.1 and 5.5 because it was much easier to compile and install.
Loving the info on this website , you have done outstanding job on the blog posts.
ReplyDeleteCual vecion es mejor 8.0
ReplyDeletemysql 8 es genial
Delete