okumudです。
弊社では AWS Aurora MySQL を利用しており、大量のデータを持つ巨大なテーブルが存在しています。テーブルに列を追加・削除するときは、ALTER TABLE構文を使用しますが、MySQL の ALTER TABLE にはいくつかのアルゴリズムがあり、それぞれ効率やロックの挙動に違いがあります。
本記事では、特にINSTANTアルゴリズムを使った、効率的で高速な列追加・削除の方法をご紹介します。
INSTANT アルゴリズムを使うメリットと具体的な使用方法
INSTANT アルゴリズムは、テーブルのメタデータのみを変更するため、巨大なテーブルに対してもロックが発生せず、非常に高速です。
MySQL 8.0.29以降では、列の途中であっても INSTANT アルゴリズムを使って列の追加や削除が可能になりました。
実際、約7千万件のテーブルに対して、たった 0.7秒 で完了しました👏
これに対して、INPLACE アルゴリズムでは 約1.5時間 かかりました(再構築を伴う場合、テーブルサイズに比例して時間が増えます)。
列追加の例:
ALTER TABLE users
ADD COLUMN followed_at DATETIME NULL AFTER status,
ALGORITHM=INSTANT;
制限事項: 利用可能なバージョン
INSTANT アルゴリズムは、特定のバージョン以降でしか使えません。もし、低いバージョンを使っているのであれば、バージョンアップをしましょう。
| MySQL | Aurora MySQL | INSTANT アルゴリズムでの操作 |
|---|---|---|
| MySQL 5.7.x | Aurora MySQL 2.x | 使用不可 |
| MySQL 8.0.12より前 | - | 使用不可 |
| MySQL 8.0.12以降 | Aurora MySQL 3.01.0以降 | 最終列の追加・削除のみ可能 |
| MySQL 8.0.29以降 | Aurora MySQL 3.05.0以降 | 列途中の追加・削除が可能 |
公式ドキュメントの記述
執筆時点において翻訳(日本語)ドキュメントでは、列の途中で使用できないような記述がありますが、
INSTANT アルゴリズムを使用してカラムを追加する場合は、次の制限が適用されます:
- カラムは、テーブルの最後のカラムとしてのみ追加できます。 他のカラム間の他の位置へのカラムの追加はサポートされていません。
オリジナルのドキュメントでは、バージョンによって列途中でも列の追加・削除が可能と記述があります。
The following limitations apply when the INSTANT algorithm adds a column:
TheINSTANTalgorithm can add a column at any position in the table. Before MySQL 8.0.29, theINSTANTalgorithm could only add a column as the last column of the table.
翻訳では古いことがあるので、オリジナルのドキュメントを参照するようにしましょう。
制限事項: 列追加・削除の上限は64回まで
非常に有用ですが、テーブルの再構築なしだと、列追加・削除は64回までという上限があります。
通常の運用で列の追加・削除は頻繁に発生しないので、十分な回数があると思います。
Oracle社のブログに上限に達した場合の解説がありますので、そちらも参照ください。
他のアプローチでの失敗例
INSTANT アルゴリズムで列追加を行いましたが、その方法に辿り着くまでに試行錯誤を行いましたので、紹介します。
失敗談1: INPLACE アルゴリズムを使う
INPLACE アルゴリズムは、テーブル全体のコピーを作成せずにテーブルを変更するアルゴリズムで、テーブルの読み取り/書き込みロックを最小化できます。
しかし、オンラインDDL (ALGORITHM=INPLACE, LOCK=NONE) でマイグレーションを実行中に、競合(ユニークキー重複)が発生すると、失敗します。
例:
ALTER TABLE users
ADD COLUMN followed_at DATETIME NULL AFTER status,
ADD INDEX index_users_on_site_id_and_followed_at (site_id, followed_at),
ALGORITHM=INPLACE, LOCK=NONE;
> Duplicate entry '....' for key
レコード操作のほとんどがバッチ処理によるものなので、 競合を発生させないようにJOBを実行するタイミングを変更する対策を取りましたが、失敗しました。
大量レコードのあるテーブルは、時間がかかる分重複発生の可能性が上がってしまいます。
実際、約7千万件のテーブルに対して約1.5時間かかり、その間に競合は何度も発生しました。
INSERT を止めることも検討しましたが、作業中はサービス停止に近い状態となるため、この方法は諦めました。
失敗談2: Amazon RDS ブルー/グリーン デプロイのみを使用して列追加を行う→列の途中追加はできない
Amazon RDS ブルー/グリーン デプロイのみでは、末尾への列追加しか対応できませんでした。
詳細の説明は省略しますが、Amazon RDS ブルー/グリーン デプロイは、2つの環境を作成し、切り替えを行うことでダウンタイムを少なくする方法です。ブルー環境が移行元で グリーン環境が移行先です。
ブルー環境からグリーン環境へレプリケーションし、レコードの状態を常に連携した状態で、グリーン環境の列定義を変更していきます。
この方法(Amazon RDS ブルー/グリーン デプロイのみの方法)は末尾に列を追加するパターンでしか使えません。
末尾以外に列を追加すると、次の現象が発生します。
- レプリケーションエラーが発生するため、切り替えができない
-
SHOW SLAVE STATUS;でエラーとなり、 ブルー環境の変更を取り込めない状態となります
-
- レプリケーションエラーは発生しないが、レコードが不正になる
- ブルー環境の変更を取り込みますが、追加した列にも値が入ります
- 追加した列以降の型が一致する場合、この状況となります
- 末尾の列に NOT NULL 制約があっても、デフォルト値が設定され、エラーとなりません
これは MySQL のレプリケーションドキュメントにも記載があります。
両方のバージョンのテーブルに共通するカラムは、ソースとレプリカで同じ順序で定義する必要があります。 (これは、両方のテーブルのカラム数が同じ場合でも当てはまります。)
また、AWSのブルー/グリーン デプロイのベストプラクティスにも言及があります。
ブルー/グリーンデプロイを使用してスキーマの変更を実装する場合は、レプリケーション互換の変更のみを行ってください。
例えば、ブルーデプロイからグリーンデプロイへのレプリケーションを中断することなく、テーブルの最後に新しい列を追加することができます。ただし、列名の変更やテーブル名の変更などのスキーマの変更は、グリーンデプロイへのレプリケーションを中断させます。
まとめ
MySQL 8.0.29以降 ならば、 巨大なテーブルに対しての列追加や列削除の操作が INSTANT アルゴリズムを使って無停止で操作可能です。機能拡張を進めており、他に効率的なテーブル構成の変更方法があればぜひ教えてください。
Discussion