MySQL 5.6 のオンラインDDLについて調べた
今更だけど MySQL 5.6 ではオンラインDDLの機能が追加されている。今日はこのオンラインDDLについて勉強したことを書いてみる。
MySQL のマニュアル
MySQL :: MySQL 5.6 Reference Manual :: 14.11 InnoDB and Online DDL にいろいろ書いてある。いまから書くことはこのマニュアルから得た知識が元になっている。
DDL てなによ？
データではなく、テーブル自身を操作するためのSQL文のこと。CREATE, ALTER, DROP, TRUNCATEなど。オンラインDDLではCREATE INDEX, DROP INDEX, ALTER TABLEに適用される。
http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_ddl
5.1 までの ALTER TABLE
すべての ALTER TABLE は実行中にテーブルの共有ロックを取得する。従って実行中にデータの読み取りはできるが書き込み(INSERT, UPDATE, DELETE)はできない。ロックしている間に既存テーブルのデータを新しいテーブルにコピーして最後にテーブルを置き換える。
以下の記事に詳しい。
漢(オトコ)のコンピュータ道: ALTER TABLEを上手に使いこなそう。
5.5 の ALTER TABLE
5.5 または 5.1 + InnoDB Plugin で Fast Index Creation という機能が追加された。これによりセカンダリインデックスの追加と削除についてテーブルのデータをコピーしないで実行できるようになった。実行中に書き込みできないことは変わっていない。
5.6 からの ALTER TABLE
テーブルのデータコピーが不要なALTER TABLEが増えた。実行中に書き込みできるようになった（できないものもある）。簡単に分類すると以下のような感じ。
- 実行中に書き込みできない（これまでと同じ）
- 実行中に書き込みできる＆テーブルコピーが必要
- カラムの追加、削除、並び替え
- カラムをNULL許可にする
- カラムをNOT NULLにする
- 実行中に書き込みできる＆テーブルコピーが不要
- インデックスの追加、削除
- カラムのデフォルト値の設定
- オートインクリメント値の変更
- 外部キー制約の追加、削除
詳細は 14.11.1 Overview of Online DDL の Table 14.5 Summary of Online Status for DDL Operations を参照。
また ALTER TABLE 文で ALGORITHM と LOCK のオプションを指定できるようになった。オプションが未指定の場合はMySQLが決めた動作になる。書き込みができるのは LOCK=NONE のとき。ALGORITHM=COPYは従来の方法で、INPLACEというのが新しい方法。
実際に試してみる（その1）- ほんとにALTER中に書き込みできるの？編
準備
- mysql-build で 5.6.23 をインストールする
- MySQLを起動する
bin/mysqld --port=3306 --socket=/tmp/mysql56.sock
- データを流し込む
mysql> select count(*) from products ; +----------+ | count(*) | +----------+ | 1048576 | +----------+ 1 row in set (0.21 sec) mysql> show create table products ; +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | products | CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `stocks` int(11) DEFAULT NULL, `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1376221 DEFAULT CHARSET=latin1 | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
インデックスの追加
インデックスの追加はテーブルデータのコピーが不要なので速い。実行中に書き込みができる。
セッション(1) セッション(2)
------------- -------------
mysql> ALTER TABLE products ADD INDEX (name);
mysql> INSERT INTO products(name, stocks, created_at, updated_at) values('high performance mysql', 1, now(), now());
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (2.99 sec)
Records: 0 Duplicates: 0 Warnings: 0
カラムの追加
カラムの追加はテーブルデータのコピーが必要だが、実行中に書き込みができる。
セッション(1) セッション(2)
------------- -------------
mysql> ALTER TABLE products ADD COLUMN sku varchar(255) AFTER name;
mysql> INSERT INTO products(name, stocks, created_at, updated_at) values('high performance mysql', 1, now(), now());
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (6.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
カラムのデータ型変更
カラムのデータ型の変更はテーブルデータのコピーが必要で、実行中に書き込みができない。
セッション(1) セッション(2)
------------- -------------
mysql> ALTER TABLE products MODIFY stocks int unsigned;
mysql> INSERT INTO products(name, stocks, created_at, updated_at) values('high performance mysql', 1, now(), now());
Query OK, 1048578 rows affected (7.99 sec)
Records: 1048578 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (7.01 sec)
気をつけること
テーブルコピーが必要/不要とか、実行中に書き込みできる/できないということ以外に気をつけたいことがいくつかある。
5.6 より前に作成されたテーブル
5.6 より前に作成されたテーブルが日時カラムを含む場合は必ず ALGORITHM=COPY によるテーブルデータのコピーが必要。5.5 からアップデートした場合が該当する。5.5 のデータをダンプして、5.6にリストアした場合は該当しない。
これは MySQL 5.6.4 から日時カラムが浮動小数点をサポートしたことで互換性が無くなったことが関係しているようだ。
Upgrading old MySQL-5.5 format temporals to MySQL-5.6 format. | MySQL Server Blog
2015/04/08 追記：MySQL 5.6.24 で avoid_temporal_upgrade というのが追加されたようです。これを有効にすればデータコピーを避けることができそう（未確認）ですが、追加されていきなりIt is deprecated and will be removed in a future MySQL release.だそうです。ま、そっか...
排他ロック
ALTER TABLE 開始時と終了時にちょっとだけ排他ロックが必要。これは実行中に行われる書き込みSQLを記録しておくための準備をしたり、最後にそれらを新しいテーブルに適用したりするのに必要。
An online DDL statement for an InnoDB table always waits for currently executing transactions that are accessing the table to commit or roll back, because it requires exclusive access to the table for a brief period while the DDL statement is being prepared. Likewise, it requires exclusive access to the table for a brief time before finishing. Thus, an online DDL statement waits for any transactions that are started while the DDL is in progress, and query or modify the table, to commit or roll back before the DDL completes.
更新ログあふれ
ALTER TABLE 実行中にテーブルに対して行われる書き込みSQLはログファイルに記録される。このログファイルにはサイズ上限があり innodb_online_alter_log_max_size で設定される。上限を超えた場合はエラーになってALTER TABLEは失敗する。
If the ALTER TABLE takes so long, and concurrent DML modifies the table so much, that the size of the temporary online log exceeds the value of the innodb_online_alter_log_max_size configuration option. This condition causes a DB_ONLINE_LOG_TOO_BIG error.
MySQL :: MySQL 5.6 Reference Manual :: 14.11.6 Implementation Details of Online DDL
変更後のテーブルでエラーになるデータ
ALTER TABLE 実行中に新しいテーブルでエラーになるようなSQLが実行されると、最後のフェーズで更新ログからそのSQLを適用する時にエラーになってALTER TABLEは失敗する。たとえばユニークインデックス追加中にDuplicate Entryができるような更新が行われたとか。
If concurrent DML makes changes to the table that are allowed with the original table definition, but not with the new one. The operation only fails at the very end, when MySQL tries to apply all the changes from concurrent DML statements. For example, you might insert duplicate values into a column while a unique index is being created, or you might insert NULL values into a column while creating a primary key index on that column. The changes made by the concurrent DML take precedence, and the ALTER TABLE operation is effectively rolled back.
MySQL :: MySQL 5.6 Reference Manual :: 14.11.6 Implementation Details of Online DDL
実際に試してみる（その2）- 気をつけること編
ユニークインデックス追加中に重複する値
ユニークインデックス追加中に Duplicate するレコードを作成するとALTER TABLEは失敗する。
セッション(1) セッション(2)
------------- -------------
mysql> UPDATE products SET sku = 'aaa' WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM products WHERE id = 1;
+----+------------------------+------+--------+---------------------+---------------------+
| id | name | sku | stocks | created_at | updated_at |
+----+------------------------+------+--------+---------------------+---------------------+
| 1 | high performance mysql | aaa | 1 | 2015-03-11 00:42:13 | 2015-03-11 00:42:13 |
+----+------------------------+------+--------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE products ADD UNIQUE INDEX (sku);
mysql> UPDATE products SET sku = 'aaa' WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
ERROR 1062 (23000): Duplicate entry 'aaa' for key 'sku'
元のテーブルに対するUPDATEは成功している。
mysql> SELECT * FROM products WHERE id = 2; +----+------------------------+------+--------+---------------------+---------------------+ | id | name | sku | stocks | created_at | updated_at | +----+------------------------+------+--------+---------------------+---------------------+ | 2 | high performance mysql | aaa | 1 | 2015-03-11 00:42:13 | 2015-03-11 00:42:13 | +----+------------------------+------+--------+---------------------+---------------------+ 1 row in set (0.00 sec)
カラム追加中に更新ログあふれ
ALTER TABLE 実行中に innodb_online_alter_log_max_size を超える量の更新を行うと失敗する。
準備として innodb_online_alter_log_max_size の値を小さくしておく。
mysql> SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size' ; +----------------------------------+-----------+ | Variable_name | Value | +----------------------------------+-----------+ | innodb_online_alter_log_max_size | 134217728 | +----------------------------------+-----------+ 1 row in set (0.00 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.00 sec)
また上記設定値を上回るデータを流し込む準備をしておく。
$ cat overflow.sql INSERT INTO products(name, stocks, created_at, updated_at) SELECT name, stocks, created_at, updated_at FROM products; INSERT INTO products(name, stocks, created_at, updated_at) SELECT name, stocks, created_at, updated_at FROM products; INSERT INTO products(name, stocks, created_at, updated_at) SELECT name, stocks, created_at, updated_at FROM products;
ALTER TABLE 実行後に overflow.sql を流し込む。
セッション(1) セッション(2)
------------- -------------
mysql> ALTER TABLE products ADD COLUMN status tinyint AFTER name;
# コンソールから実行
$ mysql -uroot -h127.0.0.1 test < overflow.sql
ERROR 1799 (HY000): Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again.
ソースコード
5.6.23 のソースコードでどのへんがオンラインDDLに関係するのかなと思ったのでちょっとのぞいてみた。このへんを見るとよさそう。
- storage/innobase/handler/handler0alter.cc
- ha_innobase::inplace_alter_table
- storage/innobase/row/row0log.cc
- row_log_table_low
まとめ
今回はMySQL5.6のオンラインDDLについて調べてみました。操作によって挙動が違うのが興味深くて、InnoDBでデータがどう保存されているかとか、セカンダリインデックスはデータ（クラスタインデックス）とは独立して作成できるとか、そういうことが垣間見えて楽しかったです。
オンラインDDL界隈には他にもいろいろなツールがあってやりかたもちょっとずつ違うと思うので、時間があればそれらの仕組みを調べるのも楽しそうです。
