This problem was originally observed while we tested MDEV-24949. MDEV-25093 has some similarity with this.

krizhanovsky ran a test that suggests that the adaptive flushing could be improved:

If stable throughput and latency are more important than minimizing write amplification (and maximizing the lifetime of the storage), that graph does show ‘bad’ behaviour. The cause of the ‘badness’ should be that we let the checkpoint age hover around the maximum. Occasionally, the page cleaner would be able to write out a larger burst of pages, presumably because the log had already been durably written up to FIL_PAGE_LSN on all of the pages. (Often, the page cleaner would have to skip such pages, hoping to be able to write them during the next batch, after more of the redo log has been written.)

While I believe that such performance may be desirable in some deployments, something definitely could be done to improve the adaptive flushing. I would like to apply control theory to this problem and implement the adaptive flushing as a PID controller. The inputs would include the checkpoint age and the length of buf_pool.flush_list, possibly relative to buf_pool.LRU (but maybe we should omit pages of temporary or dropped tables from the latter count).

After several iteration of tuning the flushing process for the workload I came to the following strange results. The bad thing about the previous graphs is that while checkpoint age and number of dirty pages were growing, there were almost no IO utill we hit the flush storm. So I set

innodb_max_dirty_pages_pct=30
innodb_max_dirty_pages_pct_lwm=10
innodb_io_capacity=10000
innodb_io_capacity_max=20000
innodb_write_io_threads=8

to make flushing to start earlier. With these settings the number of dirty pages is relatively stable, but LSN age still hits the maximum value. This graph contains buffer_flush_sync_waits, which spikes right during the performance dip.

The stange results about adaptive flushing is that the number of flushed pages reaches the peak right before the performance dip and IO is significantly reduced during the performance dip and after that.

The disk can handle up to 64K random writes of 16KB size (verified with fio), however InnoDB still doesn't reach the max IO capacity even during the flush storm.

I also collected graphs for the same workload and much smaller buffer pool and bit higher IO capacity:

innodb_io_capacity=15000

innodb_io_capacity_max=20000

innodb_buffer_pool_size= 25G

The behaviour looks the same: underutilized IO during normal operation and sharp waves when checkpoint age reaches the maximum value.

What if we make mtr_t::commit() trigger a less eager flush earlier? That is, make log_close() return at least one more value to mtr_t::finish_write(), instead of the current Boolean (indicating whether buf_flush_async() will have to be invoked). It would seem reasonable to restore two checkpoint age limits and introduce another atomic variable buf_flush_async_lsn (in addition to buf_flush_sync_lsn) for the case when only a lower checkpoint age limit is exceeded.

In buf_flush_page_cleaner(), we would avoid goto unemployed as long as buf_flush_async_lsn is set. We would reset that variable as soon as we are below the checkpoint age limit again.

Based on some analysis of stack traces in MDEV-25773, I realized that all several threads executing buf_page_write_completion() will be competing for buf_pool.flush_list_mutex. Could we employ some lock-free data structure to avoid the mutex acquisition? And let the buf_flush_page_cleaner thread remove the already flushed blocks on the next run? Maybe we could just reset the oldest_modification field to signal that the block is clean, and introduce a separate flag "exists as garbage in the buf_pool.flush_list" to prevent the list from becoming corrupted?

Even simpler idea: Use a special value such as oldest_modification=1 to indicate that the page is actually clean but exists as garbage in buf_pool.flush_list. LSN values less than 2048 are impossible by the design of the redo log file.

Garbage would be collected (list member removed and the oldest_modification reset to 0) by any code that encounters such a member in buf_pool.flush_list while holding buf_pool.flush_list_mutex. Also, if a block is evicted and its oldest_modification is not 0, we would first remove it from buf_pool.flush_list.

This statistic were collected for the default values for innodb_flushing_avg_loops, innodb_max_dirty_pages_pct, and innodb_max_dirty_pages_pct_lwn with the Marko's patch MDEV-25113.patch and

diff --git a/storage/innobase/log/log0log.cc b/storage/innobase/log/log0log.cc

index 83036d81658..e294f41f0fd 100644

--- a/storage/innobase/log/log0log.cc

+++ b/storage/innobase/log/log0log.cc

@@ -163,7 +163,7 @@ log_set_capacity(ulonglong file_size)

        log_sys.log_capacity = smallest_capacity;

-	log_sys.max_modified_age_async = margin - margin / 8;

+	log_sys.max_modified_age_async = margin - margin / 4;

 	log_sys.max_checkpoint_age = margin;

        mysql_mutex_unlock(&log_sys.mutex);

If the mutex is the problem, then I'd expect to see a spike on the mutex during the performance gap, but actually there are lower number of mutex events during the dip.

krizhanovsky, I think that the buf_pool.flush_list_mutex hold time is typically rather short, with the exception of special processing of a single tablespace (MDEV-25773). In your graphs, we see a high number of events on the mutex, which I think should be due to buf_flush_note_modification() adding blocks to the start of the list. If we implement my ‘lazy removal’ idea, the numerous buf_page_write_completion() will stop interfering with concurrent buf_flush_note_modification() from user threads.

I have an even wilder idea: Implement buf_pool.flush_list as a lock-free singly-linked list. I would expect that a lock-free implementation of a singly-linked list is almost trivial. We can see that the addition to the list is protected by log_sys.flush_order_mutex. Declaring the ‘next’ pointers as Atomic_relaxed<buf_page_t*> could be a good starting point.

Moving to a singly-linked list would require a rewrite of buf_flush_LRU_list_batch(). Currently it iterates the buf_pool.LRU list, and then invokes buf_flush_discard_page() for individual blocks. When removing from a singly-linked list, we need to know the address of the preceding block. We get it for free when traversing the buf_pool.flush_list, but in buf_flush_LRU_list_batch() an extra traversal of the list would be needed.

Also the function buf_flush_relocate_on_flush_list() (which is only used with ROW_FORMAT=COMPRESSED tables) could take a significant performance hit from moving to a singly-linked list.

I will not be able to implement any of this before I have made some significant progress with MDEV-25506 and MDEV-25783.

I worked on a prototype that implements the lazy deletion from buf_pool.flush_list. The test innodb.ibuf_not_empty is failing, presumably due to a missed page write, so it is not ready for stress testing or performance testing yet.

I noticed that buf_page_write_complete() is also acquiring buf_pool.mutex. Also some other flush list traversal is covered by both buf_pool.mutex and buf_pool.flush_list_mutex. This is likely to be somewhat of a bottleneck, and we should try to try to reduce the use of buf_pool.mutex.

After some experiments, I do not think that we can easily remove buf_pool.mutex from buf_page_write_complete().

Some follow-up work was postponed to MDEV-26004 due to mixed results.