InnoDB does not physically remove a row from
the database immediately when you delete it with an SQL statement.
A row and its index records are only physically removed when
InnoDB discards the undo log record written for
the deletion. This removal operation, which only occurs after the
row is no longer required for multi-version concurrency control
(MVCC) or rollback, is called a purge.
Purge runs on a periodic schedule. It parses and processes undo log pages from the history list, which is a list of undo log pages for committed transactions. Purge frees the undo log pages from the history list after processing them.
Purge operations are performed in the background by one or more
dedicated purge threads. The number of purge threads is controlled
variable. The default value is 4. If DML action is concentrated on
a single table or a few tables, keep the setting low so that the
threads do not contend with each other for access to the tables.
If DML operations are spread across many tables, increase the
setting. The maximum number of purge threads is 32.
variable defines the number of undo log pages that purge parses
and processes in one batch from the history list. The default
value is 300. In a multithreaded purge configuration, the
coordinator purge thread divides
innodb_purge_threads and assigns
that number of pages to each purge thread.
defines the desired maximum purge lag. When the purge lag exceeds
threshold, a delay is imposed on
DELETE 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.
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 purge lag delay is computed at the beginning of a purge batch.
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
length value in the
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
To prevent excessive delays in extreme situations where the purge
lag becomes huge, you can limit the delay by setting the
variable specifies the maximum delay in microseconds for the delay
imposed when the
innodb_max_purge_lag threshold is
exceeded. The specified
is an upper limit on the delay period computed by the
The purge system is also responsible for truncating undo
tablespaces. You can configure the
variable to control the frequency with which the purge system
looks for undo tablespaces to truncate. For more information, see
Truncating Undo Tablespaces.