pt-online-schema-changeによるMySQLオンラインDDLの仕組み

pt-online-schema-changeは、Percona Toolkitに含まれるツールで、MySQLのテーブル構造変更をオンラインで実行する際に利用されます。本稿では、公式ドキュメントおよびgeneral logの解析を通じて、その内部動作を解説します。

検証用テーブルの準備

検証には、以下のシンプルな構造のテーブルを使用します。

mysql> SHOW CREATE TABLE users \G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1005764 DEFAULT CHARSET=utf8

このテーブルには約100万件のレコードが格納されています。

mysql> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
|  1005763 |
+----------+

カラム追加の実行

以下のコマンドで、usersテーブルにcreated_atカラムを追加します。

# pt-online-schema-change --execute --alter "ADD COLUMN created_at DATETIME" D=test,t=users

ツールの実行フロー

実行時の標準出力から、主要なステップを抽出すると以下の通りです。

Found 2 slaves:
  test
  hbase
Will check slave lag on:
  test
  hbase
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`users`...
Creating new table...
CREATE TABLE `test`.`_users_new` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1005764 DEFAULT CHARSET=utf8
Created new table test._users_new OK.
Waiting forever for new table `test`.`_users_new` to replicate to test...
Altering new table...
ALTER TABLE `test`.`_users_new` ADD COLUMN created_at DATETIME
Altered `test`.`_users_new` OK.
Creating triggers...
CREATE TRIGGER `pt_osc_test_users_del` AFTER DELETE ON `test`.`users` FOR EACH ROW DELETE IGNORE FROM `test`.`_users_new` WHERE `test`.`_users_new`.`uid` <=> OLD.`uid`
CREATE TRIGGER `pt_osc_test_users_upd` AFTER UPDATE ON `test`.`users` FOR EACH ROW REPLACE INTO `test`.`_users_new` (`uid`) VALUES (NEW.`uid`)
CREATE TRIGGER `pt_osc_test_users_ins` AFTER INSERT ON `test`.`users` FOR EACH ROW REPLACE INTO `test`.`_users_new` (`uid`) VALUES (NEW.`uid`)
Created triggers OK.
Copying approximately 1005075 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_users_new` (`uid`) SELECT `uid` FROM `test`.`users` FORCE INDEX(`PRIMARY`) WHERE ((`uid` >= ?)) AND ((`uid` <= ?)) LOCK IN SHARE MODE
SELECT /*!40001 SQL_NO_CACHE */ `uid` FROM `test`.`users` FORCE INDEX(`PRIMARY`) WHERE ((`uid` >= ?)) ORDER BY `uid` LIMIT ?, 2
Copying `test`.`users`:  40% 00:44 remain
Copying `test`.`users`:  82% 00:12 remain
Copied rows OK.
Analyzing new table...
Swapping tables...
RENAME TABLE `test`.`users` TO `test`.`_users_old`, `test`.`_users_new` TO `test`.`users`
Swapped original and new tables OK.
Dropping old table...
DROP TABLE IF EXISTS `test`.`_users_old`
Dropped old table `test`.`_users_old` OK.
Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_del`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_upd`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_ins`;
Dropped triggers OK.
Successfully altered `test`.`users`.

general logによる詳細解析

1. セッション初期化フェーズ

1052 Connect    root@localhost on test
1052 Query    SET autocommit=1
1052 Query    SHOW VARIABLES LIKE 'innodb\_lock\_wait\_timeout'
1052 Query    SET SESSION innodb_lock_wait_timeout=1
1052 Query    SHOW VARIABLES LIKE 'lock\_wait\_timeout'
1052 Query    SET SESSION lock_wait_timeout=60
1052 Query    SHOW VARIABLES LIKE 'wait\_timeout'
1052 Query    SET SESSION wait_timeout=10000
1052 Query    SELECT @@SQL_MODE
1052 Query    SET @@SQL_QUOTE_SHOW_CREATE = 1
1052 Query    SELECT @@server_id /*!50038 , @@hostname*/

接続直後に、innodb_lock_wait_timeout、lock_wait_timeout、wait_timeoutなどのセッション変数が調整されます。これにより、ロック競合時の待機時間を短縮し、長時間のブロックを防止します。

2. 事前チェックフェーズ

1052 Query    SHOW VARIABLES LIKE 'wsrep_on'
1052 Query    SHOW ENGINES
1052 Query    SHOW VARIABLES LIKE 'innodb_version'
1052 Query    SHOW VARIABLES LIKE 'innodb_stats_persistent'
1052 Query    SELECT @@SERVER_ID
1052 Query    SHOW GRANTS FOR CURRENT_USER()
1052 Query    SHOW FULL PROCESSLIST
1052 Query    SHOW SLAVE HOSTS
1052 Query    SHOW GLOBAL STATUS LIKE 'Threads_running'
1052 Query    SHOW TABLES FROM `test` LIKE 'users'
1052 Query    SHOW TRIGGERS FROM `test` LIKE 'users'
1052 Query    SHOW CREATE TABLE `test`.`users`
1052 Query    EXPLAIN SELECT * FROM `test`.`users` WHERE 1=1
1052 Query    SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='test' AND referenced_table_name='users'

このフェーズでは以下の確認を行います。

データベースエンジン、バージョン、サーバーIDの確認
実行ユーザーの権限確認
レプリカサーバーの存在確認
対象テーブルの存在確認
対象テーブルに既存のトリガーが存在しないことの確認
実行計画の取得
外部キー制約によって参照されていないことの確認

