MySQL 5.6 の ALTER TABLE と Percona Toolkit の pt-online-schema-change を比較してみた

こんにちわ！みなさん MySQL 使ってますか？インフラの nobuh です。
ゲームなどのサーバーでは、時々サービスを停止していわゆるメンテナンスという作業が行われています。　ゲームをプレイしているユーザーの皆様にはご不便をお掛けして大変申し訳ないところですが、サービスを停止してまで我々アプリ開発やサーバー運用の会社の人たちが何をやっているかといいますと、その理由の大きなものの一つがデーターベースの構造の変更です。

アプリケーションを修正したり、新機能を追加するときはデータベースの構造の変更を行います。MySQL では ALTER TABLE というコマンドを使ってテーブルの構造を変更します。　MySQL が ALTER TABLE の間に内部で何をやっているかといいますと、テーブルを長期間ロックし占有して一気に変更したり、あるいはロックせずに内部的にコピーしながら変更を組み込方法、などの手法を使って構造の変更を行っています。　こうしたデータベースの変更でも多いのが　「インデックスやカラムの追加」　です。
そこで今回は MySQL 5.6 の ALTER TABLE でロックせずにカラムを追加出来る ようになった機能と、複数のクエリ実行でオンラインの変更を行う Percona Toolkit の pt-online-schema-change と、それぞれの動作を比較してみました。

実行環境

クラウド上での仮想サーバーをイメージしつつ、手っ取り早く PC の VirtualBox の仮想サーバーでテストしました。 CentOS 7 のマシンで、プロセッサとメモリはこちらです

使った MySQL

MySQL の設定でデフォルトから変えたところ

innodb_buffer_pool_size = 3000M
log_bin
sql_mode=""

カラム追加のテストに使ったテーブル

+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| report_id    | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| user_id      | int(10) unsigned      | NO   | MUL | NULL    |                |
| type         | smallint(5) unsigned  | NO   |     | NULL    |                |
| title        | varchar(50)           | NO   |     | NULL    |                |
| sub_title    | varchar(50)           | NO   |     | NULL    |                |
| body         | mediumtext            | NO   |     | NULL    |                |
| detail       | mediumtext            | YES  |     | NULL    |                |
| banner_id    | mediumint(8) unsigned | YES  |     | NULL    |                |
| is_read      | tinyint(3) unsigned   | NO   |     | 0       |                |
| is_protected | tinyint(3) unsigned   | NO   |     | 0       |                |
| is_deleted   | tinyint(3) unsigned   | NO   | MUL | 0       |                |
| created_at   | datetime              | NO   |     | NULL    |                |
| updated_at   | datetime              | NO   |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

それっぽくノンユニークなインデックスを２個つけました

+------------+------------+------------+--------------+-------------+
| Table      | Non_unique | Key_name   | Seq_in_index | Column_name |
+------------+------------+------------+--------------+-------------+
| tbl_report |          0 | PRIMARY    |            1 | report_id   |
| tbl_report |          1 | user_id    |            1 | user_id     |
| tbl_report |          1 | is_deleted |            1 | is_deleted  |
+------------+------------+------------+--------------+-------------+

まずは空のテーブルに１行データを挿入します。
プライマリキーがオートインクリメントなので　↑キー　で直前のクエリを呼び出して Enter キーを押すことで繰り返し追加できますが、ほどほどにしておきます。

INSERT INTO tbl_report (user_id,type,title,sub_title,body,detail,banner_id,created_at,updated_at)
VALUES (1111111111,55555,
'これはタイトルです５０文字有効','ここはテキストボディで５０文字＝＝＝＝＝＝＝＝＝＝＝＝＝',
'ここはメディアムテキスト＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊',
'ここもメディアムテキスト＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊',
88888888,NOW(),NOW());

今回試したいのは数十万行規模のテーブルなので、INSERT INTO … SELECT … を使って今の全件データを自分に挿入するクエリを使います

INSERT INTO tbl_report (user_id,type,title,sub_title,body,detail,banner_id,created_at,updated_at)
SELECT user_id,type,title,sub_title,body,detail,banner_id,created_at,updated_at
FROM tbl_report

このクエリを　↑キーで呼び出して実行すると、実行毎に行数が２倍になりますので、2 の n 乗のスピードで行数が増えます。
はい、いろいろやってこの行数になりました。これで準備完了です。今回はデータがすべて InnoDB のバッファープールに載っている状態を想定していますので、SELECT するなどしてバッファーに積み込みました。

+----------+
| count(*) |
+----------+
|   524288 |
+----------+

テスト：MySQL 5.6 の ALTER TABLE

ssh 端末を２枚開いて、一方で ALTER TABLE によるカラム追加、一方で参照と更新というふつうのアクセス、を同時に実行します
カラム追加の ALTER TABLE を実行します。（処理に１分かかりました）

mysql> ALTER TABLE tbl_report ADD COLUMN dummycol1 INT(10) AFTER user_id;
Query OK, 0 rows affected (1 min 2.82 sec)
Records: 0  Duplicates: 0  Warnings: 0

同時にこの INSERT で1行追加します。

mysql> INSERT INTO tbl_report (user_id,type,title,sub_title,body,detail,banner_id,created_at,updated_at)
SELECT user_id,type,title,sub_title,body,detail,b anner_id,created_at,updated_at
FROM tbl_report LIMIT 1；

約１分の ALTER 実行中に INSERT のクエリは開始して終了しています。
実験につかった仮想サーバーで通常 数十ミリ程度の INSERT 処理に約 4 秒かかっていました
MySQL 5.6 になって、ALTER TABLE によるカラム追加中も、別クエリでの平行しての更新が可能になってはいますが、コピー処理による負荷は残っていますので、想定どおりクエリの実行が通常より遅延するのを観察することができました。

テスト：Percona の pt-online-schema-change を使ってカラムを追加してみる

Percona 社さんの pt-online-schema-change は、ざっくりいうと以下の作業を行うことでカラム追加を行います。

1. カラムが追加された新テーブルを作成
2. 旧テーブルにトリガーを敷設して、更新操作が新テーブルにも反映されるようにしつつ
3. 旧テーブルから新テーブルへの必要なデータの複製
4. 新旧のテーブル名の入れ替え

とても賢いありがたいツールですが既存テーブルに対していろんな操作を行いますので、実際に使う場合は すとー さんのこちらの記事　pt-online-schema-changeを安全につかう　などをご参考に何度か練習することをお勧めいたします。
さて話は戻りまして、前述の ALTER TABLE の時と同様に、pt-online-schema-change を実行しながら 1行INSERT を行いました。

pt-online-schema-change --alter 'ADD COLUMN dummy3 INT(11)'
h=localhost,u=root,D=altertest,t=tbl_report --recursion-method processlist --execute

pt-online-schema-change の実行には ALTER TABLE と同じく約１分かかりましたが、同時実行の INSERT は 0.1 秒台で実行できました。

まとめ

ALTER TABLE と同じで、同時実行の INSERT が単独実行の場合と比較して遅延する傾向にはあるものの、pt-online-schema-change の方が少ない影響で実行することができました。

使用しているサーバーや、MySQL の設定によって結果は異なりますので、１種類のテストで結論を出すのは危険ですが、もし ALTER TABLE 実行中の負荷などでお悩みの場合は、pt-online-schema-change も試していただければと思います。
弊社では MySQL についての講習会やコンサルティングのご要望もお待ちしています。ご興味のある方はこちらまでお問い合わせください。