Database problems

[Mike-it]

For years I have used MySQL (on Ubuntu) with ProcessWire.

Lately, the database has become incredibly slow. Everything: ProcessWire, dumping & restoring databases, etc.
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 it depended from some old setting, but nothing changed. Still horribly slow.

The surprise was when I tried replacing MySQL (8) with MariaDB (10.3) – since PW supports both – and then I restored the ProcessWire databases. Result: operations 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. Anyone has any idea on what could be happening here?

Thanks

[elabx]

My experiences with terrible performance with MySQL most of the time have to with the indexes being absent or in need of rebuilt.

[Mike-it]

1 hour ago, elabx said:

My experiences with terrible performance with MySQL most of the time have to with the indexes being absent or in need of rebuilt.

 

Since I have recreated the databases from a dump in a clean installation, the indexes should have been rebuilt anyway, right?

Moreover, importing the dump is now itself an awfully slow operation.

My question is: is it possible that the out-of-the-box difference between MySQL and MariaDB is so large, to the point of making it worthwhile to migrate? Or is it just a symptom of some configuration problem with MySQL?

[Jim Bailie]

Interesting topic. I was in a very similar situation watching MySQL's performance degrade on a well established LEMP app located on an Ubuntu 14.04 Droplet.

On that server, I installed an instance of Percona's MySQL (maybe v5.7*) and bang! Everything was fine and remains that way 3 years later. Total mystery...

[flydev]

Hi, without more precise informations about like exact version of the setup, the type of hosting / server, etc, it's quite hard to give you an answer.

The only hint I can provide you, as you are talking about MySQL v8 on Ubuntu 20 LTS, it's to look at this known bug: 

https://bugs.mysql.com/bug.php?id=99593

To test the workaround (second post from the dev team):

SET GLOBAL internal_tmp_mem_storage_engine=MEMORY;

Quote

It seems this bug is a duplicate of internally filed:

Bug 30562964 : 8.0.18: PERFORMANCE REGRESSION IN SELECT DISTINCT
which was introduced in 8.0.18 and fixed in 8.0.21.

 

 

[Mike-it]

1 hour ago, flydev said:

Hi, without more precise informations about like exact version of the setup, the type of hosting / server, etc, it's quite hard to give you an answer.

The only hint I can provide you, as you are talking about MySQL v8 on Ubuntu 20 LTS, it's to look at this known bug: 

https://bugs.mysql.com/bug.php?id=99593

To test the workaround (second post from the dev team):

SET GLOBAL internal_tmp_mem_storage_engine=MEMORY;

 

 

 

I have already tried that. Made no difference.

But, meanwhile I think I have found the solution. Adding to the mysqld config

skip-log-bin
transaction_write_set_extraction=OFF

the performance is back to normal levels. It disables binary logging; since this is a standalone (non-replicated) server, this should be harmless (I think).