The Insert Benchmark, MySQL 5.6, 5.7, 8.0 and a big server

This has results for MySQL vs the insert benchmark on a big server. Results from a small server are here.

The goals are to look for performance changes from MySQL 5.6 to 8.0 and to determine the impact of compiler optimizations. The context here is a cached workload (reads from cache, writes to storage) with high concurrency.

tl;dr (still long but the results vary a lot):

• It is possible that QPS on the read+write benchmark steps (q100.1, q500.1, q1000.1) suffer from bug 109595.
• I accept that tuning might be required, but I am also wary of a DBMS that requires too much per-workload tuning.
• The insert rate for l.i1 drops from (~400k/s, 180k/s) when the database is cached by InnoDB to (~14k/s, ~25k/s) when the database is cached by the OS but not by InnoDB. The two numbers are for (20 tables, 1 table). While a reduction is expected when not cached by the database the drop is much larger for InnoDB than for Postgres.
• The o3_rel_lto build has the best performance but that usually means <= 3% better than others. The benefit for link-time optimization here is much less than on the small server perhaps because the big server has different overheads from higher concurrency.
• Max response times are mostly significantly worse in 8.0 than 5.6.
• For a database cached by InnoDB
• InnoDB was able to sustain the target background write rate for q100.1 (2000/s), q500.1 (10,000/s) and q1000.1 (20,000/s).
• With a table per client
• Write performance improved from 5.6 to 5.7 and again from 5.7 to 8.0 so that 8.0 gets 2X to 3X more throughput than 5.6. But within 8.0 there are regressions and 8.0.32 is up to 5% slower than 8.0.22.
• Read performance gets worse from 5.6 to 5.7 and again from 5.7 to 8.0 so that 5.7 gets ~10% less QPS than 5.6 while 8.0 gets ~30% less QPS than 5.6.
• With one table
• Write - vs 5.6 version 5.7 gets ~1.25X more throughput and 8.0 gets ~1.15X more
• Read - results are similar to table per client above
• For a database not cached by InnoDB
• InnoDB was unable to sustain the target background write rate in some cases and this means it failed the benchmark SLA. In all of these failures the background write rate for 8.0 is worse than for 5.6.
• It was never able to sustain the rate for the q1000.1 benchmark step for both the 20 table and 1 table configurations.The target was 20,000/s and InnoDB got between ~10,000/s and ~15,000/s. 
• It was not able to sustain the rate for the q500.1 benchmark step for the 20 table case with 8.0. The target is 10,000/s and < ~9800/s is  a failure so 5.7 passed and 5.6 almost passed. Note that with the 1 table test 5.7 and 8.0 pass but 5.6 fails.
• With a table per client
• Write - results are similar to database cached by InnoDB above
• Read - with a low background write rate (20 X 100 /s, see q100.1) 8.0 is ~5X faster than 5.6 but when it is higher (20 X 1000/s, see q1000.1) then 8.0 is only ~1.2X faster than 5.6
• With one table
• Write - MySQL 8.0 throughput is 10% to 20% less than 5.6
• Read - the comparison with 5.6 depends on the background write. For q100.1 (20 X 100 /s) then 8.0 gets ~3X more QPS vs 5.6. And then for q500.1 (20 X 500 / s) then 8.0 gets ~1.3X more QPS vs 5.6. But when it is highest (20 X 1000 / s) then 8.0 gets ~10% less QPS vs 5.6.

Benchmarks

The server is a c2-standard-60 from GCP with 30 cores, hyperthreads disabled, 240G RAM and 3T of NVMe (XFS, RAID 0 across 8 devices). Huge pages were enabled for all benchmark runs.

An overview of the insert benchmark is here and here. The insert benchmark was run for a cached database with 20 clients. The read+write steps (q100.1, q500.1, q1000.1) were run for 1800 seconds each.

Benchmarks were repeated for two configurations:

• cached by InnoDB - all data fits in the InnoDB buffer pool and vm.nr_hugepages was 85000
• cached by OS - all data fits in the OS page cache but not the InnoDB buffer pool. The buffer pool size is 4G and the database was ~180G at test end. For huge pages I set vm.nr_hugepages to 4000.

The configuration files are here for:

• cached by InnoDB: versions 5.6, 5.7, 8.0.2x, 8.0.3x
• cached by OS: versions 5.6, 5.7, 8.0.2x, 8.0.3x

The benchmark is a sequence of steps:

• l.i0 - insert 20 million rows without secondary indexes
• l.x - create 3 secondary indexes. I usually ignore results from this step.
• l.i1 - insert another 20 million rows with the overhead of secondary index maintenance
• q100.1 - do queries as fast as possible with 100 inserts/s/thread done in the background
• q500.1 - do queries as fast as possible with 500 inserts/s/thread done in the background
• q1000.1 - do queries as fast as possible with 1000 inserts/s/thread done in the background

Builds

I compiled MySQL from source with several build variations. Details are here. All builds are with upstream MySQL and gcc.

Reports

Performance summaries generated by shell scripts are below. A short guide to these results is here. There are three types of reports:

• version 8.0.31 - results for MySQL 8.0.31 using different compiler optimizations
• version 8.0 - results for MySQL 8.0.22, 8.0.28, 8.0.31 and 8.0.32
• versions 5.6 to 8.0 - results for MySQL versions 5.6.51, 5.7.40, 8.0.22, 8.0.28, 8.0.28, 8.0.31 and 8.0.32

For each type of report there are two variations:

• 20 clients, 20 tables - used 20 client & 20 tables with a client per table.
• 20 clients, 1 table - used 20 clients & 1 table

Reports for cached by InnoDB:

• 20 clients, 20 tables: version 8.0.31, version 8.0, versions 5.6 to 8.0
• 20 clients, 1 table:  version 8.0.31, version 8.0, versions 5.6 to 8.0

