Bug #73409 Moving table data directory is too slow
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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.050 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.