Bug #25697 Store ROLLBACK/UNDO to separate configurable files
Submitted: 18 Jan 2007 15:44 Modified: 13 Feb 2012 1:10
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:N/A OS:Any (All)
Assigned to: Sunny Bains CPU Architecture:Any
Tags: innodb, rollback, undo

[18 Jan 2007 15:44] Mark Leith
Description:
Feature request to add the ability to store ROLLBACK/UNDO segments (possibly configurable) outside of the central InnoDB tablespace file.

This will allow space to be reclaimed or optionally limited easily when there are long running transactions that take a lot of space. This might be especially useful for those that run with innodb_file_per_table (where the central tablespace will also not be used for table/index data). 

Something like Oracle's "external/configurable" rollback segments. 

How to repeat:
Nothing to repeat

Suggested fix:
See above
[18 Jan 2007 17:10] Heikki Tuuri
Mark,

this is a reasonable feature request.

Heikki
[9 Jun 2010 19:15] Brad Fino
Is there any chance of getting this feature added?  Currently when we delete large amounts of data for archival purposes we have to end up taking the server instance down for a number of days at a time to export/re-import our data as the ibdata1 file can grow to over 100+ GB.
[17 Jun 2010 22:37] James Day
Brad, still on the desired feature list, not in any preview release. Which means it's unlikely to happen in less than a year in any production release and that's being optimistic.

For now the best workaround is to use delete with order by the primary key and limit, then commit. Repeat until rows deleted is less than the limit specified. Limits of as few as a hundred or even less rows might be needed for transactional systems that are in full production service, depending on the concurrency/locking and disk I/O performance issues that are experienced. 10,000 or so is a reasonable sort of value for bigger batch operations.

The improvements to the insert buffer in 5.5 so that delete operations on secondary index pages can be cached for pages that aren't in the buffer pool should be particularly helpful for typical deleting of old rows jobs. I hope that it'll greatly reduce the disk I/O needs and make this work less troublesome than it can be today. Note that this is just in a preview version at the moment, there's no actual guarantee that it will be in the final version, so don't make any decisions that critically rely on it.
[13 Feb 2012 1:10] Sunny Bains
This functionality is already part of 5.6.