Reports for cached by OS:

• 20 clients, 20 tables: version 8.0.31, version 8.0, versions 5.6 to 8.0
• 20 clients, 1 table: version 8.0.31 (missing), version 8.0, versions 5.6 to 8.0

Response time

Charts with throughput and response time per 1-second intervals are here. First, the disclaimers:

• The graphs are from just one of the 20 clients. 
• I risk drawing strong conclusions from small samples.
• It is risky to compare the results for l.i0 and l.i1 between MySQL 5.6 and 8.0 because 8.0 sustains higher insert rates so that comparison wouldn't be fair.
• In the cached by OS configurations MySQL 8.0 isn't able to sustain the target rate for background writes in q1000.1 (20 X 1000 /s) and sometimes in q500.1 (20 X 500 /s). This means that InnoDB failed the benchmark SLA and a comparison with MySQL 5.6 isn't fair.

The charts:

• cached by InnoDB
• 20 clients, 20 tables: l.i0, l.i1, q100.1, q500.1, q1000.1
• For l.i0 and l.i1 the variance in response time and insert rates is worse for 8.0 than 5.6, but note that 8.0 sustains higher insert rates so this isn't a fair comparison.
• 20 clients, 1 table:  l.i0, l.i1, q100.1, q500.1, q1000.1
• Results are similar to the 20 clients, 20 tables results above. The l.i1 results have more variance than l.i0.
• cached by OS: 
• 20 clients, 20 tables: l.i0, l.i1, q100.1, q500.1, q1000.1
• Results are similar to the 20 clients, 20 tables results above. The l.i1 results have more variance than l.i0.
• For q100.1 the insert rate with 8.0 has more variance than 5.6. For q500.1 the insert rate with 8.0 has a lot more variance than 5.6. And the difference is even more stark for q1000.1. See the disclaimers above, the 8.0 configs failed to sustain the target rate for background writes.
• 20 clients, 1 table:  l.i0, l.i1, q100.1, q500.1, q1000.1
• For l.i1 the per-second insert rate drops over time for 5.6, 5.7 and 8.0
• See the disclaimers above, the 8.0 configs failed to sustain the target rate for background writes.

Up next are tables with response time details per benchmark step -- both histograms and the max. Note that ms means milliseconds, by insert I mean a multi-row insert statement and the use of brackets ([]) below indicates the range of the max response time over multiple results (1 result per build). It can be risky to draw strong inferences from a small sample (one run per build).

• cached by InnoDB
• 20 clients, 20 tables:
• l.i0 - the max is [218ms, 310ms]. The histogram improves from 5.6 to 8.0.
• l.i1 - the max grows from ~500ms in 5.6 to ~1200ms in 8.0. The response time histogram improves from 5.6 to 8.0.
• q100.1 - the max is [19ms, 28ms] for queries and [19ms, 30ms] for inserts 
• q500.1 - the max is [24ms, 30ms] for queries and [40ms, 54ms] for inserts
• q1000.1 - the max is [26ms, 36ms] for queries and [49ms, 57ms] for inserts
• For q100.1, q500.1 and q1000.1 the insert response time histogram improves from 5.6 to 8.0.
• 20 clients, 1 table:
• l.i0 - the max is [228ms, 270ms].
• l.i1 - the max drops from 773ms in 5.6.51 to 279ms in 8.0.32.
• q100.1 - the max is [23ms, 28ms] for queries and [19ms, 31ms] for inserts. 
• q500.1 - the max is [24ms, 28ms] for queries and [46ms, 52ms] for inserts.
• q1000.1 - the max is [24ms, 41ms] for queries and [50ms, 119ms] for inserts.
• For q100.1, q500.1 and q1000.1 the insert response time histogram gets worse from MySQL 5.7 to 8.0.
• cached by OS
• 20 clients, 20 tables:
• l.i0 - the max is [238ms, 313ms]. The response time histogram improves from 5.6 to 8.0.
• l.i1 - the max is [1160ms, 1297ms]. The response time histogram improves from 5.6 to 8.0.
• q100.1 - the max grows from ~100ms in 5.6 to ~600ms+ in 8.0 for queries and grows from ~600ms in 5.6 to >= 1500ms for 8.0.
• q500.1 - the max grows from ~200ms in 5.6 to >= ~1000ms in 8.0 for queries and from ~400ms in 5.6 to >= 1500ms in 8.0 for inserts. InnoDB in 8.0 was unable to sustain the target insert rate of 10,000/s.
• q1000.1 - the max grows from ~200ms in 5.6 to >= ~1000ms in 8.0 for queries and from ~500ms in 5.6 to >= 1500ms in 8.0 for inserts. InnoDB in 5.6, 5.7 and 8.0 was unable to sustain the target insert rate of 20,000/s.
• 20 clients, 1 table:
• l.i0 - the max is [231ms, 262ms].
• l.i1 - the max grows from ~1000ms in 5.6 to ~1200ms in 8.0.
• q100.1 - the max grows from 75ms in 5.6 to ~200ms in 8.0 for queries and grows from ~400ms in 5.6 to >= 600ms in 8.0 for inserts.
• q500.1 - the max grows from ~100ms in 5.6 to ~270ms in 8.0 for queries and grows from ~500ms in 5.6 to ~1000ms in 8.0 for inserts. InnoDB in 5.6 was unable to sustain the target insert rate of 10,000/s
• q1000.1 - the max grows from 90ms in 5.6 to ~400ms in 8.0 for queries and grows from 548ms in 5.6 to >= 1200ms in 8.0 for inserts. InnoDB in 5.6, 5.7 and 8.0 was unable to sustain the target insert rate of 20,000/s.