Avoid copying on REORGANIZE PARTITION where possible

Bug #998130 reported by Hartmut Holzgraefe
10
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
New
Wishlist
Unassigned
MySQL Server
Unknown
Unknown

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

E.g.

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

Elena Stepanova (elenst)
Changed in maria:
importance: Undecided → Wishlist
Revision history for this message
Mclyn (mclyn-dimaculangan) wrote :

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!

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.