mysql8 cluster performance degrades on sysbench readonly test
Hi,
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 environment, the perfomance of MySQL 8.0 degrades. Could you please help if you have experience on this.
We plan to upgrade mysql server from 5.7 to 8.0, but the performance degradation is one concern.
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.
About 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, mysql8 contains several variables to make it can work
max_allowed_packet = 512M
innodb_buffer_pool_size = 4096M
max_connections=2500
max_prepared_stmt_count=500000
transaction_isolation=READ-COMMITTED
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
expire_logs_days=3
transaction_write_set_extraction=XXHASH64
group_replication_group_name="d6e78fb4-e50c-42d6-abe8-018189c89e56"
group_replication_start_on_boot=off
group_replication_local_address="10.50.10.70:33081"
group_replication_group_seeds="10.50.10.70: 33081, 10.50.10.134: 33081, 10.50.10.101: 33081"
group_replication_bootstrap_group=off
# following settings are only for 8.0
group_replication_recovery_get_public_key=on
binlog_transaction_dependency_tracking=WRITESET
slave_preserve_commit_order=ON
slave_parallel_type=LOGICAL_CLOCK
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 (almost same)
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