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.
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.
|
1 |
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:
The summary table, the number are transactions per second (tps – the more the better)
|
1 |
+-------------------------------------------+--------------+--------------+-------+<br>| case | MySQL 5.7.25 | MySQL 8.0.15 | ratio |<br>+-------------------------------------------+--------------+--------------+-------+<br>| trx_commit=0, binlog=off | 11402 tps | 9840(*) | 1.16 |<br>+-------------------------------------------+--------------+--------------+-------+<br>| trx_commit=1, binlog=off | 8375 | 7974 | 1.05 |<br>+-------------------------------------------+--------------+--------------+-------+<br>| trx_commit=0, binlog=on, sync_binlog=1000 | 10862 | 8871 | 1.22 |<br>+-------------------------------------------+--------------+--------------+-------+<br>| trx_commit=0, binlog=on, sync_binlog=1 | 7238 | 6459 | 1.12 |<br>+-------------------------------------------+--------------+--------------+-------+<br>| trx_commit=1, binlog=on, sync_binlog=1 | 5970 | 5043 | 1.18 |<br>+-------------------------------------------+--------------+--------------+-------+ |
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
To clarify some comments, I’ve used latin1 CHARSET in this benchmark for both MySQL 5.7 and MySQL 8.0
|
1 |
[mysqld]<br><br>datadir= /mnt/data/mysql<br>socket=/tmp/mysql.sock<br>ssl=0<br><br>#innodb-encrypt-tables=ON<br><br>character_set_server=latin1 <br>collation_server=latin1_swedish_ci <br>skip-character-set-client-handshake <br><br>#skip-log-bin<br>log-error=error.log<br>log_bin = binlog<br>relay_log=relay<br>sync_binlog=1000<br>binlog_format = ROW<br>binlog_row_image=MINIMAL<br>server-id=1<br><br># Disabling symbolic-links is recommended to prevent assorted security risks<br>symbolic-links=0<br><br># Recommended in standard MySQL setup<br><br># general<br> table_open_cache = 200000<br> table_open_cache_instances=64<br> back_log=3500<br> max_connections=4000<br><br># files<br> innodb_file_per_table<br> innodb_log_file_size=15G<br> innodb_log_files_in_group=2<br> innodb_open_files=4000<br><br># buffers<br> innodb_buffer_pool_size= 40G<br> innodb_buffer_pool_instances=8<br> innodb_log_buffer_size=64M<br><br># tune<br> innodb_doublewrite= 1<br> innodb_thread_concurrency=0<br> innodb_flush_log_at_trx_commit= 0<br> innodb_flush_method=O_DIRECT_NO_FSYNC<br> innodb_max_dirty_pages_pct=90<br> innodb_max_dirty_pages_pct_lwm=10<br> innodb_lru_scan_depth=2048<br> innodb_page_cleaners=4<br> join_buffer_size=256K<br> sort_buffer_size=256K<br> innodb_use_native_aio=1<br> innodb_stats_persistent = 1<br> #innodb_spin_wait_delay=96<br><br># perf special<br> innodb_adaptive_flushing = 1<br> innodb_flush_neighbors = 0<br> innodb_read_io_threads = 16<br> innodb_write_io_threads = 16<br> innodb_io_capacity=1500<br> innodb_io_capacity_max=2500<br> innodb_purge_threads=4<br><br> innodb_adaptive_hash_index=0<br><br>max_prepared_stmt_count=1000000 |
—
Photo by Suzy Hazelwood from Pexels
