gktomar, can you please try to collect Innodb_buffer_pool_pages_flushed and Innodb_checkpoint_age during the workload?

Also, can you share a minimal complete configuration for repeating this? I do not think that Galera should play a role here, so the minimal configuration should be without Galera.

I now see that Checkpoint.png is a graph of the Innodb_checkpoint_age, but it is not clear to me what BP data.png represents.

Estimated dirty limit is set by innodb_max_dirty_pages_pct variables which is set to 90, total data in buffer pool is 163GB, hence Estimated dirty limit becomes 146.7GB. Data dirty are the Modified db pages. What BP data image is representing is dirty pages are getting flushed only when innodb_max_dirty_pages_pct is crossed, as per the doc it should have been started after crossing innodb_max_dirty_pages_pct_lwm

One other impact of this issue is stopping the mariadb takes a longer time as it have to flush the dirty pages to disk during the shutdown.

This issue can be reproduced on a stand alone mariadb instance with below configurations:

innodb_log_file_size = 2G
innodb_buffer_pool_size = 32G
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 3000
innodb_io_capacity_max = 5000
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit=0
innodb_adaptive_flushing_lwm=10
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=10
innodb_flush_sync=1

Innodb_buffer_pool_pages_flushed and Innodb_checkpoint_age around the issue are provided in the description of the ticket.

I tried to re-validate the issue with 10.5.11 and found that the adaptive flushing does kick in as expected.
------------------------------------------------------------------------

a. Started server with
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=90 (***** not the high value)
(innodb_adaptive_flushing_lwm=10 (default))

b. This means adaptive flushing will kick in till when

(A): dirty_pages > 90% of the buffer pool pages
---- OR ----
(B): redo > 10% full.

[I purposely used this configuration so that we see the effect of (B) before (A) kicks in].

------------------------------

at-start
=========

MariaDB [(none)]> show status like 'Innodb_buffer_pool%pages%'; show status like '%check%age%';

2 rows in set (0.000 sec)

-----------------

adaptive flushing kicked in
======================

MariaDB [(none)]> show status like 'Innodb_buffer_pool%pages%'; show status like '%check%age%';

------------------------------

Observations:
1. If we see the output (2) above we can observe that dirty pct is approx 5% (142583). Given our threshold of 90% (A) is not satisfied.

2. checkpoint_age > 10% that causes adaptive flushing to kick in as (B) is satisfied.
The effect of adaptive flushing could be seen with increasing Innodb_buffer_pool_pages_flushed.

Gaurav Tomar,

1. As Marko pointed you should try to monitor the pages-flushed.

2. I see with your standalone configuration you have used a pretty small redo-log file size (2G).
That means once you cross 200 MB mark you should see adaptive-flushing getting active.

[There could be a thin line as to whether you are meeting condition (A) or (B).
To ensure that you can increase the innodb_max_dirty_pages_pct_lwm so ideally
(A) is too far and (B) will be met immediately].

3. Despite the said fix there are good chances that you may still hit flush-storm.
Enabling adaptive flushing doesn't ensure that flushing can flush to match the rate
of dirty page generation.

This is another known issue. Adaptive flushing algorithm needs to be either more aggressive or better tune to handle increasing pressure.
I recall discussing it with Marko a while back there are some experiments done https://jira.mariadb.org/browse/MDEV-25113 but I have not tested it
yet.

@Krunal: Yes it seems the adaptive flushing is actually getting kicked in but the rate of flushing is not much to keep the Innodb_checkpoint_age below Innodb_checkpoint_max_age.

Will wait for the 10.5.12 release to test this scenario again.

I do not think that anything has been recently changed about the old adaptive flushing code. But, we may have made that code obsolete.

Slightly after the 10.5.11 release, we reduced the latency related to page flushing and LRU eviction, to ultimately address MDEV-25451: MDEV-25954, MDEV-25948, MDEV-25113, MDEV-26004.

The reported MDEV-25113 scenario (read-only workload being slow after a read-write workload) was addressed by 3 fixes:

1. removing buf_pool.flush_list_mutex acquisition from the page write completion callback
2. (maybe this should have been called the MDEV-25451 fix) implementing a ‘soft’ aggressive flushing trigger
3. (the main MDEV-25113 fix, postponed to MDEV-26004) reduce the latency of buf_LRU_get_free_block()

