オンラインで安全にスキーマ変更可能なpt-online-schema-change | サイバーエージェント 公式エンジニアブログ
AmebaAmeba 20th Anniv.ホームピグアメブロ
芸能人ブログ人気ブログ
新規登録
ログイン

サイバーエージェント 公式エンジニアブログ

CyberAgent Engineer Blog.

オンラインで安全にスキーマ変更可能なpt-online-schema-change

こんにちは、技術本部の鬼海雄太(@fat47)です。

Amebaのソーシャルゲーム全般のインフラを担当しつつ賃貸マンションの間取りを眺めたり、
戸境壁の工法による防音性の違いについて日夜研究しています。
いつかD値が60以上のマンションに住みたいです。

さて、今回はWebサービスを運用しているとたびたび発生する
「新機能開発の為にINDEX追加やカラム追加を行いたい」
という課題を解決する方法をご紹介します。
数年前まではそういった変更の際は、ゲームをメンテナンス状態にしてから
スキーマ変更を行っていました。
しかしゲームをメンテナンス状態にする事は、各方面への調整や利用者への影響が大きいため
気軽にできることではありませんでした。

オンラインでスキーマ変更

そこで私達のチームではオンラインでスキーマ変更が可能なpt-online-schema-change(pt-osc)を導入することにしました。
pt-online-schema-changeとは「Percona-Toolkit」に同梱されているツールで、

通常はロックが掛かってしまうALTER TABLEクエリをロックなしで実行することが可能になります。
MySQL5.6からは無停止でINDEX追加やカラム追加ができるオンラインDDLが実装されましたが、
運用しているゲームのデータベースはMySQL5.5の為、このツールによりスキーマ変更を実現させています。


pt-online-schema-changeの仕組み

下記のような順番でスキーマ変更が行われています。

1.対象テーブルAと同じスキーマ構造をした作業用テーブルBを作成

2.作業用テーブルBに変更するALTER文を適用


3.トリガーを3つ作成し、対象テーブルAへの挿入・削除・更新が作業用テーブルBに反映されるようにする


4.対象テーブルAから作業用テーブルBへレコードをコピー


5.RENAMEして対象テーブルAと作業用テーブルBを入れ替える


6.入れ替え後の古いテーブルAとトリガー3つを削除する



実際の利用例

インストールは簡単です（例：CentOSに導入する場合）
# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
# yum install percona-toolkit

あとは実行するだけです 
# pt-online-schema-change --execute --alter="ADD INDEX idx_hogehoge(hoge_id)" h=localhost,D=DB_NAME,t=TABLE_NAME,u=root
オプション名動作内容
--alterスキーマ変更を行うクエリを指定
h接続先
DDBスキーマ名を指定
tテーブル名を指定
u実行MySQLユーザを指定

pt-online-schema-changeを慎重につかうオプション

動作の仕組み上、テーブルのコピーやトリガーの処理が走るため、実行中は普段より高負荷になりやすいです。
そのためpt-online-schema-changeには本番環境へ悪影響が出ないように、リソース状況をチェックしながら動作を制御できる便利なオプションが存在しています。

オプション名デフォルト設定動作内容
--max-load25スレッド指定スレッド数を超えた場合、処理を一時停止
--critical-load50スレッド指定スレッド数を超えた場合、処理を終了する
--check-slave-lagなし全スレーブのレプリ遅延チェック（実行ユーザはMaster,Slaveで同じ権限が必要）
--max-lag1秒指定秒数のレプリ遅延が発生すると処理を一時停止する
--check-interval1秒--max-lagをチェックする間隔
--dry-runなし実際には処理が行われないドライラン確認用オプション
--executeなし処理の実行を行うオプション
--nodrop-old-tableyesスキーマ変更前の旧テーブルのDROPを行わないオプション。確認で残しておきたい時に
--set-varsなしMySQLの変数を渡すオプション。レプリケーションさせない時にset-vars="sql_log_bin=0"等

過去の失敗事例

このように色々な機能がある便利なツールですが、
運用で利用できなかったパターンや事故が発生したこともあります。
あまりこのような事例がネットの記事になかったのでいくつかご紹介したいと思います。

事例1：外部キー制約で実行失敗
発生環境
pt-online-schema-change 2.2.14
OS CentOS6.2
MySQL 5.5.34

検証環境でpt-online-schema-changeを実行
 
