MySQL + InnoDB vs sysbench on a large server

This has benchmark results for MySQL 5.6.51, 5.7.44 and 8.0.39 using sysbench on a large server. I also add a few results comparing Postgres 17rc1 with MySQL 8.0.39. My goal with that is to highlight things that can be made better in MySQL (and in Postgres).

One of the responses to my claims that MySQL is getting too many performance regressions over time is that this is true for low-concurrency tests but not for high-concurrency tests. Alas, that claim is more truthy than true and fixing some of these regressions would help make modern MySQL not look so slow when compared to modern Postgres.

tl;dr

• MySQL 8.0 is faster than 5.6 for point queries and writes but slower for range queries
• PostgresSQL 17rc1 is a lot faster than MySQL 8.0 for point queries and writes. For range queries Postgres was still faster but the difference was usually not as large.

Builds, configuration and hardware

I compiled 

• Postgres versions 17rc1 from source using -O2 -fno-omit-frame-pointer.
• MySQL versions 5.6.51, 5.7.44 and 8.0.39 from source using -DCMAKE_BUILD_TYPE =Release and -O2 -fno-omit-frame-pointer

The server is an ax162-s from Hetzner with 48 cores, AMD 128G RAM and AMD SMT disabled. It uses Ubuntu 22.04 and storage is ext4 using SW RAID 1 over 2 locally attached NVMe devices. More details on it are here. At list prices a similar server from Google Cloud costs 10X more than from Hetzner.

The Postgres configuration file is here.
The MySQL configuration files are here for 5.6.51, 5.7.44 and 8.0.39.

Benchmark

I used sysbench and my usage is explained here. There are 42 microbenchmarks and most test only 1 type of SQL statement. Benchmarks are run with the database cached by Postgres.

For the large server the tests run with 8 tables and 10M rows/table. There are 40 client threads, read-heavy microbenchmarks run for 180 seconds and write-heavy run for 300 seconds. The command line to run all tests was: bash r.sh 8 10000000 180 300 md2 1 1 40

Results

For the results below I split the 42 microbenchmarks into 5 groups -- 2 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. The spreadsheet with all data is here.

Values from iostat and vmstat divided by QPS are here for MySQL 5.6, 5.7, 8.0 and here for MySQL 8.0.39 vs Postgres 17rc1. These help to explain why something is faster or slower because it shows how much HW is used per request.

The numbers in the spreadsheets are the relative QPS and $version is faster than the base case when it is greater than 1.0. When it is 3.0 then $version is 3X faster than the base case.

The relative QPS is computed by: (QPS for $version) / (QPS for base case).

For the comparison between MySQL 5.6.51, 5.7.44 and 8.0.33 the base case is 5.6.51 and $version is one of 5.7.44 or 8.0.39.

For the comparison between MySQL 8.0.39 and Postgres 17rc1 the base case is MySQL and $version is Postgres.

Results: MySQL 5.6.51 vs 5.7.44 vs 8.0.39

Point queries, part 1

• MySQL 8.0 is slower than 5.6 on 4 of 11 tests and faster on 7 of 11 tests. 
• The 4 on which it is slower are all for secondary index lookups. From vmstat metrics, when MySQL 8.0 is slower the problem is CPU overhead (see cpu/o here).
• The 7 on which it is faster are all for PK lookups. From two of those tests, it is faster because it uses less CPU per query (see cpu/o here).

Point queries, part 2

• MySQL 8.0 is faster than 5.6 in all tests here. All of these tests use a PK index. The perf difference is larger for queries that fetch more data. This suggests that new optimizer overheads prevent it from being faster in cases when it fetches less data and/or InnoDB in MySQL 8.0 doesn't have regressions for point queries relative to MySQL 5.6.
• From vmstat metrics (see cpu/o here) MySQL 8.0 is faster because it uses less CPU

Range queries, part 1

• MySQL 5.7 and 8.0 are slower than 5.6 for range queries. This is a surprise and the regressions are getting larger over time (worse in 8.0 than 5.7). The queries here do range scans of various lengths without aggregation.
• From vmstat metrics (see cpu/o here) MySQL 8.0 is slower because it uses more CPU per query

Range queries, part 2

• All of the tests here do some aggregation except for scan (the right most bars). And MySQL 8.0 is faster than 5.6 for all such tests.
• When MySQL 8.0 is faster the primary reason is that is uses less CPU per query (see cpu/o here) and sometimes it also does fewer context switches per query (see cs/o here) where a high context switch rate often implies mutex contention.

Writes

• MySQL 8.0 is significantly faster than 5.6 for all tests. These show the benefit of the work to improve performance for high-concurrency workloads.
• MySQL 8.0 is faster because it uses less CPU per SQL statement (see cpu/o here) and does few context switches per SQL statement (see cs/o here). A high context switch rate often implies mutex contention.

Results: MySQL 8.0.39 vs Postgres 17rc1

Point queries, part 1

• The result for hot-points is an outlier, perhaps because prepared statements are better in Postgres than in MySQL. In MySQL, server-side prepared statements just save on parse. The difference is large because MySQL uses a lot more CPU than Postgres (see cpu/o here).
• In most other tests Postgres is ~1.5X faster than MySQL and the root cause is that MySQL uses more CPU per query (see cpu/o here). For the context switch rate, sometimes it is larger for MySQL and sometimes it is smaller (see cs/o here).
• Excluding hot-points, the largest difference occurs on tests that do non-covering secondary index lookups. InnoDB tables are index-organized and for non-covering secondary index lookups to fetch the missing columns InnoDB must traverse the PK index while Postgres just uses the rowID (tuple ID) to access the heap pages.

Point queries, part 2

• Postgres is ~1.5X faster for point queries, see above in Point queries, part 1 for more details

Range queries, part 1

• Postgres is much slower than InnoDB on scan (full table scan) and on range-notcovered-pk. While I can only wave hands about the scan result for the range-notcovered-pk result Postgres must scan the PK index and then lookup missing columns from heap pages but InnoDB has all columns in the PK index because it is index-organized. For scan, the CPU overhead and context switch rates are much larger for Postgres (see cpu/o and cs/o here).
• Postgres is much faster on the two tests that do non-covering secondary index scans because it fetches missing columns by following the tupleID to a heap page while InnoDB must traverse the PK index.
• Excluding the 4 outliers, InnoDB and Postgres have similar performance

Range queries, part 2

• The performance difference here is smaller than it is above for point queries. In one case, MySQL is much faster, in three cases Postgres is much faster and in two cases perf is similar.
• For tests run before writes (less b-tree fragmentation) or after writes (more b-tree fragmentation) the pattern is that the advantage for Postgres is larger when the length of the range query is shorter -- see results for read-only.pre_range=X and read-only_range=X for X in 10000, 100 and 10. My guess is that MySQL optimizer overhead hurts performance and that is more obvious for queries that take less time -- see the CPU overhead metrics here (cpu/o columns).

Writes

• Postgres is faster for all tests that do writes and is up to ~5X faster. The reason is that Postgres uses less CPU per statement (see cpu/o here) and does fewer context switches per statement (see cs/o here) where context switch rates often predict mutex contention.
• The difference is smaller for read-write* tests because they do a mix of reads and writes.