MySQL 8.0.38 vs the Insert Benchmark on a medium server

This has benchmark results for MySQL 8.0.38 and a few other 8.0 releases using the Insert Benchmark and a medium server. By small, medium or large server I mean < 10 cores for small, 10 to 19 cores for medium, 20+ cores for large. A result for Postgres 17 on the same server is here.

tl;dr

• Performance for the write-heavy (l.i0, l.i1, l.i2) and point-query tests is ~5% worse after 8.0.28 and that has yet to be fixed. AFAIK, one problem is from changes related to function inlining for InnoDB, see PS-8822 and bug 111538.
• Throughput for range queries (qr100, qr500, qr1000) improved by 5% after 8.0.28. I don't have a bug report for a fixed bug.

Builds, configuration and hardware

I compiled from source MySQL versions 8.0.28, 8.0.32, 8.0.36, 8.0.37 and 8.0.38.

The server is a c2d-highcpu-32 instance type on GCP (c2d high-CPU) with 32 vCPU, 64G RAM and SMT disabled so there are 16 cores. It uses Ubuntu 22.04 and storage is ext4 (data=writeback) using SW RAID 0 over 2 locally attached NVMe devices.

The my.cnf file is here.

The Benchmark

The benchmark is explained here and is run with 8 clients and a table per client with two workloads:

• cached - database fits in the InnoDB buffer pool
• IO-bound - database is larger than memory and there are many reads from disk

The benchmark steps are:

• l.i0
• insert X million rows per table in PK order. The table has a PK index but no secondary indexes. There is one connection per client. The value of X is 10 for cached and 128 for IO-bound.
• l.x
• create 3 secondary indexes per table. There is one connection per client.
• l.i1
• use 2 connections/client. One inserts X rows per table and the other does deletes at the same rate as the inserts. Each transaction modifies 50 rows (big transactions). This step is run for a fixed number of inserts, so the run time varies depending on the insert rate. The value of X is 40M for cached and 4M for IO-bound.
• l.i2
• like l.i1 but each transaction modifies 5 rows (small transactions) and X rows are inserted and deleted per table. The value of X is 10M for cached and 1M for IO-bound.
• Wait for X seconds after the step finishes to reduce variance during the read-write benchmark steps that follow. The value of X is a function of the table size.
• qr100
• use 3 connections/client. One does range queries and performance is reported for this. The second does does 100 inserts/s and the third does 100 deletes/s. The second and third are less busy than the first. The range queries use covering secondary indexes. This step is run for 1800 seconds. If the target insert rate is not sustained then that is considered to be an SLA failure. If the target insert rate is sustained then the step does the same number of inserts for all systems tested.
• qp100
• like qr100 except uses point queries on the PK index
• qr500
• like qr100 but the insert and delete rates are increased from 100/s to 500/s
• qp500
• like qp100 but the insert and delete rates are increased from 100/s to 500/s
• qr1000
• like qr100 but the insert and delete rates are increased from 100/s to 1000/s
• qp1000
• like qp100 but the insert and delete rates are increased from 100/s to 1000/s

Results: overview

The performance reports are here for cached and for IO-bound.

The summary (for cached and for IO-bound) in each performance report has 3 tables. The first shows absolute throughput by DBMS tested X benchmark step. The second has throughput relative to the version from the first row of the table. The third shows the background insert rate for benchmark steps with background inserts and all systems sustained the target rates. The second table makes it easy to see how performance changes over time. The third table makes it easy to see which DBMS+configs failed to meet the SLA.

Below I use relative QPS to explain how performance changes. It is: (QPS for $me / QPS for $base) where $me is my version and $base is the version of the base case. The base case here is MySQL 8.0.28 and the versions for $me are 8.0.32, 8.0.36, 8.0.37 and 8.0.38. When relative QPS is > 1.0 then performance improved over time. When it is < 1.0 then there are regressions. The Q in relative QPS measures: 

• insert/s for l.i0, l.i1, l.i2
• indexed rows/s for l.x
• range queries/s for qr100, qr500, qr1000
• point queries/s for qp100, qp500, qp1000

Below I use colors to highlight the relative QPS values with red for <= 0.95, green for >= 1.05 and grey for values between 0.95 and 1.05.

Results: cached

Summary:

• Throughput for the write-heavy (l.i0, l.i1, l.i2) and point-query tests regressed (5% to 10% slower) after 8.0.28 and that has yet to be fixed. AFAIK, one problem is from changes related to function inlining for InnoDB, see PS-8822 and bug 111538.
• Throughput for range queries (qr100, qr500, qr1000) improved by 5% after 8.0.28. I don't have a bug report for a fixed bug.
• Tables with vmstat metrics show that CPU/operation (cpupq) changes to match the throughput changes

From the summary, the relative throughput for 8.0.38 (vs 8.0.28) per benchmark step is:

• l.i0
• relative QPS is 0.96 in MySQL 8.0.38
• l.x - I ignore this for now
• l.i1, l.i2
• relative QPS is 0.92, 0.94 in MySQL 8.0.38
• qr100, qr500, qr1000
• relative QPS is 1.04, 1.04, 1.04 in MySQL 8.0.38
• qp100, qp500, qp1000
• relative QPS is 0.94, 0.95, 0.96 in MySQL 8.0.38

Results: IO-bound

Summary:

• Throughput for the write-heavy (l.i0, l.i1, l.i2) and point-query tests regressed (~5%) after 8.0.28 and that has yet to be fixed. AFAIK, one problem is from changes related to function inlining for InnoDB, see PS-8822 and bug 111538.
• Throughput for range queries (qr100, qr500, qr1000) improved by ~10% after 8.0.28. I don't have a bug report for a fixed bug.
• Tables with vmstat metrics show that CPU/operation (cpupq) changes to match the throughput changes

From the summary, the relative throughput for 8.0.38 (vs 8.0.28 per) benchmark step is:

• l.i0
• relative QPS is 0.94 in MySQL 8.0.38
• l.x - I ignore this for now
• l.i1, l.i2
• relative QPS is 0.95, 0.94 in MySQL 8.0.38
• qr100, qr500, qr1000
• relative QPS is 1.08, 1.10, 1.13 in MySQL 8.0.38
• qp100, qp500, qp1000
• relative QPS is 0.83, 0.99, 0.96 in MySQL 8.0.38