MySQL 8.0 performance degrades compared to 5.7

[Liang Cheng]

Hi, Team
We compared the mysql server (5.7.35 vs 8.0.29) performance using sysbench tool, and it seems no matter cluster or no-cluster, the perfomance of MySQL 8.0 degrades. Could you please let me know what could be the root cause?
We plan to upgrade mysql server from 5.7 to 8.0, however since the performance degrade, we are blocked.
We run the test on the same AWS VPC, with same instance type, so that the network and CPU/memory/IO is same between them.

===============================================================================
no-cluster
a. The instance type of mysql server is m4.xlarge (4cores, 16G ram), disk is 200G
b. Following are the additional setting on /etc/my.cnf
max_allowed_packet = 512M
innodb_buffer_pool_size = 4096M
port=3308
max_connections=2500
max_prepared_stmt_count=500000
transaction_isolation=READ-COMMITTED
c. the test database is created with latin1.
CREATE DATABASE sbtest DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_bin;
d. Result (threads/tranactions per second)
NOTE: The tables are 16, and each tables contain 100000 rows.
1) readonly (Almost same)
5.7
16,1391.54
32,1461.99
64,1376.97
128,1383.99
256,1415.70
512,1447.65
1024,1324.95
8.0
16,1219.74
32,1377.30
64,1444.10
128,1414.36
256,1417.00
512,1356.93
1024,1283.41

2)write-only (8.0 degrade 25~40%)
5.7
16,2407.48 32,3679.25 64,4659.77 128,5104.76 256,5292.39 512,5007.06 1024,4628.26
8.0
16,1506.66 32,2134.05 64,2852.34 128,3109.28 256,3534.04 512,3824.40 1024,3600.96

3) read-write (8.0 degrade about 15%)
5.7
16,904.02 32,1141.53 64,1258.94 128,1288.70 256,1299.84 512,1249.23 1024,1139.51
8.0
16,780.98 32,899.22 64,951.69 128,976.82 256,1019.88 512,1023.83 1024,916.67


cluster (3 mysql server, one is primary, 2 are slaves)
a. The instance type of primary server is m4.xlarge (4cores, 16G ram), disk is 200G , the type of salve servers are m4.large (2cores, 8G ram), 200G
b. Following are the addtional settings, the setting for group_replication is ignored, since besides the basic binlog and group_repilcaiton setting, there is no any special setting for perofmrnace.
max_allowed_packet = 512M
innodb_buffer_pool_size = 4096M
port=3308
max_connections=2500
max_prepared_stmt_count=500000
transaction_isolation=READ-COMMITTED
c. the test database is created with latin1.
CREATE DATABASE sbtest DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_bin;
d. Result (threads/tranactions per second)
NOTE: The tables are 16, and each tables contain 100000 rows.
1) readonly (8.0 degrade 30%)
5.7
16,1275.74 32,1372.65 64,1385.36 128,1403.80 256,1379.91 512,1280.15 1024,1165.09
8.0
16,805.34 32,918.84 64,959.96 128,961.55 256,931.51 512,851.35 1024,789.04
2) writeonly
5.7
16,1129 32,1137 64,1124.42 128,1133.45 512,1137.39 1024,1131.15
8.0
16,832 32,1160 64,1120.34 128,1125.24 512,1137.50 1024,1138.69
3) readwrite
5.7
16,661 32,743 128,804 256,814 512,791 1024,756
8.0
16,453 32,545 128,646 256,646 512,616 1024,572