オンライン DDL を期待して ALTER 文を実行したら障害になりかけた話

こんにちは。ソフトウェアエンジニアの坂井 (@manabusakai) です。

カミナシではマルチプロダクト化に向けて、認証・認可の切り出しを進めています。その対応を進める中で、既存テーブルへのカラム追加が必要になりました。

先日、そのリリースのために本番データベースにマイグレーションの ALTER 文を実行したところ、クエリが詰まって危うく障害になるところでした（幸いすぐにキャンセルして事なきを得ました）。

原因を調べたところ、オンライン DDL は複数の条件が関係することがわかりました。オンライン DDL に対する知識不足と事前検証の甘さゆえのミスでしたが、結果的には良い学びが得られました。

カミナシのバリューのひとつである「全開オープン」の気持ちで、事の顛末やそこから得た学びを公開します。

なお、今回の話は MySQL 5.7 互換の Amazon Aurora MySQL 2 で確認していますが、基本的な考え方は MySQL 8.0 以降でも同様です。

オンライン DDL を期待した ALTER 文が詰まる

MySQL 5.6 からオンライン DDL が導入されました。その名の通り、インデックスやカラムの追加・変更がオンラインで実行でき、サービスを稼働させながらスキーマ定義を変更しやすくなりました。

どんな ALTER 文でも OK というわけではなく、内容によって細かな条件があります。オンライン DDL の条件を調べるには、公式ドキュメントのサポート状況の表を確認します。

MySQL :: MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operations

今回は users テーブルに次のような ALTER 文で既存の bar カラムの直後に foo という新しいカラムを追加しようとしました（カラム名はダミーです）。

ALTER TABLE users ADD COLUMN foo VARCHAR(36) DEFAULT NULL AFTER bar;

しかし、実行した途端に users テーブルへのクエリが詰まり始めました。オンライン DDL で実行されることを期待していたため、何かがおかしいと気づきすぐにキャンセルしました。

先ほどのサポート状況の表を確認すると、"Adding a column" は "In Place" と "Permits Concurrent DML" が Yes のためオンライン DDL が使えるはずです。

最初は AFTER でカラム位置を指定していることが原因かと疑いましたが、上記のドキュメントにはカラムの順序入れ替えの例が載っています。

複数条件でオンライン DDL が失敗するケース

ローカル環境でシンプルなテーブルを作って試してみても再現しないため、先ほどの ALTER 文に明示的に ALGORITHM=INPLACE と LOCK=NONE を付けて実行してみました。

すると、オンライン DDL で実行できると思っていた ALTER 文がエラーになりました。

mysql> ALTER TABLE users ADD COLUMN foo VARCHAR(36) DEFAULT NULL AFTER bar, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.

このように期待する動作を明示することで、オンライン DDL で実行できない場合はエラーになってくれます。この挙動はドキュメントにも明記されています。

To avoid tying up the server with an ALTER TABLE operation that copies the table, include ALGORITHM=INPLACE. The statement halts immediately if it cannot use the in-place mechanism.

エラーメッセージを読んではじめて気づいたのですが、このテーブルには Generated Column が存在していました。 Generated Column は定義した式に従って値を生成することができるカラムです。

Generated Column の "Modifying VIRTUAL column order" は "In Place" と "Permits Concurrent DML" が No になっています。この例では foo カラムを Generated Column の前に追加しようとしたことで Generated Column の位置がズレることになり、結果的にオンライン DDL では実行できなかったのです。

Operation	In Place	Permits Concurrent DML
Adding a column	Yes	Yes
Modifying VIRTUAL column order	No	No

もともとの ALTER 文は ALGORITHM 句と LOCK 句を付けていなかったため、MySQL 側で ALGORITHM=COPY が選択されテーブルコピーの動作になりました。テーブルコピーで実行されるとテーブルへの更新がブロックされてしまいます。

おそらく、本番環境では次のようなことが起こっていたと推測しています。

users テーブルは頻繁に参照される
ALTER 文が ALGORITHM=COPY で実行された
他のセッションのトランザクションによって users テーブルで metadata lock が起きた（推測）
- このときに performance_schema.metadata_locks テーブルを確認しておくべきでした
ALTER 文が終わらないため連鎖的に後続のセッションが待たされた

今回の件は ALGORITHM 句と LOCK 句を付けてさえいれば防ぐことができました。条件さえ揃っていればオンライン DDL で実行されると過信していましたが、もっと慎重になってドキュメントを確認しておくべきでした。

また、本番環境で実行する前に社内のステージング環境に対して同じ ALTER 文を実行していますが、トラフィック量の差から問題になることはありませんでした。その安心感も自分たちの認識を見誤る原因のひとつになりましたが、冷静になって考えると参考にすべき情報ではありませんでした。