Checkpoint is a mechanism for protecting operating systems and enterprise applications from computer failures by taking a snapshot (the checkpoint) of the system and data at critical points. In Oracle a checkpoint occurs when Database writer (DBWR) writes all modified buffers in the System Global Area (SGA) to the database files. Checkpoints occur at every redo log switch and also at intervals specified by the DBA. One storage engine in MysqL called InnoDB implements a checkpoint mechanism called a fuzzy checkpoint. InnoDB will flush 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 for a while.In 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 already present on the disk image of the database. Then InnoDB scans the log files forward from the place of the checkpoint applying the logged modifications to the database.InnoDB writes to the log files in a circular fashion. All committed modifications which 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 in the circular fashion,it has to make sure that the database page images on disk already contain the modifications logged in the log file InnoDB is going to reuse. In other words,InnoDB has to make a checkpoint and often this involves flushing of modified database pages to disk.The above explains why making your log files very big may save disk I/O in checkpointing. It can make sense to set the total size of the log files as big as the buffer pool or even bigger. The drawback in big log files is that crash recovery can last longer because there will be more log to apply to the database.
