MySQL 5.5とそれ以前ではAlter Table操作中にはそのテーブルへの更新がブロックされ、多くのセッションが待機してしまうため本番稼働中のMySQLに対しての実行は非常に悩ましいものでした。MySQL 5.6とそれ以降からはアーキテクチャが変更されて、多くのAlter Table操作でそのテーブルへの更新を可能するinnodbオンラインDDLが実装されました。

今回はこのオンラインDDLについて紹介いたします。

従来の動作との違い

MySQL 5.5以前の従来のDDL操作はテーブルコピー方式で動作します。それは、MySQL内部でAlter Table操作をしたテーブルの空の複製テーブルを作成、データをコピー、そして古いテーブルが削除され、その複製したテーブル名を元のテーブル名に変更するという仕組みです。

よって、Alter Table操作中には対象のテーブルと同等のテーブルができるタイミングがあるためディスク領域を多く使用し、 データコピーが発生することでディスクIO負荷によるスループットの低下が懸念されます（MySQL 5.5以前でもInnoDB Fast Index Creation機能があればインデックスの作成時に更新はブロックされますがテーブルコピーされないように最適化されています⁠）⁠。

オンラインDDL操作はインプレース方式で動作します。Alter Table操作をしたテーブルをコピーすることなく一時領域を使用することで完了します。ディスク領域の使用を抑え、ディスクIOの負荷も軽減されスループットが維持されます。

オンラインDDLが可能な操作

オンラインDDLはすべてのAlter Table操作で有効ではありません。

可能な操作はマニュアルのDDL 操作のオンラインステータスのサマリーで確認できます。マニュアルの「インプレース?」カラムがはい、かつ「並列 DML を許可?」カラムがはいの操作に対してオンラインDDLが可能です。このインプレースはインプレース方式を意味し、並列 DMLはAlter Table操作中の更新を意味します。また、インプレース方式で実施可能だが並列DMLは許可されない操作などあるので、事前の確認は必要です。

そして、Alter Table構文にオンラインDDL操作を制御するためのLOCKとALGORITHM句が追加されました。

LOCK句はテーブルへのクエリと並列DML操作のレベルを制御可能です。

ALGORITHM句はインプレース方式かテーブルコピー方式の制御が可能です。

LOCKとALGORITHM句を省略した操作は、先ほどのDDL操作のオンラインステータスのサマリーに基づき、以下の順序で評価され、使用可能なオプションで実行されます。

LOCKの場合

NONE → SHARED → EXCLUSIVEの順に評価されます。

ALGORITHM句の場合

INPLACE → COPYの順に評価されます。

ここで気をつけておきたいことは、たとえばカラムのデータ型を変更するなどのインプレース方式と並列DMLが許可されていない操作に対してオンラインDDLが有効だろうと思い込み、それらの句を省略して本番環境に対して実行したためにサービスを停止させてしまうなど、痛い目にあうことがあります。

対策として、期待している動作のALGORITHM=INPLACEとLOCK=NONEを記述するとエラーとなり実行されません。オンラインDDLが有効かどうか不安な場合は、明示的にLOCKとALGORITHM句を記述したほうが良いでしょう。

mysql> desc test;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| text  | int(11)             | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+

mysql> ALTER TABLE test MODIFY text varchar(20), ALGORITHM=INPLACE,LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

調整が必要なパラメータ

オンライン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_online_alter_log_max_sizeを超えてしまうとエラーとなります。 コミットされていない並列DML操作がすべてロールバックされます。また、サイズを大きく設定すると実行できる並列DML操作は多くなりますが、DDL操作が終了時にここに格納されたデータのマージに時間がかかりそのマージ中はテーブルロックが発生します。

オンラインDDLとレプリケーション

マスター／スレーブ構成でオンラインDDLにて並列DMLが可能になったからといって、レプリケーションが遅延せずに実行されるかというとそんなことはありません。なぜなら、マスターで並列スレッドで実行されている処理もレプリケーションスレーブはそれらをcommitされた順にシリアルにがシングルスレッドで実行していくためです。正確にはマルチスレッドで動作する仕組み（MTS）もありますが、これを使用したとしても遅延を回避することは難しいでしょう。

そのため、オンラインDDL操作でもその処理が完了されるまでレプリケーションの遅延は発生します。

レプリケーションを遅延させない方法

レプリケーションを遅延させない方法として、以下手順をローリング実行することで可能です。

1. SET sql_log_bin= 0;
2. ALGORITHM=INPLACEとLOCK=NONEで実行できるAlter Table操作

SET sql_log_bin= 0;することでそのセッションの操作はバイナリログに出力しないように設定します。その後、Alter Table操作をすることでその操作はバイナリログに出力されていないのでスレーブへレプリケーションされません。 これを各スレーブで順次適用して、最後にマスターに適用することでレプリケーションの遅延なくAlter Table操作は可能です。

ただし、この方法はいくつかの制限があります。

• 処理がバイナリログに残らないためリカバリできません。実行後はフルバックアップを取得することを推奨します。
• この手段はインデックスの追加/削除に対してのみ有効です。カラムの増減やデータ型の変更などは一時的にマスターとスレーブ間で定義の差異が発生するため操作内容によってはレプリケーションがエラーにより停止する可能性があるためです。

まとめ

MySQL 5.6とそれ以降で可能となったinnodbオンラインDDLについて紹介しました。

これを使用すれば本番環境でのインデックスの追加が容易に行うことができます。繰り返しになりますが、すべてのAlter Table操作で有効ではないので、実行前にしっかりマニュアルを確認してから実施しましょう。