Not the answer you need?
Register and ask your own question!

MySQL-8.0 performance degradation (vs MySQL-5.7)

ep11ripeep11ripe Current User Role Novice
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-bin
binlog_format = ROW
userstat             = OFF
thread_statistics    = OFF
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
sync_binlog = 0
innodb_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     100060
log_write_requests:             503207      56528

buffer_page_written_undo_log:      692        190

os_data_writes:                 178572     101006
os_log_bytes_written:         90442752  107235328
os_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? 

Comments

  • vadimtkvadimtk Contributor Percona Staff Role
    Please check
    https://bugs.mysql.com/bug.php?id=94283
    https://bugs.mysql.com/bug.php?id=93734
    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
  • ep11ripeep11ripe Current User Role Novice
    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 10
    procs -----------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 10
    procs -----------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


Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.