MySQL8: poor performance after upgrade

Ubuntu 20.04 uses MySQL8 by default. It claims to be 2x faster than 5.7.

In some projects I have a database created in the 5.x series. In the current one, the complete test suite execution, on MySQL 5.7, took around 6 minutes.

In MySQL8, it took 18 minutes.

I.e., about 3x slower!

After some searching, I found an opened issue in MySQL bug tracker, with similar configuration: as storage, using a Samsung 970 EVO Plus m.2 SSD.

Solution

The configuration changes suggested in the bug tracker produced some improvements, but still far away from the 5.7.

But I’ve found in the MySQL forums an advice to configure MySQL8 to mimic MySQL 5.7 behavior. I’ve tested and… it worked!

Using sudo, edit /etc/mysql/mysql.conf.d/mysqld.cnf and add the following under [mysqld] section:

ssl=0
default_authentication_plugin=mysql_native_password
skip_log_bin=1
character_set_server=latin1
collation_server=latin1_swedish_ci
skip-character-set-client-handshake

Save and restart MySQL service with sudo service mysql restart.

mysqldump Issue

If you’re facing any issue running the mysqldump tool, more exactly:

Got error: 2061: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection. when trying to connect

Add these params to the import command line:

 --ssl-mode=DISABLED --get-server-public-key