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 that is maintained by the
InnoDB transaction system. Purge frees the undo
log pages from the history list after processing them.
By default, purge operations are performed by the
InnoDB master thread. Starting with MySQL
5.5, purge operations can performed in the background
by a dedicated purge thread rather than as part of the
InnoDB master thread. The use of a dedicated
purge thread may improve scalability by allowing the main database
operations to run independently from maintenance work happening in
You can enable a dedicated purge thread by the setting
innodb_purge_threads to 1. The
default value is 0, which means that the purge operation is
performed by the
InnoDB master thread.
variable defines the number of undo log pages that purge parses
and processes in one batch from the history list. The default
value is 20.
The purge system also frees the undo log pages that are no longer
required. It does so every 128 iterations through the undo logs.
In addition to defining the number of undo log pages parsed and
processed in a batch, the
defines the number of undo log pages that purge frees every 128
iterations through the undo logs.
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.
InnoDB transaction system maintains a list
of transactions that have index records delete-marked by
DELETE operations. The length of
the list is the purge lag. The purge lag delay is calculated by
the following formula, which results in a minimum delay of 5000
(purge lag/innodb_max_purge_lag - 0.5) * 10000
The delay is calculated at the beginning of a purge batch, every ten seconds. The operations are not delayed if purge cannot run because of an old consistent read view that could see the rows to be purged.
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
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
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
History list length to increase: