Hello List,
i do some tests with new MySQL 8 and noted a bad performance with standardfunctions. System is Ubuntu18.04, i installed MySQL8 this way:
https://www.tecmint.com/install-mysql-8-in-ubuntu/
Current Version is mysql-community-server 8.0.15-1ubuntu18.04
These are official MySQL Packages for Ubuntu, its just a basic config included.
This is a fresh installation, for testing i initialized a new system with:
mysqld --initialize-insecure --console
My first hint that something is wrong was amount of time is needed for command "mysql_upgrade -s --force" , later i want to upgrade my MySQL 5.7 servers, so i testet this command on fresh MySQL8 installation, without any data. To my surprise it took 10 minutes, absolutely slow. Same Command on MySQL 5.7 takes ~30sec.
At this point i did some basic benchmarktests using mysqlslap and an example database:
https://www.digitalocean.com/community/tutorials/how-to-measure-mysql-query-performance-with-mysqlslap
i run a command like this just doing a lot of selects:
mysqlslap --user=root --password --host=localhost --concurrency=20 --number-of-queries=1000 --create-schema=employees --query="/select_query.sql" --delimiter=";" --verbose --iterations=2 --debug-info
I got this results with mysql-community-server-core 8.0.15-1ubuntu18.04
Benchmark
Average number of seconds to run all queries: 136.572 seconds
Minimum number of seconds to run all queries: 134.275 seconds
Maximum number of seconds to run all queries: 138.869 seconds
Number of clients running queries: 20
Average number of queries per client: 50
To compare with somethig i removed all MySQL from this server and reinstalled MySQL 5.7.25 from latest updates. Again i removed all data and did a new initalisation. Benchmark showed this results:
Benchmark
Average number of seconds to run all queries: 91.984 seconds
Minimum number of seconds to run all queries: 90.090 seconds
Maximum number of seconds to run all queries: 93.878 seconds
Number of clients running queries: 20
Average number of queries per client: 50
This is a huge difference. Whats going wrong here? Some people tell to disable binlogging, but the tested 5.7.25 server also has binlogging enabled, so it cant be that difference.
Are there some recommend settings which should applied for sure? Is MySQL8 running that bad with its default settings? Iam really surprised about this benchmarkresults.
Thanks,
Hans