MySQL Bugs: #100811: performance degradation in MySQL-8.0 vs MySQL-5.7 in simple DML operations.

Bug #100811	performance degradation in MySQL-8.0 vs MySQL-5.7 in simple DML operations.
Submitted:	11 Sep 2020 9:22	Modified:	11 Sep 2020 13:25
Reporter:	Eugene Polovnikov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S5 (Performance)
Version:	8.0.20	OS:	Oracle Linux
Assigned to:		CPU Architecture:	Any

Description:
It appears that MySQL-8.0 requires lots more hardware resources to perform the same amount of operations as on MySQL-5.7, especially for simple DML operations.

the simple tests performing 100000 simple transactions with in a stored db procedure have been performed on the both MySQL-8.0 ( 8.0.20 MySQL Community Server - GPL) and MySQL-5.7 (S 5.7.22 MySQL Community Server (GPL)), on the same hardware, OS version (Oracle Linux Server release 7.5) and kernel ( 3.10.0-1127.13.1.el7.x86_64 ):

*** on MySQL-8.0:

mysql> truncate table test_wio; call do_tst_wio(100000);

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (3 min 38.70 sec)

*** on MySQL-5.7:

mysql> truncate table test_wio; call do_tst_wio(100000);

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (2 min 48.03 sec)

so, MySQL-8.0 is about 30% slower in this simple test.

And there are some metrics stats (from INNODB_METRICS) gathered during performing the "do_tst_wio" tests:

Metric 8.0 5.7

log_writes: 174794 100045
log_write_requests: 503149 56921

buffer_page_written_undo_log: 794 330

dblwr_flush_requests: 488 223

os_data_writes: 176527 100883
os_data_fsyncs: 116475 100542
os_log_bytes_written: 89524736 107632640
os_log_fsyncs: 115893 100051

number of disk write requests on a MySQL data volume (from /sys/devices/virtual/block/<DEV>/stat) is increasing as well:

541804 vs 494192

How to repeat:
1. the test table:

CREATE TABLE `test_wio` (
`id` int NOT NULL AUTO_INCREMENT,
`i` int DEFAULT NULL,
`v` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2: the stored procedure:

CREATE PROCEDURE `do_tst_wio`(iters int(10))
begin
declare i int(10) default 0;
while i < iters
do
insert into test_wio(i, v) values (i, 'test');
commit;
set i := i + 1;
end while;
end;

3. execute the test
mysql> truncate table test_wio; call do_tst_wio(100000);

the following settings was used for the both mysql-5.7 and 8.0 during tests:

[mysqld]

innodb_numa_interleave = ON

relay-log = relay-bin
relay_log_info_repository = TABLE
relay_log_recovery = ON

character-set-filesystem = utf8
default-time-zone = +00:00

log-bin-trust-function-creators = ON
explicit_defaults_for_timestamp = ON
max_allowed_packet = 16M

character_set_server = latin1
collation_server = latin1_swedish_ci
innodb_autoinc_lock_mode = 1
log_error_verbosity = 3
master_info_repository = FILE

default_authentication_plugin=mysql_native_password

max_connections = 500

log-bin = mysql-bin
binlog_format = ROW

performance_schema = OFF
event_scheduler = ON

key_buffer_size = 64M
read_rnd_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 2M

max_heap_table_size = 128M
tmp_table_size = 128M

innodb_strict_mode = ON

innodb_stats_auto_recalc = ON
innodb_stats_persistent_sample_pages = 40

innodb_log_buffer_size = 8M
innodb_log_file_size = 100M

innodb_buffer_pool_size = 4096M

innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_at_shutdown = ON

innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_flush_neighbors = 1

innodb_thread_concurrency = 0
innodb_commit_concurrency = 0

table_open_cache = 2000
table_open_cache_instances = 8

innodb_adaptive_hash_index = ON

innodb_io_capacity = 400
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_purge_threads = 2

innodb_print_all_deadlocks = ON

sync_binlog = 0

innodb_undo_log_truncate = off

Thank you for the bug report. Reported in several prior bug report: https://bugs.mysql.com/bug.php?id=94387, https://bugs.mysql.com/bug.php?id=93734,
https://bugs.mysql.com/bug.php?id=94283