📗

MySQL の Online DDL の挙動をローカルで検証して理解する

2022/12/10に公開

MySQL

tech

MySQL の Online DDL について検証しつつ備忘録としてまとめておきます。
(本記事の検証は、MySQL 5.7.40 で実施しています。)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.40    |
+-----------+
1 row in set (0.02 sec)

Online DDLとは

MySQL 5.7 Reference Manual - 14.13 InnoDB and Online DDL

Online DDL の機能によって、インデックスの作成などの DDL を実行している最中に INSERT , UPDATEなどの DML が実行できるようになっています。

MySQL 5.5 以前での ALTER TABLE は下記のような制限があったようです。

ALTER TABLE は実行中にテーブルの共有ロックを取得する
従って実行中にデータの読み取りはできるが、DML(INSERT, UPDATE etc.)は処理できない
- そのため、DDL 実行時にはメンテナンスモードにしないといけなかった

Online DDL に対応している DDL の処理は、下記から確認できます。

Online DDL の一例として、インデックスの作成があります。

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

上のサンプルの SQL(ALTER TABLE)に定義されているように、下記のオプションを指定できます。

LOCK
- LOCK=NONE を指定すると、read / write の両方が許可されます。
  - LOCK=SHARED を指定して、read のみに限定することもできる。
- https://yoku0825.blogspot.com/2016/06/alter -tablealter-table.html
  - ※ その alter table が Online DDL に対応しているかを確認する方法にもなります。
ALGORITHM
- ALGORITHM=INPLACE を指定すると、処理をブロックすることなく in-place での更新を行います。
- ALGORITHM=COPY の場合、ロックしている間に既存テーブルのデータを新しいテーブルにコピーして最後にテーブルを置き換えるという既存の手法と同じ処理を行います。

このオプションの指定によって、DML のブロックとテーブル全体のコピーが不要な DDL (Online DDL) の実行が可能となります。

一方で、auto-increment な column の追加ではテーブル全体のコピーが不要だが、DML がブロックされるようなケースもあり、それぞれ DDL の種別に応じて微妙に挙動が異なります。

Concurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation. At a minimum, ALGORITHM=INPLACE, LOCK=SHARED is required.

LOCKと ALGORITHM 句を省略した操作は、MySQL 側で評価されて使用可能なオプションで実行されます。 (e.g. LOCKであれば、LOCK=NONEから評価される)

検証

検証用に使った環境は下記です。
docker-compose で起動すると、必要なデータも事前作成されるように設定しています。

検証用のデータに関しては、下記のブログを参考にさせていただきました。

インデックスの追加

最初に、インデックスの追加で検証してみます。

