Insert benchmark: MySQL 5.6, 5.7 & 8.0 on a small server

I have another performance report for the insert benchmark using MySQL 5.6, 5.7 and 8.0 on a small server. As normal, I had to repeat the tests a few times: I make mistakes and also want results for new my.cnf settings. The goal for the tests is to understand how low-concurrency performance changes in new MySQL releases. Low-concurrency tests are great for finding CPU regressions. High-concurrency tests are also great but I only have small servers at home.

For the CPU-bound setup, new MySQL (8.0) gets between 10% and 37% less throughput than old MySQL (5.6) because 8.0 uses more CPU/query. The increase in CPU/query for MySQL from 5.6 to 8.0 is large while PostgreSQL avoids CPU regressions from 12.4 to 14.0.

Disclaimers:

• Be careful about interpreting these results, the context here is a workload with simple queries.
• These results have yet to be reviewed by others. There can be mistakes.
• 8.0.20 and 8.0.22 were exciting releases. Performance is slowly improving since then.

Details

Tests were run for MySQL versions 5.6.49, 5.7.35, 8.0.20, 8.0.22, 8.0.23, 8.0.26 and 8.0.27. I try to use similar my.cnf settings except when new versions have new features. The options are in the my56, my57 and my80 directories here (look for my.cnf.cy8). Scripts to build MySQL from source are here for MySQL 5.6, 5.7 and 8.0. One of the big changes I made was to add innodb_log_writer_threads=OFF to the MySQL 8.0 my.cnf settings. Without that the CPU overhead was much worse.

The test servers have 4 cores and tests were run with either 1 insert connection or 2 connections (rate limited inserts, not rate-limited queries). This page has links to explain the insert benchmark and perf report format. The test was run in CPU-bound and IO-bound modes. For each mode, X rows were inserted into a table without a secondary index, then 3 secondary indexes were created, then Y more rows were inserted. The values for X and Y were (20M, 20M) for CPU-bound and (500M, 10M) for IO-bound. After the inserts there were three insert+query steps where the insert connection was rate-limited to 100, 500 and 1000 inserts/s. Each query step was run for 2 hours.

The test server uses Ubuntu 20.04, gcc-9.3.0-17 and uname -a shows 5.4.0-89-generic. The database filesystem uses XFS with discard enabled. 

Results

I have shell scripts to generate perf reports. The reports for CPU-bound and IO-bound are here and here. Below I use throughput ratios where the denominator is the value for MySQL 5.6.49 and the numerator is the value for another version. A value less than 1.0 means the new version is slower than 5.6.49.

Comments on CPU-bound (see the summary):

• for the l.i0 test (inserts without secondary indexes) the throughput ratio is 0.63 for 8.0.27. The slowdown is explained by an increase in CPU/query. See the cpupq column here that is 15 for 5.6.49 and 21 for 8.0.27.
• for the l.i1 test (inserts with secondary indexes) the throughput ratio is 0.73 for 8.0.27. The slowdown is explained by an increase in CPU/query. See the cpupq column here that is 32 for 5.6.49 and 43 for 8.0.27.
• for the q100.1, q500.1 and q1000.1 tests the throughput ratios using the qps columns are 0.81, 0.76 and 0.74 for 8.0.27. This is probably explained by more CPU/query. See the cpupq columns for q100.1, q500.1 and q1000.1. The throughput ratios were much better for 8.0.22 than for 8.0.27 (0.94, 0.89 and 0.86) but I have not attempted to debug that. I was unable to explain why the wkbpi column is larger for 8.0.27 than 5.6.49 and larger means more write-amp (KB written/insert). It was larger for 8.0.27 when measured by iostat, but smaller when measured by global status counters like Innodb_dblwr_pages_written, Innodb_os_log_written, Innodb_data_written and Innodb_buffer_pool_pages_flushed. This remains a mystery. I am not sure it is possible to configure InnoDB writeback in 8.0 to match 5.6. The only difference in the my.cnf settings is the use of innodb_idle_flush_pct=1 for the 8.0 my.cnfs.

Comments on IO-bound (see the summary):

• for the l.i0 test (inserts without secondary indexes) results are similar to the CPU-bound results above because this step is still CPU-bound, 8.0 is slower because it uses more CPU/query. See here.
• for the l.x test (create index) 8.0 is faster than 5.6. I think significant changes were done for InnoDB create index. See here.
• for the l.i1 test (inserts with secondary indexes) results for 8.0.26 & 8.0.27 are better than 5.6.49, but results for 8.0.20 and 8.0.22 and 8.0.23 is in between. With the new release process there can be significant changes for performance between point releases. See the ips column here. I think this is related to the big changes to redo log code in 8.0 as CPU/query (cpupq) and context switches/query (cspq) were larger in 8.0.20 and 8.0.22.
• for the q100.1, q500.1 and q1000.1 tests the throughput ratios are larger than 1 as 8.0 is faster than 5.6, except for 8.0.20 which appears to be an exciting release. See the qps and cpupq columns here. MySQL 8.0.27 uses less CPU/query than 5.6.49.