Small Datum: mysql. innodb

Showing posts with label mysql. innodb. Show all posts

Friday, November 28, 2025

Using sysbench to measure how MySQL performance changes over time, November 2025 edition

This has results for the sysbench benchmark on a small and big server for MySQL versions 5.6 through 9.5. The good news is that the arrival rate of performance regressions has mostly stopped as of 8.0.43. The bad news is that there were large regressions from 5.6 through 8.0.

tl;dr for low-concurrency tests

for point queries

MySQL 5.7.44 gets about 10% less QPS than 5.6.51
MySQL 8.0 through 9.5 get about 30% less QPS than 5.6.51

for range queries without aggregation

MySQL 5.7.44 gets about 15% less QPS than 5.6.51
MySQL 8.0 through 9.5 get about 30% less QPS than 5.6.51

for range queries with aggregation

MySQL 5.7.44 is faster than 5.6.51 for two tests, as fast for one and gets about 15% less QPS for the other five
MySQL 8.0 to 9.5 are faster than 5.6.51 for one test, as fast for one and get about 30% less QPS for the other six

for writes

MySQL 5.7.44 gets between 10% and 20% less QPS than 5.6.51 for most tests
MySQL 8.0 to 9.5 get between 40% to 50% less QPS than 5.6.51 for most tests

tl;dr for high-concurrency tests

for point queries

for most tests MySQL 5.7 to 9.5 get at least 1.5X more QPS than 5.6.51
for tests that use secondary indexes MySQL 5.7 to 9.5 get about 25% less QPS than 5.6.51

for range queries without aggregation

MySQL 5.7.44 gets about 10% less QPS than 5.6.51
MySQL 8.0 through 9.5 get about 30% less QPS than 5.6.51

for range queries with aggregation

MySQL 5.7.44 is faster than 5.6.51 for six tests, as fast for one test and gets about 20% less QPS for one test
MySQL 8.0 to 9.5 are a lot faster than 5.6.51 for two tests, about as fast for three tests and gets between 10% and 30% less QPS for the other three tests

for writes

MySQL 5.7.44 gets more QPS than 5.6.51 for all tests
MySQL 8.0 to 9.5 get more QPS than 5.6.51 for all tests

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.

I used two servers:

small

an ASUS ExpertCenter PN53 with AMD Ryzen 7735HS CPU, 32G of RAM, 8 cores with AMD SMT disabled, Ubuntu 24.04 and an NVMe device with ext4 and discard enabled.

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:

small server: 5.6.51, 5.7.44, 8.0.4x, 8.4.x, 9.x.0
big server: 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. On the small server the benchmark is run with 1 client and 1 table with 50M rows. On the big server the benchmark is run with 40 clients and 8 tables with 10M rows per table.

The purpose is to search for regressions from new CPU overhead and mutex contention. I use the small server with low concurrency to find regressions from new CPU overheads and then larger servers with high concurrency to find regressions from new CPU overheads and mutex contention.

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 for the small server and the big server. 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

This is from the small server.

MySQL 5.7.44 gets about 10% less QPS than 5.6.51
MySQL 8.0 through 9.5 get about 30% less QPS than 5.6.51
There are few regressions after MySQL 8.0
New CPU overheads explain the regressions. See the vmstat results for the hot-points test.

This is from the large server.

For most point query tests MySQL 5.7 to 9.5 get at least 1.5X more QPS than 5.6.51

MySQL 5.7 to 9.5 use less CPU, see vmstat results for the hot-points test.

For tests that use secondary indexes (*-si) MySQL 5.7 to 9.5 get about 25% less QPS than 5.6.51.

This result is similar to what happens on the small server above.
The regressions are from extra CPU overhead, see vmstat results

MySQL 5.7 does better than 8.0 to 9.5. There are few regressions after MySQL 8.0.

Results: range queries without aggregation

This is from the small server.

