How Many Disk Flushes Occur for a Single MySQL INSERT?

The article investigates the question “How many times does MySQL flush to disk for a single INSERT?” by conducting a hands‑on experiment with the pt‑tools suite.

First, the relevant MySQL flush parameters are examined, then pt‑ioprofile is enabled to trace I/O system calls. A row is inserted into an arbitrary table, and the output of pt‑ioprofile is observed.

The results reveal that the single INSERT caused MySQL to perform three fsync calls on the redo log and one fdatasync call on the binlog, indicating that the two logs use different flushing methods.

Several caveats are noted: (1) repeating the experiment may yield different numbers because MySQL has multiple logical paths that trigger flushes, such as the InnoDB dirty‑page flushing thread; (2) an fsync that has no data to write does not impose significant disk load, so three flushes should not be a cause for concern; (3) further experiments are planned to pinpoint the exact triggers of these flushes.

pt‑ioprofile, a component of pt‑tools, works by using strace to monitor MySQL’s system calls, filtering those related to I/O, and correlating them with lsof output to identify the affected MySQL files, then reporting the count, total time, and average time of each I/O operation.