MDEV-23855, I overlooked the fact that the default value 0 of the parameter innodb_max_dirty_pages_pct_lwm has a special meaning: "ignore this parameter, and look at innodb_max_dirty_pages_pct instead". This special value used to partially cancel the effect of the parameter innodb_adaptive_flushing=ON (which is set by default). The special value 0 would cause the function af_get_pct_for_dirty() to always return either 0 or 100.
This regression was originally reported in MDEV-24272, mixed up with another performance regression that only affects the 10.2, 10.3, and 10.4 release series but not 10.5. On a hard disk, running a 5-minute oltp_read_write in sysbench with 16 threads and 8 tables with 100000 rows each, I verified valerii's finding, using the following settings on MariaDB 10.5.6 and 10.5.8:
On my 2TB Western Digital SATA 3.0 hard disk (WDC WD20EZRZ-00Z5HB0) that has a write performance of 51.9 MB/s (reported by GNOME Disks when using 1MiB block size), I got the following results:
|server||average throughput/tps||average latency/ms||maximum latency/ms|
The last line was produced with the following fix:
This above patch is only applicable to 10.5.7 and 10.5.8 only; the code was slightly refactored in
MDEV-24278 since then.
I believe that a work-around of this regression is to set innodb_max_dirty_pages_pct_lwm to the same value as innodb_max_dirty_pages_pct (default value: 90).
Side note: The parameter innodb_idle_flush_pct has no effect (