In this lesson, you will learn how to configure and rotate InnoDB undo tablespaces to regain disk space and prevent storage bloat.
Understanding Undo Tablespace Bloat
In InnoDB, undo logs are essential for MVCC (Multi-Version Concurrency Control) and transaction rollbacks. While these logs are stored in the system tablespace by default, it is best practice to move them into separate, dedicated undo tablespaces. However, a common challenge is that these files can grow indefinitely if long-running transactions prevent the cleanup of older undo records. Once a file grows, it rarely shrinks automatically, leading to unnecessary disk usage.
Configuring and Rotating Undo Tablespaces
To prevent this, you can configure multiple undo tablespaces. By setting innodb_undo_tablespaces to a value greater than 1, MySQL distributes the workload across several files. More importantly, if an undo tablespace grows too large, you can "rotate" or truncate it by marking it as inactive and then re-enabling it.
To truncate an undo tablespace, follow this lifecycle:
- Set to Inactive: You cannot truncate an active tablespace.
ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE; - Wait for Purge: The background purge thread will clean up the records in that tablespace. Once it becomes empty, the status changes to
empty. - Truncate/Rotate: Once empty, you can return it to service, which effectively resets its size.
ALTER UNDO TABLESPACE innodb_undo_001 SET ACTIVE;
Best Practices for Maintenance
To manage this effectively, monitor the size of your undo files using the information_schema.innodb_tablespaces table. If you notice a specific tablespace remains bloated, check for "hanging" transactions that might be holding onto old versions of rows, preventing the purge thread from doing its job. By proactively rotating these files, you maintain high performance and ensure your database storage remains predictable rather than ballooning due to stale transaction history.
Key Takeaway: You manage undo tablespace growth by distributing logs across multiple files and periodically setting them to INACTIVE to trigger a purge-based truncation.