1

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?

Giovanni Gigante
  • 59
  • 4
  • 1
    Please provide a specific query that is noticeably slower. Also include SHOW CREATE TABLE. From those we can probably discover what Optimization is being performed differently in the two products. And they we might have a way to speed up the query in both. I know of one 'easy to explain' case: MariaDB eliminates unnecessary JOIN, while MySQL acts as if they are important. – Rick James May 19 '23 at 16:49

2 Answers2

3

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?

Giovanni Gigante
  • 59
  • 4
  • I am glad you found a solution. The problem in your particular case was very simple : Binary Logging is enabled by default in MySQL 8.0 and you just turned it off. – RolandoMySQLDBA May 20 '23 at 10:36
  • 1
    You are allowed to tick it as the right answer – Rohit Gupta May 20 '23 at 12:32
  • skip-log-bin is not harmless if you depend on the binary log for replication, point in time recovery, or CDC tools. Disabling transaction_write_set_extraction is not harmless if you want to use MySQL group replication. – Bill Karwin May 20 '23 at 19:07
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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520