Hi Gordan, On Mon, Apr 8, 2024 at 9:41 AM Gordan Bobic via discuss <[email protected]> wrote: > > I've mentioned this here a few times before, but it looks like the > latest 10.5.x update to 10.5.24 actually made things worse than in > 10.5.23. > > I have attached two screenshots of transaction log checkpoint age over > a 5 minute oltp_write_only sysbench test with 10.5.23 and 10.5.24. > > 10.5.23 starts off with the flushing all or nothing behaviour but it > dampens out after the first minute or so. 10.5.24 exhibits it > permanently. I ran multiple separate tests and the behaviour is very > consistent for each version. > > 10.5.23 almost (not quite but almost) seems to have had things fixed > to a state where things were in 10.5.8.
Thank you for the report. A nitpick on the terminology: There is no such thing as "the transaction log" in InnoDB. There is the write-ahead log or redo log (ib_logfile0), which is the primary mechanism for making changes durable. Each write transaction is associated with a number of undo log pages, which, just like any other persistent pages, are covered by the write-ahead log. I suspect that your observation is related to page flushing, and not writes of the log file. I see that MariaDB Server 10.5.24 includes two commits related to MDEV-26055: https://github.com/MariaDB/server/commit/9a545eb67ca8a666b87fc0aa8d22fe0a01c4d9d4 and its parent. The reason why these fixes were ported from 10.6 was that a regression test mentioned in https://jira.mariadb.org/browse/MDEV-32681 was failing due to taking an extreme amount of time. > I also tested the latest 10.6.17 and that exhibits the > same broken behaviour. This is interesting. Can you try to collect more metrics, something like the oltp_*.png graphs in https://jira.mariadb.org/browse/MDEV-23855? Based on just one metric (such as transaction throughput or I/O rate), it is difficult to make any accurate conclusions. Please note that the 10.6.17 release is affected by a performance regression https://jira.mariadb.org/browse/MDEV-33508. It would make sense to apply that fix or to test the latest 10.6 development snapshot. > Is a fix for this behaviour expected any time soon? As soon as we know what is going on, things should be simple. > The config I tested with is as follows: I see that your innodb_log_file_size is only a quarter of the innodb_buffer_pool_size. Increasing it could reduce the need to write out dirty pages. I don't remember what exactly innodb_log_write_ahead_size was supposed to do. The log writing was simplified and made compatible with O_DIRECT and physical block sizes up to 4096 bytes, in MDEV-14425, which is available in MariaDB Server 10.11. Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
