Continuing with the checkpointing topic I restarted a month ago with MongoDB and followed with PostgreSQL, I wanted to check how MySQL and MariaDB perform.
It was actually some time when I last looked into MySQL in this regard, so I am curious how new MySQL versions (8.0.21 in this case) would perform. And while we’re at it, why don’t we compare this with the new MariaDB 10.5.4, which comes with interesting changes in InnoDB subsystems.
Benchmark
To evaluate MariaDB and MySQL, I will use an identical setup to when I evaluated PostgreSQL: using sysbench-tpcc with 1000 Warehouses.
The hardware I use is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | System | Supermicro; SYS-F619P2-RTN; v0123456789 (Other) Platform | Linux Release | Ubuntu 18.04.4 LTS (bionic) Kernel | 5.3.0-42-generic Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.27 SELinux | No SELinux detected Virtualized | No virtualization detected # Processor ################################################## Processors | physical = 2, cores = 40, virtual = 80, hyperthreading = yes Models | 80xIntel(R) Xeon(R) Gold 6230 CPU @ 2.10GHz Caches | 80x28160 KB # Memory ##################################################### Total | 187.6G |
With the storage on SATA SSD INTEL SSDSC2KB960G8 (Intel Enterprise-grade SSD D3-S4510).
The short settings overview:
- Data will totally fit into memory (The datasize is ~100GB, memory on the server is 188GB, and we allocate 140GB for MySQL and MariaDB innodb_buffer_pool_size).
- The workload on storage will be mostly write-intensive (reads will be done from memory), with full ACID-compliant and data safe settings in MySQL and MariaDB.
The benchmark command line is:
1 | ./tpcc.lua --mysql-host=127.0.0.1 --mysql-user=sbtest --mysql-password=sbtest --mysql-db=sbtest --time=10000 --threads=56 --report-interval=1 --tables=10 --scale=100 --use_fk=0 --mysql_table_options='DEFAULT CHARSET=utf8mb4' prepare |
This means that the benchmark will run for three hours with reporting throughput every one sec. I will use a three-hour time frame for a reason which will be apparent later from the results.
Results
Let’s see what results I’ve got with this setup:
Wow.
I really did not expect such wave patterns for MySQL, and that’s why I extended the run time from 1 hour to 3 hours to see if MySQL ever stabilizes from wave pattern to a more stable line. And actually, it did, by the end of the 3rd hour, which is quite too long in my opinion.
MariaDB shows stabilization much sooner, but the variation in the throughput is higher.
The averaged results for MariaDB by the end of the 3rd hour are 4900 tps, and for MySQL is 7500 tps, so there, MySQL 8.0.21 outperforms MariaDB by some margin (about 1.5x times).
If we draw the boxplots for the last 1000 sec of the runs, it is:
Disclaimer:
MariaDB 10.5.4 comes with a lot of changes in the InnoDB subsystem and I did not investigate what might be a cause of such difference. I’ve asked MariaDB for the comments.
Final Thoughts
Checkpointing (in providing minimal performance variation) continues to be a hard problem for databases. MariaDB is more stable, but the average performance is not great. For MySQL, I would like to see a way to stabilize the wave line faster than within 3 hours.
If you are interested in the raw results and notebooks, it is available here:
https://github.com/Percona-Lab-results/2020-07-MySQL-MariaDB
You’re probably affected by https://jira.mariadb.org/browse/MDEV-23017. this is already fixed, small blunder, just in time before the 10.5.4 release.
Furthermore, the upcoming 10.5.5 release might slightly improve write performance thanks to the https://jira.mariadb.org/browse/MDEV-22110 fix. I did not check if MySQL 8.0 is affected by that, but at least up to MySQL 5.7, mini-transaction commit would unnecessarily add unmodified buffer pool pages to buf_pool_t::flush_list if the mini-transaction had modified some page and acquired sx-latch or x-latch on the unmodified pages.
10.5.5 release notes say “Fixes to performance regressions introduced in MariaDB 10.5.4 (MDEV-23017, MDEV-23369, MDEV-23410)”