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?

1
  • 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, 2023 at 16:49

2 Answers 2

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?

3
  • 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. May 20, 2023 at 10:36
  • 1
    You are allowed to tick it as the right answer May 20, 2023 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. May 20, 2023 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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.