Tuning UNDO Operations in MySQL

In MySQL, undo operations are used to provide transactional consistency and to allow for rolling back changes made to the database during a transaction. Undo operations are implemented in the InnoDB storage engine, which is the default storage engine in MySQL.

When a transaction modifies data in the database, InnoDB stores the undo information in undo logs. This information is used to undo the changes made during the transaction in case of a rollback or crash recovery. InnoDB uses a combination of undo logs and the transaction log (also known as the binary log) to ensure transactional consistency and to provide crash recovery.

The performance of undo operations in MySQL can be influenced by several factors. For example, if the undo logs are too small, they may become a bottleneck for performance as the database needs to perform disk I/O to read and write undo information. On the other hand, if the undo logs are too large, they may consume too much disk space, leading to performance degradation due to increased disk I/O.

Configuring MySQL for optimal undo operations 

innodb_purge_batch_size: This system variable determines the number of undo log pages to be purged in one batch. The default value is 20, meaning that 20 undo log pages are purged in a single batch.

innodb_max_purge_lag: This system variable determines the maximum number of undo log records that can be left behind after a transaction has been committed. The default value is 0, meaning that all undo log records are purged immediately after a transaction has been committed.

innodb_max_purge_lag_delay: This system variable determines the maximum number of milliseconds that the purge thread can be delayed waiting for the completion of a table rebuild or for other background activities. The default value is 0, meaning that the purge thread is not delayed.

These system variables can greatly impact the performance and scalability of your MySQL database. If the number of purge threads is too low, the undo log purging process may become a bottleneck, causing slowdowns in other parts of the system. On the other hand, if the number of purge threads is too high, it may cause increased CPU utilization and memory usage.

It is recommended to tune these variables based on the workload and system resources. You can start with the default values and adjust them based on your system’s performance. You can monitor the performance of the purge threads using the performance_schema.threads table and the Purge column to see if they are causing any bottlenecks.

Additionally, it is recommended to set innodb_max_purge_lag to a value that is appropriate for your workload and system resources, so that you can avoid any delays in the purging process. If you set innodb_max_purge_lag to a high value, it may cause the undo log to grow excessively, leading to performance degradation.

About Shiv Iyer 219 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.