Description:
Moving table data directory by using ALTER TABLE REORGANIZE PARTITION directive is too slow, especially for compressed table. This is unacceptable because:
* The ALTER TABLE REORGANIZE PARTITION cause table metadata lock, so that this operation involve offline maintenance. Since maintenance time is limited, the DDL should done relatively in short time.
* But the operation time is slow (the operation for compressed table is 2~3 times slower than with uncompressed table).
How to repeat:
Table t0 and its data are given:
CREATE TABLE IF NOT EXISTS t0
(
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
on_time BIGINT NOT NULL,
INDEX USING BTREE ( user_id, on_time )
) ENGINE = 'InnoDB';
Table pt1 is partitioned and uncompressed table.
CREATE TABLE IF NOT EXISTS pt1
(
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
on_time BIGINT NOT NULL,
INDEX USING BTREE ( user_id, on_time )
) ENGINE = 'InnoDB'
PARTITION BY RANGE (id) (
PARTITION p00 VALUES LESS THAN (500000000),
PARTITION p01 VALUES LESS THAN (1000000000),
PARTITION p02 VALUES LESS THAN (1500000000),
PARTITION p03 VALUES LESS THAN (2000000000),
PARTITION pX VALUES LESS THAN MAXVALUE);
Table pt1 is partitioned and compressed table.
CREATE TABLE IF NOT EXISTS pt2
(
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
on_time BIGINT NOT NULL,
INDEX USING BTREE ( user_id, on_time )
) ENGINE = 'InnoDB' ROW_FORMAT=COMPRESSED
PARTITION BY RANGE (id) (
PARTITION p00 VALUES LESS THAN (500000000),
PARTITION p01 VALUES LESS THAN (1000000000),
PARTITION p02 VALUES LESS THAN (1500000000),
PARTITION p03 VALUES LESS THAN (2000000000),
PARTITION pX VALUES LESS THAN MAXVALUE);
INSERT INTO pt1 SELECT * FROM t0;
Query OK, 6960814 rows affected (1 min 24.60 sec)
Records: 6960814 Duplicates: 0 Warnings: 0
ALTER TABLE pt1 REORGANIZE PARTITION p00 INTO (
PARTITION p00 VALUES LESS THAN (500000000) DATA DIRECTORY '/usr/local/tmp/mysql-data' );
Query OK, 6960814 rows affected (56.00 sec)
Records: 6960814 Duplicates: 0 Warnings: 0
INSERT INTO pt2 SELECT * FROM t0;
Query OK, 6960814 rows affected (3 min 18.06 sec)
Records: 6960814 Duplicates: 0 Warnings: 0
ALTER TABLE pt2 REORGANIZE PARTITION p00 INTO (
PARTITION p00 VALUES LESS THAN (500000000) DATA DIRECTORY '/usr/local/tmp/mysql-data' );
Query OK, 6960814 rows affected (2 min 43.59 sec)
Records: 6960814 Duplicates: 0 Warnings: 0
REORGANIZE PARTITION operation for pt2 is 3 times slower than for pt1.
Suggested fix:
There are several alternative approaches:
* To reduce an operation time. It may be possible to avoid decompression/recompression pages while REORGANIZE PARTITION operation.
* To implement a non-locking ALTER TABLE REORGANIZE PARTITION (online-DDL).
* To allow ALTER TABLE EXCHANGE PARTITION directive to pass a table that data directory is specified. Currently ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE t does not allow table t to have 'DATA DIRECTORY' attribute.
