MySQLでオンラインでDDLを実行する際の注意点について

2024年12月6日

はじめに

過去の古いMySQLのバージョンでは、テーブル定義の変更を伴うようなDDLを実行する際には、元のテーブルとは別に新しいテーブルを作成し、元のテーブルデータを新テーブルにコピーした後に元のテーブルと入れ替えるといった手法で行われていました。

また、その間の対象テーブルへの更新はブロックされるといった挙動であった為、アプリケーションサービスを稼働中にDDLを実行するというのが、かなり敷居が高いものでした。

それが、MySQL5.5 より Fast Index Creation が採用され、InnoDBのセカンダリインデックスの作成、削除にはテーブルコピーを伴わないで実行できるようになり、処理の高速化が図られています。

その後、MySQL5.6 ではオンラインDDLが使用できるようになり一部のDDLでは、テーブルコピーを伴わずに、更新もブロックしないということが可能となり、更に MySQL 8.0.12 からは、メタデータの更新のみで処理が完了するようなDDLも存在しています。

メタデータの更新のみを行う INSTANT DDL については弊社の以下のブログでも紹介しておりますので、ご一読頂ければと思います。

MySQL 8.0 の INSTANT DDL について

そんな中、今回は、アプリケーションサービスを稼働中にDDLを実行する際に考慮が必要になる、以下の点をピックアップして記載したいと思います。

実行に伴うロック待ちの考慮
実行に要する時間に対する考慮
実行中のテーブルデータの更新量に関する考慮

前提知識

まず、ALTER TABLE には ALGORITHM 指定をする事が可能で、DDLを実行するアルゴリズムとして、以下3つの指定が可能となっています。

ALGORITHM	概要
INSTANT	データディクショナリのメタデータのみを変更する。
INPLACE	テーブル定義をインプレースで変更する。一部のDDLはテーブルの再構築を伴う。
COPY	新たにテーブルを作成し、既存のテーブルからデータをコピーした後に、テーブルを入れ替える。

上記表の下に記載されているもの程、サービス影響は高く、DDLによって指定できる ALGORITHM は異なりますが、特に指定のなかった場合、もしくは DEFAULT を指定した場合は、上から順に操作可能なものを評価して選択されます。

こちらについての詳細は、以下のリファレンスをご確認下さい。

15.1.9 ALTER TABLE Statement / Performance and Space Requirements

また、ALTER TABLE では LOCK 指定も可能で、DDL実行中にDMLへの操作を制御することが可能です。

LOCK	概要
NONE	実行中に対象テーブルの読み取りと書き込みを許可する。
SHARED	実行中に対象テーブルの読み取りは許可されるが、書き込みはブロックする。
EXCLUSIVE	実行中に対象テーブルの読み取りと書き込みもブロックする。

こちらも、上記表の下に記載されているもの程、サービス影響は高く、DDLによって指定できる LOCK は異なりますが、特に指定のなかった場合、もしくは DEFAULT を指定した場合は、上から順に操作可能なものを評価して選択されます。

こちらについての詳細は、以下のリファレンスをご確認下さい。

15.1.9 ALTER TABLE Statement / Concurrency Control

実行に伴うロック待ちの考慮

メタデータロック（DDL実行フェーズ）

どんなDDLを実行するにしても、メタデータロック待ちが発生する可能性があることを考慮する必要があります。

通常のDML操作でテーブル参照を行う場合に取得するメタデータロックは、共有ロックとなりますが、DDL操作では排他ロックを取得する為、共有ロックと排他ロックでロック競合が発生しえます。

以下で一例を見てみます。
まずは、あるセッション内でトランザクションを開始し、t1テーブル をSELECTします。

[Session.1]

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM t1 WHERE id = 15;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM t1 WHERE id = 15;

+----------+

| COUNT(*) |

+----------+

| 1 |

+----------+

1 row in set (0.00 sec)

その後、別のセッションで、 t1テーブル のカラムからデフォルト値を削除します。

[Session.2]

mysql> ALTER TABLE t1 ALTER COLUMN col1 DROP DEFAULT, LOCK=NONE;

1	mysql> ALTER TABLE t1 ALTER COLUMN col1 DROP DEFAULT, LOCK=NONE;

デフォルト値の削除は、以下のリファレンスの Dropping the column default value に該当し、Instant:Yes で Permits Concurrent DML:Yes である為、ロック待ちも発生せずに瞬時に処理が終了するのかと思いきや、しばらく経ってもDDLは終了しません。

17.12.1 Online DDL Operations

そこで、SHOW PROCESSLIST; を確認してみると、DDLの実行は Waiting for table metadata lock となっており、メタデータロック待ちとなっていることが分かります。