It may be that triggering a little more aggressive flushing earlier would reduce or eliminate the need for the old adaptive flushing mechanism. In control theory, PID controllers are well established and understood. The pre-flushing trigger is an example of a Positional controller with no I or D component. The "position" would be the checkpoint age.

If we still need the old adaptive flushing mechanism, I think that we should spend time to fully understand it, and conduct some experiments where we would collect more data and try to tune the P,I,D coefficients (if the old code really forms a PID controller).

gktomar, did you try MariaDB Server 10.5.12 yet?

@marko I'm currently testing this on 10.5.12, will get back with the results.

gktomar, do you have any results yet?

@Marko I tested the dirty pages flushing behavior in MariaDB 10.5.12, and below are my observations.

1. Adaptive flushing is getting enabled only when innodb_max_dirty_pages_pct_lwm<>0 even if innodb_adaptive_flushing is set to on.
2. Adaptive flushing is getting enabled both the times when the Innodb_checkpoint_age crosses the configured value of innodb_adaptive_flushing_lwm or when dirty pages cross the configured value of innodb_max_dirty_pages_pct_lwm.
3. Innodb_checkpoint_age does not reach the Innodb_checkpoint_max_age in this version as it used to in 10.5.11.
4. Instead when the Innodb_checkpoint_age reaches ~ 71% of the total redo log size, async flushing starts, keeping the Innodb_checkpoint_age below Innodb_checkpoint_max_age. Since innodb_flush_sync is enabled in our setup, we see a burst of IO at this point (this happens with furious flushing aka sync flush but not sure if this should be the case with async flushing also) and a small dip in QPS.

[ 466s ] thds: 36 tps: 8597.20 qps: 171976.99 (r/w/o: 120391.79/34389.80/17195.40) lat (ms,99%): 5.99 err/s: 0.00 reconn/s: 0.00
[ 467s ] thds: 36 tps: 6853.07 qps: 137122.31 (r/w/o: 95985.92/27431.26/13705.13) lat (ms,99%): 6.79 err/s: 0.00 reconn/s: 0.00
[ 468s ] thds: 36 tps: 8804.10 qps: 176017.02 (r/w/o: 123205.41/35202.40/17609.20) lat (ms,99%): 5.99 err/s: 0.00 reconn/s: 0.00

[ 696s ] thds: 36 tps: 8634.82 qps: 172760.46 (r/w/o: 120947.52/34541.29/17271.65) lat (ms,99%): 6.21 err/s: 0.00 reconn/s: 0.00
[ 697s ] thds: 36 tps: 6767.29 qps: 135356.78 (r/w/o: 94745.04/27077.16/13534.58) lat (ms,99%): 7.04 err/s: 0.00 reconn/s: 0.00
[ 698s ] thds: 36 tps: 8811.16 qps: 176146.18 (r/w/o: 123289.22/35235.64/17621.32) lat (ms,99%): 6.55 err/s: 0.00 reconn/s: 0.00

Here is the graph for checkpoint age in 10.5.12.

gktomar, thank you. It sounds like the flush-ahead patch is working as designed and preventing a deeper stall.

We may still want to do something about adaptive flushing, to have some background flushing prevent even that small drop of performance. On faster storage (such as NVMe), the write bursts should be less of a problem.

I have been running some benchmarks on 10.6 today. On a SATA SSD, when having about 40GiB of data in a 30GiB buffer pool (so that LRU eviction will be constantly happening during the oltp_update_non_index workload), I am seeing rather stable throughput. If I increase the buffer pool size to 40GiB so that no LRU eviction will take place, then I am seeing 5-second intervals with 0tps every now and then. We might dismiss that as a user error, because the innodb_io_capacity was configured way too high (for my NVMe, not the much slower SATA SSD).

It remains to be seen how much MDEV-26626 would improve this.

I have the same issue on 10.6.7. Regardless of `innodb_io_capacity`, or `innodb_adaptive_flushing_lwm` the checkpoint age keeps growing until it reaches the 75% and then its flushed furiously.

I agree with @Gaurav Tomar that:

