[MDEV-27942] No background flushing until innodb_adaptive_flushing_lwm is reached Created: 2022-02-24  Updated: 2022-08-26

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.4.24
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 0
Labels: None
Environment:

CentOS 7

Attachments: PNG File 10.4.15.png     PNG File 10.4.24.png     PNG File Screenshot 2022-08-26 at 10.50.24.png     PNG File Screenshot 2022-08-26 at 10.50.48.png     PNG File screenshot-1.png    

 Description   

There seems to be almost no flushing whatsoever until checkpoint age reaches innodb_adaptive_flushing_lwm. Afterwards flushing goes on for several minutes, until we get to about innodb_adaptive_flushing_lwm-1% of max checkpoint age. Then flushing stops again and checkpoint age builds up to innodb_adaptive_flushing_lwm.

Expected:
I would expect adaptive flushing to happen even before lwm is hit. According to documentation:

If set to 1, the default, the server will dynamically adjust the flush rate of dirty pages in the InnoDB buffer pool. This assists to reduce brief bursts of I/O activity. If set to 0, adaptive flushing will only take place when the limit specified by innodb_adaptive_flushing_lwm is reached.

It seems to me that my instance behaves as if it was set to OFF, as adaptive flushing kicks in ONLY when lwm is reached.

This is very different from the 10.4.15 version which has a LOT of background flushes all the time and does not actually allow any checkpoint age buildup at all. Both issues seem not optimal, but since 10.4.15 is long gone, I am opening the issue for 10.4.24. Attached screenshot of 10.4.15 is just for an idea of what it looks like on second slave, which has SAME CONFIG and SAME LOAD. Totally different behavior.

+---------------------------------------------+------------------------+
 
| Variable_name                               | Value                  |
 
+---------------------------------------------+------------------------+
 
| innodb_adaptive_flushing                    | ON                     |
 
| innodb_adaptive_flushing_lwm                | 10.000000              |
 
| innodb_adaptive_hash_index                  | OFF                    |
 
| innodb_adaptive_hash_index_parts            | 8                      |
 
| innodb_flush_log_at_timeout                 | 1                      |
 
| innodb_flush_log_at_trx_commit              | 2                      |
 
| innodb_flush_method                         | O_DIRECT               |
 
| innodb_flush_neighbors                      | 0                      |
 
| innodb_flush_sync                           | ON                     |
 
| innodb_io_capacity                          | 1024                   |
 
| innodb_io_capacity_max                      | 2048                   |
 
| innodb_lru_scan_depth                       | 100                    |
 
| innodb_max_dirty_pages_pct                  | 75.000000              |
 
| innodb_max_dirty_pages_pct_lwm              | 0.000000               |
 
+---------------------------------------------+------------------------+



 Comments   
Comment by [ 2022-02-25 ]

Seems that lowering innodb log size from exaggerated 44GB to reasonable 4GB created much more stable flushing.

Comment by brahma [ 2022-08-26 ]

We're struggling with the same issue - innodb_adaptive_flushing_lwm is set to default value 10 , currently we're running all productions on mariadb 5.5 and planning to migrate to 10.6.9. Noticing the checkpoint age is growing up to 6GB in 10.6.9 (total red_log allocated - 8GB) Vs 1.5GB in 5.5 (total red_log allocated - 8GB) then sudden flushing is happening, worried about the crash recovery with 6GB of un check-pointed redo log.

Tried by changing the innodb_adaptive_flushing_lwm value between 0 to 65 with innodb_adaptive_flushing (with ON & OFF) but checkpoint age is still growing until 6GB out of 8GB of redo log size in 2 hrs. 

 
 
+--------------------------------+-----------+
 
| Variable_name                  | Value     |
 
+--------------------------------+-----------+
 
| flush                          | OFF       |
 
| flush_time                     | 0         |
 
| innodb_adaptive_flushing       | ON        |
 
| innodb_adaptive_flushing_lwm   | 10.000000 |
 
| innodb_flush_log_at_timeout    | 1         |
 
| innodb_flush_log_at_trx_commit | 1         |
 
| innodb_flush_method            | O_DIRECT  |
 
| innodb_flush_neighbors         | 1         |
 
| innodb_flush_sync              | ON        |
 
| innodb_flushing_avg_loops      | 30        |
 
| innodb_lru_flush_size          | 32        |
 
+--------------------------------+-----------+
 
 
 
+------------------------+-------+
 
| Variable_name          | Value |
 
+------------------------+-------+
 
| innodb_io_capacity     | 6000  |
 
| innodb_io_capacity_max | 12000 |
 
+------------------------+-------+
 
 
 
+--------------------------------+-----------+
 
| Variable_name                  | Value     |
 
+--------------------------------+-----------+
 
| innodb_max_dirty_pages_pct     | 80.000000 |
 
| innodb_max_dirty_pages_pct_lwm | 0.000000  |
 
+--------------------------------+-----------+

This this how it works in newer versions of MariaDB or were are any other parameters introduced in the new version of MariaDB to control the check point age.

Thanks

Comment by brahma [ 2022-08-26 ]

Check point age in MariaDB 5.5

Check point age in MariaDB 10.6.9

Generated at Thu Feb 08 09:56:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.