Small Datum: Sysbench for MySQL 5.6 through 9.5 on a 2-socket, 24-core server

This has results for the sysbench benchmark on a 2-socket, 24-core server. A post with results from 8-core and 32-core servers is here.

tl;dr

old bad news - there were many large regressions from 5.6 to 5.7 to 8.0
new bad news - there are some new regressions after MySQL 8.0

Normally I claim that there are few regressions after MySQL 8.0 but that isn't the case here. I also see regressions after MySQL 8.0 on the other larger servers that I use, but that topic will explained in another post.

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 a SuperMicro SuperWorkstation 7049A-T with 2 sockets, 12 cores/socket, 64G RAM, one m.2 SSD (2TB, ext4 with discard enabled). The OS is Ubuntu 24.04. The CPUs are Intel Xeon Silver 4214R CPU @ 2.40GHz.

The config files are here for 5.6, 5.7, 8.0, 8.4 and 9.x.

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 16 clients and 8 tables with 10M rows per table.

The purpose is to search for regressions from new CPU overhead and mutex contention. The workload is cached -- there should be no read IO but will be some write IO.

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 base version)

When the relative QPS is > 1 then some version is faster than the base version. 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 the base version.

I present two sets of charts. One set uses MySQL 5.6.51 as the base version which is my standard practice. The other uses MySQL 8.0.44 as the base version to show

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. Converting the Google Sheets charts to PNG files does the wrong thing for some of the test names listed at the bottom of the charts below.

Results: point queries

Summary

from 5.6 to 5.7 there are big improvements for 5 tests, no changes for 2 tests and small regressions for 2 tests
from 5.7 to 8.0 there are big regressions for all tests
from 8.0 to 9.5 performance is stable
for 9.5 the common result is ~20% less throughput vs 5.6

Using vmstat from the hot-points test to understand the performance changes (see here)

context switch rate (cs/o) is stable, mutex contention hasn't changed
CPU per query (cpu/o) drops by 35% from 5.6 to 5.7
CPU per query (cpu/o) grows by 23% from 5.7 to 8.0
CPU per query (cpu/o) is stable from 8.0 through 9.5

Results: range queries without aggregation

Summary

from 5.6 to 5.7 throughput drops by 10% to 15%
from 5.7 to 8.0 throughput drops by about 15%
from 8.0 to 9.5 throughput is stable
for 9.5 the common result is ~30% less throughput vs 5.6

Using vmstat from the scan test to understand the performance changes (see here)

context switch rates are low and can be ignored
CPU per query (cpu/o) grows by 11% from 5.6 to 5.7
CPU per query (cpu/o) grows by 15% from 5.7 to 8.0
CPU per query (cpu/o) is stable from 8.0 through 9.5

Results: range queries with aggregation

Summary

from 5.6 to 5.7 there are big improvements for 2 tests, no changes for 1 tests and regressions for 5 tests
from 5.7 to 8.0 there are regressions for all tests
from 8.0 through 9.5 performance is stable
for 9.5 the common result is ~25% less throughput vs 5.6

Using vmstat from the read-only-count test to understand the performance changes (see here)

context switch rates are similar
CPU per query (cpu/o) grows by 16% from 5.6 to 5.7
CPU per query (cpu/o) grows by 15% from 5.7 to 8.0
CPU per query (cpu/o) is stable from 8.0 through 9.5

Results: writes

Summary

from 5.6 to 5.7 there are big improvements for 9 tests and no changes for 1 test
from 5.7 to 8.0 there are regressions for all tests
from 8.4 to 9.x there are regressions for 8 tests and no change for 2 tests
for 9.5 vs 5.6: 5 are slower in 9.5, 3 are similar and 2 are faster in 9.5

Using vmstat from the insert test to understand the performance changes (see here)

in 5.7, CPU per insert drops by 30% while context switch rates are stable vs 5.6
in 8.0, CPU per insert grows by 36% while context switch rates are stable vs 5.7
in 9.5, CPU per insert grows by 3% while context switch rates grow by 23% vs 8.4

The first chart doesn't truncate the y-axis to show the big improvement for update-index but that makes it hard to see the smaller changes on the other tests.