MySQL 5.7.44 gets about 15% less QPS than 5.6.51
MySQL 8.0 through 9.5 get about 30% less QPS than 5.6.51
There are few regressions after MySQL 8.0
New CPU overheads explain the regressions. See the vmstat results for the scan test.

This is from the large server.

MySQL 5.7.44 gets about 10% less QPS than 5.6.51
MySQL 8.0 through 9.5 get about 30% less QPS than 5.6.51
There are few regressions after MySQL 8.0
New CPU overheads explain the regressions. See the vmstat results for the scan test.

Results: range queries with aggregation

This is from the small server.

for the read-only-distinct test, MySQL 5.7 to 9.5 are faster than 5.6.51
for the read-only_range=X tests

with the longest range scan (*_range=10000), MySQL 5.7.44 is faster than 5.6.51 and 8.0 to 9.5 have the same QPS as 5.6.51
with shorter range scans (*_range=100 & *_range=10) MySQL 5.6.51 is faster than 5.7 to 9.5. This implies that the regressions are from code above the storage engine layer.
From vmstat results the perf differences are explained by CPU overheads

for the other tests

MySQL 5.7.44 gets about 15% less QPS than 5.6.51
MySQL 8.0 to 9.5 get about 30% less QPS than 5.6.51
From vmstat results for read-only-count the reason is new CPU overhead

This is from the large server.

for the read-only-distinct test, MySQL 5.7 to 9.5 are faster than 5.6.51
for the read-only_range=X tests

MySQL 5.7.44 is as fast as 5.6.51 for the longest range scan and faster than 5.6.51 for the shorter range scans
MySQL 8.0 to 9.5 are much faster than 5.6.51 for the longest range scan and somewhat faster for the shorter range scans
From vmstat results the perf differences are explained by CPU overheads and possible from changes in mutex contention

for the other tests

MySQL 5.7.44 gets about 20% less QPS than 5.6.51 for read-only-count and about 10% more QPS than 5.6.51 for read-only-simple and read-only-sum
MySQL 8.0 to 9.5 get about 30% less QPS than 5.6.51 for read-only-count and up to 20% less QPS than 5.6.51 for read-only-simple and read-only-sum
From vmstat results for read-only-count the reason is new CPU overhead

Results: writes

This is from the small server.

For most tests

MySQL 5.7.44 gets between 10% and 20% less QPS than 5.6.51
MySQL 8.0 to 9.5 get between 40% to 50% less QPS than 5.6.51
From vmstat results for the insert test, MySQL 5.7 to 9.5 use a lot more CPU

For the update-index test

MySQL 5.7.44 is faster than 5.6.51
MySQL 8.0 to 9.5 get about 10% less QPS than 5.6.51
From vmstat metrics MySQL 5.6.51 has more mutex contention

For the update-inlist test

MySQL 5.7.44 is as fast as 5.6.51
MySQL 8.0 to 9.5 get about 30% less QPS than 5.6.51
From vmstat metrics MySQL 5.6.51 has more mutex contention

This is from the large server and the y-axis truncates the result for the update-index test to improve readability for the other results.

For all tests MySQL 5.7 to 9.5 get more QPS than 5.6.51

From vmstat results for the write-only test MySQL 5.6.51 uses more CPU and has more mutex contention.

For some tests (read-write_range=X) MySQL 8.0 to 9.5 get less QPS than 5.7.44

These are the classic sysbench transaction with different range scan lengths and the performance is dominated by the range query response time, thus 5.7 is fastest.

For most tests MySQL 5.7 to 9.5 have similar perf with two exceptions

For the delete test, MySQL 8.0 to 9.5 are faster than 5.7. From vmstat metrics 5.7 uses more CPU and has more mutex contention than 8.0 to 9.5.
For the update-inlist test, MySQL 8.0 to 9.5 are faster than 5.7. From vmstat metrics 5.7 uses more CPU than 8.0 to 9.5.

