Senior LevelSQL & MySQL
What is the role of the Undo Log and Purge Thread in InnoDB MVCC cleanup?
❓ Interview Prompt: Explain Undo Log segments, History List Length (HLL), InnoDB Purge Threads, and long-running transaction bloat.
The Undo Log stores historical versions of modified rows for MVCC read views and transaction rollbacks:
Purge Thread Cleanup:
•
Once all active transactions finish reading old row versions, the Purge Thread garbage-collects obsolete Undo Log pages.
•
⚠️ Long-Running Transaction Pitfall: A single transaction left open for hours prevents the Purge Thread from freeing old Undo Logs, causing History List Length (HLL) to explode and degrading server performance!
Code Implementation Example
sql
-- Monitoring InnoDB History List Length (HLL) bloat
SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';💡Monitoring InnoDB History List Length for Undo Log bloat.
🎯 Interview Key Takeaway
Avoid long-running read transactions to prevent Undo Log History List Length bloat and performance degradation.
#InnoDB#Undo Log#Purge Thread#MVCC#Senior
Back to all SQL & MySQL Questions→