MySQL-8.0 performance degradation vs MySQL-5.7
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 following simple tests 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 ):
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;
results:
*** 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/dm-3/stat) is increasing as well:
541804 vs 494192
Is it something expected and normal? and what is causing such increase in a number of redo and undo write operations ?
the identical settings in my.cnf were used 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
Subject
Views
Written By
Posted
MySQL-8.0 performance degradation vs MySQL-5.7
1380
September 10, 2020 04:17AM
571
September 10, 2020 09:17AM
607
September 11, 2020 01:19AM
502
September 11, 2020 09:33AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.