データ件数が大量に(レコード数100万以上とか)ある場合、ALTER TABLE時にテーブルの書き込みロックが無視できないレベル(時間)で発生します。
テーブルの書き込みロックについて
MySQL5.5以前: ALTER TABLE時に発生
MySQL5.6以降: ALTER TABLEでカラムのデータ型変更、全文検索用インデックスの追加、プライマリキーの削除、文字コードの変換/指定 を行った時に発生
参考: http://takatoshiono.hatenablog.com/entry/2015/03/11/013316
pt-online-schema-changeを使用することで、テーブルロックなしでのALTER TABLEができます。
pt-online-schema-changeのインストール方法(Mac homebrew)
$ brew install percona-toolkit
※pt-online-schema-changeはpercona-toolkitに含まれるコマンドです
RDSでの実行方法
パラメータグループ変更
RDSのパラメータグループにlog_bin_trust_function_creators=1 を追加(既に設定されている場合は不要)
※このパラメータはApply TypeがDynamicなので、設定変更後のRDSの再起動は不要です。コマンド実行(以下、コマンド例)
$ pt-online-schema-change --alter='CHANGE COLUMN fooColumn fooColumn BIGINT NOT NULL DEFAULT 0' h=xxxxx.xxxxxxxxxx.ap-northeast-1.rds.amazonaws.com,P=3306,D=foodb,t=footable,u=root,p=************ --recursion-method processlist --dry-run
※RDSの場合、--recursion-method processlistは必須らしい
問題なければ--dry-runを--executeに変更して実行
参考: http://blog.livedoor.jp/sasata299/tag/pt-online-schema-change
ローカルから実行したいが直接RDSに接続できない場合
RDSがローカル環境から接続できない場合は踏み台サーバを経由してMySQLをトンネリングする。
$ ssh -f -N -L 13306:xxxxx.xxxxxxxxxx.ap-northeast-1.rds.amazonaws.com:3306 [踏み台サーバのログインユーザ名]@[踏み台サーバのIPアドレス]
$ pt-online-schema-change --alter='CHANGE COLUMN fooColumn fooColumn BIGINT NOT NULL DEFAULT 0' h=127.0.0.1,P=13306,D=foodb,t=footable,u=root,p=************ --recursion-method processlist --dry-run
※事前にssh-addで証明書が使える状態にして踏み台サーバにSSHアクセスできるようにしておくこと
素のALTER TABLEとの比較
条件
| 項目 | 値 |
|---|---|
| MySQL version | 5.6.21 |
| Instance Class | db.m3.2xlarge |
| Storage Type | General Purpose(SSD) |
| IOPS | disabled |
| Storage | 200GB |
| レコード数 | 600万 |
| カラム数 | 20 |
| バースト | あり |
結果
| 項目 | 素のALTER TABLE | pt-online-schema-change |
|---|---|---|
| 実行時間 | 450秒 | 323秒 |
| Write IOPS最大値 | 3000 | 2500 |
| Queue Depth | 150 | 15 |
| Write Latency | 200〜400ms | 8ms |
| Read Latency | 100ms | 10ms |
pt-online-schema-changeを使用したほうが負荷は低くなるので、MySQL5.6でテーブルロックしないALTER TABLEを実行する場面でも有効だと思われます。