はじめに
こんにちは、webエンジニアの@an_sonyです。
最近巨大テーブルをalterする機会があり、そのツールとしてpt-online-schema-changeを初めて利用しました。そこで得た知見をまとめてみます。
pt-online-schema-change（以下pt-osc）とは？
主にMySQL向けに作られた運用ツール群perconaの一つです。
テーブルへのread/writeを許容できる状態に保ったままalter操作を実行することを可能にします。
なぜpt-oscが必要か
なにも考えずに通常のalter操作を実行すると、共有ロックを取得するのでinsert/updateができなくなります。alterしようとしているテーブルのレコード数が多い場合、その間書き込みプロセスは待ち状態となり処理が完了しないため、サービスが停止してしまいます。
なお、MySQL5.6からはオンラインDDLができるようになりましたが、全てのalter操作ができるわけではないです。例えばインデックスの追加・削除なら可能ですが、カラムのデータ型の変更は不可です。
pt-oscの仕組み
仕組みについては、分かりやすくまとまっている記事がありますので参照ください。
ざっくり流れを記載すると↓
- 旧テーブルと同じスキーマ構造をした、新テーブルを作る
- 新テーブルにalter tableを適用
- トリガーを3つ作成し、旧テーブルのinsert/update/deleteが新テーブルに反映されるようにする
- 旧テーブルのレコードを新テーブルにコピー
- 新旧テーブルを入れ替え(rename)
- 旧テーブルの削除
- 3で作ったトリガーを削除
実行した時のログ↓
Altering `testdb`.`testtable`...
Creating new table...
Created new table testdb._testtable_new OK.
Altering new table...
Altered `testdb`.`_testtable_new` OK.
2021-2-27T08:00:32 Creating triggers...
2021-2-27T08:00:32 Created triggers OK.
2021-2-27T08:00:32 Copying approximately 10 rows...
2021-2-27T08:00:32 Copied rows OK.
2021-2-27T08:00:32 Analyzing new table...
2021-2-27T08:00:32 Swapping tables...
2021-2-27T08:00:32 Swapped original and new tables OK.
2021-2-27T08:00:32 Dropping old table...
2021-2-27T08:00:33 Dropped old table `testdb`.`_testtable_old` OK.
2021-2-27T08:00:33 Dropping triggers...
2021-2-27T08:00:33 Dropped triggers OK.
Successfully altered `testdb`.`testtable`.
注意点
実行中リソースへの負荷が高い
実行中はデータコピーやトリガー処理が走るので、ディスクI/O、CPUに負荷がかかります。
pt-oscにはリソース状況をチェックしながら動作を制御できる便利なオプションが存在しています。
例えば --check-slave-lagで全スレーブのレプリケーション遅延をチェックできたり、--max-lagで指定秒数のレプリケーション遅延が発生すると処理を一時停止するといった制御が可能です。
十分なディスク容量が必要
一時的なテーブルコピーをするので、相応（具体的な数値は分かりませんでした）のディスク容量が必要です。
デットロックは発生する
pt-oscは旧テーブルのデータを細切れで新テーブルにコピーするツールなので、コピーした瞬間はロックがかかります。その瞬間でクエリがバッティングしてしまうとデットロックが発生します。
テーブルロックを取得するタイミングがある
トリガー作成/削除、またはrename tableのタイミングでテーブルロックを取得します。その間はselectもできなくなるので、同時アクセスが多い場合は処理が詰まりやすいです。
まとめ
便利なツールではありますが、なんでも解決してくれるわけではないので事前に十分な検証が必要です。
できるだけテーブルにアクセスが少ない時間帯を選んだり、実行中はバッチなどによる大量データ処理（read/write）も避けた方が良いです。また、alterしたいテーブルへのread/write頻度が高かったり、デットロックが発生するとサービスへの影響が大きい場合は、他の方法を検討するしかなさそうです。
参考
https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html
https://ameblo.jp/principia-ca/entry-12129289966.html
https://dev.mysql.com/doc/refman/5.6/ja/innodb-create-index-overview.html
https://nisshiee.org/blog/2018/105/