In-memory insert benchmark for InnoDB on a small server: MySQL 5.0 to 8.0

This post explains performance for the insert benchmark with InnoDB from MySQL versions 5.0 to 8.0. The goal is to understand how performance has changed across releases. This uses an in-memory workload with an i3 NUC and i5 NUC. The i5 NUC is newer and faster. The next post covers an IO-bound test for InnoDB.

tl;dr - from 5.0.96 to 8.0.3

• Regressions are frequently larger on the i3 NUC than the i5 NUC. Maybe modern MySQL and the core i3 NUC aren't great together because regressions are also larger on the i3 NUC for MyISAM.
• Insert performance improved by ~3X after MySQL 5.5 when I switched from built-in InnoDB to modern InnoDB.
• The query rate decreased by 15% on the i5 NUC and 39% on the i3 NUC for the test with 100 inserts/second

tl;dr - from 5.6.35 to 8.0.3

• Most of the drop in performance from 5.0 to 8.0 occurs between 5.6.35 and 8.0.3. The drop is similar for MyISAM and InnoDB. I assume the drop is from code above the storage engine.
• The insert rate decreased by 12% on the i5 NUC and 26% on the i3 NUC
• The query rate decreased by 13% on the i5 NUC and 22% on the i3 NUC for the test with 100 inserts/second
• Scan performance improved

Configuration

The tests used InnoDB from upstream MySQL versions 5.0.96, 5.1.72, 5.5.51, 5.6.35, 5.7.17 and 8.0.3. The built-in InnoDB (remember that?) was used for 5.0, 5.1 and 5.5 and then I switched to modern InnoDB starting with 5.6. All tests used jemalloc with mysqld. The i3 and i5 NUC servers are described here. The insert benchmark is described here. The my.cnf files are here for the i3 NUC and i5 NUC. The i5 NUC has more RAM, faster CPUs and faster storage than the i3 NUC. I tried to tune my.cnf for all engines including: disabled SSL for MySQL 5.7, used the same charset and collation and set innodb_purge_threads=1 to reduce mutex contention. For all tests the binlog was enabled but fsync was disabled for the binlog and database redo log. I compiled all of the MySQL versions on the test servers and did not use binaries from upstream.

The database fits in RAM as the test table has ~10M rows. The i3 NUC has 8gb of RAM and the i5 NUC has 16gb. The insert benchmark loaded the table with 10M rows, then did a full scan of each index on the table (PK, 3 secondary, PK again), then two read-write tests. The first read-write test tries to insert 1000 rows/second with one client while the other client does short range scans as fast as possible. The second read-write test is similar except the insert rate limit is 100/second.

Results

All of the data for the tests is here.

Results: load

The graph below has the insert rate for each release relative to the rate for InnoDB in 5.0.96. Performance improves dramatically in 5.6 thanks to switching from the built-in InnoDB to modern InnoDB. There are regressions post-5.6 that I hope get addressed.

Additional metrics help to explain performance. The metrics are explained here. With the switch from built-in InnoDB (5.0 to 5.5) to modern InnoDB (5.6, 5.7, 8.0) write-amplification (wKB/i) improved on the i3 and i5 NUC while CPU overhead per insert (Mcpu/i) decreased on the i5 NUC (i3 NUC results are odd). From 5.6 to 8.0 the insert rate dropped by 26% on the i3 NUC and 12% on the i5 NUC. The larger CPU overhead (Mcpu/i) probably explains this on the i3 NUC. It is harder to explain on the i5 NUC but note that the CPU overhead includes foreground (user threads) and background (write back, purge). In my previous post the regression for MyISAM was ~10% on both the i3 and i5 NUC. So the InnoDB regression is similar for the i5 NUC but not the i3 NUC. From this result and others I suspect the regression is from code that runs above InnoDB.

i3 NUC
        IPS     wKB/i   Mcpu/i  size(GB)
5.0.96   4803   35.75   3069    2.5
5.1.72   4737   35.71   3129    2.5
5.5.51   4643   38.03   3427    2.5
5.6.35  18797    8.87   1737    2.4
5.7.17  15083   10.36   3497    2.4
8.0.3   13908   11.02   3783    2.4

i5 NUC
        IPS     wKB/i   Mcpu/i  size(GB)
5.0.96   8368   35.69   2643    2.5
5.1.72   8326   35.70   2713    2.5
5.5.51   7628   37.98   2972    2.5
5.6.35  29586    8.06   1461    2.4
5.7.17  25907    9.66   1491    2.4
8.0.3   26178    9.60   1474    2.5

Results: scan

