MySQL Bugs: #25697: Store ROLLBACK/UNDO to separate configurable files

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

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

Mark,

this is a reasonable feature request.

Heikki

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.

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.

This functionality is already part of 5.6.