IO-bound sysbench vs MySQL on a 48-core server

This has results for an IO-bound sysbench benchmark on a 48-core server for MySQL versions 5.6 through 9.5. Results from a CPU-bound sysbench benchmark on the 48-core server are here.

tl;dr

• the regressions here on read-only tests are smaller than on the CPU bound workload, but when they occur are from new CPU overheads
• the large improvements here on write-heavy tests are similar to the CPU bound workload

Builds, configuration and hardware

I compiled MySQL from source for versions 5.6.51, 5.7.44, 8.0.43, 8.0.44, 8.4.6, 8.4.7, 9.4.0 and 9.5.0.

The server is:

• an ax162s from Hetzner with an AMD EPYC 9454P 48-Core Processor with SMT disabled
• 2 Intel D7-P5520 NVMe storage devices with RAID 1 (3.8T each) using ext4
• 128G RAM
• Ubuntu 22.04 running the non-HWE kernel (5.5.0-118-generic)

The config files are here: 5.6.51, 5.7.44, 8.0.4x, 8.4.x, 9.x.0

Benchmark

I used sysbench and my usage is explained here. I now run 32 of the 42 microbenchmarks listed in that blog post. Most test only one type of SQL statement. Benchmarks are run with the database cached by InnoDB.

The read-heavy microbenchmarks are run for 600 seconds and the write-heavy for 900 seconds. The benchmark is run with 40 clients and 8 tables with 250M rows per table. With 250M rows per table this is IO-bound. I normally use 10M rows per table for CPU-bound workloads.

The purpose is to search for regressions from new CPU overhead, mutex contention and IO stress. 

Results

The microbenchmarks are split into 4 groups -- 1 for point queries, 2 for range queries, 1 for writes. For the range query microbenchmarks, part 1 has queries that don't do aggregation while part 2 has queries that do aggregation. 

I provide charts below with relative QPS. The relative QPS is the following:

(QPS for some version) / (QPS for MySQL 5.6.51)

When the relative QPS is > 1 then some version is faster than MySQL 5.6.51.  When it is < 1 then there might be a regression. When the relative QPS is 1.2 then some version is about 20% faster than MySQL 5.6.51.

Values from iostat and vmstat divided by QPS are here. These can help to explain why something is faster or slower because it shows how much HW is used per request, including CPU overhead per operation (cpu/o) and context switches per operation (cs/o) which are often a proxy for mutex contention.

The spreadsheet and charts are here and in some cases are easier to read than the charts below.

Results: point queries

This has two charts. The y-axis is truncated on the second chart to improve readability for all tests but hot-points which is a positive outlier.

Summary:

• the improvement for hot-points is similar to the CPU-bound results
• the regressions here for the IO-bound tests are smaller than for the CPU-bound results
• the regression in point-query is from new CPU overhead, see cpu/o here which is 1.37X larger in 9.5.0 vs 5.6.51
• the regression in points-covered-si is from new CPU overhead, see cpu/o here which is 1.24X larger in 9.5.0 vs 5.6.51. This test is CPU-bound, the queries don't do IO because the secondary indexes are cached.

Results: range queries without aggregation

Summary:

• the regressions here for the IO-bound tests are smaller than for the CPU-bound results, except for the scan test
• the regressions in scan are from new CPU overhead, see cpu/o here, which is 1.38X larger in 9.5.0 vs 5.6.51

Results: range queries with aggregation

Summary:

• the regressions here for the IO-bound tests are smaller than for the CPU-bound results
• the regressions in read-only-count are from new CPU overhead, see cpu/o here, which is 1.25X larger in 9.5.0 vs 5.6.51

Results: writes

Summary:

• the improvements here for the IO-bound tests are similar to the CPU-bound results
• the largest improvement, for the update-index test, is from using less CPU, fewer context switches, less read IO and less write IO per operation -- see cpu/o, cs/o, rKB/o and wKB/o here