Bug #94283 MySQL 8.0.15 is slower than MySQL 5.7.25
Submitted: 11 Feb 21:01 Modified: 20 Feb 15:28
Reporter: Vadim Tkachenko Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S5 (Performance)
Version:8.0.15 OS:Ubuntu
Assigned to: CPU Architecture:Any

[11 Feb 21:01] Vadim Tkachenko
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
[20 Feb 15:28] Sinisa Milivojevic
Hi Vadim,

Your report is verified as reported.

Performance of our 8.0 server is been worked upon, so this is work-in-progress.

Thank you.
[28 Jun 7:34] tu ming
try this configuration with default values of MySQL 5.7.25:
innodb_max_dirty_pages_pct=75
innodb_max_dirty_pages_pct_lwm=0
[28 Jun 11:56] Sinisa Milivojevic
Thank you, Mr. Ming.