14.12.3 InnoDB Checkpoints

Making your log files very
large may reduce disk I/O during
checkpointing. It often
makes sense to set the total size of the log files as large as the
buffer pool or even larger. Although in the past large log files
could make crash recovery take excessive time, starting with MySQL
5.5, performance enhancements to crash recovery make it possible
to use large log files with fast startup after a crash. (Strictly
speaking, this performance improvement is available for MySQL 5.1
with the InnoDB Plugin 1.0.7 and higher. It is with MySQL 5.5 that
this improvement is available in the default InnoDB storage
engine.)

How Checkpoint Processing Works

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
disrupt 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.