It appears that MySQL-8.0 requires lots more hardware resources to perform the same amount of operations as on MySQL-5.7, especially disk write operations.
the following quite simple tests have been executed on the both MySQL-8.0 (Server version: 8.0.20-11 Percona Server (GPL), Release 11, Revision 613e312) and MySQL-5.7 (Server version: 5.7.20-18-log Percona Server (GPL), Release 18, Revision 2c06f4d), on the same hardware, OS version (Oracle Linux Server release 7.5) and kernel ( 3.10.0-1127.13.1.el7.x86_64 ) and some stats are gathered:
mostly identical settings in my.cnf were used during tests:
max_connections = 500
log-bin = mysql-binbinlog_format = ROWuserstat = OFFthread_statistics = OFFperformance_schema = OFFevent_scheduler = ONkey_buffer_size = 64Mread_rnd_buffer_size = 8Mread_buffer_size = 2Msort_buffer_size = 2M
max_heap_table_size = 128Mtmp_table_size = 128M
innodb_strict_mode = ON
innodb_stats_auto_recalc = ONinnodb_stats_persistent_sample_pages = 40
innodb_log_buffer_size = 8Minnodb_log_file_size = 100M
innodb_buffer_pool_size = 4096M
innodb_buffer_pool_instances = 8innodb_buffer_pool_load_at_startup = ONinnodb_buffer_pool_dump_at_shutdown = ON
innodb_flush_method = O_DIRECTinnodb_flush_log_at_trx_commit = 1innodb_flush_neighbors = 1
innodb_thread_concurrency = 0innodb_commit_concurrency = 0
table_open_cache = 2000table_open_cache_instances = 8
innodb_adaptive_hash_index = ON
innodb_io_capacity = 400innodb_write_io_threads = 4innodb_read_io_threads = 4innodb_purge_threads = 2sync_binlog = 0innodb_undo_log_truncate = off
STR:
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 AUTO_INCREMENT=100001 DEFAULT CHARSET=latin1;
2: stored procedures:
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;
CREATE PROCEDURE do_tst_rio(iters int(10))begin declare ii int(10) default 0; declare vv varchar(2000); while ii < iters do select v into vv from test_wio where id = ii; set ii := ii + 1; end while;end;
runs On MySQL-8.0:
mysql> truncate table test_wio; call do_tst_wio(100000); call do_tst_rio(100000); call do_tst_rio(100000);Query OK, 0 rows affected (0.13 sec)
Query OK, 0 rows affected (3 min 45.00 sec)
Query OK, 1 row affected (14.75 sec)
Query OK, 1 row affected (14.31 sec)
On MySQL-5.7:
mysql> truncate table test_wio; call do_tst_wio(100000); call do_tst_rio(100000); call do_tst_rio(100000);Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (2 min 44.67 sec)
Query OK, 1 row affected (10.82 sec)
Query OK, 1 row affected (10.65 sec)
And there are some metrics stats (from INNODB_METRICS) gathered during performing the “do_tst_wio” tests (8.0 vs 5.7)
log_writes: 176594 100060log_write_requests: 503207 56528
buffer_page_written_undo_log: 692 190
os_data_writes: 178572 101006os_log_bytes_written: 90442752 107235328os_log_fsyncs: 117650 100083
number of disk write requests on a MySQL data volume (from /sys/devices/virtual/block/dm-3/stat) is increasing as well:
546573 vs 493750
Is it expected and what is causing such increase in a number of redo and undo write operations ? why quite simple a read only test “do_tst_rio” consumes lots more CPU time?
Any ways to improve it?
Please check
MySQL Bugs: #94283: MySQL 8.0.15 is slower than MySQL 5.7.25
MySQL Bugs: #93734: MySQL 8.0 is 36 times slower than MySQL 5.7
Both bugs are marked as fixed in 8.0.22, so we need to wait on 8.0.22 release and see if there are improvements
I’m not sure if those bug reports are directly related to my case which was a single thread, while in those reports were lots of user threads.
There are of course several internal threads, which may be concurring for the same internal resources such as redo log buffers, but what is really worrying me is such a big difference in a number of log writes for the same number of transaction (100000) : 176594 and 100060 and a difference of “buffer_page_written_undo_log”: 692 vs 190.
Looks like in MySQL-5.7 there weren’t at all writes to redo logs corresponding to undo segment changes.
has something in undo handling & redo generation been changed significantly in MySQL-8.0?
on the other hand MySQL-8.0 also consumes more CPU resources per seconds while doing less real work:
MySQL-8.0> vmstat 10procs -----------memory---------- —swap-- -----io---- -system-- ------cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st 1 0 0 1258132 190872 1939220 0 0 0 9860 7381 16106 15 23 46 16 0
2 0 0 1253356 190872 1941868 0 0 0 11029 7356 15855 15 24 46 16 0 3 0 0 1250620 190872 1944512 0 0 0 9934 7545 16093 16 23 45 15 0 1 0 0 1245960 190872 1947140 0 0 0 9661 7230 15766 15 23 46 16 0 3 1 0 1242752 190872 1950208 0 0 0 11832 7973 17851 15 23 47 15 0 MySQL-5.7:
> vmstat 10procs -----------memory---------- —swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 1 1 0 5516068 190712 1916928 0 0 0 11890 4793 8527 12 15 50 24 0 1 1 0 5512400 190712 1919172 0 0 0 15493 5336 10886 15 15 56 14 0 1 0 0 5510416 190712 1921120 0 0 0 14665 4435 9801 13 16 57 14 0 1 1 0 5508416 190712 1923052 0 0 0 14194 4329 9695 12 17 58 14 0 1 0 0 5506928 190712 1924540 0 0 0 11225 4705 8516 11 14 52 24 0