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 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:
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 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
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)
For 11.4 with the fix for MDEV-33894 (ma1104_240723_2ee061c2_ilwas) relative to 10.2.44 (ma100244_rel_withdbg)
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
.png)
No comments:
Post a Comment