Friday, June 16, 2017

Sysbench for MySQL 5.0, 5.1, 5.5, 5.6, 5.7 and 8

Update - the regression isn't as bad as I have been reporting. Read this post to understand why.

After sharing results for in-memory sysbench with MySQL 5.6, 5.7 and 8 I was curious about older releases and here I have results for MySQL 5.0, 5.1 and 5.5 in addition to 5.6, 5.7 and 8. This is one more result in my series on low-concurrency performance regressions.

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.
For update-nonindex the biggest drop in QPS is from 5.6 to 5.7. There is also a big drop from 5.1 to 5.5, but that is fixed in 5.6. Looks like 5.5 was a lousy release for performance.
For read-write.range100 the biggest drop in QPS is from 5.6 to 5.7.
For read-write.range10000 the biggest drop in QPS was from 5.6 to 5.7.
For read-only.range10 the biggest drop in QPS is from 5.6 to 5.7.
For read-only.range10000 the biggest drop in QPS is from 5.6 to 5.7.
For point-query the biggest drop in QPS is from 5.6 to 5.7.
For insert the QPS regression is small.

11 comments:

  1. 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.

    ReplyDelete
    Replies
    1. Oracle 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.

      Delete
  2. I'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.

    To 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.

    ReplyDelete
    Replies
    1. 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.

      Delete
  3. Readers 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.

    ReplyDelete
    Replies
    1. I 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.

      Delete
  4. The 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.

    ReplyDelete
    Replies
    1. Not yet, first I will take a refresher course from http://www.brendangregg.com/ and then I will be ready.

      Delete
    2. It is very easy, ping me if you have issues.

      Delete
  5. Can 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

    ReplyDelete
    Replies
    1. This 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.

      I used built-in InnoDB for 5.0, 5.1 and 5.5 because it was much easier to compile and install.

      Delete