[[422164]]
本文转载自微信公众号「数据和云」,机解作者崔虎龙。制详转载本文请联系数据和云公众号。机解
MySQL为了保证数据会做很多checkpoint动作。制详特别是机解InnoDB采用Write Ahead Log策略来防止宕机导致的数据丢失:即事务提交时,先写重做日志,制详再修改内存数据页的机解方式脏数据刷新等。除此之外,制详还有服务重新启动。机解
checkpoint是制详为了解决哪些问题呢?
InnoDB引擎通过LSN(Log Sequence Number)来标记版本,LSN是日志空间中每条日志的结束点,用字节偏移量来表示。每个Page有LSN,每个Redo log有LSN,每个checkpoint也有LSN。
checkpoint会对哪些MySQL实体做操作?
从官方提供的说明中checkpoint分为两个:
通过以上两个方式,在不同的情况下触发checkpoint:
flush_lru_list checkpoint是在单独的page cleaner线程中执行的。Buffer Pool的LRU空闲列表中保留一定数量的空闲页面,来保证Buffer Pool中有足够的空间应对新的数据库请求。
在空闲列表不足时,发生flush_lru_list checkpoint,空闲数量阈值是可以配置的。
如innodb_page_cleaners线程的数量超过了缓冲池实例(innodb_buffer_pool_instances)的数量,则innodb_page_cleaners将自动设置为与innodb_buffer_pool_instances相同的值。
脏页数量太多时,InnoDB引擎会强制进行checkpoint,下面有几个核心参数控制的checkpoint点。
innodb_max_dirty_pages_pct_lwm阈值的目的是控制缓冲池中脏页的百分比,防止脏页的数量达到innodb_max_dirty_pages_pct变量定义的阈值(默认值为90)。当缓冲池中的脏页百分比达到阈值时,InnoDB会主动刷新缓冲池中的页。
InnoDB会尝试从缓冲池中刷新数据,这样脏页的百分比就不会超过这个值。innodb_max_dirty_pages_pct默认90%。
定义低水位标记,表示启用预冲洗以控制脏页比率的脏页百分比。0值将完全禁用预刷新行为。配置的值应该总是低于innodb_max_dirty_pages_pct的值。
变量定义了从缓冲池中刷新一个页是否也会刷新相同范围内的其他脏页。
默认设置0禁用innodb_flush_neighbors。
设置为1将刷新同一区段中的连续脏页。
设置为2将刷新同一区段中的脏页。
当表数据存储在传统的HDD存储设备上时,与在不同时间刷新单个页相比,在一次操作中刷新相邻页可以减少I/O开销(主要用于磁盘寻道操作)。对于SSD来说:普遍场景在IO方面的处理能力已经非常优秀。可以打开这个参数。
变量定义了对于每个缓冲池实例,缓冲池LRU列出的页面清理器线程扫描的脏页面的深度。这是一个由页面page clear thread每秒执行一次的后台操作。
小于默认值的设置通常适用于大多数工作负载,显著高于必要值时可能会影响性能。只有在典型工作负载下有空闲I/O容量时,才考虑增加该值。相反,如果写密集的工作负载使您的I/O容量饱和,则需要降低该值,特别是在大型缓冲池的情况下。
另外,在改变缓冲池实例数量时,考虑调整innodb_lru_scan_depth,因为innodb_lru_scan_depth * innodb_buffer_pool_instances定义了page clear thread每秒执行的工作量。
innodb_flush_neighbors和innodb_lru_scan_depth变量主要用于写密集型的工作负载。对于大量DML活动,如果刷新不够激烈,则刷新可能会滞后;如果刷新太激烈,磁盘写可能会使I/O容量饱和。
设置适用于所有的缓冲池实例。当刷新脏页时,I/O容量将平均分配给缓冲池实例。
注意,如果刷新落后,缓冲池的刷新速率可能会超过InnoDB可用的I/O容量,这是由innodb_io_capacity设置定义的。innodb_io_capacity_max值定义了这种情况下的I/O容量上限,这样I/O活动的峰值不会占用服务器的整个I/O容量。一般设置有不同的硬盘类型配置 SAS 200~1000 ,SSD 2000~5000 ,PCI-E 10000-50000
当产生大量写密集型工作负载时,可能会导致吞吐量的突然变化。当InnoDB Redo日志文件满了,就会出现一个Sharp checkpoint,导致临时的吞吐量降低。即使innodb_max_dirty_pages_pct阈值未达到,也会出现这种情况。
innodb_adaptive_flushing_lwm变量定义了Redo日志容量的低水位标志。当超过该阈值时,启用自适应刷新(Adaptive Flushing)。
innodb_flushing_avg_loops定义了InnoDB保持先前计算的刷新状态快照的迭代次数,控制自适应刷新对前台工作负载变化的响应速度。就是说控制统计前N个page flush速率,避免太快flush。
高的值意味着InnoDB保持先前计算的快照的时间更长,因此自适应刷新响应更慢。如日志空间利用率未达到75%,则应该使用较高的innodb_flushing_avg_loops值来保持尽可能平滑的刷新。对于具有极端负载峰值或日志文件不提供大量空间的系统,应使较小的值允许flush以密切跟踪工作负载更改,并有助于避免达到75%的日志空间利用率。
从MySQL 8.0.18开始,你可以使用innodb_idle_flush_pct变量来限制空闲时间段(数据库页面不被修改的时间段)的缓冲池刷新速率。innodb_idle_flush_pct的值是innodb_io_capacity设置的百分比,innodb_io_capacity定义了每秒可用于InnoDB的I/O操作次数。innodb_idle_flush_pct的默认值是100,这是innodb_io_capacity设置的100%。为了限制空闲时间的刷新,定义一个innodb_idle_flush_pct小于100的值。
在空闲期间限制页面刷新可以帮助延长固态存储设备的寿命,但其的副作用可能包括在长时间的空闲期间之后更长的关闭时间以及在服务器发生故障时更长的恢复时间等问题。
Redo日志在物理上表示为一组文件,通常命名为ib_logfile0和ib_logfile1。重做日志中的数据按照受影响的记录进行记录,这些数据统称为重做。重做日志的数据通过不断增加的LSN值表示。
Redo日志的磁盘布局受配置选项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重做日志进行归档,主要考虑到备份操作进行时,复制重做日志记录的备份实用程序有时可能无法跟上重做日志生成的速度,从而导致重写重做日志记录而丢失这些记录。除此之外也可以作为数据恢复的日志记录。
出现这个page_cleaner的问题是脏页产生的太快,导致页面清理程序清理不过来。
目前解决方式,可以组合以下参数进行调整:
对于MySQL的checkpoint机制来说,是对IO和内存做了平衡操作。
通过调节参数,对于不同的应用系统,都是提升性能的一种方式,普遍情况下采取默认方式。
另一个思路:重做日志可以无限增大,磁盘足够大,同时缓冲池足够大,能够缓存所有数据,那么就不需要将缓冲池中的脏页频繁刷新。
关于作者
崔虎龙,云和恩墨MySQL技术顾问,长期服务于金融、游戏、物流等行业的数据中心,设计数据存储架构,并熟悉数据中心运营管理的流程及规范,自动化运维等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验,10年数据库运维工作经验,其中专职做MySQL工作8年;曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务;涉及行业:金融(银行、理财)、物流、游戏、医疗、重工业等。
责任编辑:武晓燕 来源: 数据和云 MySQLCheckpoint机制
(责任编辑:焦点)
2025年底全国宾馆酒店民宿不再主动提供一次性塑料用品 强化政策支持保障
我国交通运输固定资产投资 今年将超3.2万亿元 加快补齐短板
国科微(300672.SZ):股东陈岗解除质押245万股 占其所持股份比例22.32%
2020年1月份工业生产者出厂价格同比上涨0.1% 农副产品类价格持平