本ブログはMySQL InnoDB’s Instant Schema Changes: What DBAs Should Knowの翻訳版です。
MySQL 8.0.12ではテーブル定義を変更するDDL文に、テーブルをブロックせずメタデータのみ更新することで再構成の高速化を図るALGORITHM句を新しく導入しました。最初に実装されたINSTANT DDL操作はテーブルの最後にカラムを追加する機能です。こちらはTencent Games様からのコントリビューションです。
次に、MySQL 8.0.29でテーブル内の任意の場所にカラムを追加または削除できる機能を追加しました。
上記の機能について詳しくはMayank Prasadが執筆した記事(英語)をご覧下さい。
本記事では、これらの機能を使用する際に留意いただきたい事項についてご説明します。
デフォルト値と操作回数
MySQL 8.0.12以降サポートされているDDL操作のALGORITHM句の値は、デフォルトでINSTANTに設定されています。つまり、ALTER文がデータ・ディクショナリ内のテーブルのメタデータのみを変更するということです。このDDL操作の準備中および実行中に、対象のテーブルに排他的メタデータ・ロックが取得されることはありません。テーブルのデータは影響を受けないため、操作は瞬時に行われます。
INSTANT以外のALGORITHM句の値は、COPYとINPLACEです。
参考: オンラインDDL 操作
INSTANT DDL操作がサポートされていても、INSTANTスキーマ変更の操作回数には上限があります。テーブル毎に計64回のINSTANTスキーマ変更に対応しており、この上限に到達したらテーブルの再構築が必要になります。
DDL操作の一つであるALTER文でALGORITHM句が明示的に指定されていない場合、適切なALGORITHM句の値が自動で選択されます。本番環境が予期しない動作をするということを防ぐために、データベース管理者はこの特徴について知っておく必要があるでしょう。
ALGORITHM句の指定を推奨
DDL操作を実行する際に特定の値がデフォルトで設定されていたとしても、常にALGORITHM句を明示的に指定することをお勧めします。ALGORITHM句が指定されていれば、MySQLが指定された値をサポートしていない場合に、別の値が自動で使用されることはなく、エラーが排出されます。
SQL > ALTER TABLE `test/t1` DROP col1, ALGORITHM=INSTANT; ERROR: 4092 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
INSTANT DDL操作の監視
テーブルで実行されたINSTANTスキーマ変更の回数を確認することもお勧めです。MySQLは行のバージョンをINFORMATION_SCHEMAのINNODB_TABLESに保持しています。
SQL > SELECT NAME, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE '%test%';
+--------------------------+--------------------+
| NAME | TOTAL_ROW_VERSIONS |
+--------------------------+--------------------+
| instant_test/test@002ft1 | 63 |
+--------------------------+--------------------+
1 row in set (0.00 sec)
上の例でカウンターは63を示しているため、テーブルの再構築が必要になる前にINSTANT DDL操作を実行できるのは残り1回ということがわかります。
データベース管理者の皆様には、全テーブルを監視し各テーブルがいつ再構築の必要があるのか判断することをお勧めします。
下記は、INSTANT DDL操作を実行できる残りの回数をテーブル毎に表示するクエリ文の例です。監視ツールに追加するなど、適宜使用して下さい。
mysql> SELECT NAME, TOTAL_ROW_VERSIONS, 64-TOTAL_ROW_VERSIONS AS "REMAINING_INSTANT_DDLs", ROUND(TOTAL_ROW_VERSIONS/64 * 100,2) AS "DDLs %" -> FROM INFORMATION_SCHEMA.INNODB_TABLES -> WHERE TOTAL_ROW_VERSIONS > 0 ORDER BY 2 DESC; +--------------------------+--------------------+------------------------+--------+ | NAME | TOTAL_ROW_VERSIONS | REMAINING_INSTANT_DDLs | DDLs % | +--------------------------+--------------------+------------------------+--------+ | instant_test/test@002ft1 | 63 | 1 | 98.44 | | instant_test/test@002ft2 | 4 | 60 | 6.25 | | instant_test/sbtest | 1 | 63 | 1.56 | +--------------------------+--------------------+------------------------+--------+ 3 rows in set (0.00 sec)
カウンターをリセットしてテーブルを再構築するには、OPTIMIZE TABLE テーブル名 または ALTER TABLE テーブル名 ENGINE=InnoDB を実行します。
訳者注: 下記は翻訳版で追加した、テーブル再構築の実行例です。
OPTIMIZE TABLE テーブル名によるテーブル再構築の実行例。
mysql> SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES where NAME like '%test%'; +--------------------------+--------------------+ | NAME | TOTAL_ROW_VERSIONS | +--------------------------+--------------------+ | instant_test/test@002ft1 | 63 | +--------------------------+--------------------+ 1 row in set (0.00 sec) mysql> OPTIMIZE TABLE `test/t1`; +----------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------------+----------+----------+-------------------------------------------------------------------+ | instant_test.test/t1 | optimize | note | Table does not support optimize, doing recreate + analyze instead | | instant_test.test/t1 | optimize | status | OK | +----------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.02 sec) mysql> SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES where NAME like '%test% +--------------------------+--------------------+ | NAME | TOTAL_ROW_VERSIONS | +--------------------------+--------------------+ | instant_test/test@002ft1 | 0 | +--------------------------+--------------------+ 1 row in set (0.00 sec)
ALTER TABLE テーブル名 ENGINE=InnoDB によるテーブル再構築の実行例。
mysql> SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES where NAME like '%test%'; +--------------------------+--------------------+ | NAME | TOTAL_ROW_VERSIONS | +--------------------------+--------------------+ | instant_test/test@002ft1 | 63 | +--------------------------+--------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE `test/t1` ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES where NAME like '%test% +--------------------------+--------------------+ | NAME | TOTAL_ROW_VERSIONS | +--------------------------+--------------------+ | instant_test/test@002ft1 | 0 | +--------------------------+--------------------+ 1 row in set (0.00 sec)
まとめ
上で説明したとおりMySQL 8.0で導入されたINSTANT DDL操作は、テーブルのブロックを回避しスキーマ変更を高速化するという、スキーマ変更における革命です。ただしINSTANT DDL操作によるスキーマ変更はテーブル毎に64回が上限なので、テーブルの再構築が必要となり予期しない動作を避けるために、ALTER文でALGORITHM句を明示的に指定することをお勧めします。また、二つめにご紹介したとおりINFORMATION_SCHEMAのINNODB_TABLESで、INSTANT DDL操作によるテーブル変更の回数を監視することも有益です。これによって、実行可能回数の上限に達することによる予期せぬ事態を回避し、テーブルの再構築を計画的に行うことができます。