Why delayed flushing can result in less workBaron Schwartz
I can think of at least two major reasons why systems delay flushing changes to durable storage:
1. So they can do the work when it’s more convenient.
2. So they can do less work in total.
Let’s look at how the second property can be true.
A commenter on Deva’s recent post on InnoDB adaptive flushing asked,
Thatâ€™s really interesting stuff; am I reading it correctly though that adaptive flush actually increased the IOOPS? Looking at the IO graphs, it looks like both the peak IO rate and average IO rate were higher with adaptive flush nabled (assuming Iâ€™m reading properly).
Yes. Adaptive flushing actually increased the overall number of I/O operations performed. Smoothing out the workload can cause more work to be done. To see why, remember that InnoDB works in 16kb pages at a time. Suppose someone makes a change to a row. If InnoDB is flushing constantly, it flushes that entire 16kb page. Just afterwards, another row on the same page gets changed, and another page flush results. If the first flush had been delayed, the two flushes could have been done as one flush. This is called write combining. In some workloads, the same rows could be updated many, many times — so delaying and permitting write combining could be an enormous reduction in the of number of I/O operations.
Now on to the next question from that comment:
Seems to imply that if recovery time isnâ€™t a major factor, youâ€™re better off (for this workload at least) running w/o that option enabled?
Maybe. It depends on whether the flushes are consuming a resource that something else needs. If nothing else needs the disk, what the heck — go crazy, write as much as you want. If the data fits in the buffer pool, no reads are happening, so the disk is only used for writes. If everything is lined up at the operating system and RAID controller layers, then a read isn’t required for a write to take place, either. Remember too that these writes are background operations, so they aren’t blocking anything in the foreground, unless there is a side effect such as mutex contention inside MySQL or InnoDB. So whether this matters for you is workload-dependent.