MySQL5.6のオンラインDDLでメタデータロックがかかった話

こんにちは、ラクマの宮崎です。

MySQLのオンラインDDLを実行する際にメタデータロックがかかってしまい困ったので、オンラインDDLとメタデータロックについて調べて手元で試したことをまとめました。

MySQLのバージョンは 5.6、ストレージエンジンは InnoDB です。

この記事の多くは、MySQL 公式ドキュメントの以下のページを参考に書いています。
公式ドキュメント①：14.11.1 オンライン DDL の概要
公式ドキュメント②：14.11.2 オンライン DDL でのパフォーマンスと並列性に関する考慮事項

DDLとは

DDL は Data Definition Language の略で、データ定義言語です。
SELECT などのテーブルのレコードを操作するものではなく、データベース自体を操作するものを指します。
CREATE, ALTER, DROP などが DDL です。

対して、SELECT, UPDATE, DELETE などの、テーブルのレコードを操作するものは DML（データ操作言語、Data Manipulation Language）と呼ばれています。

オンラインDDLとは

MySQL5.6より前のバージョンでは、一部を除いたDDLは、テーブルの全ての行のコピーやDDL実行中のDMLのブロックを必要とするコストの高い操作でした。
そのため、DDLを行うためにはサービスをメンテモードにするなどの対応が必要でした。

これに対し、MySQL5.6で拡張されたオンラインDDLは、テーブル全体のコピーを行わず、実行中のDMLのブロックも必要とせずにDDLを行えるようになりました。
テーブル全体のコピーとDMLブロックが不要なDDLをオンラインDDLと呼んでいますが、「テーブルコピーは不要だがDMLブロックは必要」などといったDDLも一部存在します。

公式ドキュメント① にオンラインDDLについての詳しい説明があります。

オンラインDDLを有効にするために特別なことをする必要はなく、オンラインDDLが可能な場合はオンラインDDLが適用されます。
このことは、MySQL公式ドキュメント 14.11.3 オンライン DDL の SQL 構文 に記載があります。

ただ、全てのDDLがオンラインDDLに対応している訳ではありません。

オンラインDDLに対応しているDDL

公式ドキュメント① に、それぞれのDDLでオンラインDDLのどの操作が可能かが一覧表でまとまっています。

一覧表で「インプレース？」が「はい」になっているものがテーブル全体のコピーが不要なDDL、「並列DMLを許可？」が「はい」になっているものがDDL実行中にDMLの実行が可能なものです。

ざっくりですが、よく行う操作だと カラムのデータ型変更以外はほぼオンラインDDLに対応していると思って良いと思います。

便利な機能として、DDLに「ALGORITHM=INPLACE, LOCK=NONE」というオプションをつけて実行すると、オンラインDDLができない場合に実行せずにエラーを返してくれます。
「ALGORITHM=INPLACE」は、テーブル全体のコピーが必要ない INPLACE 方式でDDLを実行することを指定し、「LOCK=NONE」はDDL中にロックせずに並列DMLを可能にすることを指定します。これらのオプションは、指定した方法でDDLが実行できない場合にエラーにします。
確実にオンラインDDLで実行したい場合は、このオプションをつけてDDLを実行すると良さそうです。
ALGORITHM=INPLACE は公式ドキュメント①、LOCK=NONE は公式ドキュメント② の中で説明されています。

また、DDLを実行した際にテーブル全体のコピーを行ったかどうかを確認できます。
DDL実行のレスポンスが「0 row affected」の場合、テーブル全体のコピーが行われずにDDLが実行されています。
こちらは、公式ドキュメント② で説明されています。

オンラインDDLができないカラムの型変換で、実際にオプションを試してみました。

まずオプションをつけずに実行すると問題なく成功しました。usersテーブルの age カラムを varchar(255) 型に変更しています。

mysql> ALTER TABLE `users` CHANGE `age` `age` varchar(255) DEFAULT NULL;
Query OK, 4800 rows affected, 2 warnings (0.19 sec)
Records: 4800  Duplicates: 0  Warnings: 2

レスポンスが「4800 rows affected」となっているので、テーブル全体のコピーが行われたと分かります。

