Bug #73409 Moving table data directory is too slow

Submitted: 28 Jul 2014 2:53 Modified: 29 Jul 2014 17:58
Reporter: Mitsuru Oka Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.6.19 OS:Any
Assigned to: CPU Architecture:Any

[28 Jul 2014 2:53] Mitsuru Oka
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.
[29 Jul 2014 17:58] MySQL Verification Team
This is a valid feature request and alternatives should be considered.

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.