I think that it is correct to claim that regression this is also caused by MDEV-33053, because that change enables buf_flush_page_cleaner() to run continuously when the buffer pool is about to run out.

Thanks Marko. I 100% agree and am happy that my review comment was actually pointing to the right issue.

origin/10.6-MDEV-33508 208f3ee34381f7c8a79d114f610c42bb53a5f394 2024-02-21T11:35:06+02:00
behaved as good as the official 10.6 during RQG testing.

Some prerequisites for hitting this anomaly are:

• The system is running out of buffer pool
• buf_pool.flush_list is large

The buf_pool.flush_list size is reflected by the status variable Innodb_buffer_pool_pages_dirty, and the buf_pool.free size (number of available pages) is reflected by Innodb_buffer_pool_pages_free. There is also the buf_pool.LRU (Innodb_buffer_pool_pages_data) that reflects the number of data pages in the buffer pool.

These can be queried for instance as follows:

SELECT variable_name,variable_value FROM information_schema.global_status

WHERE variable_name LIKE 'Innodb_buffer_pool_pages%';

If Innodb_buffer_pool_pages_free is larger than innodb_lru_scan_depth / 2, no traversal of the entire buf_pool.flush_list should take place.

Configuring a larger innodb_buffer_pool_size might work around this problem, if the workload fits in the buffer pool. Normally it should be set to 60 to 80 per cent of the available memory.

If innodb_max_dirty_pages_pct_lwm is set to a nonzero value, or if innodb_max_dirty_page_pct is set to a lower value than the default 90%, the buf_pool.flush_list should remain short. The reason why we allow up to 90% of the buffer pool to be dirty is that it helps avoid write amplification: the same pages can be modified over and over again in the buffer pool, without having to be written back to the data files every time. Only the write to the write-ahead log (ib_logfile0) is really mandatory for durability.

A good indicator for "running out of buffer pool" is that the Innodb_buffer_pool_wait_free status variable is increasing.