Tuning InnoDB history list performance

Tuning the InnoDB history list performance involves adjusting various parameters related to the purge process. The key parameters that can be tuned to optimize the history list performance in InnoDB are:

  • innodb_purge_batch_size: This parameter determines the number of undo log records purged in each batch during the purge process.
    • Increasing the value can speed up the purge process, but it may also increase the resource usage and contention.
    • You can experiment with different values and monitor the impact on the system’s resource usage and performance.
  • innodb_purge_threads: This parameter controls the number of purge threads that run in parallel to process the undo log records.
    • Increasing the value can improve the purge performance by utilizing multiple threads to process the purge work.
    • However, it’s essential to balance the number of purge threads with the available system resources to avoid excessive resource consumption.
  • innodb_max_purge_lag: This parameter specifies the maximum allowed purge lag in bytes.
    • If the purge process falls behind this limit, it may cause the undo logs to accumulate, leading to increased storage requirements and potentially impacting performance.
    • Adjust this parameter based on the available storage and the rate at which undo logs are generated to prevent excessive purge lag.
  • innodb_max_purge_lag_delay: This parameter defines the maximum delay between purging an undo log page and updating the low limit of the history list.
    • Increasing this value can allow more time for other processes to access older versions of the data, but it can also increase the storage requirements for undo logs.
    • Adjust this parameter based on the requirements of your workload and the need for accessing older versions of data.

To tune these parameters, follow these general steps:

  1. Monitor the system’s performance, including undo log utilization, purge lag, and overall system resource usage.
  2. Review the current values of the above parameters and compare them against the system’s workload and available resources.
  3. Make incremental changes to the parameters and observe the impact on performance and resource usage.
  4. Benchmark the system with different parameter values to determine the optimal settings for your specific workload and hardware configuration.
  5. Continuously monitor and fine-tune these parameters as the workload and system requirements evolve.

Note: It’s crucial to thoroughly test and benchmark any configuration changes in a non-production environment before applying them to a production system. Additionally, consult the official MySQL documentation and consider engaging with database experts or consultants for in-depth performance tuning assistance tailored to your specific use case.

About Shiv Iyer 456 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.