Bug #46158 Make partition maintenance commands more efficient
Submitted: 13 Jul 2009 23:53 Modified: 26 Jan 2011 22:33
Reporter: Lachlan Mulcahy Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: partitioning

[13 Jul 2009 23:53] Lachlan Mulcahy 
Description:
Current behaviour of partition maintenance commands within ALTER TABLE is to copy the entire table to a new temporary table, perform the requested action on the specified partitions and then move the new table into place of the old table.

This is inefficient, especially for very large tables with many partitions, as only the selected partitions really need temporary copies made.

How to repeat:
Perform an ALTER TABLE ... OPTIMIZE PARTITION.. on a subset of partitions on a partitioned table and observe the temporary table files in the database directory created for the entire table.

Suggested fix:
Make ALTER TABLE "smarter" for partition maintenance commands so that only the partitions of the table to be changed need to be copied OR if this is too tricky due to the "mutli-command" ability of ALTER TABLE, create a new ALTER PARTITION command.

eg. 

ALTER PARTITION p0,p1 FROM t1 OPTIMIZE
ALTER PARTITION p0,p1 FROM t1 ANALYZE
..etc.

This would read lock the table, copy the partitions to be altered to scratch space, perform the requested action on the partitions and then move them in place of the original partitions. Read lock on the table would be released, allowing new writes to process.
[14 Jul 2009 7:06] Valeriy Kravchuk 
Thank you for the feature request.
[15 Jul 2009 2:19] Lachlan Mulcahy 
My interpretation of the behaviour was incorrect.

After looking at the code, it seems only OPTIMIZE PARTITION on InnoDB tables causes a complete copy of the table to be made. This is likely due to the way the InnoDB storage engine handles the partition optimize call and not a fault of ALTER TABLE functionality itself.

Closing this for now and will reopen a new feature request on InnoDB if necessary.
[23 Jul 2009 22:41] Lachlan Mulcahy 
Bug http://bugs.mysql.com/bug.php?id=42822 basically covers this already. Please consider closing this/merging?
[26 Jan 2011 22:33] Mattias Jonsson 
closing as duplicate of bug#42822.