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

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.

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569