# pt-online-schema-change --execute --alter="ADD INDEX idx_hoge(id_hoge)" h=localhost,D=d1,t=t_1,u=root 

実行時のエラー文
 
You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool's documentation carefully.

--alter-foreign-keys-methodというオプションで外部キー制約の動作を制御できるようです。
https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html#cmdoption-pt-online-schema-change--alter-foreign-keys-method

auto　

自動判別するモード。rebuild_constraintsが使えれば使い、使えなかったらdrop_swapを使う

rebuild_constraints　

新しいテーブルを参照する外部キー制約を削除してから再追加する。高速

drop_swap

外部キーチェックを無効にする（FOREIGN_KEY_CHECKS = 0）、新しいテーブルを作成して元のテーブルを削除して入れ替える。

この方法は高速でブロックしないが、元のテーブルを削除し一時テーブルの名前を変更する間の時間はテーブルは存在せず、それに対するクエリはエラーになる。

このオプションでテストしてみます

pt-online-schema-change --execute --alter-foreign-keys-method auto --alter="ADD INDEX idx_hoge(id_hoge)" h=localhost,D=d1,t=t1,u=root
実行結果

実行したマスターDBサーバで良さそうな感じの実行ログが出てご満悦。
でしたが、スレーブのステータスを確認してみるとレプリケーションが停止していました。
Last_SQL_Error: Error 'Cannot add or update a child row: a foreign key constraint fails *****

事例にもある通り、サービス稼働時のdrop_swapは難しいかもしれません。
事例2：複合主キーを持つ巨大テーブルで実行失敗
 発生環境
pt-online-schema-change 2.2.13
OS CentOS6.5
MySQL 5.5.34

実行コマンド 
# pt-online-schema-change --execute --alter="ADD INDEX idx_hoge_event(hoge_id, hoge_type, fuga_id)" h=localhost,D=d1,t=t1,u=root

実行エラー文 
2015-03-28T00:54:36 Error copying rows from `d1`.`t1` to `d1`.`_t1_new`:
2015-03-28T00:54:36 Error copying rows at chunk 1 of d1.t1 because MySQL used only 12 bytes of the PRIMARY index instead of 20.  See the --[no]check-plan documentation for more information.
解決策はテーブル設計を変更して2列までの複合主キーにするしかなさそうです。
PT3.0では解決してくれるのでしょうか？
Chunking handles multi-column indexes poorly
事例3：--set-vars="sql_log_bin=0"が無視されてエラー
発生環境
pt-online-schema-change 2.2.7-1
OS CentOS6.2
MySQL 5.5.24

pt-online-schema-changeでは普通に実行してもレプリ遅延は発生しづらい作りになっているのですが、
より遅延の発生をなくすために、sql_log_bin=0（binlogを出さないオプション）をつけて実行する運用をしている事がありました。
マスター、スレーブ構成のDBで、先にスレーブ全台でonline-schema-changeでカラム追加してから、
最後にマスターでsql_log_bin=0で実行してカラム追加を行う方法です。
※Rolling Schema Upgrade（RSU)というかっこいい呼び方もあるようです。
この方法でカラム追加を行ったところ、マスターでpt-osc実行後にスレーブ全台のレプリケーションがエラーで停止しました。 
Last_SQL_Error: Error executing row event: 'Table 'hoge._fuga_table_new' doesn't exist'
先程説明したpt-oscの仕組みの中で
1.対象テーブルAと同じスキーマ構造をした作業用テーブルBを作成
2.作業用テーブルBに変更するALTER文を適用
3.トリガーを3つ作成し、対象テーブルAへの挿入・削除・更新が作業用テーブルBに反映されるようにする
という動きがあります。

本来はpt-oscのオプションでsql_log_bin=0をつけているのでこれらのクエリはスレーブ側には飛ばないはずです。
しかし、仕組みの項目(3)のトリガー作成だけがスレーブ側で実行され、
スレーブ側には既に存在しない「作業用テーブルB」への更新がトリガーで実行されてしまい
エラーになってレプリケーションが停止しました。
このエラーが発生する状況を調査したのですが、最終的にこれだという結論に辿り着くことはできませんでした。
検証内容結果
OSやMySQL、pt-oscのバージョン変更して検証エラー再現
同じテーブル構造でレコード数を少ない状態で検証エラーなし
別のテーブルでレコード数を数億件追加して検証エラーなし

