MySQL 5.Alter Table
操作中にはそのテーブルへの更新がブロックされ、Alter Table
操作でそのテーブルへの更新を可能するinnodbオンラインDDLが実装されました。
今回はこのオンラインDDLについて紹介いたします。
従来の動作との違い
MySQL 5.Alter Table
操作をしたテーブルの空の複製テーブルを作成、
よって、Alter Table
操作中には対象のテーブルと同等のテーブルができるタイミングがあるためディスク領域を多く使用し、 データコピーが発生することでディスクIO負荷によるスループットの低下が懸念されますInnoDB Fast Index Creation
機能があればインデックスの作成時に更新はブロックされますがテーブルコピーされないように最適化されています)。
オンラインDDL操作はインプレース方式で動作します。Alter Table
操作をしたテーブルをコピーすることなく一時領域を使用することで完了します。ディスク領域の使用を抑え、
オンラインDDLが可能な操作
オンラインDDLはすべてのAlter Table
操作で有効ではありません。
可能な操作はマニュアルのDDL 操作のオンラインステータスのサマリーで確認できます。マニュアルの
がはい、
がはいの操作に対してオンラインDDLが可能です。このインプレース
はインプレース方式を意味し、並列 DML
はAlter Table
操作中の更新を意味します。また、
そして、Alter Table
構文にオンラインDDL操作を制御するためのLOCK
とALGORITHM
句が追加されました。
LOCK
句はテーブルへのクエリと並列DML操作のレベルを制御可能です。
オプション | 説明 |
---|---|
NONE | クエリーと並列DML操作を許可 |
SHARED | クエリーを許可・ |
EXCLUSIVE | クエリーと並列DML操作をブロック |
DEFAULT | 使用可能なもっとも低いレベルのロックを使用 |
LOCK句省略 | DEFAULTと同じ |
ALGORITHM
句はインプレース方式かテーブルコピー方式の制御が可能です。
オプション | 説明 |
---|---|
INPLACE | インプレース方式 |
COPY | テーブルコピー方式 |
DEFAULT | インプレース方式を優先 |
ALGORITHM句省略 | DEFAULTと同じ |
LOCK
とALGORITHM
句を省略した操作は、
LOCK
の場合NONE
→SHARED
→EXCLUSIVE
の順に評価されます。ALGORITHM
句の場合INPLACE
→COPY
の順に評価されます。
ここで気をつけておきたいことは、カラムのデータ型を変更する
などのインプレース方式と並列DMLが許可されていない操作に対してオンラインDDLが有効だろうと思い込み、
対策として、ALGORITHM=INPLACE
とLOCK=NONE
を記述するとエラーとなり実行されません。オンラインDDLが有効かどうか不安な場合は、LOCK
とALGORITHM
句を記述したほうが良いでしょう。
調整が必要なパラメータ
オンラインDDLはいくつか調整すべきパラメータに注意しなくてはいけません。
- innodb_
sort_ buffer_ size - デフォルト:1MB
- オンラインDDLでのInnoDBインデックスの作成時に、
データを格納するソートバッファーのサイズ - インデックスの作成が完了すると、
バッファーの割り当てが解除 - 並列DMLを記録するために、
一時ログファイルが拡張される量を制御
- innodb_
online_ alter_ log_ max_ size - デフォルト : 128MB
- オンラインDDL操作時に並列DML操作のデータが格納する一時ログファイルのサイズ
- innodb_
sort_ buffer_ sizeの値で必要になったときに、 最大で innodb_ online_ alter_ log_ max_ sizeで指定された最大値まで拡張
ここで注意すべきことは以下の通りです。
innodb_
はサイズが大きければインデックスの作成は早くなりますが、sort_ buffer_ size 1つのInnoDBインデックスの作成に対してメモリーを確保するので、 同時に複数インデックスの作成を行うとメモリーを大量に消費します。 - 更新の多いテーブルの場合、
DDL操作中に innodb_
を超えてしまうとエラーとなります。 コミットされていない並列DML操作がすべてロールバックされます。また、online_ alter_ log_ max_ size サイズを大きく設定すると実行できる並列DML操作は多くなりますが、 DDL操作が終了時にここに格納されたデータのマージに時間がかかりそのマージ中はテーブルロックが発生します。
オンラインDDLとレプリケーション
マスター/
そのため、
レプリケーションを遅延させない方法
レプリケーションを遅延させない方法として、
SET sql_
log_ bin= 0; ALGORITHM=INPLACE
とLOCK=NONE
で実行できるAlter Table
操作
SET sql_
することでそのセッションの操作はバイナリログに出力しないように設定します。その後、Alter Table
操作をすることでその操作はバイナリログに出力されていないのでスレーブへレプリケーションされません。 これを各スレーブで順次適用して、Alter Table
操作は可能です。
ただし、
- 処理がバイナリログに残らないためリカバリできません。実行後はフルバックアップを取得することを推奨します。
- この手段はインデックスの追加/削除に対してのみ有効です。カラムの増減やデータ型の変更などは一時的にマスターとスレーブ間で定義の差異が発生するため操作内容によってはレプリケーションがエラーにより停止する可能性があるためです。
まとめ
MySQL 5.
これを使用すれば本番環境でのインデックスの追加が容易に行うことができます。繰り返しになりますが、Alter Table
操作で有効ではないので、