3. 新規テーブル作成とALTER適用フェーズ

39 Query    CREATE TABLE `test`.`_users_new` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1005764 DEFAULT CHARSET=utf8
39 Query    ALTER TABLE `test`.`_users_new` ADD COLUMN created_at DATETIME

対象テーブルと同一構造の_users_newテーブルを作成した後、この新規テーブルに対してALTER文を実行します。つまり、スキーマ変更自体はMySQLネイティブのDDL機能に委ねられます。

4. トリガー作成フェーズ

39 Query    CREATE TRIGGER `pt_osc_test_users_del` AFTER DELETE ON `test`.`users` FOR EACH ROW DELETE IGNORE FROM `test`.`_users_new` WHERE `test`.`_users_new`.`uid` <=> OLD.`uid`
39 Query    CREATE TRIGGER `pt_osc_test_users_upd` AFTER UPDATE ON `test`.`users` FOR EACH ROW REPLACE INTO `test`.`_users_new` (`uid`) VALUES (NEW.`uid`)
39 Query    CREATE TRIGGER `pt_osc_test_users_ins` AFTER INSERT ON `test`.`users` FOR EACH ROW REPLACE INTO `test`.`_users_new` (`uid`) VALUES (NEW.`uid`)

元テーブルに対して3つのトリガー（DELETE/UPDATE/INSERT）を作成します。これにより、データコピー中に元テーブルに対して発生したDML操作が新規テーブルへ同期されます。

REPLACE INTO構文は主キーまたはユニークキーに基づいて動作するため、対象テーブルにこれらのキーが存在することが前提となります。これが、pt-online-schema-changeが対象テーブルに主キーまたはユニークインデックスを要求する理由です。

5. チャンク単位のデータコピー

39 Query    SHOW GLOBAL STATUS LIKE 'Threads_running'
39 Query    SELECT /*!40001 SQL_NO_CACHE */ `uid` FROM `test`.`users` FORCE INDEX(`PRIMARY`) WHERE ((`uid` >= '1')) ORDER BY `uid` LIMIT 999, 2
39 Query    INSERT LOW_PRIORITY IGNORE INTO `test`.`_users_new` (`uid`) SELECT `uid` FROM `test`.`users` FORCE INDEX(`PRIMARY`) WHERE ((`uid` >= '1')) AND ((`uid` <= '1000')) LOCK IN SHARE MODE
39 Query    SHOW GLOBAL STATUS LIKE 'Threads_running'
39 Query    SELECT /*!40001 SQL_NO_CACHE */ `uid` FROM `test`.`users` FORCE INDEX(`PRIMARY`) WHERE ((`uid` >= '1001')) ORDER BY `uid` LIMIT 28516, 2
39 Query    INSERT LOW_PRIORITY IGNORE INTO `test`.`_users_new` (`uid`) SELECT `uid` FROM `test`.`users` FORCE INDEX(`PRIMARY`) WHERE ((`uid` >= '1001')) AND ((`uid` <= '29517')) LOCK IN SHARE MODE

データコピーはチャンク（小単位）ごとに実行されます。各チャンクの処理フローは以下の通りです。

SHOW GLOBAL STATUS LIKE 'Threads_running'でシステム負荷を監視
主キーインデックスを強制使用し、次のチャンク境界を特定するためのクエリを発行
対象範囲のレコードに対してLOCK IN SHARE MODEで共有ロックを取得しつつ、新規テーブルへデータをコピー

共有ロックの使用により、コピー対象レコードに対するクライアントからのDML操作はブロックされます。ただし、コピーはチャンク単位で行われるため、ロック保持時間は短く抑えられます。

また、チャンクサイズは動的に調整されます。上記の例では、最初のチャンクが999行（境界値として2行を取得）であったのに対し、2つ目のチャンクは28516行まで拡大されています。

6. テーブル入れ替えとクリーンアップ

39 Query    ANALYZE TABLE `test`.`_users_new`
39 Query    RENAME TABLE `test`.`users` TO `test`.`_users_old`, `test`.`_users_new` TO `test`.`users`
39 Query    DROP TABLE IF EXISTS `test`.`_users_old`
39 Query    DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_del`
39 Query    DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_upd`
39 Query    DROP TRIGGER IF EXISTS `test`.`pt_osc_test_users_ins`

データコピー完了後、以下の処理が実行されます。

ANALYZE TABLEで新規テーブルの統計情報を更新
RENAME TABLEで元テーブルと新規テーブルを入れ替え
旧テーブル（_users_old）を削除
作成した3つのトリガーを削除

ANALYZE TABLEの実行は重要です。公式ドキュメントでは、以下のように説明されています。

This circumvents a potentially serious issue related to InnoDB optimizer statistics.
If the table being altered is busy and the tool completes quickly, the new table will
not have optimizer statistics after being swapped. This can cause fast, index-using
queries to do full table scans until optimizer statistics are updated (usually after
10 seconds). If the table is large and the server very busy, this can cause an outage.

統計情報が未更新のままテーブルが入れ替わると、オプティマイザが適切な実行計画を選択できず、インデックスを利用すべきクエリがフルテーブルスキャンに堕落する危険があります。これを防ぐため、入れ替え前に必ず統計情報を更新します。

タグ: pt-online-schema-change MySQL Percona Toolkit Online DDL DDL

10月13日 23:15 投稿

異端開発室