本文转载自微信公众号《数据与云》，作者崔虎龙。转载本文请联系数据和云公众号。MySQL会做很多checkpoint动作来保证数据。特别是InnoDB采用了WriteAheadLog策略来防止宕机导致的数据丢失：即事务提交时，先写入redolog，然后修改内存数据页刷新脏数据等。除此之外，还有服务重启。1.Checkpoint介绍Checkpoint解决什么问题？对于需要频繁更新数据的场景，需要实时更新。对于MySQL来说，仅仅处理IO就可以耗尽性能。重做日志的大小也有限制。通过刷新策略，可以更有效地重用文件，而无需开辟新的空间。缓冲区大小是有限的。数据没有刷入硬盘，查询业务的命中率越来越小。数据库宕机，在崩溃恢复时，需要从上一个检查点恢复，提高效率。物理备份日志点。InnoDB引擎使用LSN（LogSequenceNumber）来标记版本。LSN是日志空间中每条日志的结束点，用字节偏移量表示。每个Page都有一个LSN，每个Redolog都有一个LSN，每个checkpoint也有一个LSN。检查点将在哪些MySQL实体上运行？Dirtypage：InnoDB缓冲池中在内存中已经更新过，并且变化还没有写入（刷新）到数据文件中的页面。Flush：将更改写入已缓存在内存区或磁盘暂存区的数据库文件中。Redolog：数据变化信息记录文件。2、checkpoint机制从官方描述分为两种checkpoint：Fuzzycheckpoint：刷新部分脏页，有效回收Redologs。Sharpcheckpoint：当数据库关闭时，将所有脏页刷新回磁盘。通过以上两种方法，checkpoint在不同情况下被触发：1）flush_lru_listflush_lru_listcheckpoint是在一个单独的pagecleaner线程中执行的。BufferPool的LRUfreelist中保留了一定数量的空闲页面，以保证BufferPool中有足够的空间来处理新的数据库请求。当freelist不足时，会产生flush_lru_listcheckpoint，空闲数阈值可配置。如果innodb_page_cleaners线程数超过缓冲池实例数（innodb_buffer_pool_instances），innodb_page_cleaners将自动设置为与innodb_buffer_pool_instances相同的值。2）当DirtyPage脏页数量过多时，InnoDB引擎会强制检查点。下面有几个核心参数控制的checkpoint点。flushratioinnodb_max_dirty_pages_pct_lwm阈值的目的是控制缓冲池中脏页的百分比，防止脏页数量达到innodb_max_dirty_pages_pct变量定义的阈值（默认值为90）。当缓冲池中的脏页百分比达到阈值时，InnoDB将主动刷新缓冲池中的页面。innodb_max_dirty_pages_pct：InnoDB会尝试从缓冲池中刷新数据，使脏页的百分比不超过这个值。innodb_max_dirty_pages_pct默认为90%。innodb_max_dirty_pages_pct_lwm：定义低水位线，指示启用preflushing的脏页百分比，以控制脏页比例。值为0将完全禁用预刷新行为。配置值应始终低于innodb_max_dirty_pages_pct的值。innodb_flush_neighbors变量定义从缓冲池中刷新页面是否也刷新相同范围内的其他脏页。默认设置0禁用innodb_flush_neighbors。设置为1将刷新相同范围内的连续脏页。设置为2将以相同的程度刷新脏页。当表数据存储在传统HDD存储设备上时，与在不同时间刷新单个页面相比，一次操作刷新相邻页面可减少I/O开销（主要用于磁盘寻道操作）。对于SSD：IO在普通场景下的处理能力已经很好了。这个参数可以打开。innodb_lru_scan_depth变量为每个缓冲池实例定义脏页被缓冲池LRU中列出的页面清理器线程扫描的深度。这是页面清除线程每秒执行的后台操作。小于默认值的设置通常适用于大多数工作负载，而明显高于必要值的值可能会影响性能。仅当您在典型工作负载下有备用I/O容量时，才考虑增加此值。相反，如果写入密集型工作负载使您的I/O容量饱和，您将需要降低此值，尤其是在大型缓冲池的情况下。另外，当改变bufferpool实例数时，考虑调整innodb_lru_scan_depth，因为innodb_lru_scan_depth*innodb_buffer_pool_instances定义了pageclear线程每秒执行的工作量。innodb_flush_neighbors和innodb_lru_scan_depth变量主要用于写入密集型工作负载。对于繁重的DML活动，如果不够积极，刷新可能会滞后；如果过于激进，磁盘写入会使I/O容量饱和。innodb_io_capacity设置适用于所有缓冲池实例。刷新脏页时，I/O容量将在缓冲池实例之间平均分配。请注意，如果刷新落后，缓冲池刷新率可能会超过InnoDB的可用I/O容量，如innodb_io_capacity设置所定义。在这种情况下，innodb_io_capacity_max值定义了I/O容量的上限，因此I/O活动的峰值不会消耗服务器的整个I/O容量。一般有不同的硬盘类型配置：SAS200~1000，SSD2000~5000，PCI-E10000-500003)AdaptiveFlushing当产生大量写入密集型工作负载时，可能会导致吞吐量突然变化。当InnoDBRedo日志文件满时，会出现尖锐的检查点，导致吞吐量暂时下降。即使未达到innodb_max_dirty_pages_pct阈值，也会发生这种情况。innodb_adaptive_flushing_lwm变量定义重做日志容量的低水位线。超过此阈值时，将启用自适应刷新。innodb_flushing_avg_loops定义了InnoDB维护先前计算的刷新状态快照的迭代次数，控制自适应刷新响应前台工作负载变化的速度。也就是说在统计前控制前N页的刷新率，避免刷新过快。高值意味着InnoDB保留先前计算的快照更长时间，因此自适应刷新响应更慢。如果日志空间利用率没有达到75%，则应使用更高的innodb_flushing_avg_loops值以尽可能保持刷新顺畅。对于具有极端负载峰值或日志文件不提供大量空间的系统，较小的值应该允许flush密切跟踪工作负载变化并帮助避免达到75%的日志空间利用率。4)限制空闲期间的缓冲区刷新从MySQL8.0.18开始，您可以使用innodb_idle_flush_pct变量来限制空闲期间（数据库页面未被修改的时期）的缓冲池刷新率。innodb_idle_flush_pct的值是innodb_io_capacity设置的一个百分比，它定义了InnoDB每秒可用的I/O操作数。innodb_idle_flush_pct的默认值为100，即innodb_io_capacity设置的100%。要在空闲时间限制刷新，请定义一个小于100的innodb_idle_flush_pct值。在空闲期间限制页面刷新有助于延长固态存储设备的寿命，但副作用可能包括长时间空闲后关机时间延长和服务器发生故障时恢复时间更长。5)重做日志重做日志在物理上表示为一组文件，通常命名为ib_logfile0和ib_logfile1。redolog中的数据是按照受影响的记录来记录的，这些数据统称为redo。重做日志数据由递增的LSN值表示。用于记录数据的修改记录、顺序记录。崩溃恢复期间使用的基于磁盘的数据结构，用于更正由不完整事务写入的数据。重做日志的磁盘布局受配置选项innodb_log_file_size、innodb_log_group_home_dir和innodb_log_files_in_group的影响。重做日志操作的性能也受日志缓冲区innodb_log_buffer_size的影响。在崩溃恢复期间，InnoDB需要找到一个写入日志文件的检查点。对数据库的所有修改都出现在数据库磁盘映像上的LSN之前，之后InnoDB从检查点扫描日志文件并将日志修改应用于数据库。Innodb_redo_log_archive_dirs重做日志归档，主要是考虑到在进行备份操作时，复制重做日志记录的备份实用程序有时可能跟不上重做日志生成的速度，导致重写重做日志记录和丢失这些记录。此外，它还可以作为数据恢复的日志记录。6）常见checkpoint压力下log中page_cleaner的问题是脏页产生太快，导致pagecleaner清理不干净。目前的解决方案可以通过结合以下参数进行调整：innodb_lru_scan_depth值设置为较小的值。innodb_io_capacity、innodb_io_max_capacity要合理设置。innodb_max_dirty_page_pct也设置得更小。innodb_adaptive_hash_index关闭。3.总结对于MySQL的checkpoint机制，它平衡了IO和内存。通过调整参数，是针对不同应用系统提高性能的一种方式，一般采用默认的方式。另一种思路：redolog可以无限增长，磁盘足够大，bufferpool足够大，可以缓存所有数据，所以不需要频繁刷新bufferpool中的脏页。作者简介崔虎龙，云和恩墨MySQL技术顾问，长期服务于金融、游戏、物流等行业的数据中心，设计数据存储架构，熟悉数据中心运行管理、自动化运维和管理的流程和规范。维护等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障排除、备份恢复、升级迁移、性能优化。自学通过MySQLOCP5.6和MySQLOCP5.7认证。2年以上开发经验，10年数据库运维工作经验，其中8年全职MySQL工作；曾担任项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职位；相关行业：金融（银行业、财务管理）、物流、游戏、医疗、重工业等。
