Bug #42822 optimize partition recreates full table instead just partition
Submitted: 13 Feb 2009 10:24 Modified: 7 Nov 2012 18:02
Reporter: Bogdan Kecman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.30 OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[13 Feb 2009 10:24] Bogdan Kecman
Description:
InnoDB table with multiple partitions,
when ALTER TABLE OPTIMIZE PARTITION on only ONE partition is run,
ALL partitions are copied to /tmp files and rebuilt.

When running the command ALTER TABLE REBUILD PARTITION on only ONE partition,
only ONE partition is copied to a tmp file.

The ALTER TABLE OPTIMIZE PARTITION command should only optimize the partitions specified. 

How to repeat:
execute ALTER TABLE OPTIMIZE PARTITION on a single partition of innodb table

Suggested fix:
.
[20 Feb 2009 17:54] Mattias Jonsson
The workaround is to do:
ALTER TABLE t REBUILD PARTITION <list of partitions>
ALTER TABLE t ANALYZE PARTITION <list of partitions>

Which is the way it is done for innodb.

Since innodb does not support optimize and suggest ALTER instead, mysql recreates the table and then doing analyze on it afterwards, so this workaround is just to be explicit about it.

See also bug#20129
[21 Sep 2009 8:58] Cyril SCETBON
not fixed in 5.1.39 (from mysql labs)
[26 Jan 2011 22:34] Mattias Jonsson
marked bug#46158 as duplicate of this.
[18 Jun 2011 1:31] Rick James
Not fixed in 5.1.48.
[27 Sep 2011 9:27] Emre Erkunt
Not fixed in 5.1.58
[19 Apr 2012 19:21] Matthew Moles
Still present in 5.5.20, can a note be added to the documentation, if I had discovered this a week later it would have caused several hours of downtime on a live system
[30 Jun 2012 7:40] Jon Stephens
Discussed issue with Valeriy on Jabber. Putting this in Documenting status for now, so that the issue/workaround gets noted.
[2 Jul 2012 7:52] Jon Stephens
Added a warning about this issue (with workaround) to "Partitioning Limitations" and "ALTER TABLE Syntax" in versions 5.1+ of the Manual.

Set bug status back to Verified awaiting further resolution.
[24 Aug 2012 18:19] Rick James
There is no note in the documentation on OPTIMIZE PARTITION:
http://dev.mysql.com/doc/refman/5.5/en/partitioning-maintenance.html
http://dev.mysql.com/doc/refman/5.1/en/partitioning-maintenance.html
Please add it there.
[7 Nov 2012 18:02] Jon Stephens
Already documented as a known issue. Closed.
[7 Nov 2012 19:00] Jon Stephens
Hi Rick,

This might come a little late (I didn't know this bug report existed before today), but OPTIMIZE TABLE is documented here:

https://dev.mysql.com/doc/refman/5.5/en/optimize-table.html

and ALTER TABLE ... OPTIMIZE PARTITION is documented here:

https://dev.mysql.com/doc/refman/5.5/en/alter-table-partition-operations.html

Both of these pages are linked from the first couple of paragraphs of https://dev.mysql.com/doc/refman/5.5/en/partitioning-maintenance.html
[7 Nov 2012 19:15] Joe Grasse
I see that the documentation task has been done, but I don't think that should count as closing the issue? It seems like the actual issues should be fix. Such as perform the suggested workaround under the covers when an optimize is issued on a partitions.
[7 Nov 2012 19:33] Rick James
My complaint was, and still is, that
   https://dev.mysql.com/doc/refman/5.5/en/partitioning-maintenance.html
fails to warn about the deficiency in OPTIMIZE.  That page feels self contained; the user does not necessarily see a need to go to
   https://dev.mysql.com/doc/refman/5.5/en/alter-table-partition-operations.html
where the caveat is buried.

The link is hidden as "ALTER TABLE" in
"You can use a number of extensions to [ALTER TABLE] for performing operations of this type on one or more partitions directly, as described in the following list: "

+1 for Joe Grasse's comment.
[14 Nov 2012 23:43] James Day
I've asked Jon to look at this, looks as though he missed posting the relevant release note here.
[8 May 2013 13:46] Rainer Stumbaum
Just stepped into that hole as well..... damn.
[14 May 2015 23:48] James Day
For 5.6 and later a workaround that may be viable sometimes is to use ALTER TABLE ... EXCHANGE PARTITION then optimise the table before reversing the exchange to move it back.

The manual was updated to describe this limitation and now also references this bug.

Here's the missing release note for this bug report:

Documented as follows in the 5.1.68, 5.5.30, 5.6.9, and 5.7.0 changelogs:

        Previously, when attempting to optimize one or more partitions
        of a partitioned table that used a storage engine that does not
        support partition-level OPTIMIZE, such as InnoDB, MySQL reported
        "Table does not support optimize, doing recreate + analyze
        instead", then re-created the entire table, but did not analyze
        it. Now in such cases, the message is "Table does not support
        optimize on partitions. All partitions will be rebuilt and
        analyzed." In addition, the entire table is analyzed after first
        being rebuilt.

The limitation that InnoDB doesn't support per-partition OPTIMIZE TABLE yet and feature request to implement that remains.

There might be some movement on that once the native InnoDB partition work has been completed. No ETA, though 5.7 is unlikely.

James Day, MySQL Senior Principal Support Engineer, Oracle
[1 Jun 2017 9:25] Arnaud Adant
Is there a FR for fixing this in 8.0 ?

Also this could apply to partitions :

https://bugs.mysql.com/bug.php?id=57583