operations to allow time for purge operations to catch up. The default value is 0, which means there is no maximum purge lag and no delay. 

The InnoDB  transaction system maintains a list of transactions that have index records delete-marked by UPDATE  or DELETE  operations. The length of the list is the purge lag. Prior to MySQL 8.0.14, the purge lag delay is calculated by the following formula, which results in a minimum delay of 5000 microseconds: 

(purge lag/innodb_max_purge_lag - 0.5) * 10000 

As of MySQL 8.0.14, the purge lag delay is calculated by the following revised formula, which reduces the minimum delay to 5 microseconds. A delay of 5 microseconds is more appropriate for modern systems. 

(purge_lag/innodb_max_purge_lag - 0.9995) * 10000 

The delay is calculated at the beginning of a purge batch. 

A typical innodb_max_purge_lag  setting for a problematic workload might be 1000000 (1 million), assuming that transactions are small, only 100 bytes in size, and it is permissible to have 100MB of unpurged table rows. 

The purge lag is presented as the History list length  value in the TRANSACTIONS  section of SHOW ENGINE INNODB STATUS  output. 

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 

The History list length  is typically a low value, usually less than a few thousand, but a write- heavy workload or long running transactions can cause it to increase, even for transactions that are read only. The reason that a long running transaction can cause the History list length  to increase is that under a consistent read transaction isolation level such as REPEATABLE READ , a transaction must return the same result as when the read view for that transaction was created. Consequently, the InnoDB multi-version concurrency control (MVCC) system must keep a copy of the data in the undo log until all transactions that depend on that data have completed. The following are examples of long running transactions that could cause the History list length  to increase: 

•A mysqldump  operation that uses the --single-transaction  option while there is a significant amount of concurrent DML. 

•Running a SELECT  query after disabling autocommit , and forgetting to issue an explicit COMMIT  or ROLLBACK . 

To prevent excessive delays in extreme situations where the purge lag becomes huge, you can limit the delay by setting the innodb_max_purge_lag_delay  variable. The innodb_max_purge_lag_delay variable specifies the maximum delay in microseconds for the delay imposed when the innodb_max_purge_lag  threshold is exceeded. The specified innodb_max_purge_lag_delay  value is an upper limit on the delay period calculated by the innodb_max_purge_lag  formula. 

## Purge and Undo Tablespace Truncation 

The purge system is also responsible for truncating undo tablespaces. You can configure the innodb_purge_rseg_truncate_frequency  variable to control the frequency with which the purge system looks for undo tablespaces to truncate. For more information, see Truncating Undo Tablespaces. 

