MySQL Bugs: #42822: optimize partition recreates full table instead just partition

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

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:
.

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

not fixed in 5.1.39 (from mysql labs)

marked bug#46158 as duplicate of this.

Not fixed in 5.1.48.

Not fixed in 5.1.58

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

Discussed issue with Valeriy on Jabber. Putting this in Documenting status for now, so that the issue/workaround gets noted.

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.

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.

Already documented as a known issue. Closed.

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

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.

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.

I've asked Jon to look at this, looks as though he missed posting the relevant release note here.

Just stepped into that hole as well..... damn.

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

Is there a FR for fixing this in 8.0 ?

Also this could apply to partitions :

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