Delayed Flushing & Its Role in MySQL SQL Performance Tuning

Fact: delayed flushing may actually lead to reduced load. It may sound hard to believe, but if you ask an expert about the reasons behind systems delaying flushing changes to durable storage, you might get one or both of these answers:

1. In order to complete the task at a more convenient time
2. To have less work overall

In this post, we will examine the second reason and whether it helps MySQL SQL performance tuning.

Higher Peak & Average IO Rates with Adaptive Flush

The total number of I/O operations performed definitely improves with adaptive flushing. What happens here is, the workload gets smoothed out by adaptive flushing, which results in more work getting done.

Why does this happen? As you might recall, InnoDB works on 16kb pages at a time. Suppose one of the users manipulates the data of one of the rows. If InnoDB flushes constantly, it will flush the whole 16kb page right then. Just after this flush, a different row undergoes changes on the same page, which causes one more page flush.

If the first flush were to be delayed, the two flushes would be performed as a single flush. Delaying a flush to perform along with other flushes as one flush is known as write combining in MySQL database and SQL.

In certain workloads, the same rows may end up getting updated numerous times, which is where delaying and allowing write combining may dramatically reduce the quantity of I/O operations.

Does Recovery Time Affect this Result?

Some of us might think that recovery time is a significant factor for MySQL SQL performance tuning, and that enabling this option is not really necessary if that isn’t the case. However, it actually depends on the workload, and if each flush takes up a resource that is also required by another task.

If the data is able to fit in the buffer pool, and no read operations are taking place at that moment - which means the disk isn’t required for any other operations - the disk would only be in use for write operations. Also, if everything is ready at the OS and RAID controller layers, a read operation won’t be needed for a write either, helping Oracle database performance tuning.

An important point to remember is that other than a mutex contention inside MySQL or InnoDB, write operations typically run in the background - so they don’t block foreground tasks.