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

This has results for MySQL vs the insert benchmark on a small server. Results from a big 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 low concurrency and a small server.

tl;dr

• The rel_native_lto build has the best performance which is usually <= 4% better than the base case except for the l.x (create index) benchmark step.
• QPS on the read+write benchmark steps (q100.1, q500.1, q1000.1) suffer from bug 109595. Fixing b109595 would increase QPS by ...
• ~1.25X for the cached by InnoDB setups
• (~2X, ~6X and ~6X) for q100.1, q500.1, q1000.1) for the cached by OS setups. To be clear, I mean that QPS for q500.1 and q1000.1 is about 1/6th of what it could be if this bug were fixed.
• For the cached by InnoDB setup with one client (connection, thread) the throughput for 8.0.32 is between 56% and 63% of 5.6.51. Most of the perf loss is from new CPU overhead.

Benchmarks

An overview of the insert benchmark is here and here. The insert benchmark was run for a cached database with both 1 and 4 clients. For 1 client the benchmark used 1 table. For 4 clients the benchmark was first run with 4 tables (client per table) and then again with 1 table (all clients shared the table). The read+write steps (q100.1, q500.1, q1000.1) were run for 1800 seconds each.

The benchmark server is a Beelink SER 4700u described here with 8 AMD cores, 16G of RAM and 1T of NVMe SSD. The OS is Ubuntu 22.04 and the filesystem is XFS.

Benchmarks were repeated for two configurations:

• cached by InnoDB - all data fits in the InnoDB buffer pool
• cached by OS - all data fits in the OS page cache but not the InnoDB buffer pool. The buffer pool size is 1G and the database was ~10G at test end. 

The configuration files are here for:

• cached by InnoDB: versions 5.6, 5.7, 8.0
• cached by OS: versions 5.6, 5.7, 8.0

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 sometimes 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:

• 1 client, 1 table - used 1 client & 1 table
  
• 4 clients, 4 tables - used 4 clients & 4 tables with a client per table
• 4 clients, 1 table - used 4 clients & 1 table with all clients sharing one table

Reports for cached by InnoDB:

• 1 client, 1 table - version 8.0.31, version 8.0, versions 5.6 to 8.0
• 4 clients, 4 tables - version 8.0.31, version 8.0, versions 5.6 to 8.0
• 4 clients, 1 table - version 8.0.31, version 8.0, versions 5.6 to 8.0

Reports for cached by OS:

• 1 client, 1 table - version 8.0.31, version 8.0, versions 5.6 to 8.0
• 4 clients, 4 tables - version 8.0.31, version 8.0, versions 5.6 to 8.0
• 4 clients, 1 table - version 8.0.31, version 8.0, versions 5.6 to 8.0

Summaries:

• There has been a gradual reduction in performance in each release because there has been a gradual increase in CPU overhead. The following excludes the l.x (create index) benchmark step.
• 1 client, 1 table
• Cached by InnoDB
• QPS for 8.0.32 is between 56% and 63% relative to 5.6.51
• Cached by OS
• QPS for 8.0.32 relative to 5.6.51 is (61%, 27%, 30%, 7%, 6%) for (l.i0, l.i1, q100.1, q500.1, q1000.1). See bug 109595.
• 4 clients, 4 tables
• Cached by InnoDB
• QPS for 8.0.32 is between 60% and 67% relative to 5.6.51
• Cached by OS
• QPS for 8.0.32 relative to 5.6.51 is (61%, 19%, 1%, 2%, 15%) for (l.i0, l.i1, q100.1, q500.1, q1000.1). See bug 109595.
• 4 clients, 1 table
• Cached by InnoDB
• QPS for 8.0.32 is between 62% and 75% relative to 5.6.51
• Cached by OS
• QPS for 8.0.32 relative to 5.6.51 is (60%, 18%, 2%, 2%, 9%) for (l.i0, l.i1, q100.1, q500.1, q1000.1). See bug 109595.