# Enormous increase in number of redo writes in 8.0 with innodb log writer threads and slow transactions processing without it **URL:** \ **Category:** Percona Server for MySQL 8.0\ **Created:** [July 23, 2021, 1:09pm UTC](https://forums.percona.com/t/enormous-increase-in-number-of-redo-writes-in-8-0-with-innodb-log-writer-threads-and-slow-transactions-processing-without-it/11491 "2021-07-23T13:09:04Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![ep11ripe](https://avatars.discourse-cdn.com/v4/letter/e/ea5d25/32.png) [@ep11ripe](https://forums.percona.com/u/ep11ripe)\ **Post date:** [July 23, 2021, 1:09pm UTC](https://forums.percona.com/t/enormous-increase-in-number-of-redo-writes-in-8-0-with-innodb-log-writer-threads-and-slow-transactions-processing-without-it/11491/1 "2021-07-23T13:09:04Z")
while performing performance tests of our system in preparation for an upgrade to Percona MySQL 8.0 we have noticed that IO load increased by about two times and almost up to 100% utilization. so after concluding series of tests, which include a very short transaction performing one of insert operations used by our system and executed with the same fixed TPS per thread (16) and with the same number of threads (128) plus a periodical cleanup of records inserted earlier, we have discovered that a lot more of writes to redo logs and log fsyncs are performed in comparation with MySQL 5.7: there are in avg 48.91k (max 71.3k) of log write operations and in avg 25.43MB/s (max 36.85MB/s) on Percona MySQL 8.0.22 vs 1.71k (1.87k) and 5.98MB/s (8.55MB/s) on Percona MySQL 5.7.20. it appears that the new log writer threads, which are enabled by default, are responsible for it and after turning it off via the new option “innodb\_log\_writer\_threads”, the number of redo log write operations and amount of log data have been returned to values close to ones on MySQL 5.7. However, an average elapsed time per transaction is greatly increased: from 0.505ms on MySQL 8.0.22 with log writer threads (and 0.485ms on MySQL 5.7) up to 2.120ms on MySQL 8.0.22 without log writer threads and most of time (~1.68ms) have been spent during commits. the only changed metrics are ones related to row locks: an average number of threads waiting on row locks, which is most likely a consequence of an increased transaction time, and a number of innodb contentions: there are about three times more of OS wait operations and Spin waits. the both situations, i.e. a soaring spike of redo log related operations caused by redo log writer threads and an increased transaction time without it, look like serious degradations and probably caused by some bugs. any ideas how to relieve such situation? is it the expected behavior or should I file a formal bug report for it? there is a screenshot of PMM graphs showing redo log IOs during the test runs and contentions: a time interval from 14:20 until 14:40 corresponds MySQL 5.7, from 15:35 to 15:55 - MySQL 8.0 with redo log writer threads turned on and from 16:40 to 17:00 - MySQL 8.0 with redo log writer threads turned off. ![57_vs_80](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/6/632d811e94ed9cc81b1c8b2512545a294b2d2e2f.png) ![contentions_57vs80](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/5/56d16f6432d08bb24b5b0bf2c22251eafce6925e.png) the same options are used for the both mysql 5.7 and 8.0: [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 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 = 8192M 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 # audit log configuration plugin-load = audit\_log=audit\_log.so audit\_log\_strategy = ASYNCHRONOUS audit\_log\_file = audit.log audit\_log\_format = NEW audit\_log\_policy = QUERIES audit\_log\_rotate\_on\_size = 1073741824 audit\_log\_rotations = 10 audit\_log\_handler = FILE sync\_binlog = 0 # PMM performance\_schema=ON performance-schema-instrument=‘statement/%=ON’ performance-schema-consumer-statements-digest=ON innodb\_monitor\_enable=all ---
**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:** [July 27, 2021, 3:43pm UTC](https://forums.percona.com/t/enormous-increase-in-number-of-redo-writes-in-8-0-with-innodb-log-writer-threads-and-slow-transactions-processing-without-it/11491/2 "2021-07-27T15:43:44Z")
Hi @ep11ripe I believe what you see is related to new MySQL 8.0 redo log design, you can read more there: [https://mysqlserverteam.com/mysql-8-0-new-lock-free-scalable-wal-design/](https://mysqlserverteam.com/mysql-8-0-new-lock-free-scalable-wal-design/) Unfortunately I think it may lead to performance issues you observe, and I do not have good recommendation how to solve them. You may play with [https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-logging.html](https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-logging.html) See section “Optimize the use of spin delay by user threads waiting for flushed redo. Spin delay helps reduce latency.” ---
**Author:** ![ep11ripe](https://avatars.discourse-cdn.com/v4/letter/e/ea5d25/32.png) [@ep11ripe](https://forums.percona.com/u/ep11ripe)\ **Post date:** [July 28, 2021, 3:55pm UTC](https://forums.percona.com/t/enormous-increase-in-number-of-redo-writes-in-8-0-with-innodb-log-writer-threads-and-slow-transactions-processing-without-it/11491/3 "2021-07-28T15:55:13Z")
it seems that one of goals of reworking redo logs is too reduce a number of fsync operations, but in reality I see an opposite picture even in a simple single thread test doing a 100k of inserts with a single insert per transaction. there are some metrics collected for runs with and without log writer threads on percona mysql 8.0.22. they perform about the same number of work: trx\_commits\_insert\_update 100040 100036 trx\_rw\_commits 100041 100037 both log writer and log flusher threads are actively working in the 1st case: log\_flusher\_no\_waits 21064 0 log\_flusher\_wait\_loops 125614 0 log\_flusher\_waits 99947 0 log\_flush\_notifier\_no\_waits 71 0 log\_flush\_notifier\_wait\_loops 200354 0 log\_flush\_notifier\_waits 111635 0 log\_on\_flush\_wait\_loops 173413 0 log\_on\_flush\_waits 100109 0 log\_write\_notifier\_no\_waits 12 0 log\_write\_notifier\_wait\_loops 175388 0 log\_write\_notifier\_waits 107184 0 log\_writer\_no\_waits 62797 0 log\_writer\_wait\_loops 209129 0 log\_writer\_waits 118107 0 a total number of log flushes is about 20% bigger with new log writer threads os\_log\_fsyncs 120731 100152 os\_data\_fsyncs 121602 100772 dblwr\_async\_requests 944 624 and while some of stats are almost identical : log\_full\_block\_writes 59100 59083 buffer\_pool\_write\_requests 505061 504693 log\_write\_requests 503220 502838 the amount of data written is increased by ~15% and a number log\_partial\_block\_writes increased by ~20% and it’s almost the same as for number of flushes. buffer\_data\_written 136507392 120154112 log\_writes 180904 159210 os\_log\_bytes\_written 92658688 81554944 os\_data\_writes 182860 160524 log\_partial\_block\_writes 121804 100127 buffer\_pages\_written 944 624 innodb\_dblwr\_pages\_written 944 624 innodb\_dblwr\_writes 944 624 buffer\_page\_written\_undo\_log 741 438 the similar tests but using a “real” table with several secondary indexes shows more than 240% increase in a number of flushes and log partial block writes as well as more than 150% increase in amount of log data written and the same test performed in parallel (128 threads doing 16 tps each) the difference is just monstrous ![log_writes_details_80](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/a/a0dc77f311a15e551f6cdbc9441b655637dfb809.png) ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [July 28, 2021, 9:11pm UTC](https://forums.percona.com/t/enormous-increase-in-number-of-redo-writes-in-8-0-with-innodb-log-writer-threads-and-slow-transactions-processing-without-it/11491/4 "2021-07-28T21:11:42Z")
@ep11ripe You have plenty of data to open a case at [https://bugs.mysql.com/](https://bugs.mysql.com/) since this is their design.