Adaptive flushing is getting enabled only when `innodb_max_dirty_pages_pct_lwm<>0` even if `innodb_adaptive_flushing` is set to `on`.

When I set `innodb_max_dirty_pages_pct_lwm=0.000001` the adaptive flushing seems to kick in properly. With `innodb_max_dirty_pages_pct_lwm=0`, the adaptive flushing never kicks in, even when `innodb_adaptive_flushing_lwm` is reached

shalogo, yes, the value innodb_max_dirty_pages_pct_lwm=0 is special ("use innodb_max_dirty_pages_pct"). For a few releases between MDEV-23855 and MDEV-24537, that special meaning had been lost. See also MDEV-27295.

axel mentioned some time ago that the adaptive flushing does not work as well as he would like to. I think that some test parameters and results are needed before we can improve this area.

MySQL Bug #74637 has been filed for an interesting idea. Rephrased in the MariaDB 10.5+ terminology: If the innodb_max_dirty_pages_pct or innodb_max_dirty_pages_pct_lwm condition is satisfied but we are not yet near the log_free_check() limit (neither buf_flush_async_lsn nor buf_flush_sync_lsn has been set), then buf_flush_page_cleaner() could write out pages from buf_pool.LRU instead of buf_pool.flush_list.

There are two parameters related to background flushing: innodb_max_dirty_pages_pct_lwm>0 (default 0) and innodb_adaptive_flushing=ON (default ON).

I noticed that there is one more parameter related to adaptive flushing: innodb_adaptive_flushing_lwm (default: 10 percent of the log capacity). That could enable adaptive flushing even when innodb_max_dirty_pages_pct_lwm=0. If the innodb_log_file_size is large and the workload consists of update-in-place of non-indexed columns and involves a lot of thrashing (the working set is larger than the buffer pool), such checkpoint-oriented adaptive flushing may not help much. You’d additionally need to set innodb_max_dirty_pages_pct_lwm to the maximum allowed percentage of dirty pages in the buffer pool before page writes would be initiated.

jgagne suggested to me that 1 second is a long time to wait for buf_flush_page_cleaner() to free up some pages. But, in an oltp_update_index test with innodb_adaptive_flushing_lwm=0, I observed a significant regression of throughput for reducing the scheduling interval to 0.1 seconds:

This was a quick 120-second run with 16 concurrent connections, 4GiB buffer pool and log, 8×2M rows.

The first revision (making buf_flush_page_cleaner() stock the buf_pool.free list) could be worth broader testing.

I revised the main commit, still based on the same 10.6 parent commit 67a6ad0a4a36bd59fabbdd6b1cdd38de54e82c79. I did not run any performance tests yet, but I think that this one should work more accurately and with fewer operations on buf_pool.mutex. I think that the original commit may have been wrongly evicting pages that had been freed.

Many performance fixes on top of this were made in MDEV-26827.

axel managed to reproduce a couple of times a strange anomaly where the system throughput would soar for a while, and the buf_flush_page_cleaner thread would remain idle.

It looks like in MDEV-24369, a multiplication by 100 for calculating pct_for_dirty was inadvertently deleted. This follow-up fix should address that and make the adaptive flushing proportionally reduce the page write rate below innodb_io_capacity/s. By MDEV-26827, any ‘unused’ page writes in each one-second slot would be used for writing out up to innodb_lru_scan_depth dirty pages from the end of buf_pool.LRU list.

Something similar to jeanfrancois.gagne’s idea had been implemented in MDEV-6932 but probably lost when the InnoDB changes from MySQL 5.7.9 were applied to MariaDB Server 10.2.2.

In order to fix a frequent regression test due to a timeout (MDEV-32681), I ported this change to 10.5 as well.

I observed a small improvement when running a 120-second, 16-client Sysbench oltp_update_index test on Intel Optane 960 NVMe, with 4 GiB buffer pool and log size, 8×2M rows, and innodb_flush_log_at_trx_commit=0. This is an I/O bound workload; the data directory would grow to 12 or 13 GiB (that is, 8 or 9 GiB of data files in addition to the 4 GiB ib_logfile0).

The maximum latency as well as the sum of latency are slightly worse, but those are a little random in nature. We can observe some improvement in the throughput as well as in the typical latency.