もし今後このRolling Schema Upgradeをする運用を続けるのであれば、
仕組みの項目(1)の作業用テーブルBを事前にスレーブ側に作成しておくことでエラーの回避をすることが可能です。
カラム追加するテーブル名が「user_account」の場合、「_user_account_new」という作業用テーブルを作成しておくことで、
トリガーがもし作成されてレプリケーションの停止は発生しなくなります。
事例4：テーブルのCOMMENTが日本語の場合すべて文字化けする
 発生環境
pt-online-schema-change 2.2.14
OS CentOS6.2
MySQL 5.5.34

このようなテーブル構造があるとします。

pt-oscを実行します 
# pt-online-schema-change --execute --alter="ADD COLUMN hoge_test int NULL DEFAULT NULL COMMENT 'テストだよ' AFTER from_days" h=localhost,D=fuga,t=m_hoge,u=root
日本語がすべて文字化けしてしまいました

--charsetオプションでutf8を指定してみます。MySQLのSET NAMESとperlの標準出力を制御してくれます。 
pt-online-schema-change --execute --charset=utf8　--alter="ADD COLUMN hoge_test int NULL DEFAULT NULL COMMENT 'テストだよ' AFTER from_days" h=localhost,D=fuga,t=m_hoge,u=root
無事文字化けしなくなりました


事例5：Replicate_Ignore_Tableを使っているスレーブでレプリケーション遅延が発生する
 発生環境
pt-online-schema-change 2.2.5
OS CentOS6.2
MySQL 5.5.24

pt-oscをマスターで実行した際に、スレーブの設定でReplicate_Ignore_Tableで指定されているテーブルに
スキーマ変更をかけた場合、 本来ならばスレーブでは無視されるはずのクエリが実行されて、
作業用テーブルが作成されてしまいデータのコピーが始まります。
その間レプリケーションが遅延し続けてしまいます。
Replicate_Do_Tableを利用しているサーバでは発生しませんでした。

設定例
スレーブ名レプリケーション設定
スレーブAReplicate_Ignore_Table: Table_A
スレーブBReplicate_Do_Table: Table_B
スレーブCレプリケーションするテーブル指定なし

実行例 
# pt-online-schema-change --execute --alter="ADD INDEX idx_hogehoge(hoge_id)" h=localhost,D=hoge,t=Table_A,u=root

スレーブA 
[root@スレーブA ~]# ls -lh /var/lib/mysql/hoge/*.ibd
-rw-rw---- 1 mysql mysql 21G 2月 18 11:02 2016 /var/lib/mysql/hoge/_Table_A_new.ibd
-rw-rw---- 1 mysql mysql 42G 2月 18 11:05 2016 /var/lib/mysql/hoge/Hoge_Table.ibd
-rw-rw---- 1 mysql mysql 11G 2月 17 17:30 2016 /var/lib/mysql/hoge/Table_B.ibd

スレーブB 
[root@スレーブB ~]# ls -lh /var/lib/mysql/hoge/*.ibd
-rw-rw---- 1 mysql mysql 11G 2月 17 17:30 2016 /var/lib/mysql/hoge/Table_B.ibd

スレーブC 
[root@スレーブB ~]# ls -lh /var/lib/mysql/hoge/*.ibd
-rw-rw---- 1 mysql mysql 21G 2月 18 11:02 2016 /var/lib/mysql/hoge/_Table_A_new.ibd
-rw-rw---- 1 mysql mysql 59G 2月 18 11:02 2016 /var/lib/mysql/hoge/Table_A.ibd
-rw-rw---- 1 mysql mysql 42G 2月 18 11:05 2016 /var/lib/mysql/hoge/Hoge_Table.ibd
-rw-rw---- 1 mysql mysql 11G 2月 17 17:30 2016 /var/lib/mysql/hoge/Table_B.ibd
スレーブAではTable_A以外のテーブルをレプリケーションするように設定されていますが、
_Table_A_newというTable_Aの作業用テーブルが作成されてしまい全力でデータコピーが走っています。
この間スレーブAではずっとレプリケーション遅延が発生してしまいました。
これ以降の運用では、テーブル指定のレプリケーションをしている環境においては
Rolling Schema Upgrade方式でスキーマ変更をかけることにしました。

まとめ

pt-online-schema-changeはかゆいところに手が届く便利ツール！
銀の弾丸ではないので入念な事前検証と動作確認を！