mysql> SHOW PROCESSLIST;
+-------+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------------------------------------+
| Id    | User            | Host      | db   | Command | Time  | State                           | Info                                                     |
+-------+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------------------------------------+
|     5 | event_scheduler | localhost | NULL | Daemon  | 28844 | Waiting on empty queue          | NULL                                                     |
| 26412 | root            | localhost | db01 | Query   |     0 | init                            | SHOW PROCESSLIST                                         |
| 26418 | root            | localhost | db01 | Query   |    15 | Waiting for table metadata lock | ALTER TABLE t1 ALTER COLUMN col1 DROP DEFAULT, LOCK=NONE |
| 26840 | root            | localhost | db01 | Sleep   |  1434 |                                 | NULL                                                     |
+-------+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> SHOW PROCESSLIST;

+-------+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------------------------------------+

+-------+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------------------------------------+

| 26840 | root | localhost | db01 | Sleep | 1434 | | NULL |

+-------+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------------------------------------+

4 rows in set (0.00 sec)

この状態で、別のセッションで t1テーブル をSELECTしてみると、こちらもレスポンスが返されません。

[Session.3]

mysql> SELECT * FROM t1 WHERE id = 20;

1	mysql> SELECT * FROM t1 WHERE id = 20;

再度、SHOW PROCESSLIST; を確認してみると、[Session.3]で実行したSELECTも Waiting for table metadata lock となっており、メタデータロック待ちとなっていることが分かります。

mysql> SHOW PROCESSLIST;
+-------+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------------------------------------+
| Id    | User            | Host      | db   | Command | Time  | State                           | Info                                                     |
+-------+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------------------------------------+
|     5 | event_scheduler | localhost | NULL | Daemon  | 28878 | Waiting on empty queue          | NULL                                                     |
| 26412 | root            | localhost | db01 | Query   |     0 | init                            | SHOW PROCESSLIST                                         |
| 26418 | root            | localhost | db01 | Query   |    49 | Waiting for table metadata lock | ALTER TABLE t1 ALTER COLUMN col1 DROP DEFAULT, LOCK=NONE |
| 26840 | root            | localhost | db01 | Query   |    12 | Waiting for table metadata lock | SELECT * FROM t1 WHERE id = 20                           |
+-------+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> SHOW PROCESSLIST;

+-------+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------------------------------------+

+-------+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------------------------------------+

+-------+-----------------+-----------+------+---------+-------+---------------------------------+----------------------------------------------------------+

4 rows in set (0.00 sec)

これは、[Session.1] のSELECTで t1テーブル の共有ロックが取得され、その後、 [Session.2] のDDLが排他ロックを取得しようとして、メタデータロック待ちとなります。
この状態になると、排他ロックの取得待ちとなっている [Session.2] が存在することで、[Session.3]のSELECTや、その他の t1テーブル を参照するDMLは全て共有ロックが取得できずにロック待ち状態となります。

なので、DDLをサービス停止を伴わないで実行する際には、長きに渡って実行されているトランザクションがないかを確認し、DDLを実行するセッションでは以下のように lock_wait_timeout(デフォルト：31536000秒=365日) を短めに設定してからDDLを実行することが安全策と言えます。

mysql> SET SESSION lock_wait_timeout=5;
mysql> ALTER TABLE ・・・

1 2	mysql> SET SESSION lock_wait_timeout=5; mysql> ALTER TABLE ・・・

これにより、排他ロックを取得するDDLがロック待ち状態になったとしても5秒経過するとタイムアウトエラーとなる為、他のセッションで共有ロックを取得するDMLが実行された場合には、最大5秒のロック待ちで済むことになります。

メタデータロックによる問題が発生した場合の対処法については、以下のブログにも記事を投稿しておりますので、ご確認頂ければと思います。

メタデータロックの調べ方

メタデータロック（DDLコミットフェーズ）

上でご紹介したのは、以下のリファレンスの Phase 2: Execution に記載されているメタデータロックについてとなります。

17.12.2 Online DDL Performance and Concurrency / Online DDL and Metadata Locks

例で挙げたように、DDLを実行する前段階で瞬間的に排他ロックを取得する為、この時点で既に共有ロックを取得していたトランザクションが終了していなければ、ロック待ちになるというケースとなります。

加えて、同リファレンスの Phase 3: Commit Table Definition に記載されているように、DDLの実行の終了段階となるコミットフェーズでも、排他ロックを取得する為、DDLの実行時にメタデータロック待ちが発生しなくても、終了するまでに対象テーブルを参照したトランザクションが終了していなければ、DDLの実行は最後にメタデータロック待ちとなります。

