Migrate to Percona software for MySQL – an open source, production-ready, and enterprise-grade MySQL alternative.
Recently, I published a series of posts on MySQL and MariaDB where MariaDB 10.5.4 did not perform well in some scenarios:
In comments on the NVMe post, it was noted that MariaDB 10.5.4 had known performance bugs that would be fixed in MariaDB 10.5.5. Now that MariaDB 10.5.5 is available, it includes:
This post revisits those benchmarks using MariaDB 10.5.5.
All tests use sysbench-tpcc (1000 warehouses, ~100GB dataset) under the following scenarios:
After warm-up differences, performance is roughly the same between versions.
This scenario shows improvements in MariaDB 10.5.5:
MariaDB 10.5.5 improves throughput and even exceeds MySQL 8.0.21 on average, though with higher variability.
Performance is similar to 10.5.4, with slightly worse results and an initial drop before recovery.
This scenario exposes a severe issue in MariaDB 10.5.5 (MDEV-23399).
Throughput frequently drops below 100 TPS, compared to over 5000 TPS in MySQL.
MariaDB 10.5.5 introduces improvements in some IO-bound scenarios, particularly on SATA SSD.
However, performance on NVMe under IO-bound workloads is significantly degraded due to a critical bug.
This makes MariaDB 10.5.5 unsuitable for certain production workloads until the issue is resolved.
Apparently they solved most of the problems you pointed out and they slightly beat MySQL in those areas. The last test is still a disaster.
There is clearly a problem here. MariaDB 10.5.4 was the first 10.4 stable release, but it included important changes to InnoDB. They should have waited to declare is stable.
This is comparable to MySQL that constantly introduces changes in GA versions, which at some point broke compatibility with Xtrabackup – fortunately Percona fixed the problem on Xtrabackup side relatively soon (congrats!), but I’d like to know how many environments had broken backups because of this…
I suspect that what you call “weird initial drop followed by recovery” is the purge of history for INSERT.
Since MDEV-12288 in MariaDB Server 10.3, the hidden DB_TRX_ID column will be reset to 0 when the row is visible to all readers. The motivation for this is to avoid unnecessary lookups of old transactions, speeding up non-locking reads and checks for implicit locks.
Maybe you did not wait for the purge to run into completion before you started the benchmark?
MDEV-515 in a future MariaDB release should improve this by allowing inserts into empty tables or partitions skip undo logging altogether. The initial data would be inserted with DB_TRX_ID=0 straight from the beginning.
MDEV-23399 has been filed for the performance regression in write-heavy I/O bound workloads.
Marko – that is an interesting optimization. Two questions:
1) Is this the first time InnoDB purge is doing work after inserts?
2) If purge falls behind and the pages with inserts are no longer in cache will purge threads then do reads from storage to bring them back? So there can be extra storage reads?
3) Similar to #2, if purge is slow does this risk making a page dirty twice — thus being written back twice (more storage writes) where the first write follows the insert and the second write follows setting DB_TRX_ID to 0?
And is there another way to set DB_TRX_ID=0 with less risk of IO? For example when a page is made dirty, then consider setting DB_TRX_ID=0 at that point (or immediately prior to writeback) because it is in cache and it is already dirty.
Marco,
My procedure is following:
1. Load data (I do not wait for any internal processes to finish, just the application to finish its queries)
2. Stop database
3. Make backup
The benchmark procedure is:
1. Restore from backup
2. Run benchmark
It would be interesting to test Marko’s theory and wait for all internal processes to finish before backing up the database. I think the database might have been backed up in a state with all those historical transactions that are no longer necessary since all the rows already visible to all readers anyway.
I wonder if you will start seeing better performance on both mySQL & MariaDB since there won’t be additional lookups for the old transactions and cost of locking checks. (This is according to Marko’s comment)
Thanks Vadim for these great articles I learned a lot! I report on your work on my YouTube channel and will be interesting to see the new findings.
Mark,
The answer to your first 3 questions is ‘yes’ and to the 4th ‘currently no’. The old idea of attempting to purge history on every visit to the page could be worth pursuing, but I do not think that it would help in this particular case. With per-record transaction identifier in secondary indexes (MDEV-17598), I think that such ‘pre-purge’ should be entirely feasible.
Currently, to ensure that the database is in a stable and clean state after loading data, SET GLOBAL innodb_fast_shutdown=0 should be executed before initiating shutdown. In MariaDB Server 10.5, the slow shutdown should complete faster than in earlier releases.
Hm, I was thinking that each release should pass numerous tests before going live. I am sure it will be quickly fixed, but still thank you Vadim for pointing this issues out.
Hey,
very interesting benchmarks. As far as I know the performance bug should be fixed with 10.5.7 or 10.5.8, any plans to check the performance of the newest 10.5 release?
I’ve seen a massive query performance problem with a few of my queries with 10.5.8 and now again after updating to 10.5.12, and I’m uncertain if it’s the optimizer doing the wrong thing, or some issue with the database engine. It’s bad though. I have queries that run 16 seconds on MySQL 5.6 that are running for over 1000 seconds on 10.5.8-12. It’s driving me nuts.
Hello,
Can this problem be visible under Mariadb 10.6?
After an import, SELECT is very slow, an optimise is necessary to restore the situation.