mysql> ALTER TABLE products ADD INDEX (name);
Query OK, 0 rows affected (12.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT table_schema, table_name, index_name, column_name, seq_in_index
    -> FROM information_schema.statistics
    -> WHERE table_schema = "products";
+--------------+------------+------------+-------------+--------------+
| table_schema | table_name | index_name | column_name | seq_in_index |
+--------------+------------+------------+-------------+--------------+
| products     | products   | PRIMARY    | id          |            1 |
| products     | products   | name       | name        |            1 |
+--------------+------------+------------+-------------+--------------+
2 rows in set (0.04 sec)

インデックスの追加とは別のセッションで適当な SELECT , INSERTを実行してみましたが、特にブロックされることはありませんでした。

mysql> select count(*) from products;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (1.23 sec)

mysql> INSERT INTO products(name, stocks, created_at, updated_at) values('high performance mysql', 1, now(), now());
Query OK, 1 row affected (0.01 sec)

カラムの追加

続いて、カラムの追加です。

mysql> ALTER TABLE products ADD COLUMN comment varchar(255) AFTER name;
Query OK, 0 rows affected (6.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

これに関しても、特にブロックされることはありませんでした。

mysql> INSERT INTO products(name, stocks, created_at, updated_at) values('high performance mysql', 1, now(), now());
Query OK, 1 row affected (0.01 sec)

なお、カラムの追加に関しては、実行中に書き込みができる一方でテーブルデータのコピーが必要という処理になっています。

カラムの削除に関しても同様にブロックされることはありませんでした。
ちなみに、処理中に SHOW ENGINE INNODB STATUS\G でトランザクションの項目を見てみると、ALTER TABLEの処理について 0 row lock(s) となっているのがわかります。

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2022-12-10 06:24:04 0x40bfba8700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 61 seconds

...

------------
TRANSACTIONS
------------
Trx id counter 1974
Purge done for trx's n:o < 1966 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281750090301952, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---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 1970, ACTIVE 5 sec
mysql tables in use 1, locked 1
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 2, OS thread handle 277810923264, query id 78 localhost root altering table
ALTER TABLE products DROP COLUMN comment
Trx read view will not see trx with id >= 1972, sees < 1971

Online DDLの注意事項

Online DDL の利用にあたってはいくつかの考慮事項があります。

例えば、Online DDL の開始前、完了前に短時間の排他的アクセスが必要になります。
Online DDL の制限の項目にも下記のように記載があります。

Before an online DDL operation can finish, it must wait for transactions that hold metadata locks on the table to commit or roll back. 
An online DDL operation may briefly require an exclusive metadata lock on the table during its execution phase, and always requires one in the final phase of the operation when updating the table definition. 
Consequently, transactions holding metadata locks on the table can cause an online DDL operation to block.

記載されている内容をまとめると下記のようになります。

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

この挙動についても、実際に確認してみましょう。
先ほどとは違い、トランザクションを開始した状態で、Oneline DDL(インデックスの追加)を実行します。

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO products(name, stocks, created_at, updated_at) values('high performance mysql', 1, now(), now());
Query OK, 1 row affected (0.01 sec)

mysql> ALTER TABLE products ADD INDEX test_index(name);

Online DDL の先ほどの挙動とは異なり、応答が帰ってこなくなりました。
SHOW ENGINE INNODB STATUS\Gでトランザクションの状況を確認します。

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2022-12-10 07:02:39 0x40bfba8700 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

show full processlist; で確認すると、応答がないセッションの State の欄に、Waiting for table metadata lockと記載されているのがわかります。

mysql> show full processlist;
+----+------+-----------+----------+---------+------+---------------------------------+-------------------------------------------------+
| Id | User | Host      | db       | Command | Time | State                           | Info                                            |
+----+------+-----------+----------+---------+------+---------------------------------+-------------------------------------------------+
|  2 | root | localhost | products | Sleep   |   90 |                                 | NULL                                            |
|  3 | root | localhost | products | Query   |   87 | Waiting for table metadata lock | ALTER TABLE products ADD INDEX test_index(name) |
|  5 | root | localhost | products | Query   |    0 | starting                        | show full processlist                           |
+----+------+-----------+----------+---------+------+---------------------------------+-------------------------------------------------+
3 rows in set (0.00 sec)

commit した上で、再度 show full processlist; を実行すると、lock が解除されていることがわかります。

mysql> show full processlist;
+----+------+-----------+----------+---------+------+----------------+-------------------------------------------------+
| Id | User | Host      | db       | Command | Time | State          | Info                                            |
+----+------+-----------+----------+---------+------+----------------+-------------------------------------------------+
|  2 | root | localhost | products | Sleep   |    5 |                | NULL                                            |
|  3 | root | localhost | products | Query   |  113 | altering table | ALTER TABLE products ADD INDEX test_index(name) |
|  5 | root | localhost | products | Query   |    0 | starting       | show full processlist                           |
+----+------+-----------+----------+---------+------+----------------+-------------------------------------------------+
3 rows in set (0.00 sec)

最終的に、応答も返ってきました。

mysql> ALTER TABLE products ADD INDEX test_index(name);
Query OK, 0 rows affected (1 min 59.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

まとめ

今回は MySQL5.7 の環境を利用して Online DDL について調べてみました。
DDL の操作によって挙動が異なるのはハマりポイントですが、こういったローカルでサクッと検証できる環境があるとハマりポイントも回避しやすくなるかもしれません。

Online DDLとは

検証

インデックスの追加

カラムの追加

Online DDLの注意事項

まとめ

Discussion