Description:
MySQL 8.0.15 performs worse in sysbench oltp_read_write than MySQL 5.7.25
Initially I was testing Group Replication performance and was puzzled why MySQL 8.0.15 performs consistently worse than MySQL 5.7.25.
But it appears it is because that single instance server also affect by performance degradation.
My testing setup:
Hardware:
Baremetal server provided by packet.net, instance size: c2.medium.x86
Hardware details:
24 Physical Cores @ 2.2 GHz
(1 X AMD EPYC 7401P)
Memory
64 GB of ECC RAM
Storage : INTEL® SSD DC S4500, 480GB
This is server grade SATA SSD.
Benchmark:
sysbench oltp_read_write --report-interval=1 --time=1800 --threads=24 --tables=10 --table-size=10000000 --mysql-user=root --mysql-socket=/tmp/mysql.sock run
In the following summary I use combinations:
innodb_flush_log_at_trx_commit=0,1 and Binlog: off, on with sync_binlog=1000, sync_binlog=1
The summary table, the number are tps (the more the better)
+-------------------------------------------+--------------+--------------+-------+
| case | MySQL 5.7.25 | MySQL 8.0.15 | ratio |
+-------------------------------------------+--------------+--------------+-------+
| trx_commit=0, binlog=off | 11402 tps | 9840(*) | 1.16 |
+-------------------------------------------+--------------+--------------+-------+
| trx_commit=1, binlog=off | 8375 | 7974 | 1.05 |
+-------------------------------------------+--------------+--------------+-------+
| trx_commit=0, binlog=on, sync_binlog=1000 | 10862 | 8871 | 1.22 |
+-------------------------------------------+--------------+--------------+-------+
| trx_commit=0, binlog=on, sync_binlog=1 | 7238 | 6459 | 1.12 |
+-------------------------------------------+--------------+--------------+-------+
| trx_commit=1, binlog=on, sync_binlog=1 | 5970 | 5043 | 1.18 |
+-------------------------------------------+--------------+--------------+-------+
Summary: MySQL 8.0.15 is persistently worse than MySQL 5.7.25.
In the worst case with trx_commit=0 and sync_binlog=1000, it is worse by 22%, which is huge.
I was looking to use these settings for Group Replication testing, but these settings MySQL 8.0.15 results are much worse than MySQL 5.7.25 results
(*) - in case of trx_commit=0, binlog=off, MySQL 5.7.25 performance is very stable, practically stays on 11400 level, while MySQL 8.0.15 varies a lot from 8758 tps to 10299 tps in 1 second resolution measurements
My.cnf:
[mysqld]
datadir= /mnt/data/mysql
socket=/tmp/mysql.sock
ssl=0
#innodb-encrypt-tables=ON
#skip-log-bin
log-error=error.log
log_bin = binlog
relay_log=relay
sync_binlog=1000
binlog_format = ROW
binlog_row_image=MINIMAL
server-id=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
# general
table_open_cache = 200000
table_open_cache_instances=64
back_log=3500
max_connections=4000
# files
innodb_file_per_table
innodb_log_file_size=15G
innodb_log_files_in_group=2
innodb_open_files=4000
# buffers
innodb_buffer_pool_size= 40G
innodb_buffer_pool_instances=8
innodb_log_buffer_size=64M
# tune
innodb_doublewrite= 1
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit= 0
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=10
innodb_lru_scan_depth=2048
innodb_page_cleaners=4
join_buffer_size=256K
sort_buffer_size=256K
innodb_use_native_aio=1
innodb_stats_persistent = 1
#innodb_spin_wait_delay=96
# perf special
innodb_adaptive_flushing = 1
innodb_flush_neighbors = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity=1500
innodb_io_capacity_max=2500
innodb_purge_threads=4
innodb_adaptive_hash_index=0
max_prepared_stmt_count=1000000
How to repeat:
Make MySQL 8 Great Again
MySQL 8.0 does not have to be slower than MySQL 5.7
