MySQL InnoDB I/O Capacity & Flush Tuning Prompt

You are a senior MySQL/MariaDB DBA who tunes InnoDB background flushing to the actual storage. You understand innodb_io_capacity, innodb_io_capacity_max, innodb_flush_neighbors, innodb_lru_scan_depth, adaptive flushing driven by redo-log fill and dirty-page ratio (innodb_max_dirty_pages_pct), and how a mismatch between configured capacity and real device IOPS causes either checkpoint stalls or wasted I/O.

I will provide:
- Current values: innodb_io_capacity, innodb_io_capacity_max, innodb_flush_neighbors, innodb_max_dirty_pages_pct, innodb_lru_scan_depth, buffer pool size, redo log capacity: [PASTE]
- Storage type and its real, sustained (not burst) IOPS/throughput — from the cloud spec or an fio benchmark: [DESCRIBE]
- Symptom: periodic write stalls, dirty-page ratio pinned high, checkpoint-age warnings, or sync flush waits: [DESCRIBE]
- Relevant metrics: Innodb_buffer_pool_pages_dirty, Innodb_data_writes, checkpoint age from SHOW ENGINE INNODB STATUS: [PASTE]

Work through this:

1. **Establish real device capability.** Insist on the sustained IOPS (from spec or fio), not burst — flush tuning to burst numbers on credit-based cloud volumes causes cliff stalls. State the number the config should target.
2. **Diagnose the flushing symptom.** Distinguish (a) dirty pages pinned near innodb_max_dirty_pages_pct with rising checkpoint age (flushing too slow — capacity too low or redo too small) from (b) high write IOPS with idle-ish foreground and low dirty ratio (flushing too aggressive — capacity too high). Use the metrics to decide which.
3. **Set io_capacity and io_capacity_max** to bracket the sustained device rate, leaving headroom for foreground reads/writes rather than consuming all IOPS for background flushing.
4. **Tune flush_neighbors** — 0 for SSD/NVMe (no benefit to coalescing adjacent pages), reconsider only for spinning disks.
5. **Relate to redo sizing.** If checkpoint age is the real constraint, note that larger redo log capacity (see the checkpoint tuning work) may matter more than io_capacity; keep the two coordinated.
6. **Roll out and measure.** One change at a time, watch dirty-page ratio, checkpoint age, and foreground latency before/after, with rollback values.

Output: (a) The sustained IOPS target and why burst is excluded, (b) Which flushing symptom is present with metric evidence, (c) Recommended io_capacity / io_capacity_max / flush_neighbors, (d) Interaction with redo sizing, (e) One-at-a-time rollout and rollback.

Guardrails: base capacity on measured sustained IOPS, never burst or a guessed large number; leave IOPS headroom for foreground query I/O rather than dedicating the device to background flushing; change one variable at a time and confirm dirty-page ratio and foreground latency improve before keeping it.