オプションをつけて実行すると想定通りエラーとなりました。

mysql> ALTER TABLE `users` CHANGE `age` `age` varchar(255) DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

INPLACE 方式だとできないので、テーブル全体のコピーを伴う COPY 方式で実行してね、というエラーが発生しています。

Ruby on RailsのMigrationでオプションをつける方法

私は普段 Ruby on Rails を書いており、Rails の Migration で前述のオプションをつける方法を調べました。

私が調べた限り、execute で任意のDDLを実行する以下の方法で行うしかなさそうでした。この例は、usersテーブルに test というコメントをつけるDDLです。

class Test < ActiveRecord::Migration
  def up
    execute "ALTER TABLE `users` COMMENT 'test', ALGORITHM=INPLACE, LOCK=NONE"
  end
  def down
    execute "ALTER TABLE `users` COMMENT '', ALGORITHM=INPLACE, LOCK=NONE"
  end
end

== 20200706064613 Test: migrating =============================================
-- execute("ALTER TABLE `users` COMMENT 'test', ALGORITHM=INPLACE, LOCK=NONE")
D, [2020-07-06T16:15:50.402667 #2523] DEBUG -- :    (15.1ms)  ALTER TABLE `users` COMMENT 'test', ALGORITHM=INPLACE, LOCK=NONE
   -> 0.0155s
== 20200706064613 Test: migrated (0.0156s) ====================================

execute を使った任意のDDL実行については、こちらの記事を参考にしました。
Execute SQL in Rails migrations. You may come across the following… | by Josua Schmid | Medium

migration時に実行されるSQLを確認する方法は、こちらの記事を参考にしました。
Rails で migrate 時に実行される SQL を確認する - volpe’s diary

オンラインDDLの注意点とWaiting for table metadata lock が発生するケース

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

さらに、オンラインDDLがトランザクションの完了を待機している状態で同じテーブルへDMLを実行すると、DMLの対象レコードがトランザクションの対象レコードと別であってもオンラインDDLと同様に待機状態になってしまいます。

このようなケースで、待機状態になっているDDL、DML が Waiting for table metadata lock の状態になっています。
メタデータ（meta data）とは、カラム名、データベース名などのデータベースについての情報のことです。
トランザクション中のメタデータの変更を防ぐため、トランザクション中はメタデータのロックがかかるようになっており、DDLを実行すると Waiting for table metadata lock となります。

トランザクションが長引いたりすると、Waiting for table metadata lock がたまり続けて障害の引き金になる可能性もあります。

詳細は 公式ドキュメント② と、こちらの公式ドキュメントに記載されています。
8.10.4 メタデータのロック

Waiting for table metadata lock が発生するケースを実際に試してみる

トランザクション中にDMLを行うケース

まずはオンラインDDLを実行しないケースを試してみます。

mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
mysql> update users set name='test2' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

別セッション

mysql> select last_name from users where id=2;
+-----------+
| name      |
+-----------+
| taro      |
+-----------+
1 row in set (0.00 sec)

トランザクションを実行しているレコードと、DML を行なっているレコードが別で、where は index が効いているカラムに対して行なっているので、トランザクション中でも DML は正常に完了しています。

トランザクション中にオンラインDDL、DMLを行うケース

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update users set name='test' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

別セッション

mysql> ALTER TABLE `users` CHANGE `gender` `gender` int(11) DEFAULT 2 NOT NULL COMMENT 'test comment';

（応答なし）

別セッション

mysql> select name from users where id=2;

（応答なし）

この時に、show full processlist を実行すると、応答なしとなっている2つのセッションが「Waiting for table metadata lock」となっています。

そしてトランザクションを commit すると、応答がなかった2つのセッションで応答が返ってきました。

おわりに

オンラインDDLが可能なDDLだからといって、何も考えずにDDLを行うのは危険ですね。
ただ、トランザクションが長時間発生しないテーブルであれば、オンラインDDLを実行するオプションをつけて実行することでリスクはかなり小さく抑えられそうです。

公式ドキュメントを読み込み、手元で実際に試してみると理解がかなり深まりますね。