MySQL 8, twenty times slower than MariaDB 10.3?

Question

For years I have worked on some servers with Ubuntu + MySQL.

Lately, the databases have become incredibly slow. Everything, including restoring databases from a dump. We are talking here about rather small databases (Web CMS), in the 10-20 MB range when in SQL dump form.

I believe this started happening when I have upgraded from Ubuntu 18 LTS to Ubuntu 20 LTS, which also upgrades MySQL to version 8 (from 5.x).

I have tried to remove and purge MySQL, and then reinstall it from scratch, just in case the problem originated from some ancient setting, but nothing changed. Still horribly slow.

I have tried mysqlcheck on the imported data; it found no problem and made no difference.

The big surprise was when I tried replacing MySQL (8) with MariaDB (10.3) and then I restored the same databases from a dump. Result: operations (both restoring and using the data) are more or less twenty times faster.

Now, I understand that MariaDB is generally considered a bit faster than MySQL, but twenty times seems a bit excessive. Also, MySQL used to be much faster. Anyone has any idea on what could be happening here?

Answer 1

Found the solution. By adding the following to the mysqld config, the performance is back to normal:

skip-log-bin
transaction_write_set_extraction=OFF

since this is a standalone (non-replicated) server, this should be harmless, correct?

Answer 2

This is not a new issue. Over these years, MySQL, MariaDB, Percona Server have been compared to one another.

Comparing them is somewhat unfair because out-of-the-box, older versions of MySQL can be faster than newer versions given the configuration of the server they run on.

You can only effectively answer this for yourself and tune the options until you get the desired performance, regardless of which MySQL fork you use. Only after tuning as much as possible would you want to say which one works the fastest or the best overall.

I have mentioned this some 12 years ago.

• Jun 19, 2011 : How do I properly perform a MySQL bake-off?
• Mar 26, 2012 : Percona vs MySQL
• Apr 09, 2012 : Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB?
• Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
• Nov 24, 2012 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)