14.6.10.2 InnoDB Checkpoints

InnoDB implements a checkpoint mechanism known
as “fuzzy” checkpointing. InnoDB
flushes modified database pages from the buffer pool in small
batches. There is no need to flush the buffer pool in one single
batch, which would in practice stop processing of user SQL
statements during the checkpointing process.

During crash recovery, InnoDB looks for a
checkpoint label written to the log files. It knows that all
modifications to the database before the label are present in the
disk image of the database. Then InnoDB scans
the log files forward from the checkpoint, applying the logged
modifications to the database.

InnoDB writes to its log files on a rotating
basis. It also writes checkpoint information to the first log file
at each checkpoint. All committed modifications that make the
database pages in the buffer pool different from the images on
disk must be available in the log files in case
InnoDB has to do a recovery. This means that
when InnoDB starts to reuse a log file, it has
to make sure that the database page images on disk contain the
modifications logged in the log file that
InnoDB is going to reuse. In other words,
InnoDB must create a checkpoint and this often
involves flushing of modified database pages to disk.

The preceding description explains why making your log files very
large may reduce disk I/O in checkpointing. It often makes sense
to set the total size of the log files as large as the buffer pool
or even larger. The disadvantage of using large log files is that
crash recovery can take longer because there is more logged
information to apply to the database.