Bug #65184 Impossible to optimize subpartitions
Submitted: 2 May 2012 15:09 Modified: 18 Sep 2013 13:15
Reporter: Михаил Веселов Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.5.9, 5.5.25 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D3 (Medium)

[2 May 2012 15:09] Михаил Веселов
Description:
We have an actively used big table with 500M records in it.
For fast data access table has partitions by RANGE and subpartitions by HASH.

We need to optimize table after periodical bulk data deletions.
We can execute 'ALTER TABLE `bigtable` OPTIMIZE PARTITION `p2000`;' to optimize one partition. But it can take a long time and cause some blocking issues.

So we need to optimize table by subpartitions. 

After check commands like 'ALTER TABLE `bigtable` OPTIMIZE PARTITION `p2000sp1`;' doesn't work.

How to repeat:
CREATE TABLE IF NOT EXISTS `bigtable` (
`userid` int(11) unsigned NOT NULL,
`status` tinyint(1) unsigned NOT NULL DEFAULT '0',
`dt` int(11) DEFAULT NULL, -- represents timestamp
KEY `dt` (`dt`),
KEY `ud` (`userid`,`dt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
PARTITION BY RANGE (dt)
SUBPARTITION BY HASH (userid)
SUBPARTITIONS 96
(PARTITION p2000 VALUES LESS THAN (946684800) ENGINE = MyISAM,
PARTITION p2011q2 VALUES LESS THAN (1309478400) ENGINE = MyISAM,
PARTITION p2011q3 VALUES LESS THAN (1317427200) ENGINE = MyISAM,
PARTITION p2011q4 VALUES LESS THAN (1325376000) ENGINE = MyISAM,
PARTITION p2012q1 VALUES LESS THAN (1333238400) ENGINE = MyISAM,
PARTITION p2012q2 VALUES LESS THAN (1341100800) ENGINE = MyISAM,
PARTITION p2012q3 VALUES LESS THAN (1349049600) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = MyISAM); 

INSERT INTO `bigtable` (`userid`, `status`, `dt`) VALUES
(15, 4, 1305636033),
(15, 4, 1305636092),
(6938, 4, 1305636147),
(6938, 0, 1305636154),
(6938, 0, 1305637792),
(6938, 4, 1305637809),
(6938, 4, 1305637839),
(6938, 4, 1305637869),
(6938, 4, 1305637900),
(6938, 4, 1305637930);
[2 May 2012 19:16] Sveta Smirnova
Thank you for the report.

Verified as described.
[18 Sep 2013 13:15] Jon Stephens
Fixed in 5.7. Documented as follows in the 5.7.2 changelog:

        The following operations are now supported for individual
        subpartitions as well as partitions: ANALYZE, CHECK, OPTIMIZE,
        REBUILD, REPAIR, and TRUNCATE.

Closed.
[7 Jul 2014 13:32] Jon Stephens
Updated changelog entry to read as follows:

      The following operations are now supported for individual
      subpartitions as well as partitions: ANALYZE, CHECK, OPTIMIZE,
      REPAIR, and TRUNCATE.

      Note. This fix also allows the use of REPAIR with individual
      subpartitions, even though this is not actually supported by
      MySQL, and has no effect. This issue is fixed in MySQL 5.7.5 and
      later by disallowing REPAIR with subpartitions in ALTER TABLE
      statements.

      See also BUG#19075411 and BUG#73130.
[7 Jul 2014 13:32] Jon Stephens
See BUG#65184 for additional docs info.