MySQL 5.6における大量データロード時の考慮点

ご縁があってAWS User Group - Japanにお誘いいただき、10月4日に第18回 AWS User Group - Japan 東京勉強会で発表をしてきました。運営のみなさま、当日お越しいただいたみなさま、どうもありがとうございます。

今回は「秋のDB祭り」ということで、MySQLに限らずさまざまなデータベースに関する話題が取り扱われていました。その中でもRedshiftのセッションが複数あり、注目度の高さが伺えました。クラスメソッドさん、EnterpriseZineさんが勉強会の様子を詳しくレポートされています。

• 第18回 AWS User Group - Japan 東京勉強会 に参加してきた #jawsug ｜ Developers.IO
• JAWS-UG東京勉強会で濃ゆーい「秋のDB祭り」：企業のIT・経営・ビジネスをつなぐ情報サイト EnterpriseZine (EZ)

私のセッションはMySQL 5.6における最適なデータロード手順をまとめたものです。こうしたMySQL 5.6の細かいノウハウは、これからも折を見て書きためていけたらと思います。資料は勉強会後に少し修正してRevision 2となっています。

• プレゼンテーション資料 (PDF)

プレゼンテーション資料からリンクしているウェブサイトの一覧です。特に、日本HPさんの資料について内容を理解していることが前提となります。

• 日本HP Linux ブループリント
  • MySQL Server 5.0 InnoDBデータベース 大量データ投入方法 (PDF)
• MySQL :: MySQL 5.6 Reference Manual :: 5.5.1 Overview of Online DDL
• MySQL Bugs: #57583: fast index create not used during "alter table foo engine=innodb"

tmpdirについて少し補足します。MySQLがtmpdirに作成した一時ファイルはlsなどのコマンドでは見ることができません。これはMySQLが一時ファイルを作成したあと、すぐに削除してしまうためです。

LinuxなどのOSでは、ファイルを削除してもそのファイルをオープンしているプロセスがある限り実際の削除は行われません。逆に言えば、こうしておくことでプロセス終了時に一時ファイルが自動的にクリーンアップされるというわけです。lsofコマンドであればMySQLが作成した一時ファイルを確認することができます。

# lsof -p 15260
COMMAND   PID  USER   FD   TYPE             DEVICE   SIZE/OFF    NODE NAME
…
mysqld  15260 mysql   37u   REG             252,33   77594624      17 /opt/mysql56/tmp/ibogf9Oi (deleted)
mysqld  15260 mysql   38u   REG             252,33  264241152      18 /opt/mysql56/tmp/ibflHj2M (deleted)
mysqld  15260 mysql   39u   REG             252,33  264241152      19 /opt/mysql56/tmp/ib3syufh (deleted)

測定に使用したスクリプトを以下に貼り付けておきます。

-- ---------------------------------------------------------
SELECT 'PATTERN 1';

DROP TABLE IF EXISTS `order_line`;

CREATE TABLE `order_line` (
  `ol_o_id` int(11) NOT NULL DEFAULT '0',
  `ol_d_id` int(11) NOT NULL DEFAULT '0',
  `ol_w_id` int(11) NOT NULL DEFAULT '0',
  `ol_number` int(11) NOT NULL DEFAULT '0',
  `ol_i_id` int(11) DEFAULT NULL,
  `ol_supply_w_id` int(11) DEFAULT NULL,
  `ol_delivery_d` datetime DEFAULT NULL,
  `ol_quantity` decimal(2,0) DEFAULT NULL,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`),
  KEY `order_line_ix1` (`ol_i_id`),
  KEY `order_line_ix2` (`ol_dist_info`),
  CONSTRAINT `order_line_fk1`
    FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`)
    REFERENCES `orders` (`o_w_id`, `o_d_id`, `o_id`),
  CONSTRAINT `order_line_fk2`
    FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`)
    REFERENCES `stock` (`s_w_id`, `s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOAD DATA LOCAL INFILE '/home/taira/order_line.dat'
  INTO TABLE `order_line`;

ANALYZE TABLE `order_line`;

SHOW TABLE STATUS LIKE 'order_line'\G

-- ---------------------------------------------------------
SELECT 'PATTERN 2';

DROP TABLE IF EXISTS `order_line`;

CREATE TABLE `order_line` (
  `ol_o_id` int(11) NOT NULL DEFAULT '0',
  `ol_d_id` int(11) NOT NULL DEFAULT '0',
  `ol_w_id` int(11) NOT NULL DEFAULT '0',
  `ol_number` int(11) NOT NULL DEFAULT '0',
  `ol_i_id` int(11) DEFAULT NULL,
  `ol_supply_w_id` int(11) DEFAULT NULL,
  `ol_delivery_d` datetime DEFAULT NULL,
  `ol_quantity` decimal(2,0) DEFAULT NULL,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`),
  KEY `order_line_ix1` (`ol_i_id`),
  KEY `order_line_ix2` (`ol_dist_info`),
  CONSTRAINT `order_line_fk1`
    FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`)
    REFERENCES `orders` (`o_w_id`, `o_d_id`, `o_id`),
  CONSTRAINT `order_line_fk2`
    FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`)
    REFERENCES `stock` (`s_w_id`, `s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET SESSION foreign_key_checks = OFF;

