• MySQL知识库 :: ms sql server
  • What is the equivalent of MS SQL Server 2000’s checkpoints in MySQL?

  • MS SQL Server 2000's checkpoint mechanism flushes dirty data and log pages from the buffer cache of the current database. This reduces the number of changes that have to be applied during a recovery. These checkpoints can be explicit with the CHECKPOINT statement, implicit with an event like ALTER DATABASE. They can also be automatically determined by the system to enforce the recovery interval setting for the database.

    InnoDB, a storage engine for MySQL, implements a checkpoint mechanism called a fuzzy checkpoint. It's similar to the automatic checkpoint mechanism in MS SQL Server. 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 in 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 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 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.