This is also from the large server and does not truncate the update-index test result.

Friday, July 26, 2024

Sysbench on a medium server: MariaDB is faster than MySQL

Modern MariaDB is significantly faster than modern MySQL on a medium server with cached sysbench because MySQL suffers from too many CPU performance regressions (code bloat, etc) over time.

This post has results for sysbench with a cached database and medium server (16 cores) using MariaDB and MySQL. The benchmark is run with 12 threads. A full writeup for MySQL on this HW is here and for MariaDB on a smaller server is here.

Note that the great results for MariaDB only occur when MDEV-33894 has been fixed in versions 10.11 or greater -- it isn't a bug prior to 10.11. I reported that bug a few months ago and then worked with the MariaDB core team to explain the problem and validate the fix.

The gap between MariaDB and MySQL is likely to grow. While MySQL 8.0.40 might have fixes that undo some of the regressions that arrived in InnoDB after 8.0.28 (see PS-8822 and MySQL 111538), I expect to see improvements in MariaDB as well. I opened three perf bugs for MariaDB earlier this year. The great results here are due in part to fixing MDEV-33894 and there will be even better results in the future when the two other bugs are fixed: MDEV-34178 and MDEV-33966.

tl;dr

The fix for MDEV-33894 is important
MariaDB has done great at avoiding CPU performance regressions over time, MySQL has not
Modern MariaDB is faster than modern MySQL

MariaDB was faster on 41 microbenchmarks and slower on one (update-inlist)
A typical result is MariaDB is ~11% faster than MySQL

Builds, configuration and hardware

For MySQL I compiled 8.0.38 from source.

For MariaDB I compiled these versions from source:

ma100244_rel_withdbg

MariaDB 10.2.44

ma100339_rel_withdbg

MariaDB 10.3.29

ma100434_rel_withdbg

MariaDB 10.4.34

ma100525_rel_withdbg

MariDB 10.5.25

ma100618_rel_withdbg

MariaDB 10.6.18

ma101108_rel_withdbg

MariaDB 10.11.8

ma1011_240723_c944cd6e_ilwas

MariaDB 10.11 compiled on 24/07/23 at git hash c944cd6f with innodb_log_writeahead_size =4096. Note the git hash in ma1011_240723_c944cd6e_ilwas is a typo (should end in 6f)

ma110402_rel_withdbg

MariaDB 11.4.2

ma1104_240723_2ee061c2_ilwas

MariaDB 11.4 compiled on 24/07/23 at git hash 2ee061c2 with innodb_log_writeahead_size =4096

ma110501_rel_withdbg

MariaDB 11.5.1

ma1105_240723_2f4b0ba3_ilwas

MariaDB 11.5 compiled on 24/07/23 at git hash 2f4b0ba3 with innodb_log_writeahead_size =4096

ma110600_rel_withdbg

MariaDB 11.6.0

ma1106_240723_d8374262_ilwas

MariaDB 11.6 compiled on 24/07/23 at git hash d8374262 with innodb_log_writeahead_size =4096

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 for MySQL 8.0.38.

The my.cnf files for MariaDB are here in per-version subdirectories. For 10.x the cz11a_c24r64 variant was used. For 11.x the cz11b_c24r64 variant was used. For the *_ilwas binaries above I added innodb_log_writeahead_size =4096 to the my.cnf file, although that wasn't needed because it was =4096 when I didn't set it in my.cnf.

Benchmark

I used sysbench and my usage is explained here. There are 42 microbenchmarks and most test only 1 type of SQL statement. The database is cached by InnoDB.

The benchmark is run with 12 threads, 8 tables and 10M rows per table.. Ech microbenchmark runs for 300 seconds if read-only and 600 seconds otherwise. Prepared statements were enabled.

The command lines for my helper scripts were:

