今回はALTER TABLEステートメント実行時の注意点について紹介します。
MySQLを運用していると、サービスの仕様変更や新機能のために既存のテーブルに対してテーブル定義を変更することが多いと思います。その際によく起こる問題や注意点に説明します。
ALTER TABLEステートメント
まずは、簡単にALTER TABLE ステートメントについて紹介します。テーブル定義を変更するにはALTER TABLE
ステートメントを利用します。MySQL 5.
オンラインDDLでは、DDL実行に最小限の時間のロックを取得することで、そのDDLと並列で実行されるDMLの待機も最小限になります。そのため、サービス無停止でALTER TABLE
ステートメントを実行しても問題ない場合もあります。しかし、操作の種類によってはオンラインでの実行ができないケースがあります。MySQLのALTER TABLEステートメントは大きく分けて3つの方式があります。
- インスタント方式
- インプレース方式
- コピー方式
どのような操作がどの方式に当てはまるかは、15.
インスタント方式
インスタント方式はテーブル定義の変更をデータディクショナリのメタデータのみを書き換えるため、テーブルのサイズの大小に関わらず即時に完了します。インスタント方式で動作する操作には以下のようなものがあります。
- カラムの追加
- カラム名の変更
- インデックスの削除
構文としては、ALTER TABLE
ステートメントにALGORITHM=INSTANT
をつけるとインスタント方式で動作します。インスタント方式で実行できない操作の場合はエラーとなります。
補足として、カラムの追加においては最後のカラムに追加する場合は、MySQL 8.AFTER
句やFIRST
句を用いた中間にカラムを追加する場合はインプレース方式で動作していました。MySQL 8.
インプレース方式
インプレース方式はテーブル定義をインプレースで変更します。並列のDMLはロックされず、オンラインでの実行が可能です。テーブルの再構築を伴う操作と伴わない操作がありますが、再構築を伴う操作はテーブルサイズに比例して実行時間は大きくなります。このあと紹介するコピー方式よりも高速で処理を完了します。
インプレース方式で動作する操作は以下のようなものがあります。
- インデックスの追加
- カラムのNULLまたはNOT NULLへの変更
構文としては、ALTER TABLE
ステートメントにALGORITHM=INPLACE
をつけるとインプレース方式で動作します。インプレース方式で実行できない操作の場合はエラーとなります。
コピー方式
コピー方式はテーブル定義の変更をテーブルのデータを読み取り、コピーし再構築します。ALTER TABLEステートメント実行中はそのテーブルをロックします。そのため並列でのDMLは待機されるので、これはオンラインDDLではありません。大きなテーブルに対してステートメントを実行するには、サービスのメンテナンスが必要になるでしょう。
コピー方式で動作する操作は以下のようなものがあります。
- カラムのデータ型変更
- FULLTEXTインデックスの作成
- キャラクターセットの変換
構文としては、ALTER TABLE
ステートメントにALGORITHM=COPY
をつけるとコピー方式で動作します。
ステートメントにALGORITHM
句を付与せずに実行すると、インスタンス方式→インプレース方式→コピー方式の順に、その操作が実行可能なものから選択されます。
ALTER TABLEステートメント実行時の注意点
ALTER TABLEステートメントを実行する前に確認しておくべき注意点があります。
メタデータロック
メタデータロックとはデータベースオブジェクトへの同時アクセスを制御し一貫性を確保するために、オブジェクトのメタデータに対して取得するロックです。詳しくはリファレンスマニュアルの8.
ALTER TABLEステートメントを実行すると、対象のテーブルに排他メタデータロックを取得します。この排他メタデータロックは前述の方式ごとに取得するタイミングとロックの保持期間が異なります。
方式 | ロック取得タイミング | サービスへの影響度 |
---|---|---|
インスタント | データディクショナリの排他メタデータロックのみ取得。ただし、ステートメント操作は即時で完了 | 低 |
インプレース | ステートメント開始時と完了時の短期間だけ排他メタデータロックを取得 | 中 |
コピー | ステートメント実行開始から完了するまで、排他メタデータロックを取得 | 高 |
運用中によく起こる問題としては、他セッションとのメタデータロックの競合があります。コピー方式では
インプレース方式では
そして、ALTER TABLEステートメントがメタデータロック取得に待機することで、別の問題も起こります。
他セッションがトランザクション中に触ったテーブル
よって、ALTER TABLEステートメント実行する前には対象のテーブルに対して実行中のロングトランザクションやクエリがないかを確認してから実行する必要があります。メタデータロックを取得しているセッションを探すには、performance_
.metadata_
テーブルを確認しましょう。
また、レプリカにおいても同様です。レプリカにて対象テーブルへの長時間実行されているクエリがあると待機し、レプリケーション遅延を招く恐れがあります。
ディスク容量
再構築を伴うインプレース方式やコピー方式においては、一時的に対象のテーブルと同じサイズのテーブルを内部で作成することになります。そのため、ディスクの空き領域が対象のテーブルサイズ以上残っていることを確認しましょう。
レプリケーション遅延
インスタント方式や小さなテーブルであれば問題ありませんが、インプレースやコピー方式で大きなテーブルであるとレプリケーション遅延は発生します。サービスに読み取り専用レプリカを利用していて、レプリケーション遅延が許容されないのであればサービス停止が必要になるでしょう。
まとめ
今回はALTER TABLEステートメント実行時の注意点について紹介しました。安全にALTER TABLEステートメントを完了させるために、筆者はこれら注意点を必ず確認するようにしています。また、今回は詳しく触れませんが、インプレース方式やコピー方式によるレプリケーション遅延やテーブルロックを回避したいことがあると思います。その場合はpt-online-schema-changeやgh-ostのようなオンラインマイグレーションツールを利用するといいでしょう。これらのツールを利用する際も、ディスク容量とメタデータロックに関して同じく注意しなければいけません。