Below are tables that show the number of seconds for each full index scan: 1 is the PK, 2/3/4 are the secondary indexes and 5 is the PK again. The scan doesn't take long and the result is rounded to a whole number so the numbers aren't that useful. If there is a regression from 5.0 to 8.0 it isn't apparent in this result. Scan performance got worse from 5.5.51 (builtin InnoDB) to 5.6.35 (modern InnoDB). But then it improved in 5.7.17 for the i5 NUC and for many other tests I do, but for some reason it didn't on the i3 NUC.

#seconds to scan an index, i3 NUC
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
6       3       2       3       6        8      5.0.96
3       3       2       3       3        8      5.1.72
3       3       3       3       3        9      5.5.51
4       4       4       4       4       12      5.6.35
3       4       7       3       3       14      5.7.17
4       4       3       4       3       11      8.0.3

#seconds to scan an index, i5 NUC
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
5       2       2       2       5        6      5.0.96
3       2       3       2       2        7      5.1.72
3       2       3       3       2        8      5.5.51
3       4       3       4       3       11      5.6.35
3       3       3       2       3        8      5.7.17
2       3       3       3       3        9      8.0.3

Results: read-write, 1000 inserts/second

This section has results for the read-write tests where the writer does 1000 inserts/second. The graph has the query rate relative to the rate for MySQL 5.0.96. The QPS regression from MySQL 5.0.96 to 8.0.3 is 41% for the i3 NUC and 19% for the i5 NUC. Most of the regression occurs after 5.6.35. The large regression on the i3 NUC is another example of odd results for the i3 NUC. The regression was much smaller for MyISAM in my previous post.

The built-in InnoDB used for 5.0, 5.1 and 5.5 was unable to sustain the target insert rate. Write-amplification was also larger with built-in InnoDB. But it was able to sustain a larger QPS. QPS drops by 25% on the i3 NUC and 12% on the i5 NUC from 5.6.35 to 8.0.3. Write stalls were worse for built-in InnoDB, but even modern InnoDB had some stalls given the values in IPS.99 that are less than 999. Additional metrics help explain the performance. The  metrics are explained here. The CPU overhead per query (CPU/q) more than doubles on the i3 NUC from 5.6.35 to 5.7.17. That helps to explain the QPS reduction. The CPU overhead per query increased on the i5 NUC by 11% from 5.6.35 to 8.0.3.

i3 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
992     770     64.13   5675    5277     5332   5.0.96
992     731     64.03   4990    4863     5910   5.1.72
998     943     67.66   4364    4249     6845   5.5.51
999     994     43.90   4817    4454     5970   5.6.35
999     995     40.27   3748    3540    13834   5.7.17
999     995     41.32   3527    3349    14750   8.0.3

i5 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
999     993     64.31   6183    5534    4776    5.0.96
998     963     64.05   5646    5594    5118    5.1.72
999     993     70.40   5284    5052    5591    5.5.51
999     998     44.12   5798    5410    4846    5.6.35
999     998     40.62   5184    4917    5186    5.7.17
999     998     41.23   4995    4763    5389    8.0.3

Results: read-write, 100 inserts/second

This section has results for the read-write tests where the writer does 100 inserts/second. The graph has the query rate relative to the rate for MySQL 5.0.96. The regression from MySQL 5.0.96 to 8.0.3 is 39% on the i3 NUC and 15% on the i5 NUC. The regression from 5.6.35 to 8.0.3 is 22% for the i3 NUC and 13% for the i5 NUC. On the i5 NUC the CPU overhead per insert, Mcpu/i, increased by 18% from 5.0 to 8.0 and by 16% from 5.6 to 8.0 which probably explains the decrease in the insert rate.

All of the engines were able to sustain the target insert rate on average (ips.av) and write stalls were not a problem. Additional metrics help explain the performance. The  metrics are explained here. 

i3 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
100     100     67.34   6058    5877     4238   5.0.96
100     100     67.38   5033    4891     5013   5.1.72
100     100     98.90   4465    4274     5766   5.5.51
100     100     51.85   4729    4499     5457   5.6.35
100     100     52.94   3906    3717    12636   5.7.17
100     100     61.08   3681    3524    13407   8.0.3

i5 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
100     100     67.54   6062    5543    4211    5.0.96
100     100     67.25   5655    5586    4439    5.1.72
100     100     98.98   5189    5035    4931    5.5.51
100     100     51.95   5937    5548    4309    5.6.35
100     100     55.24   5349    4976    4803    5.7.17
100     100     54.07   5148    4819    4978    8.0.3