# 8 tables, 10M rows/table, 12 threads
bash r.sh 8 10000000 300 600 md0 1 1 12

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. For each microbenchmark group there is a table with summary statistics. I don't have charts because that would use too much space, but the results per microbenchmark are in the spreadsheets.

The numbers in the spreadsheets and the tables below are the relative QPS which is (QPS for my version) / (QPS for base case). When the relative throughput is > 1 then that version is faster than the base case.

For all results below the base case is InnoDB from MySQL 8.0.26

Results: iostat and vmstat

This section has data from vmstat and iostat normalized by throughput to understand HW consumption per database operation. When MySQL is slower than MariaDB the usual culprit is CPU overhead (see the cpu/o column) and one example is the result for scans (see here) where cpu/o is 0.059403 for MySQL vs 0.043163 for MariaDB which is ~1.38X larger for MySQL.

But the results also show new problems for MariaDB and MDEV-34178 and MDEV-33966 are open for these problems. The usual signal is an increase in the context switch rate (cs/o). Examples of that occur on:

The problem is new in MariDB 10.11

random-points (see here)
range-notcovered (see here)
scan (see here)

The problem is new in MariaDB 10.5

update-inlist (see here) - the problem arrives in 10.5
update-index (see here) - the problem arrives in 10.5

The full results are here for MariaDB vs MySQL and here for MariaDB.

Results: MariaDB vs MySQL, summary statistics

Each table has summary statistics per microbenchmark group. The numbers are the relative QPS.

Relative QPS is: (QPS for MariaDB) / (QPS for MySQL)
MariaDB is ma1104_240723_2ee061c2_ilwas (11.4 with MDEV-33894 fix)
MySQL is 8.0.38
MariaDB is faster than MySQL on all microbenchmarks except for update-inlist

MariaDB	min	max	avg	median
point-1	1.05	1.27	1.09	1.08
point-2	1.06	1.09	1.07	1.07
range-1	1.03	1.35	1.12	1.11
range-2	1.10	1.37	1.19	1.11
writes	0.83	2.06	1.36	1.37

Results: MariaDB, summary statistics

Each table has summary statistics per microbenchmark group. The numbers are the relative QPS.

Relative QPS is: (QPS for modern MariaDB) / (QPS for older MariaDB)
modern MariaDB is 11.4 without and with the MDEV-33894 fix -- ma110402_rel_withdbg and ma1104_240723_2ee061c2_ilwas
older MariaDB is 10.2.44 -- ma100244_rel_withdbg

Summary

Writes are faster in 11.4 than in 10.2 with the MDEV-33894 fix
Reads are between 13% slower and 23% faster in 11.4 than in 10.2
MariaDB has done great at avoiding regressions over time

For 11.4.2 ( ma110402_rel_withdbg) relative to 10.2.44 (ma100244_rel_withdbg)

11.4.2	min	max	avg	median
point-1	0.86	0.98	0.92	0.93
point-2	0.87	0.91	0.89	0.88
range-1	0.87	1.18	0.95	0.93
range-2	0.92	1.04	0.97	0.94
writes	0.43	0.75	0.62	0.61

For 11.4 with the fix for MDEV-33894 (ma1104_240723_2ee061c2_ilwas) relative to 10.2.44 (ma100244_rel_withdbg)

11.4.ilwas	min	max	avg	median
point-1	0.86	0.96	0.92	0.93
point-2	0.87	0.91	0.89	0.88
range-1	0.87	1.25	0.95	0.93
range-2	0.92	1.04	0.97	0.94
writes	0.74	1.29	1.06	1.12

Results: MariaDB, charts

The y-axis starts at 0.85 for the read microbenchmark groups (point-1, point-2, range-1, range-2) to improve readability.

Summary:

MariaDB has done much better than MySQL at avoiding CPU perf regressions over time
For the scan microbenchmark modern MariaDB is faster than older MariaDB
For write microbenchmarks the MDEV-33894 fix is important as QPS is almost 2X larger with it