While preparing a post on the design of ZFS based servers for use
with MySQL, I stumbled on the topic of fsync call performance.
The fsync call is very expensive, but it is essential to
databases as it allows for durability (the “D” of
the ACID acronym).
Let’s first review the type of disk IO operations executed by
InnoDB in MySQL. I’ll assume the default InnoDB variable values.
The first and most obvious type of IO are pages reads and writes
from the tablespaces. The pages are most often read one at a
time, as 16KB random read operations. Writes to the tablespaces
are also typically 16KB random operations, but they are done in
batches. After every batch, fsync is called on the tablespace
To avoid partially written pages in the tablespaces (a source of
data corruption), InnoDB performs a doublewrite. During a
doublewrite operation, a batch of dirty pages, from 1 to about
100 pages, is …[Read more]