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
  • 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
MariaDBminmaxavgmedian
point-11.051.271.091.08
point-21.061.091.071.07
range-11.031.351.121.11
range-21.101.371.191.11
writes0.832.061.361.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.2minmaxavgmedian
point-10.860.980.920.93
point-20.870.910.890.88
range-10.871.180.950.93
range-20.921.040.970.94
writes0.430.750.620.61

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

11.4.ilwasminmaxavgmedian
point-10.860.960.920.93
point-20.870.910.890.88
range-10.871.250.950.93
range-20.921.040.970.94
writes0.741.291.061.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

Comments

Post a Comment

Popular posts from this blog

Fixing bug 109595 makes MySQL almost 4X faster on the Insert Benchmark

Image
MySQL 8.0.35 includes a fix for bug 109595  and with that fix the QPS is almost 4X larger on the read+write benchmark steps compared to MySQL 8.0.34. Thank you to MySQL for fixing this quickly. I reported the bug in January of 2023. I have been aware of the performance problem for years, but didn't spend time debugging it until this year. I assume this problem was limited to InnoDB because I did not see problems with MyRocks. The chart below shows relative QPS which is (QPS for MySQL 8.0.35) / (QPS for MySQL 8.0.34). When the value is larger than 1.0 then MySQL 8.0.35 is faster than 8.0.34. And below the relative QPS is almost 4.0 on the read+write benchmark steps: q100, q500, q1000. The Insert Benchmark is explained here .
Read more

Postgres versions 11, 12, 13, 14, 15, and 16 vs sysbench with a medium server

Image
This provides additional results for Postgres versions 11 through 16 vs Sysbench on a medium server. My previous post is here . The goal is to document how performance changes over time with a focus on CPU overheads. tl;dr Postgres avoids performance regressions from old to new versions. This is excellent. Postgres 16.0 is mostly faster than 11.21 on read-only benchmark steps Postgres 16.0 and 11.21 have similar throughput on read-write and write-only benchmark steps Builds I compiled Postgres 11.21, 12.16, 13.12, 14.9, 15.2, 15.3, 15.4 and 16.0. The builds are named  o3_native_lto  which is shorthand for using: -O3 -march=native -mtune=native -flto. Benchmark I used sysbench and my usage is  explained here . Postgres was configured to cache all tables. This benchmark used a c2-standard-30 server from GCP with 15 cores, hyperthreads disabled, 120G of RAM, Ubuntu 22.04 and 1.5TB of NVMe SSD with XFS (SW RAID 0 over 4 local devices). The benchmark is run with 8 clients and 4 table with 2
Read more

Postgres vs MySQL: the impact of CPU overhead on performance

Image
This post documents how MySQL and Postgres performance has changed over time using sysbench. It compares QPS for Postgres 11.21 vs MySQL 5.6.21 and then Postgres 16.0 vs MySQL 8.0.34. The focus here is on CPU overhead and for that a workload that is low-concurrency, CPU-bound and cached is sufficient. However a full comparison would also include results for workloads with more concurrency and workloads that are IO-bound. The benchmark was explained in a series of posts - see  part 1 ,  part 2 ,  part 3 ,  part 4  and part 5  and the results here are from my small server. Usually I avoid direct comparisons between Postgres and MySQL but I am doing this because I care about the future of MySQL and the CPU regressions in it over time have become a problem. tl;dr Postgres 11.21 and MySQL 5.6.21 have similar QPS on most sysbench tests Postgres 16.0 gets much more QPS than MySQL 8.0.34 on most sysbench tests The problem for MySQL is new CPU overhead Results The spreadsheet with all of the nu
Read more