先程の例で言うと、先に [Session.2] でDDLを実行し、これが終了するまでに、[Session.1] のような、対象テーブルを参照してトランザクションが終了していない状態のセッションが存在した場合、DDLの実行は終了せずにメタデータロック待ちとなります。

DDL実行中のロック

以下のリファレンスの表の Permits Concurrent DML が No と記載されているDDLについては、同時DMLが許可されておらず、LOCK=SHARED となる為、DDLが実行されている間の対象テーブルへの更新はロック待ちとなることを認識しておく必要があります。

17.12.1 Online DDL Operations

さらに、Permits Concurrent DML が Yes* のものも条件によっては、LOCK=SHARED になることがあり得るということと、 Permits Concurrent DML が Yes のものでも上記リファレンスの表外の詳細説明で ALGORITHM=COPY となるケースが記載されていたりする為、これらも LOCK=SHARED となります。

なので、DDL実行中に更新も含めたDMLが実行可能であるかどうかは、検証環境等で同じテーブル構造を持つテーブルに対して LOCK=NONE を指定してDDLを実行し、エラーとならないかを確認してみるのがよいでしょう。

更新をブロックしないと実行できないケースでは、以下のようなエラーになります。

mysql> ALTER TABLE t1 DROP PRIMARY KEY, LOCK=NONE;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED.

1 2	mysql> ALTER TABLE t1 DROP PRIMARY KEY, LOCK=NONE; ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try LOCK=SHARED.

実行に要する時間に対する考慮

以下のリファレンスの表の In Place が No と記載されているDDLについては、Copy形式になるもので、これはテーブルデータ量に応じて実行に時間を要します。

17.12.1 Online DDL Operations

In Place が Yes であっても Rebuilds Table が Yes でテーブル再構築を伴うものもあり、このようなDDLもテーブルデータ量に応じて実行に時間を要します。

他にも、In Place が Yes で Rebuilds Table が No でも、同様に時間がかかることがあります。

例えば、Creating or adding a secondary index はセカンダリインデックスの作成になりますが、In Place が Yes かつ Rebuilds Table が No となりますが、テーブルのデータ量に応じて実行時間がかかることが考えられます。

つまりは、Instant が Yes 以外のDDLについては、実行に時間がかかる可能性がある為、DDLの実行にかかる時間を事前に把握したい場合は、同じテーブル定義及びデータ量を持ち、かつ本番環境と同レベルのサーバリソースとなる検証環境などで、事前に確認する必要があります。

セカンダリインデックスの作成または再構築を伴うDDLの実行時間を改善したい場合、CPU負荷及びディスクI/Oがボトルネックになっていなければ、以下のパラメータを調整することで改善する見込みがありますので、調整してみてもよいでしょう。

innodb_parallel_read_threads
- クラスター化インデックスのスキャンに使用できるスレッド数
innodb_ddl_threads
- 一時ソートファイルのデータの並び替え及びセカンダリインデックスにソートされたデータをロードするスレッド数

もしも、セカンダリインデックスの作成または再構築を伴うDDLの実行中にメモリ不足となるようなケースでは、以下のパラメータのバッファサイズを制限することで問題を回避できる可能性があります。

innodb_ddl_buffer_size

これらに関する詳細は、以下のリファレンスをご確認下さい。

※ 上記のそれぞれのパラメータは以下のバージョンで追加されています。

innodb_parallel_read_threads （MySQL 8.0.14）
innodb_ddl_threads, innodb_ddl_buffer_size （MySQL 8.0.27）

実行に時間がかかるDDLは、基本的にCPU負荷及びディスクI/Oの負荷が高まる為、その間のアプリケーションが発行するDMLのレスポンスが遅くなってしまう可能性もあります。
また、レプリケーション構成でDDLを実行した場合、ロック待ち以外の時間でソースサーバで時間がかかるということは、レプリカサーバでも同様に時間がかかる為、レプリケーション遅延が大きく発生してしまう可能性もあります。

サービスが稼働している状態で、DDLを実行するということは、ロック待ち以外にも影響を及ぼす可能性がある点も考慮すべき点かと思います。

但し、レプリケーション遅延やリソース負荷に関する問題に関しては、以下の製品を用いることで、回避できる可能性がある為、検証してみるとよいかもしれません。

実行中のテーブルデータの更新量に関する考慮

DDLの実行中に更新が可能なケースでは、実行中の該当テーブルへの更新情報を別のログファイルに書き出し、テーブル定義を変更した後に、その更新情報を適用するといった動作になります。

