🦔

MysqlのオンラインDDLとメタデータロックを理解した

2024/02/04に公開1件

SQL

tech

概要

mysqlにはご存知の通りオンラインDDLの機能があります。
そのため、indexの追加などを普通にしても問題ないんでしょうと私は思ってました。
しかし、実際はそんなに甘くなさそうだったので、頭を整理するがてら、まとめようと思います。

Online DDLとは

インデックスの作成などの DDL を実行している最中に INSERTなどの DML が実行できるようにするものです。

インデックスの作成の例

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

LOCK 句(ドキュメント引用)
デフォルトでは、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 アクセスが不要な場合に使用します。また、予期しないテーブルアクセスを避けるために、サーバーがアイドル状態であると想定される場合にも、この句を使用できます。

ALGORITHM
- ALGORITHM=INPLACE を指定すると、処理をブロックすることなく in-place での更新を行います。
- ALGORITHM=COPY の場合、ロックしている間に既存テーブルのデータを新しいテーブルにコピーして最後にテーブルを置き換えるという既存の手法と同じ処理を行います。

オンライン DDL およびメタデータロック

オンライン 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 テーブルを介しても公開されます。

※ innodbのログからロックの確認

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2024-02-4 07:02:39 0x50tcfg5760 INNODB MONITOR OUTPUT
...
TRANSACTIONS
------------
Trx id counter 2075
Purge done for trx's n:o < 2073 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281750090301040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281750090300128, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2074, ACTIVE 30 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 277810923264, query id 116 localhost root

簡単にまとめると
テーブルのにカラムを追加した際やindexを追加する際などに確かにオンラインDDLは行われるがメタデータを変更するときはメタデータロックがかかるから、データ数が多いindex追加のようなメタデータの更新に時間のかかるような操作をするとselectでの取得がロックが解除されるまで待機になるので、パフォーマンスが落ちるよということのよう。

Online DDL 操作では、操作の最終段階でテーブル定義を更新する際には常に排他的なメタデータのロックが必要になる。
その結果、テーブルのメタデータ・ロックを保持しているトランザクションが存在すると、Online DDL 操作がブロックされる可能性がある。

Discussion

hiro 2ヶ月前