When a transaction updates a row in an InnoDB table, InnoDB's MVCC implementation keeps old versions of the row in the InnoDB undo log. The old versions are kept at least until all transactions older than the transaction that updated the row are no longer open. At that point, the old versions can be deleted. InnoDB has purge process that is used to delete these old versions.
Optimizing Purge Performance
Configuring the Purge Threads
The number of purge threads can be set by configuring the innodb_purge_threads system variable. This system variable can be specified as a command-line argument to mysqld or it can be specified in a relevant server option group in an option file. For example:
[mariadb] ... innodb_purge_threads = 6
Configuring the Purge Batch Size
The purge batch size is defined as the number of InnoDB redo log records that must be written before triggering purge. The purge batch size can be set by configuring the innodb_purge_batch_size system variable. This system variable can be specified as a command-line argument to mysqld or it can be specified in a relevant server option group in an option file. For example:
[mariadb] ... innodb_purge_batch_size = 50
Configuring the Max Purge Lag
If purge operations are lagging on a busy server, then this can be a tough situation to recover from. As a solution, InnoDB allows you to set the max purge lag. The max purge lag is defined as the maximum number of InnoDB undo log that can be waiting to be purged from the history until InnoDB begins delaying DML statements.
SET GLOBAL innodb_max_purge_lag=1000;
[mariadb] ... innodb_max_purge_lag = 1000
SET GLOBAL innodb_max_purge_lag_delay=100;
[mariadb] ... innodb_max_purge_lag_delay = 100
Configuring the Purge Rollback Segment Truncation Frequency
The purge rollback segment truncation frequency is defined as the number of purge loops that are run before unnecessary rollback segments are truncated. The purge rollback segment truncation frequency can be set by configuring the innodb_purge_rseg_truncate_frequency system variable. This system variable can be changed dynamically with SET GLOBAL. For example:
SET GLOBAL innodb_purge_rseg_truncate_frequency=64;
[mariadb] ... innodb_purge_rseg_truncate_frequency = 64
Configuring the Purge Undo Log Truncation
SET GLOBAL innodb_undo_log_truncate=ON;
[mariadb] ... innodb_undo_log_truncate = ON
An InnoDB undo log tablespace is truncated when it exceeds the maximum size that is configured for InnoDB undo log tablespaces. The maximum size can be set by configuring the innodb_max_undo_log_size system variable. This system variable can be changed dynamically with SET GLOBAL. For example:
SET GLOBAL innodb_max_undo_log_size='64M';
[mariadb] ... innodb_max_undo_log_size = 64M
Purge's Effect on Row Metadata
An InnoDB table's clustered index has three hidden system columns that are automatically generated. These hidden system columns are:
DB_ROW_ID- If the table has no other
PRIMARY KEYor no other
UNIQUE KEYdefined as
NOT NULLthat can be promoted to the table's
PRIMARY KEY, then InnoDB will use a hidden system column called
DB_ROW_ID. InnoDB will automatically generated the value for the column from a global InnoDB-wide 48-bit sequence (instead of being table-local).
DB_TRX_ID- The transaction ID of either the transaction that last changed the row or the transaction that currently has the row locked.
DB_ROLL_PTR- A pointer to the InnoDB undo log that contains the row's previous record. The value of
DB_ROLL_PTRis only valid if
DB_TRX_IDbelongs to the current read view. The oldest valid read view is the purge view.
If a row's last InnoDB undo log record is purged, this can obviously effect the value of the row's
DB_ROLL_PTR column, because there would no longer be any InnoDB undo log record for the pointer to reference.
In MariaDB 10.2 and before, the purge process wouldn't touch the value of the row's
However, in MariaDB 10.3 and later, the purge process will set a row's
DB_TRX_ID column to
0 after all of the row's associated InnoDB undo log records have been deleted. This change allows InnoDB to perform an optimization: if a query wants to read a row, and if the row's
DB_TRX_ID column is set to
0, then it knows that no other transaction has the row locked. Usually, InnoDB needs to lock the transaction system's mutex in order to safely check whether a row is locked, but this optimization allows InnoDB to confirm that the row can be safely read without any heavy internal locking.
This optimization can speed up reads, but it come at a noticeable cost at other times. For example, it can cause the purge process to use more I/O after inserting a lot of rows, since the value of each row's
DB_TRX_ID column will have to be reset.