MySQL Forums
Forum List  »  Performance

MySQL-8.0 performance degradation vs MySQL-5.7
Posted by: Eugene Polovnikov
Date: September 10, 2020 04:17AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL-8.0 performance degradation vs MySQL-5.7
243
September 10, 2020 04:17AM


Sorry, only registered users may post in this forum.

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.