オンラインDDLについてのメモ

オンラインDDLとは

こちらの説明がわかりやすかった。

• インデックスの作成などの DDL を実行している最中に INSERT , UPDATEなどの DML が実行できる機能

  • DDLの種類によって、オンラインDDLが使える場合と使えない場合がある

• MySQLだと5.6以降で使える

• ALTER文の実行オプション

  • LOCK=NONE : read / write の両方を許可。
    • LOCK=SHARED : readのみが許可。
  • ALGORITHM=INPLACE : 既存のテーブルをそのまま使う（コピーはしない）。
    • ALGORITHM=COPY : 従来の方法。テーブル新しく作り直してデータをコピー。
    • ALGORITHM=INSTANT : Mysql8.0.12から使えるようになった。
      • INPLACEよりもより高速で、負荷も小さい。
      • INPLACEよりも使える範囲が狭い。
        https://blog.s-style.co.jp/2018/09/2525/
  • つまり、LOCK=NONE、ALGORITHM=INPLACE（またはINSTANT) で実行すると、ALTER文実行中もINSERT , UPDATEなどの DML が実行できる

いつ使えるか

MySQLの公式ドキュメントに説明があった。

よく使いそうな部分をメモしておく。

index

基本的にindexの追加や削除はオンライン（INPLACE) で実行できるよう。（FULLTEXTインデックスだけ注意が必要。事前にカラムの準備が必要なよう。）INSTANTでは実行できない。

表 15.16 インデックス操作のオンライン DDL サポートより

※セカンダリインデックス：プライマリーキー以外のindexのこと

カラム追加・削除

カラムの削除・追加ともにオンライン（INPLACE) で実行できる。ただ、INPLACEではテーブルの全コピーではないものの、再構築が行われるよう。AUTO_INCREMENT カラムの追加では少なくともALGORITHM=INPLACE, LOCK=SHARED が必要（＝書き込みはできない）。

カラム追加はINSTANTでできる場合もある。主に以下が条件（全ての条件は公式ドキュメントをご覧ください。）

• ALGORITHM=INSTANT が使えない他のALTER TABLEと一緒に実行しない
  • NG例(DROPと組み合わせる）

  ALTER TABLE users
  ADD COLUMN nickname VARCHAR(50),
  DROP COLUMN old_column;
  

• 最後のカラムとして追加する（位置指定は負荷）
• ROW_FORMAT=COMPRESSED のテーブルではない
  • ROW_FORMAT はテーブル内の行データやページ（データを保存する単位）を圧縮して保存する形式
  • FULLTEXT インデックスを含まないテーブル

カラムの削除もINPLACEでできるが、データの大幅再編成が行われ、コストが大きい。

表 15.18 カラム操作のオンライン DDL サポートより

メタデータロック

オンラインDDLとして実行した場合も、全くロックが行われないわけではないらしい。
以下がわかりやすい（そしておそろしい。）

オンラインDDLの開始前、完了前にそれぞれ短時間ではあるもののテーブルのメタデータに関する排他> 的アクセスが必要となります。
つまり、オンラインDDLの開始前と完了前にDDLの対象テーブルに実行中のトランザクションがあった> 場合、そのトランザクションがコミットまたはロールバックするまで待機する必要があります。

ロングトランザクションが発生しがちなことが判明していたり、非常に頻繁に参照が行われるテーブルの場合は、メンテナンスタイムまたは参照の少ない夜間などに変更した方が無難なのかもしれない。

おまけ

CakePHPを使っている場合、テーブル定義（カラム名や型）をキャッシュしている。カラム追加や削除をしても、このキャッシュが残っているとCakePHPの動作に反映されなかったり、消したはずのカラムを探してエラーになった。このキャッシュもメンテナンスタイムや夜間にDDL実行が必要かの判断材料になりそう。

こちらの説明がわかりやすかった。

find() などカラム名を指定していないとき、かつ本番環境（debugモードが0の環境)で発生するよう。

おわり

これまでテーブル構造の変更は基本的にメンテナンスタイムに行っていたが、現職でオンラインDDLで実行できることがあると知った。メンテナンスタイムはユーザーさんに不便をかけてしまうし、夜間対応もしなくてよければしないにこしたことはないので、知ることができてよかった。一方で、頻回参照のテーブルやCakePHPのキャッシュなどがあり、常にメンテナンスタイムをとらなくていいわけではないことは注意したい。