Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL)
Description
A customer who uses remarkably large innodb_buffer_pool_size noted that there are some performance stalls when the buffer pool is running out of free pages.
In a scenario where background flushing is not enabled (innodb_max_dirty_pages_pct_lwm is not being exceeded in the buffer pool, or it is 0 and innodb_max_dirty_pages_pct is not being exceeded), the buf_flush_page_cleaner() thread could sit idle while pages in the buffer pool are slowly running out.
It could make sense to wake up the buf_flush_page_cleaner() thread in buf_LRU_get_free_block() when buf_pool.page_cleaner_idle() holds and there are fewer than innodb_lru_scan_depth blocks available.
If there are fewer than innodb_lru_scan_depth/2 blocks available in the buffer pool and the page cleaner is not idle but being invoked once per second, it would make sense to wake up the page cleaner from its sleep immediately. LRU eviction flushing should ignore the innodb_io_capacity throttling, because running out of buffer pool will risk blocking all operations in the database.
Attachments
Issue Links
- causes
-
-
- Closed
-
-
-
- Closed
-
- is blocked by
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
debarun, you mentioned related to MDEV-28800 that you observed something that I think could match this description. Can you take a look? Can you reproduce the described scenario?
My attempt at blindly fixing this did not significantly improve the situation in the customer’s environment.
marko Can we try to get following information from customer ? It would help analysing the scenario better.
1. Select * from innodb_buffer_pool_stats \G
- Can be called a few times at intervals nearby time when stall is observed
2. Configuration parameters if different from default:
show variables like "%_io_capacity%";
show variables like "%_io_threads%";
show variables like "%_dirty_page%";
show variables like "innodb_log_file_size";
show variables like "innodb_buffer_pool_size";
3. The TPS/QPS readings over time around the stall
4. cpu and iostat on the disk during stall
5. DB size on disk
I could analyze the issue from the support ticket. The detailed information have been very helpful.
Along with flushing appropriate number of pages page cleaner thread has the responsibility of maintaining enough number of free pages
available for user threads to consume. In buf_flush_LRU, the LRU list is scanned from tail flushing any dirty pages and evicting non-dirty
pages to fill the free list.
The earlier page cleaner improvements have shifted the balance in such a way that the LRU flushing and eviction can be skipped for long until the
buffer pool is out of free pages or there is a need to flush from the LRU list based on LSN or dirty page percentage. In second case, the LRU
scan can be terminated early if dirty pages are found in LRU tail.
The issue could be repeated in local and I have created the initial patch. We would need to have the review discussions and tests.
I have a tentative patch.
https://github.com/MariaDB/server/pull/3003
debarun, thank you for the ideas and discussion. I revised (or replaced) my initial fix https://github.com/MariaDB/server/pull/2949/ with some ideas from you, some from me. One part that we might still want to revise or make controllable by a debug parameter similar to innodb_flush_sync is that when the number of free pages is below innodb_lru_scan_depth, the page cleaner thread will keep running continuously. Normally, the innodb_io_capacity and innodb_io_capacity_max specify the number of pages to be written per a one-second iteration of the page cleaner. When the page cleaner runs in continuous or ‘furious’ mode, several iterations can be run per second.
origin/10.6-MDEV-33053 c854fa7c2df579de0c669acb22341ef0a3f89937 2024-01-18T20:16:40+02:00
performed well in RQG testing. No new problems.
One effect of this MDEV is that it makes the buf_flush_page_cleaner the only thread that will initiate any page flushing. Previously, any thread that was trying to allocate a page from the buffer pool could do that when we are running out of pages.
This can cause performance issues when the buffer pool is full as threads waiting for a free page has to wait for the page cleaner.
MDEV-34265 fixes this issue
My fix would depend on some changes that were made in
MDEV-26827(10.6, not 10.5).