MySQL Bugs: #65286: Avoid copying on REORGANIZE PARTITION where possible

Bug #65286	Avoid copying on REORGANIZE PARTITION where possible
Submitted:	11 May 2012 16:28	Modified:	12 May 2012 10:34
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S5 (Performance)
Version:	mysql 5.1, 5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When modifying a range partition using ALTER TABLE...REORGANIZE PARTITION it may happen that all rows from the original partition end up in the same new partition. In this case the underlying table could simply be reused intead of being copied over as it happens now.

How to repeat:
CREATE TABLE `t1` (
`id` int auto_increment primary key,
) ENGINE=InnoDB
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (1500000) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
;

INSERT INTO t1 SELECT NULL;
INSERT INTO t1 SELECT NULL FROM t1; -- repeat until > 1 million affected rows are reported

SELECT max(id) FROM t1; -- verify that number of rows is somehere between 1.6 and 2.9 million

ALTER TABLE t1 REORGANIZE PARTITION p2 INTO (
  PARTITION p2 VALUES LESS THAN (3000000),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);

All rows from the original p2 will end up in the new p2 anyway, so the REORGANIZE could just create the new empty p3 partition and modify the top level partition meta information, but the operation is actually taking quite some time as it simply seems to copy over all data from the old p2 partition to the new one ...

Suggested fix:
Reuse underlying partition tables if all rows from the original partition will end up in the same new partition anyway, only copy rows over to new internal tables if they will end up in more than one of the new reorganized partitons

Thank you for the problem report. Verified with 5.5.x on Mac OS X.

Any update on this.

For using partitioning on a live system to provide a rolling table of partitions, add new to front drop old off back, this is really needed to reduce downtime. Especially for large tables used within a replication scenario.

The assumption
'if all rows from the original partition will end up in the same new partition anyway'
needs to scan all rows in the partition in the general case, so it will not be an instant operation. Only if there is an usable index, one could do this in a fast way.

In this case I suggest to REORGANIZE the last partition before any rows is inserted into it. Or not using a MAXVALUE partition and instead ADD new partitions to increase the end range.

ALTER TABLE table_name 
 REORGANIZE PARTITION current_partition INTO
 PARTITION current_partition VALUES LESS THAN (18000000), 
 PARTITION new_partition VALUES LESS THAN MAXVALUE);

Executing this query took us 2-3 minutes and INSERT or UPDATE on the table cannot be performed during the partitioning operation. Fixing this bug as soon as possible would help us decrease our application's downtime. Thanks!

The same problem also occurs on 5.6.20.