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. 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 save disk I/O in checkpointing. It often makes
sense to set the total size of the log files as big as the
buffer pool or even bigger. The drawback of using large log
files is that crash recovery can take longer because there is
more logged information to apply to the database.
