The Ultimate Guide to High Availability with MariaDB

InnoDB Purge

You are viewing an old version of this article. View the current version here.

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 Purge Rollback Segment Truncate Frequency

The purge rollback segment truncate 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;

This system variable can also 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_rseg_truncate_frequency = 64

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.