This has results from the sysbench benchmark for MariaDB and MySQL on a small server with a cached and low-concurrency workload. For MariaDB I tested LTS releases from 10.2 through 11.4. For MySQL I tested 5.6, 5.7 and 8.0. The results from MySQL here are a good reason to use changepoint detection to spot regressions early, like that provided by Nyrkiö.
This work was done by Small Datum LLC and sponsored by the MariaDB Foundation.
My standard disclaimer is that sysbench with low-concurrency is great for spotting CPU regressions. However, a result with higher concurrency from a larger server is also needed to understand things. Results from IO-bound workloads and less synthetic workloads are also needed. But low-concurrency, cached sysbench is a great place to start.
tl;dr
- MariaDB is great at avoiding CPU regressions over time
- MySQL is less than great at avoiding CPU regressions over time
- Modern MariaDB is 13% to 22% faster than modern MySQL (on this setup)
Builds and configuration
For MariaDB I used the latest point releases from LTS versions: 10.2.44, 10.3.39, 10.4.33, 10.5.24, 10.6.17, 10.11.7 and the upcoming LTS 11.4.1.
For MySQL I used 5.6.51, 5.7.44 and 8.0.36.
The first GA release for MariaDB 10.2 was in 2017. The first GA release for MySQL 5.6 was in 2013. So while I cannot claim that my testing covers MySQL and MariaDB from the same time period, I can claim that I am testing old versions of both.
Everything was compiled from source with similar CMake command lines and CMAKE_BUILD_TYPE set to Release. It is much easier to compile older MariaDB releases than older MySQL releases. For MariaDB I did not have to edit any source files. Notes on compiling MySQL are here for 5.6, for 5.6 and 5.7, for 5.7 and for 8.0. A note on using cmake is here.
For MySQL I used 5.6.51, 5.7.44 and 8.0.36.
The first GA release for MariaDB 10.2 was in 2017. The first GA release for MySQL 5.6 was in 2013. So while I cannot claim that my testing covers MySQL and MariaDB from the same time period, I can claim that I am testing old versions of both.
Everything was compiled from source with similar CMake command lines and CMAKE_BUILD_TYPE set to Release. It is much easier to compile older MariaDB releases than older MySQL releases. For MariaDB I did not have to edit any source files. Notes on compiling MySQL are here for 5.6, for 5.6 and 5.7, for 5.7 and for 8.0. A note on using cmake is here.
The my.cnf files are here for MariaDB and MySQL.
Benchmarks
I used sysbench and my usage is explained here. There are 42 microbenchmarks and each tests ~1 type of SQL statement.
Tests were run on a small server I have at home and is the v3 server with an i7 CPU described here. The server has 4 cores, 16G of RAM and 1 m.2 storage device with XFS and Ubuntu 22.04. The test tables are cached by InnoDB.
The benchmark is run with:
The benchmark is run with:
- one connection
- 30M rows and a database cached by InnoDB
- each microbenchmark runs for 300 seconds if read-only and 600 seconds otherwise
- prepared statements were enabled
The command line was: bash r.sh 1 30000000 300 600 nvme0n1 1 1 1
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 and charts is here and is easier to read.
All of the charts have relative throughput on the y-axis where that is (QPS for $me) / (QPS for $base), $me is some DBMS version (for example MariaDB 11.4.1) and $base is the DBMS version for the base case. The base version is specified below depending on what I am comparing. The y-axis doesn't start at 0 to improve readability. When the relative throughput is > 1 then the throughput on some DBMS version is greater than the throughput for the base case.
The legend under the x-axis truncates the names I use for the microbenchmarks and I don't know how to fix that other than sharing the link to the Google Sheet I used.
Results: MariaDB from old to new
This section uses MariaDB 10.2.44 as the base version and then compares that with MariaDB versions 10.3.39, 10.4.33, 10.5.24, 10.6.17, 10.11.7 and 11.4.1. The goal is to determine how throughput (QPS) changes from older releases like 10.2 to the latest release (11.4).
- MariaDB is great at avoiding CPU regressions over time
This table has summary statistics of the relative throughput for MariaDB 11.4.1 vs 10.2.44 for each of the microbenchmark groups. A value greater than one means the throughput for MariaDB 11.4.1 is better than for 10.2.44. From the results here, new MariaDB (11.4.1) gets at least 93% of the throughput relative to old MariaDB (10.2.44) using the median relative throughput per microbenchmark group. New features added to MariaDB don't get in the way of performance.
Graphs per microbenchmark group
Results: MySQL from old to new
This section uses MySQL 5.6.51 as the base version and then compares that with MySQL versions 5.7.44 and 8.0.36. The goal is to determine how throughput (QPS) changes from older to newer releases.
- MySQL is less than great at avoiding CPU regressions over time
This table has summary statistics of the relative throughput for MySQL 8.0.36 vs 5.6.51 for each of the microbenchmark groups. A value greater than one means the throughput for MySQL 8.0.36 is better than for 5.6.51. From the results here, new MySQL (8.0.36) gets between 63% and 95% of the throughput relative to old MySQL (5.6.51) using the median relative throughput per microbenchmark group. New features in modern MySQL come at the cost of much CPU overhead.
Graphs per microbenchmark group
Results: MariaDB vs MySQL
This section uses MySQL 8.0.36 as the base version and then compares that with MariaDB 11.4.1. The goal is to determine which DBMS gets more throughput (or uses less CPU/query).
- Modern MariaDB is faster than modern MySQL (on this setup)
This table has summary statistics of the relative throughput for MariaDB 11.4.1 vs MySQL 8.0.36 for each of the microbenchmark groups. A value greater than one means the throughput for MariaDB 11.4.1 is better than for MySQL 8.0.36. From the results here, modern MariaDB (11.4.1) gets between 113% and 122% of the throughput relative to modern MySQL (8.0.36) using the median relative throughput per microbenchmark group.
Graphs per microbenchmark group
No comments:
Post a Comment