その為、サービスが稼働している状態でDDLの実行に時間がかかる場合、ログファイルのサイズが大きくなり、更新情報の適用量が多くなることにより、DDLの実行時間が長くなるといったことも考慮する必要があります。

加えて、更新情報を記録するログファイルサイズには制限があり、innodb_online_alter_log_max_size の制限を超えて更新が発生した場合、更新情報をテーブルスペースファイルに適用する段階で、DDLの実行がエラー終了することとなります。

それでは挙動を確認してみます。
まずは、検証前に innodb_online_alter_log_max_size を最小値となる64KBに変更しておきます。

mysql> SET GLOBAL innodb_online_alter_log_max_size = 65536;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size' ;
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_online_alter_log_max_size | 65536 |
+----------------------------------+-------+
1 row in set (0.01 sec)

mysql> SET GLOBAL innodb_online_alter_log_max_size = 65536;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size' ;

+----------------------------------+-------+

| Variable_name | Value |

+----------------------------------+-------+

| innodb_online_alter_log_max_size | 65536 |

+----------------------------------+-------+

1 row in set (0.01 sec)

設定値変更後に、以下の sysbench で5千万件のレコードを持つテーブルを1つ作成しました。

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE "sbtest1" (
  "id" int NOT NULL AUTO_INCREMENT,
  "k" int NOT NULL DEFAULT '0',
  "c" char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '                                                                                                                        ',
  "pad" char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '                                                            ',
  PRIMARY KEY ("id"),
  KEY "k_1" ("k")
) ENGINE=InnoDB AUTO_INCREMENT=50000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

mysql> show create table sbtest1\G

*************************** 1. row ***************************

Table: sbtest1

Create Table: CREATE TABLE "sbtest1" (

"id" int NOT NULL AUTO_INCREMENT,

"k" int NOT NULL DEFAULT '0',

"c" char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT ' ',

"pad" char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT ' ',

PRIMARY KEY ("id"),

KEY "k_1" ("k")

) ENGINE=InnoDB AUTO_INCREMENT=50000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

その後、以下のコマンドで、このテーブルに更新負荷をかけるように oltp_write_only シナリオを実行します。

sysbench --db-driver=mysql \
  --mysql-host={Host} \
  --mysql-user={User} \
  --mysql-password={Password} \
  --mysql-db={Database} \
  --tables=1 \
  --table_size=50000000 \
  --threads=2 \
  --events=0 \
  --time=1800 \
  oltp_write_only \
  run

sysbench --db-driver=mysql \

--mysql-host={Host} \

--mysql-user={User} \

--mysql-password={Password} \

--mysql-db={Database} \

--tables=1 \

--table_size=50000000 \

--threads=2 \

--events=0 \

--time=1800 \

oltp_write_only \

run

続いて、対象テーブルにインデックスを追加する ALTER文を実行します。

mysql> ALTER TABLE sbtest1 ADD INDEX i(pad);

1	mysql> ALTER TABLE sbtest1 ADD INDEX i(pad);

25分程の時間が経過した時点で、上記、ALTER文はエラーとなりました。

ERROR 1799 (HY000): Creating index 'i' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

1	ERROR 1799 (HY000): Creating index 'i' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.

実行に時間のかかる DDL が終了段階に近づいているにも関わらず、このような形でエラーになってしまう残念な結果となってしまう為、サービスの稼働中に、DDLを実行する対象テーブルに多くの更新が入ることが予想される場合は、innodb_online_alter_log_max_size を事前に調整するようにしましょう。

また、他によく知られている例としてユニークインデックスを追加中に、対象インデックス項目に、既存のテーブルデータと重複するレコードの登録があった場合も同様です。
テーブル定義を変更した後に、更新情報をテーブルスペースファイルに適用する段階で、DDLの実行がエラー終了することになりますので、プライマリキーやユニークキーを追加する際は、注意が必要です。

まとめ

今回は、アプリケーションサービスを稼働している中で、DDLを実行する際の注意点について記載しました。
可能な限り、メンテナンス期間を設けてDDLを実行するのが望ましいのですが、メンテナンス期間を設けられないようなサービスもあるかと思います。

しかし、実行中に更新を許可する ALGORITHM=INPLACE でかつ LOCK=NONE であるオンラインDDLには、以下のようなバグが存在しています。

上記バグについては、DDL実行中に予期せず行が削除されるというものと、プライマリキーやユニークキーの重複が発生していないのにも関わらず、重複キーエラーでDDLの実行がエラーとなるといったものです。
こちらは、LTSとなるMySQL8.0、MySQL8.4では、執筆時点(2024/12/6)では修正されておらず、以下のバージョンが影響を受けます。