オンライン DDL は、MySQL 操作のいくつかの側面を改善します:
DDL 操作の進行中にテーブルに対するクエリーおよび DML 操作を続行できるため、テーブルにアクセスするアプリケーションの応答性が向上します。 ロックを削減し、MySQL サーバーリソースを待機すると、DDL 操作に関係しない操作でもスケーラビリティが向上します。
即時操作では、データディクショナリのメタデータのみが変更されます。 テーブルに対するメタデータロックは行われず、テーブルデータは影響を受けず、操作が即時に行われます。 同時 DML は影響を受けません。
オンライン操作により、テーブルコピー方法に関連付けられたディスク I/O および CPU サイクルが回避され、データベースの全体的な負荷が最小限に抑えられます。 負荷を最小限に抑えると、DDL 操作中に良好なパフォーマンスと高スループットを維持できます。
オンライン操作は、テーブルコピー操作より少ないデータをバッファープールに読み取り、頻繁にアクセスされるデータのメモリーからのパージを削減します。 頻繁にアクセスされるデータをパージすると、DDL 操作後に一時的なパフォーマンスが低下する可能性があります。
デフォルトでは、MySQL は DDL 操作中にできるだけ少ないロックを使用します。 必要に応じて、LOCK 句をインプレース操作および一部のコピー操作に指定して、より限定的なロックを強制できます。 LOCK 句で、特定の DDL 操作に許可されている制限レベルより低いロックが指定されている場合、ステートメントはエラーで失敗します。 LOCK 句については、次に、最も制限の少ないものから順に説明します:
-
LOCK=NONE:同時クエリーおよび DML を許可します。
たとえば、長い DDL 操作中にテーブルを使用できないようにするには、顧客のサインアップまたは購入を含むテーブルに対してこの句を使用します。
-
LOCK=SHARED:同時クエリーは許可されますが、DML はブロックされます。
たとえば、データウェアハウステーブルでこの句を使用すると、DDL 操作が終了するまでデータロード操作を遅延できますが、クエリーを長期間遅延することはできません。
-
LOCK=DEFAULT:可能なかぎり多くの同時実行性を許可します (同時クエリーまたは DML、あるいはその両方)。
LOCK句を省略することは、LOCK=DEFAULTを指定することと同じです。DDL ステートメントのデフォルトのロックレベルでテーブルの可用性の問題が発生することが予想されない場合は、この句を使用します。
-
LOCK=EXCLUSIVE:同時クエリーおよび DML をブロックします。
この句は、主な懸念事項が可能なかぎり短い時間で DDL 操作を終了することで、同時クエリーおよび DML アクセスが不要な場合に使用します。 また、予期しないテーブルアクセスを避けるために、サーバーがアイドル状態であると想定される場合にも、この句を使用できます。
オンライン DDL 操作は、次の 3 つのフェーズを持つものとして表示できます:
-
フェーズ 1: 初期化
初期化フェーズでは、サーバーは、ストレージエンジンの機能、ステートメントで指定された操作、およびユーザー指定の
ALGORITHMオプションとLOCKオプションを考慮して、操作中に許可される同時実行性を決定します。 このフェーズでは、現在のテーブル定義を保護するために、アップグレード可能な共有メタデータロックが取得されます。 -
フェーズ 2: Execution
このフェーズでは、ステートメントが準備されて実行されます。 メタデータロックが排他的にアップグレードされるかどうかは、初期化フェーズで評価される要因によって異なります。 排他的メタデータロックが必要な場合は、ステートメントの準備中にのみ簡単に取得されます。
-
フェーズ 3: テーブル定義のコミット
テーブル定義のコミットフェーズでは、メタデータロックが排他的にアップグレードされ、古いテーブル定義が削除されて新しい定義がコミットされます。 付与されると、排他的メタデータロックの期間が短くなります。
前述の排他的メタデータロック要件のため、オンライン DDL 操作では、テーブルのメタデータロックを保持する同時トランザクションがコミットまたはロールバックされるまで待機する必要がある場合があります。 DDL 操作の前または実行中に開始されたトランザクションは、変更されるテーブルのメタデータロックを保持できます。 長時間実行中または非アクティブなトランザクションの場合、オンライン DDL 操作は排他的メタデータロックの待機中にタイムアウトすることがあります。 また、オンライン DDL 操作によってリクエストされた保留中の排他的メタデータロックによって、テーブルの後続のトランザクションがブロックされます。
次の例は、排他的メタデータロックを待機しているオンライン DDL 操作と、保留中のメタデータロックがテーブルの後続のトランザクションをブロックする方法を示しています。
セッション 1:
mysql> CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
mysql> START TRANSACTION;
mysql> SELECT * FROM t1;
セッション 1 の SELECT ステートメントは、テーブル t1 で共有メタデータロックを取得します。
セッション 2:
mysql> ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE;テーブル定義の変更をコミットするためにテーブル t1 の排他的メタデータロックを必要とするセッション 2 のオンライン DDL 操作は、セッション 1 のトランザクションがコミットまたはロールバックされるまで待機する必要があります。
セッション 3:
mysql> SELECT * FROM t1;
セッション 3 で発行された SELECT ステートメントは、セッション 2 の ALTER TABLE 操作によってリクエストされた排他的メタデータロックが付与されるのを待機してブロックされます。
SHOW FULL PROCESSLIST を使用して、トランザクションがメタデータロックを待機しているかどうかを確認できます。
mysql> SHOW FULL PROCESSLIST\G
...
*************************** 2. row ***************************
Id: 5
User: root
Host: localhost
db: test
Command: Query
Time: 44
State: Waiting for table metadata lock
Info: ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE
...
*************************** 4. row ***************************
Id: 7
User: root
Host: localhost
db: test
Command: Query
Time: 5
State: Waiting for table metadata lock
Info: SELECT * FROM t1
4 rows in set (0.00 sec)
メタデータロック情報は、セッション間のメタデータロックの依存関係、セッションが待機しているメタデータロック、および現在メタデータロックを保持しているセッションに関する情報を提供するパフォーマンススキーマ metadata_locks テーブルを介しても公開されます。 詳細は、セクション27.12.13.3「metadata_locks テーブル」を参照してください。
DDL 操作のパフォーマンスは、操作が即時に実行されるかどうか、インプレースで実行されるかどうか、およびテーブルを再構築するかどうかによって主に決定されます。
DDL 操作の相対パフォーマンスを評価するには、ALGORITHM=INSTANT、ALGORITHM=INPLACE および ALGORITHM=COPY を使用して結果を比較します。 old_alter_table を有効にしてステートメントを実行し、ALGORITHM=COPY を強制的に使用することもできます。
テーブルデータを変更する DDL 操作の場合は、コマンドの終了後に表示される「「影響を受ける行」」値を参照して、DDL 操作で変更を実行するか、テーブルのコピーを実行するかを決定できます。 例:
-
カラムのデフォルト値の変更 (高速、テーブルデータへの影響なし):
Query OK, 0 rows affected (0.07 sec) -
インデックスの追加 (時間はかかりますが、
0 rows affectedはテーブルがコピーされないことを示しています):Query OK, 0 rows affected (21.42 sec) -
カラムのデータ型の変更 (かなりの時間がかかり、テーブルのすべての行を再構築する必要があります):
Query OK, 1671168 rows affected (1 min 35.54 sec)
大規模なテーブルに対して DDL 操作を実行する前に、次のように操作が高速か低速かを確認します:
テーブル構造をクローニングします。
クローンテーブルに少量のデータを移入します。
クローニングされたテーブルで DDL 操作を実行します。
「rows affected」の値が 0 かどうかをチェックします。 ゼロ以外の値は、特別な計画を必要とする可能性があるテーブルデータがコピーされることを意味します。 たとえば、スケジュールされた停止時間中に DDL 操作を実行したり、各レプリカサーバーで一度に 1 つずつ DDL 操作を実行できます。
DDL 操作に関連する MySQL 処理をより深く理解するには、DDL 操作の前後に InnoDB に関連するパフォーマンススキーマおよび INFORMATION_SCHEMA テーブルを調べて、物理読取り、書込み、メモリー割当てなどの数を確認します。
パフォーマンススキーマのステージイベントを使用して、ALTER TABLE の進行状況をモニターできます。 セクション15.16.1「パフォーマンススキーマを使用した InnoDB テーブルの ALTER TABLE の進行状況のモニタリング」を参照してください。
同時 DML 操作によって行われた変更の記録、最後へのそれらの変更の適用に関連する処理作業がいくつかあるため、オンライン DDL 操作は、他のセッションからのテーブルのアクセスをブロックするテーブルコピーメカニズムよりも全体的に時間がかかる可能性があります。 raw パフォーマンスの低下は、そのテーブルを使用するアプリケーションの応答性の向上とバランスがとれています。 テーブル構造を変更する手法を評価する場合は、web ページのロード時間などの要因に基づいて、エンドユーザーがパフォーマンスを認識することを検討してください。