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.
It appears that a single server instance is affected by a performance degradation.
My testing setup
Hardware details:
Bare metal server provided by packet.net, instance size: c2.medium.x86
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 a 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 used these combinations:
- innodb_flush_log_at_trx_commit=0 or 1
- Binlog: off or on
- sync_binlog=1000 or sync_binlog=1
The summary table, the number are transactions per second (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, when used with MySQL 8.0.15, provide much worse results than I had with MySQL 5.7.25
(*) in the case of trx_commit=0, binlog=off, MySQL 5.7.25 performance is very stable, and practically stays at the 11400 tps level. MySQL 8.0.15 varies a lot from 8758 tps to 10299 tps in 1 second resolution measurements
Update:
To clarify some comments, I’ve used latin1 CHARSET in this benchmark for both MySQL 5.7 and MySQL 8.0
Appendix:
[mysqld] datadir= /mnt/data/mysql socket=/tmp/mysql.sock ssl=0 #innodb-encrypt-tables=ON character_set_server=latin1 collation_server=latin1_swedish_ci skip-character-set-client-handshake #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
—
Photo by Suzy Hazelwood from Pexels