Tuning MariaDB innodb_page_cleaners: Eliminating I/O Spikes from Dirty Page Flushing

Operating high-throughput transactional databases (such as ERP systems, core banking platforms, and e-commerce stores across Pakistan) requires sustaining thousands of writes per second. When rows are modified, InnoDB updates the pages in the RAM Buffer Pool and appends the changes to the Redo Log. These in-memory modified pages are known as dirty pages.

Eventually, dirty pages must be flushed back to physical disk storage. In default or improperly tuned MariaDB configurations, background flushing falls behind incoming write rates. When the ratio of dirty pages breaches innodb_max_dirty_pages_pct or the redo log approaches full capacity, InnoDB enters Synchronous Flush Mode (Furious Flushing).

During furious flushing:

1. Foreground client transaction threads are forcefully halted to help write dirty pages to disk.
2. Storage controller I/O wait (%iowait) surges to 60%+, saturating NVMe queues.
3. User transactions freeze for 5 to 20 seconds, triggering cascading application connection timeouts.

To achieve smooth, continuous flushing without latency spikes, administrators must properly calibrate innodb_page_cleaners, innodb_buffer_pool_instances, and Adaptive Flushing. In this architectural guide, we dissect the page cleaner threading model, calculate optimal flushing ratios, and eliminate write freezes on enterprise MariaDB hosts.

---

The Flushing Bottleneck: Single Thread vs. Multi-Cleaner Architecture

Historically, MySQL and older MariaDB versions used a single coordinator thread to flush dirty pages across all buffer pool instances. On modern multi-socket hardware with large buffer pools (64 GB+ split across 16 instances), a single thread cannot dispatch writes fast enough to prevent dirty page accumulation.

Single Page Cleaner Bottleneck:
[ Buffer Pool 0 ] [ Buffer Pool 1 ] ... [ Buffer Pool 15 ]
        │                 │                    │
        └─────────────────┼────────────────────┘
                          ▼
            [ Single Coordinator Thread ] (CPU Saturated! Cannot keep pace!)
                          │
                          ▼
        [ Furious Synchronous Flushing Stalls Production! ]

Multi-Page Cleaner Architecture (Parallel NVMe Dispatch):
[ Buffer Pool 0 ] ──▶ [ Page Cleaner Worker 0 ] ──┐
[ Buffer Pool 1 ] ──▶ [ Page Cleaner Worker 1 ] ──┼──▶ [ Parallel NVMe Async I/O ]
[ Buffer Pool 15] ──▶ [ Page Cleaner Worker 15] ──┘    (Constant ~15% Dirty Ratio!)

Golden Architectural Rule: The number of innodb_page_cleaners MUST match the number of innodb_buffer_pool_instances! If you configure 16 buffer pool instances, you must configure 16 page cleaner threads so each instance flushes concurrently and independently.

Deploying database systems on bare-metal Dedicated Servers provides the dedicated PCIe Gen4/Gen5 NVMe channels and multi-core processing needed to absorb sustained asynchronous writes without controller contention.

---

Step 1: Diagnosing Dirty Page Accumulation in Real Time

Connect to MariaDB and query dirty page ratios:

SELECT 
    ROUND((PAGES_DIRTY / PAGES_TOTAL) * 100, 2) AS dirty_page_pct,
    PAGES_DIRTY,
    PAGES_TOTAL
FROM (
    SELECT 
        (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'INNODB_BUFFER_POOL_PAGES_DIRTY') AS PAGES_DIRTY,
        (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'INNODB_BUFFER_POOL_PAGES_TOTAL') AS PAGES_TOTAL
) AS status_data;

Check the engine status for flushing warnings:

SHOW ENGINE INNODB STATUS\G

Under the BUFFER POOL AND MEMORY section, locate:

Buffer pool size        4194304  (64 GB)
Free buffers            1024
Database pages          4090280
Modified db pages       1482010  (36% Dirty Pages!)
Pending reads           0
Pending writes: LRU 0, flush list 142, single page 0

If Modified db pages exceeds 25% or Pending writes: flush list is continuously high, the database is on the verge of synchronous flush freezing!

---

Step 2: Calibrating my.cnf for High-Throughput NVMe

Configure /etc/my.cnf.d/server.cnf:

[mariadb]
# Number of buffer pool instances (1 instance per 2-4GB of buffer pool)
# For a 64GB buffer pool, use 16 instances
innodb_buffer_pool_instances = 16

# MATCH page cleaners to buffer pool instances for 1:1 dedicated worker concurrency
innodb_page_cleaners = 16

# Calibrate I/O capacity to match underlying NVMe performance
# For enterprise PCIe Gen4 NVMe, set sustained to 10k-20k, and max to 40k IOPS
innodb_io_capacity = 15000
innodb_io_capacity_max = 30000

# Target dirty page ratio (default 75% is far too high for write-heavy workloads!)
# Lowering to 25%-35% ensures the background flush starts early and stays smooth
innodb_max_dirty_pages_pct = 30
innodb_max_dirty_pages_pct_lwm = 15

# Enable adaptive flushing to calculate flush rates based on redo log aging
innodb_adaptive_flushing = ON
innodb_adaptive_flushing_lwm = 10.0

# Flush neighbor pages (DISABLE for SSD/NVMe! Zero benefit on flash storage)
innodb_flush_neighbors = 0

# Scale background I/O writer threads
innodb_write_io_threads = 16

Restart MariaDB:

systemctl restart mariadb

---

Step 3: Verifying Smooth Adaptive Flushing Telemetry

To verify that page cleaner threads are actively flushing without stalls:

-- Query InnoDB metrics for page cleaner execution times
SELECT NAME, COUNT, SUBSYSTEM 
FROM information_schema.innodb_metrics 
WHERE NAME LIKE '%page_cleaner%';

Sample output:

+-------------------------------------+--------+-----------+
| NAME                                | COUNT  | SUBSYSTEM |
+-------------------------------------+--------+-----------+
| buffer_page_cleaner_cooldown        |  18420 | buffer    |
| buffer_flush_adaptive_pages_total   | 892014 | buffer    |
| buffer_flush_adaptive_total_pages   | 892014 | buffer    |
+-------------------------------------+--------+-----------+

Notice that buffer_flush_adaptive_pages_total increments smoothly and continuously every second, completely eliminating the cyclical “flush and freeze” pattern.

---

Operational Comparison: Default vs Tuned Page Cleaners

Operational Dimension	Default MariaDB (cleaners=4, io=200)	Tuned NVMe (cleaners=16, io=15000)
Flushing Behavior	Cyclical Spikes (Furious Flushing)	Smooth, Continuous Background Streaming
Max Dirty Page Ratio	Swells to 75%+, triggers stall	Stable between 15% and 25%
Peak Storage I/O Wait	Spikes to 65% during flush waves	Consistently < 1.2% Disk Wait
p99 Transaction Write Latency	Spikes from 2ms to 18 seconds	Consistent < 3.2 milliseconds

Hosting your high-volume transactional databases on dedicated Dedicated Servers in Pakistan ensures that multi-core CPU architectures and enterprise NVMe storage controllers deliver peak performance without unexpected write stalls.