株式会社インフィニットループ PHPとスマホアプリ開発を行う札幌のシステム会社

技術ブログ

  1. トップ>
  2. 技術ブログ>
  3. MySQL 5.6 の ALTER TABLE と Percona Toolkit の pt-online-schema-change を比較してみた

2015年11月19日 (木)

著者 : 

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

こんにちわ!みなさん MySQL 使ってますか?インフラの nobuh です。

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

21233325363_bb22dbed87_z

アプリケーションを修正したり、新機能を追加するときはデータベースの構造の変更を行います。MySQL では ALTER TABLE というコマンドを使ってテーブルの構造を変更します。 MySQL が ALTER TABLE の間に内部で何をやっているかといいますと、テーブルを長期間ロックし占有して一気に変更したり、あるいはロックせずに内部的にコピーしながら変更を組み込方法、などの手法を使って構造の変更を行っています。 こうしたデータベースの変更でも多いのが 「インデックスやカラムの追加」 です。

そこで今回は MySQL 5.6 の ALTER TABLE でロックせずにカラムを追加出来る ようになった機能と、複数のクエリ実行でオンラインの変更を行う Percona Toolkit の pt-online-schema-change と、それぞれの動作を比較してみました。

実行環境

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

vbox

使った MySQL
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    |                |
+--------------+-----------------------+------+-----+---------+----------------+

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

+------------+------------+------------+--------------+-------------+
| 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  |
+------------+------------+------------+--------------+-------------+

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

INSERT INTO tbl_report (user_id,type,title,sub_title,body,detail,banner_id,created_at,updated_at) 
VALUES (1111111111,55555,
'これはタイトルです50文字有効','ここはテキストボディで50文字=============',
'ここはメディアムテキスト****************************************',
'ここもメディアムテキスト*****************************************',
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倍になりますので、2 の n 乗のスピードで行数が増えます。
はい、いろいろやってこの行数になりました。これで準備完了です。今回はデータがすべて InnoDB のバッファープールに載っている状態を想定していますので、SELECT するなどしてバッファーに積み込みました。

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

テスト:MySQL 5.6 の ALTER TABLE

ssh 端末を2枚開いて、一方で ALTER TABLE によるカラム追加、一方で参照と更新というふつうのアクセス、を同時に実行します

カラム追加の ALTER TABLE を実行します。(処理に1分かかりました)

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;

約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 と同じく約1分かかりましたが、同時実行の INSERT は 0.1 秒台で実行できました。

まとめ

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

カラム追加 変更にかかった時間 同時実行した0.04秒程度の INSERT
MySQL 5.6 ALTER TABLE 1分 4秒
pt-online-schema-change 1分 0.1秒

使用しているサーバーや、MySQL の設定によって結果は異なりますので、1種類のテストで結論を出すのは危険ですが、もし ALTER TABLE 実行中の負荷などでお悩みの場合は、pt-online-schema-change も試していただければと思います。

弊社では MySQL についての講習会やコンサルティングのご要望もお待ちしています。ご興味のある方はこちらまでお問い合わせください。

コメントはまだありません

No comments yet.

  • このブログについて

    このブログは、札幌市の「株式会社インフィニットループ」が運営する技術ブログです。
    お仕事で使えるITネタを社員たちが発信します!

    最新の記事