# MySQL-8.0 performance degradation (vs MySQL-5.7)

**URL:** <https://forums.percona.com/t/mysql-8-0-performance-degradation-vs-mysql-5-7/7958>\
**Category:** Percona Server for MySQL 8.0\
**Created:** [August 27, 2020, 4:14am UTC](https://forums.percona.com/t/mysql-8-0-performance-degradation-vs-mysql-5-7/7958 "2020-08-27T04:14:42Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ep11ripe](https://avatars.discourse-cdn.com/v4/letter/e/ea5d25/32.png) [@ep11ripe](https://forums.percona.com/u/ep11ripe)\
**Post date:** [August 27, 2020, 4:14am UTC](https://forums.percona.com/t/mysql-8-0-performance-degradation-vs-mysql-5-7/7958/1 "2020-08-27T04:14:42Z")

</div>

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.&nbsp;  
  
the&nbsp; 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)&nbsp;and kernel (&nbsp;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&nbsp; &nbsp; &nbsp; &nbsp;= mysql-binbinlog\_format = ROWuserstat&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= OFFthread\_statistics&nbsp; &nbsp; = OFFperformance\_schema&nbsp; &nbsp;= OFFevent\_scheduler&nbsp; &nbsp; &nbsp; = ONkey\_buffer\_size&nbsp; &nbsp; &nbsp; = 64Mread\_rnd\_buffer\_size = 8Mread\_buffer\_size&nbsp; &nbsp; &nbsp;= 2Msort\_buffer\_size&nbsp; &nbsp; &nbsp;= 2M  
max\_heap\_table\_size&nbsp; = 128Mtmp\_table\_size&nbsp; &nbsp; &nbsp; &nbsp;= 128M  
innodb\_strict\_mode&nbsp; &nbsp;= ON  
innodb\_stats\_auto\_recalc&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= ONinnodb\_stats\_persistent\_sample\_pages = 40  
innodb\_log\_buffer\_size = 8Minnodb\_log\_file\_size&nbsp; &nbsp;= 100M  
innodb\_buffer\_pool\_size&nbsp; &nbsp; &nbsp; &nbsp;= 4096M  
  
innodb\_buffer\_pool\_instances&nbsp; = 8innodb\_buffer\_pool\_load\_at\_startup&nbsp; = ONinnodb\_buffer\_pool\_dump\_at\_shutdown = ON  
innodb\_flush\_method&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = O\_DIRECTinnodb\_flush\_log\_at\_trx\_commit = 1innodb\_flush\_neighbors&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;= 1  
innodb\_thread\_concurrency&nbsp; &nbsp; &nbsp;= 0innodb\_commit\_concurrency&nbsp; &nbsp; &nbsp;= 0  
table\_open\_cache&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = 2000table\_open\_cache\_instances&nbsp; &nbsp; = 8  
innodb\_adaptive\_hash\_index&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = ON  
innodb\_io\_capacity&nbsp; &nbsp; &nbsp; = 400innodb\_write\_io\_threads = 4innodb\_read\_io\_threads&nbsp; = 4innodb\_purge\_threads&nbsp; &nbsp; = 2sync\_binlog = 0innodb\_undo\_log\_truncate = off  
  
STR:  
  
1. the test table:  
&nbsp;CREATE TABLE `test_wio` (&nbsp; `id` int NOT NULL AUTO\_INCREMENT,&nbsp; `i` int DEFAULT NULL,&nbsp; `v` varchar(200) DEFAULT NULL,&nbsp; PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO\_INCREMENT=100001 DEFAULT CHARSET=latin1;  
  
2: stored procedures:  
  
CREATE&nbsp; PROCEDURE `do_tst_wio`(iters int(10))begin&nbsp; declare i int(10) default 0;&nbsp; while i \< iters&nbsp; do&nbsp; &nbsp; insert into test\_wio(i, v) values (i, ‘test’);&nbsp; &nbsp; commit;&nbsp; &nbsp; set i := i + 1;&nbsp; end while;end;  
  
CREATE&nbsp; PROCEDURE `do_tst_rio`(iters int(10))begin&nbsp; declare ii int(10) default 0;&nbsp; declare vv varchar(2000);&nbsp; while ii \< iters&nbsp; do&nbsp; &nbsp; select v into vv from test\_wio where id = ii;&nbsp; &nbsp; set ii := ii + 1;&nbsp; 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:** &nbsp;  
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:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;176594&nbsp; &nbsp; &nbsp;100060log\_write\_requests:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;503207&nbsp; &nbsp; &nbsp; 56528  
buffer\_page\_written\_undo\_log:&nbsp; &nbsp; &nbsp; 692&nbsp; &nbsp; &nbsp; &nbsp; 190  
os\_data\_writes:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;178572&nbsp; &nbsp; &nbsp;101006os\_log\_bytes\_written:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;90442752&nbsp; 107235328os\_log\_fsyncs:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 117650&nbsp; &nbsp; &nbsp;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?&nbsp;

---

<div class="post-metadata">

**Author:** ![vadimtk](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vadimtk/32/9528_2.png) [@vadimtk](https://forums.percona.com/u/vadimtk)\
**Post date:** [August 27, 2020, 6:42am UTC](https://forums.percona.com/t/mysql-8-0-performance-degradation-vs-mysql-5-7/7958/2 "2020-08-27T06:42:03Z")

</div>

Please check  
[MySQL Bugs: #94283: MySQL 8.0.15 is slower than MySQL 5.7.25](https://bugs.mysql.com/bug.php?id=94283)  
[MySQL Bugs: #93734: MySQL 8.0 is 36 times slower than MySQL 5.7](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

---

<div class="post-metadata">

**Author:** ![ep11ripe](https://avatars.discourse-cdn.com/v4/letter/e/ea5d25/32.png) [@ep11ripe](https://forums.percona.com/u/ep11ripe)\
**Post date:** [August 27, 2020, 9:21am UTC](https://forums.percona.com/t/mysql-8-0-performance-degradation-vs-mysql-5-7/7958/3 "2020-08-27T09:21:54Z")

</div>

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.&nbsp;   
  
There are of course several internal threads, which may be concurring for the same internal resources such as&nbsp; 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) :&nbsp;176594 and&nbsp;100060 and a difference of “buffer\_page\_written\_undo\_log”:&nbsp;&nbsp;&nbsp;692&nbsp; &nbsp;vs 190.  
  
Looks like in MySQL-5.7 there weren’t&nbsp; at all writes&nbsp; to redo logs corresponding to undo segment changes.  
  
has something in undo handling & redo generation&nbsp; been&nbsp;changed&nbsp;significantly in&nbsp;MySQL-8.0?  
  
on the other hand MySQL-8.0 also consumes more CPU resources per seconds while doing less real&nbsp; work:  
  
MySQL-8.0\> vmstat 10procs -----------memory---------- —swap-- -----io---- -system-- ------cpu-----  
&nbsp;r&nbsp; b&nbsp; &nbsp;swpd&nbsp; &nbsp;free&nbsp; &nbsp;buff&nbsp; cache&nbsp; &nbsp;si&nbsp; &nbsp;so&nbsp; &nbsp; bi&nbsp; &nbsp; bo&nbsp; &nbsp;in&nbsp; &nbsp;cs us sy id wa st&nbsp;1&nbsp; 0&nbsp; &nbsp; &nbsp; 0 1258132 190872 1939220&nbsp; &nbsp; 0&nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp;0&nbsp; 9860 7381 16106 15 23 46 16&nbsp; 0  
&nbsp;2&nbsp; 0&nbsp; &nbsp; &nbsp; 0 1253356 190872 1941868&nbsp; &nbsp; 0&nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp;0 11029 7356 15855 15 24 46 16&nbsp; 0&nbsp;3&nbsp; 0&nbsp; &nbsp; &nbsp; 0 1250620 190872 1944512&nbsp; &nbsp; 0&nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp;0&nbsp; 9934 7545 16093 16 23 45 15&nbsp; 0&nbsp;1&nbsp; 0&nbsp; &nbsp; &nbsp; 0 1245960 190872 1947140&nbsp; &nbsp; 0&nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp;0&nbsp; 9661 7230 15766 15 23 46 16&nbsp; 0&nbsp;3&nbsp; 1&nbsp; &nbsp; &nbsp; 0 1242752 190872 1950208&nbsp; &nbsp; 0&nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp;0 11832 7973 17851 15 23 47 15&nbsp; 0&nbsp;MySQL-5.7:  
  
\> vmstat 10procs -----------memory---------- —swap-- -----io---- -system-- ------cpu-----&nbsp;r&nbsp; b&nbsp; &nbsp;swpd&nbsp; &nbsp;free&nbsp; &nbsp;buff&nbsp; cache&nbsp; &nbsp;si&nbsp; &nbsp;so&nbsp; &nbsp; bi&nbsp; &nbsp; bo&nbsp; &nbsp;in&nbsp; &nbsp;cs us sy id wa st&nbsp;1&nbsp; 1&nbsp; &nbsp; &nbsp; 0 5516068 190712 1916928&nbsp; &nbsp; 0&nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp;0 11890 4793 8527 12 15 50 24&nbsp; 0&nbsp;1&nbsp; 1&nbsp; &nbsp; &nbsp; 0 5512400 190712 1919172&nbsp; &nbsp; 0&nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp;0 15493 5336 10886 15 15 56 14&nbsp; 0&nbsp;1&nbsp; 0&nbsp; &nbsp; &nbsp; 0 5510416 190712 1921120&nbsp; &nbsp; 0&nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp;0 14665 4435 9801 13 16 57 14&nbsp; 0&nbsp;1&nbsp; 1&nbsp; &nbsp; &nbsp; 0 5508416 190712 1923052&nbsp; &nbsp; 0&nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp;0 14194 4329 9695 12 17 58 14&nbsp; 0&nbsp;1&nbsp; 0&nbsp; &nbsp; &nbsp; 0 5506928 190712 1924540&nbsp; &nbsp; 0&nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp;0 11225 4705 8516 11 14 52 24&nbsp; 0