LOAD DATA LOCAL INFILE '/home/taira/order_line.dat'
  INTO TABLE `order_line`;

SET SESSION foreign_key_checks = ON;

ANALYZE TABLE `order_line`;

SHOW TABLE STATUS LIKE 'order_line'\G

-- ---------------------------------------------------------
SELECT 'PATTERN 3';

DROP TABLE IF EXISTS `order_line`;

CREATE TABLE `order_line` (
  `ol_o_id` int(11) NOT NULL DEFAULT '0',
  `ol_d_id` int(11) NOT NULL DEFAULT '0',
  `ol_w_id` int(11) NOT NULL DEFAULT '0',
  `ol_number` int(11) NOT NULL DEFAULT '0',
  `ol_i_id` int(11) DEFAULT NULL,
  `ol_supply_w_id` int(11) DEFAULT NULL,
  `ol_delivery_d` datetime DEFAULT NULL,
  `ol_quantity` decimal(2,0) DEFAULT NULL,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`),
  CONSTRAINT `order_line_fk1`
    FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`)
    REFERENCES `orders` (`o_w_id`, `o_d_id`, `o_id`),
  CONSTRAINT `order_line_fk2`
    FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`)
    REFERENCES `stock` (`s_w_id`, `s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET SESSION foreign_key_checks = OFF;

LOAD DATA LOCAL INFILE '/home/taira/order_line.dat'
  INTO TABLE `order_line`;

SET SESSION foreign_key_checks = ON;

ALTER TABLE `order_line`
  ADD KEY `order_line_ix1` (`ol_i_id`);

ALTER TABLE `order_line`
  ADD KEY `order_line_ix2` (`ol_dist_info`);

ANALYZE TABLE `order_line`;

SHOW TABLE STATUS LIKE 'order_line'\G

-- ---------------------------------------------------------
SELECT 'PATTERN 4';

DROP TABLE IF EXISTS `order_line`;

CREATE TABLE `order_line` (
  `ol_o_id` int(11) NOT NULL DEFAULT '0',
  `ol_d_id` int(11) NOT NULL DEFAULT '0',
  `ol_w_id` int(11) NOT NULL DEFAULT '0',
  `ol_number` int(11) NOT NULL DEFAULT '0',
  `ol_i_id` int(11) DEFAULT NULL,
  `ol_supply_w_id` int(11) DEFAULT NULL,
  `ol_delivery_d` datetime DEFAULT NULL,
  `ol_quantity` decimal(2,0) DEFAULT NULL,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`),
  CONSTRAINT `order_line_fk1`
    FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`)
    REFERENCES `orders` (`o_w_id`, `o_d_id`, `o_id`),
  CONSTRAINT `order_line_fk2`
    FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`)
    REFERENCES `stock` (`s_w_id`, `s_i_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET SESSION foreign_key_checks = OFF;

LOAD DATA LOCAL INFILE '/home/taira/order_line.dat'
  INTO TABLE `order_line`;

SET SESSION foreign_key_checks = ON;

ALTER TABLE `order_line`
  ADD KEY `order_line_ix1` (`ol_i_id`),
  ADD KEY `order_line_ix2` (`ol_dist_info`);

ANALYZE TABLE `order_line`;

SHOW TABLE STATUS LIKE 'order_line'\G

-- ---------------------------------------------------------
SELECT 'PATTERN 5';

DROP TABLE IF EXISTS `order_line`;

CREATE TABLE `order_line` (
  `ol_o_id` int(11) NOT NULL DEFAULT '0',
  `ol_d_id` int(11) NOT NULL DEFAULT '0',
  `ol_w_id` int(11) NOT NULL DEFAULT '0',
  `ol_number` int(11) NOT NULL DEFAULT '0',
  `ol_i_id` int(11) DEFAULT NULL,
  `ol_supply_w_id` int(11) DEFAULT NULL,
  `ol_delivery_d` datetime DEFAULT NULL,
  `ol_quantity` decimal(2,0) DEFAULT NULL,
  `ol_amount` decimal(6,2) DEFAULT NULL,
  `ol_dist_info` char(24) DEFAULT NULL,
  PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOAD DATA LOCAL INFILE '/home/taira/order_line.dat'
  INTO TABLE `order_line`;

SET SESSION foreign_key_checks = OFF;

ALTER TABLE `order_line`
  ADD KEY `order_line_ix1` (`ol_i_id`),
  ADD KEY `order_line_ix2` (`ol_dist_info`),
  ADD CONSTRAINT `order_line_fk1`
    FOREIGN KEY (`ol_w_id`, `ol_d_id`, `ol_o_id`)
    REFERENCES `orders` (`o_w_id`, `o_d_id`, `o_id`),
  ADD CONSTRAINT `order_line_fk2`
    FOREIGN KEY (`ol_supply_w_id`, `ol_i_id`)
    REFERENCES `stock` (`s_w_id`, `s_i_id`);

SET SESSION foreign_key_checks = ON;

ANALYZE TABLE `order_line`;

SHOW TABLE STATUS LIKE 'order_line'\G