关于innodb的checkpoint

fan_zhen_hua

找了下innodb的checkpoint。
对于innodb中,innodb innodb_flush_log_at_trx_commit=1，在每次commit后，innodb buffer同步刷新输出到文件，这时进行checkpoint。因为事务日志是顺序写的。所以在实例crash时，可以根据checkpoint来进行恢复。innodb的checkpoint发生在事务日志中，（oracle则在control file中）。不知道这样的理解对不对。除了commit之外，不知道是否还有别的动作会触发checkpoint。当然，stop mysql也会发生

以下摘自官网。光从文字的描述中，对有些点还是不太了解。大家可以讨论一下。

InnoDB实现一种被认识为“模糊”检查点设置的检查点机制。InnoDB以小批量从缓冲池刷新已修改的数据库页。没必要以单个批次刷新缓冲池，单批次刷新实际操作中可能会在检查点设置进程中停止用户SQL语句的处理。

在崩溃恢复中，InnoDB找寻被写进日志的检查点标签。它知道所有在该标签之前对数据库的修改被呈现在数据库的磁盘映像中。然后InnoDB从检查点往前扫描日志文件，对数据库应用已写入日志的修改。

InnoDB以循环方式写日志文件。所有使得缓冲池里的数据库页与磁盘上的映像不同的已提交修改必须出现在日志文件中，以备万一InnoDB需要做一个恢复。这意味着，当InnoDB开始重新使用一个日志文件，它需要确认在磁盘上的数据库页映像包含已写进InnoDB准备重新使用的日志文件里的修改。换句话说，InnoDB必须创建一个检查点，这经常涉及已修改数据库页到磁盘的刷新。

前面的叙述解释了为什么使你的日志文件非常大会在设置检查点中节约磁盘I/O。设置日志文件总的大小和缓冲池一样大或者甚至比缓冲池大通常是有意义的。大日志文件的缺点是崩溃恢复要花更长的时间，因为有更多写入日志的信息要应用到数据库上。

接自innodb的mvcc，还是新发一个贴吧。

fan_zhen_hua

摘自官网:
innodb_log_file_size

在日志组里每个日志文件的大小。在32位计算机上日志文件的合并大小必须少于4GB。默认是5MB。明智的值从1MB到N分之一缓冲池大小，其中N是组里日志文件的数目。值越大，。但更大的日志文件也意味这在崩溃时恢复得更慢。

checkpoint还和这个参数有关系，大家是如何理解的？

dushenglin

你的理解有些问题，checkpoint是指每次log file switch 时候，把buffer pool里面的dirty buffer  写到disk.
innodb_flush_log_at_trx_commit=2 means the log buffer is written out to the file at each commit, flushing on the log file takes place once per second.


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 and InnoDB 1.1 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 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.

fan_zhen_hua

恩。我的理解错误！！3Q~