MySQL Bugs: #108927: The documentation for innodb_lru_scan_depth does not mention free list size.

Bug #108927	The documentation for innodb_lru_scan_depth does not mention free list size.
Submitted:	29 Oct 2022 14:43	Modified:	31 Oct 2022 6:49
Reporter:	Jean-François Gagné	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	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...

From what I see, the behavior #2 of innodb_lru_scan_depth is not documented in the MySQL Manual [1].  I think it should be added.

Many thanks for looking into this,

Jean-François Gagné

How to repeat:
Sorry, I am lazy and not providing full repro that innodb_lru_scan_depth controls the free list size.  If you really need me to, I will.  But I will quote extracts from [2] and [3] mentioned in the bug description...

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.

Note that both 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.

Related: Bug#108928 - Please consider adding innodb_free_list_target_size global variable.

Also missing from the documentation: this parameter is controlling not only:

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

2. the target size of the free list.

but also:

3. how far a "query" thread needing a free page when the free list is empty scans the LRU for finding a non-dirty page to evict (I am not fully sure about the exact behavior here, but it should be in the documentation).

Hello Jean-François,

Thank you for the report and feedback.

regards,
Umesh