The InnoDB history list length increased significantly – What to do next?

The InnoDB history list length can grow significantly if there are long-running transactions that generate a lot of undo logs or if there are frequent transactions with large amounts of data being modified. This can cause a buildup of undo logs that the InnoDB storage engine needs to keep track of. If the undo logs are not being purged fast enough, this can result in the history list growing significantly and consuming large amounts of disk space.

To troubleshoot this issue, you can start by checking the system variables related to undo logs, such as innodb_max_undo_log_size and innodb_undo_logs. If these variables are set too low, you may need to increase them to accommodate the amount of undo logs being generated.

You should also monitor the UNDO_SPACE metric in the performance schema to see if there is a lot of unused undo space being consumed. If so, you may want to consider increasing the innodb_purge_batch_size or innodb_purge_threads system variables to speed up the purging of undo logs.

It may also be helpful to monitor the activity of long-running transactions and see if there are any queries that are locking up a lot of rows for an extended period of time. In such cases, you can try to optimize these queries or break them into smaller transactions to reduce the amount of undo logs generated.

Finally, you should also monitor the disk space usage on the server to see if there is a significant increase in disk space consumption. If so, you may need to take steps to free up disk space or add more disk space to accommodate the growth of the history list.

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.