1. 官方文档连接
https://dev.mysql.com/doc/refman/8.2/en/innodb-purge-configuration.html
2. 说明
- 当你用sql语句去删除一行数据的时候,innodb物理上不会立即从数据库中移除一行记录。当innodb丢弃关于这段删除的undo日志记录的时候,记录和他的索引记录才会物理移除,这个移除操作,仅仅发生在行不在被MVCC或者rollback需要的时候,这个行为叫做purge
- purge按定期计划运行。它从历史列表中解析和处理undo日志页面,历史列表是由InnoDB事务系统维护的已提交事务的undo日志页面列表。“清除”在处理完undo日志页后,将它们从历史记录列表中释放出来。
3. 配置purge线程
- purge操作在后台被一个或者多个purge线程执行,purge线程的数量有参数innodb_purge_threads控制,这个参数的默认值是4.
- 如果DML操作集中在一个表上,则该表的purge操作由一个清除线程执行,这可能会导致purge操作较慢、增加purge lag,并且如果DML操作涉及大的对象值,则会增加表空间文件大小。如果超过innodb_max_purge_lag设置,purge工作将自动在可用的清除线程之间重新分配。在这种情况下,过多的活动purge线程可能会导致与用户线程的争用,因此请相应地管理innodb_purge_threads设置。innodb_max_purge_lag变量默认设置为0,这意味着默认情况下没有最大的purge lag。
- 如果DML操作集中在少量几个表上,请将innodb_urge_threads设置为较低,这样线程就不会为了访问繁忙的表而相互竞争。如果DML操作分布在多个表中,请考虑更高的innodb_urge_threads设置。清除线程的最大数量为32。
- innodb_purge_threads设置是允许的最大的purge线程数量,purge ststen会自动调节使用的purge线程的数量
4. 配置purge的batch size
- innodb_purge_batch_size变量定义了从历史列表中purge解析和处理一个批次的undo日志页的数量。默认值为300。在多线程purge配置中,协调器purge线程将innodb_urge_batch_size除以innodb_purge_threads,并将该页数分配给每个清除线程。
- 清除系统还释放不再需要的undo日志页。它通过undo日志每128次迭代执行一次。除了定义在一个批处理中解析和处理的undo日志页面的数量外,innodb_urge_batch_size变量还定义了通过撤消日志每128次迭代purge释放的undo日志页的数量。
- innob_purge_batch_size变量用于高级性能调整和实验。大多数用户不需要更改innodb_burge_batch_size的默认值。
5. 配置最大的purge lag
- innodb_max_purge_lag变量定义了所需的最大purge lag。当purge lag超过innodb_max_purge_lag阈值时,将对INSERT、UPDATE和DELETE操作施加延迟,以便purge操作有时间跟上。默认值为0,这意味着没有最大清除滞后和延迟。
- InnoDB事务系统维护一个事务列表,该列表中的索引记录被UPDATE或delete操作标记为删除。列表的长度是purge lag。
- purge lag延迟通过下面的公式计算,延迟在purge batch的开始被计算
(purge_lag/innodb_max_purge_lag - 0.9995) * 10000 - 对于有问题的工作负载,典型的innodb_max_purge_lag设置可能是1000000(100万),假设事务很小,只有100字节大小,是允许有100MB的unpurge的表行的
- puge lag显示为SHOW ENGINE INNODB STATUS输出的TRANSACTIONS部分中的History列表长度值。
mysql> SHOW ENGINE INNODB STATUS;
...
TRANSACTIONS
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
History list length 20
6) "History list length"通常很低,通常小于几千,但对于一个write-heavy workload或长时间运行的事务可能会导致其增加,即使是只读事务也是如此。长时间运行的事务可能导致History list length增加的原因是,在一致的读取事务隔离级别(如REPEATABLE read)下,事务必须返回与创建该事务的读取视图时相同的结果。因此,InnoDB多版本并发控制(MVCC)系统必须在undo日志中保留一份数据副本,直到所有依赖该数据的事务都完成为止。以下是可能导致History list length增加的long running transaction的示例:
i. 当存在大量并发DML时,使用--single transaction选项的mysqldump操作。
ii. 在禁用自动提交后运行SELECT查询,忘记发出显式COMMIT或ROLLBACK。
7) 为了防止在purge lag变得巨大的极端情况下出现过度延迟,可以通过设置innodb_max_purge_lag_delay变量来限制延迟。innodb_max_purge_lag_delay变量指定当超过innodb_max_purge_lag阈值时施加的延迟的最大延迟(以微秒为单位)。指定的innodb_max_purge_lag_delay值是由innodb_max_purge_lag公式计算的延迟周期的上限。
6. purge和undo tablespace truncation
- purge system也会负责truncating undo表空间。你可以通过参数innodb_purge_rseg_truncate_frequency来控制purge system查找undo 表空间去truncate频率