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.
Update: At Ubuntu 22.04, which uses MySQL 8.0.31, this is fixed.
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