MySQL Bugs: #108928: Please consider adding innodb_free_list_target

Bug #108928	Please consider adding innodb_free_list_target_size global variable.
Submitted:	29 Oct 2022 14:44	Modified:	28 Nov 2022 19:55
Reporter:	Jean-François Gagné	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.7, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,

from what I understand, the innodb_lru_scan_depth global variables controls two things:

1. how far down the buffer pool LRU page list the page cleaner thread scans looking for dirty pages to flush (from the documentation [1]);

2. the target size of the free list (from my tests and blog posts [2] and [3]).

[1]: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_lru_scan_dept...

[2]: https://www.percona.com/blog/2020/05/14/tuning-mysql-innodb-flushing-for-a-write-intensive...

[3]: https://www.percona.com/blog/2020/01/22/innodb-flushing-in-action-for-percona-server-for-m...

Note Bug#108927 for adding #2 above to the documentation.

From [2], I can read:

> innodb_lru_scan_depth is a very poorly named variable. A better name would be innodb_free_page_target_per_buffer_pool. It is a number of pages InnoDB tries to keep free in each buffer pool instance to speed up read and page creation operations.

From [3], I can read:

> To speed up reads and page creation, InnoDB tries to always have a certain number of free pages in each buffer pool instance. Without some free pages, InnoDB could need to flush a page to disk before it can load a new one.
>
> This behavior is controlled by another poorly understood variable: innodb_lru_scan_depth. This is a pretty bad name for what the variable controls. Although the name makes sense if you look at the code, for a normal DBA the name should be innodb_free_pages_per_pool_target.

Both [2] and [3] quotes above "forget" that non only the page cleaner is responsible to refill the free list, but also responsible to clean the tail of the LRU.

To avoid this confusion, and for another reason given below, having two configuration variables would be better:

- innodb_lru_scan_depth only controlling LRU flushing;

- innodb_free_pages_per_pool_target or innodb_free_list_target_size, controlling the size of the free list.

I see a scenario where I would like to have a small free list target size and a large LRU scan [flush] depth.  When running MySQL with a small buffer pool, I probably do not want a lot of free pages in the free list, but I might want a larger portion of the LRU to be clean.  Having a longer clean LRU avoids flushing stalls waiting for IOs when needing a lot of free pages.  Having two configurations variables would be useful in such case.

Many thanks for looking into this,

Jean-François Gagné

How to repeat:
N/A because feature request.

Edit the title, from "Please consider splitting adding innodb_free_list_size global variable" to "Please consider adding innodb_free_list_size global variable".

Edit title, from "Please consider adding innodb_free_list_size global variable" to "Please consider adding innodb_free_list_target_size global variable" (sorry for changing my mind many times on this, I am sloppy today.

I realize I have not thought this through...

If we only add one new variable, people not aware of the new variable might change innodb_lru_scan_depth thinking it will affect the free list target size without knowing about innodb_free_list_target_size.  So maybe we need 2 new variables:

a. innodb_free_list_target_size

b. innodb_lru_clean_target_size

For backward compatibility, changing innodb_lru_scan_depth would update both above and should probably be deprecated.

Hello Jean-François,

Thank you for the feature request!

regards,
Umesh

We would also need a 3rd configuration variable (mentioned in Bug#108927, innodb_lru_scan_depth controls 3 things).

This 3rd variable would be "innodb_lru_scan_size": the length of the tail of the LRU that is scanned by a query thread to find a clean page when the free list is empty, before falling-back to a single page flush.

An example of the configuration would be:
- innodb_free_list_target_size = 128
- innodb_lru_clean_target_size = 1024
- innodb_lru_scan_size = 256

In above, we waste little pages in the free list, we keep a large tail of the LRU clean, and we scan 1/4 of the clean tail of the LRU before doing a single page flush.  It is unclear to me if innodb_lru_scan_size > innodb_lru_clean_target_size would make sense (maybe on a system with slow IOs, to save a single page flush, but if there are already 256 consecutive dirty pages at the tail of the LRU, I am not sure it is worth scanning the LRU more).