innodb transactions history list
mysql版本:5。7.25
场景:
写压力负载比较重的MySQL实例上,InnoDB可能积累了较长的没有被purge掉的transaction history,
导致实例性能的衰减,或者空闲空间被耗尽。
history list
计划由innodb purge的标记删除的记录的事务列表,记录在undo log.
innodb purge
一种由一个或多个后台线程(由innodb_purge_threads控制)定期执行回收垃圾内容的计划。
从undo log历史列表删除已经被标记删除并且没有被mvcc或者roll back使用的记录。
处理他们之后从历史列表中purge空闲的undo log pages。
show engine innodb status:
TRANSACTIONS
------------
Trx id counter 1135719
Purge done for trx's n:o < 1135718 undo n:o < 0 state: running but idle
History list length 24
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422161455699792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422161455702528, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
当undo log被更新时会记录到history list。&trx_sys->rseg_history_len
由trx_sys函数指针传到值到结构体trx_sys_t的参数rseg_history_len
/********************************************************************//**
Adds the update undo log as the first log in the history list. Removes the
update undo log segment from the rseg slot if it is too big for reuse. */
void
trx_purge_add_update_undo_to_history(
/*=================================*/
trx_t* trx, /*!< in: transaction */
trx_undo_ptr_t* undo_ptr, /*!< in/out: update undo log. */
page_t* undo_page, /*!< in: update undo log header page,
x-latched */
bool update_rseg_history_len,
/*!< in: if true: update rseg history
len else skip updating it. */
ulint n_added_logs, /*!< in: number of logs added */
mtr_t* mtr) /*!< in: mtr */
{
/* Add the log as the first in the history list */
flst_add_first(rseg_header + TRX_RSEG_HISTORY,
undo_header + TRX_UNDO_HISTORY_NODE, mtr);
if (update_rseg_history_len) {
os_atomic_increment_ulint(
&trx_sys->rseg_history_len, n_added_logs);
srv_wake_purge_thread_if_not_active();
}
}
trx_sys_t数据结构
struct trx_sys_t{
ulint rseg_history_len; 在提交事务的时候更新undo logs 并且加mutex锁。
/*!< Length of the TRX_RSEG_HISTORY
list (update undo logs for committed
transactions), protected by
rseg->mutex */
};
当写库并发执行大查询的时候,transaction history就会因为undo log无法purge而一直增加。
当执行DML操作时会通过row_mysql_delay_if_needed判断purge是否需要延迟。
/*******************************************************************//**
Delays an INSERT, DELETE or UPDATE operation if the purge is lagging. */
static
void
row_mysql_delay_if_needed(void)
/*===========================*/
{
if (srv_dml_needed_delay) {
os_thread_sleep(srv_dml_needed_delay);
}
}
当row_mysql_delay_if_needed判断生效后会调用trx_purge_dml_delay,默认的delay时间为0.
当设置innodb_max_purge_lag>0即srv_max_purge_lag > 0,delay时间被设置为5000微妙。
当delay的值大于设置的innodb_max_purge_lag_delay的值,则把delay置为innodb_max_purge_lag_delay设置的时间。
Calculate the DML delay required.
@return delay in microseconds or ULINT_MAX */
static
ulint
trx_purge_dml_delay(void)
/*=====================*/
{
/* Determine how much data manipulation language (DML) statements
need to be delayed in order to reduce the lagging of the purge
thread. */
ulint delay = 0; /* in microseconds; default: no delay */
/* If purge lag is set (ie. > 0) then calculate the new DML delay.
Note: we do a dirty read of the trx_sys_t data structure here,
without holding trx_sys->mutex. */
if (srv_max_purge_lag > 0) {
float ratio;
ratio = float(trx_sys->rseg_history_len) / srv_max_purge_lag;
if (ratio > 1.0) {
/* If the history list length exceeds the
srv_max_purge_lag, the data manipulation
statements are delayed by at least 5000
microseconds. */
delay = (ulint) ((ratio - .5) * 10000);
}
if (delay > srv_max_purge_lag_delay) {
delay = srv_max_purge_lag_delay;
}
MONITOR_SET(MONITOR_DML_PURGE_DELAY, delay);
}
return(delay);
}
总结:innodb_purge_threads参数控制提供多少个后台进程处理purge操作,当大事务影响purge操作并且history list 持续增长的情况下可以考虑使用innodb_max_purge_lag,innodb_max_purge_lag_delay来优化purge的操作。