As you can read from my Innodb Architecture and Performance Optimization presentation Innodb automatically manages undo area in system tablespace so you never need to care about it. I present it as positive feature reducing administration effort needed but it also can cause a troubles as it happened for me today:
InnoDB: 11 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 292735956 row operations to undo
InnoDB: Trx id counter is 0 96267520
So MySQL Server was restarted (it likely was admin mistake in this case) and spend hours to undo almost 300.000.000 of row operations being unavailable during all of this time.
This was MySQL 4.1, MySQL 5.0 would do better by performing roll back in background but affected data still might not be fully available.
Why one would use such large transaction ? Well it was development mistake. Long and complex data load process was performed in single transaction. Funny enough Innodb handled it so well no one noticed it until rollback was needed during crash recovery.
The bad thing is – there is no way in Innodb to protect from such runaway transactions. You can find them by looking at SHOW INNODB STATUS however.
It would be great and I expect not so complex to add innodb_max_transaction_undo_rows or something similar which will protect from runaway transactions. Setting this setting to appropriate value system administrators could ensure data recovery can be completed withing limited timeframe and catch development errors. Having is SESSION variable would allow to raise the limit if some large transaction needs to be executed which would violate the limit. It is also possible to use global limit for undo entries but I think it is less valuable.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.