Hi Gordan, On Mon, Apr 8, 2024 at 9:41 AM Gordan Bobic via discuss <discuss@lists.mariadb.org> 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/9a545eb67ca8a666b87fc0aa8d22fe0a01c... 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