Investigating InnoDB Dirty Page Flushing Impact on Business Using GDB Experiments

Problem

The author questions whether slow InnoDB dirty‑page flushing affects business workloads and proposes an experiment to verify the impact.

Experiment

A test database is created, and the thread responsible for dirty‑page flushing is identified via MySQL status information.

Using GDB, the MySQL process is attached, and the specific thread (ID 13) is isolated. A breakpoint is set at pc_sleep_if_needed, halting the flushing thread.

After releasing the main server thread, load is generated against MySQL with continuous INSERT statements. The INSERTs stall, and the InnoDB status shows that the buffer pool no longer flushes dirty pages.

Metrics reveal that the buffer pool contains 7,745 pages, of which 6,586 are dirty. The redo log shows a modified age of about 75 MB, exceeding the synchronous water‑mark, causing business operations to block.

The experiment confirms that when the modified age surpasses the synchronous water‑mark, InnoDB blocks queries until dirty pages are flushed.

Extended Knowledge

Additional reading from Percona explains the three causes of InnoDB flushing: high dirty‑page ratio, low free pages, and adaptive flushing based on redo‑log usage.

Running the stalled environment for over ten minutes can cause MySQL to crash with a semaphore‑wait error, highlighting the need for careful diagnosis beyond simple symptom matching.

The experiment also clarifies that only the dedicated flushing thread performs actual page writes; SQL execution threads merely request flushing.

Operations Advice

It is recommended to monitor the three flushing reasons and the flushing intensity in monitoring systems to assess whether business performance is being impacted by InnoDB flushing behavior.