はじめに
DDL（Data Definition Language）は、MySQLの開発・運用では欠かせない要素です。MySQL5.6で導入された「オンラインDDL」では、従来READロックを取得していたALTER TABLEコマンドがほぼロック無しで実行できるようになり（例外あり）、運用の柔軟性も増してきました。
しかし、Percona XtraDB Cluster（以下、PXC）におけるDDLの仕様は、通常のMySQLとは少々異なります。本稿ではその部分について解説します。
DDLとは
まず初めにDDLに関しての説明です。DDLはデータベースのオブジェクト（メタデータ）を操作するSQL（クエリ、コマンド）を指しています。ここでいうオブジェクトとは、主にデータベース / テーブルに関する定義情報です。例えば、データベースを作成する「CREATE DATABASE」や、テーブルを削除する「DROP TABLE」などが該当します。
MySQLにおける代表的なDDLは「ALTER TABLE」です。これは既存のテーブルの構造を変更するSQLであり、以下のようなことができます。
- インデックス / カラムの追加・削除
- テーブル名 / カラム名の変更
- カラムのデータ型変更
- テーブルの文字コード変更
- InnoDBページ圧縮の有効化
MySQL5.5までは、DDLの実行中は参照クエリは受け付けますが、更新クエリ（DML）はDDLが完了するまで待たされる仕様になっていました。
しかし、MySQL5.6から実装された「オンラインDDL」が利用できる場合（※）は、DDL実行中であっても更新クエリを受け付けるようになりました。
※ マニュアルの『表 14.5 DDL 操作のオンラインステータスのサマリー』で「並列 DML を許可?」が「はい」になっているもの
オンラインDDLのおかげで、サービスに影響（停止）を与えることなくDDLを実行することが可能になり、メンテナンス作業などの運用に大きなメリットが生まれました。
PXCにおけるDDL
PXC（Galera）でも、問題なくDDLは実行できます。しかし、「マルチマスタ」であり全ノードで更新を受け付ける仕様上、DDLが原因でノード間でテーブル構造に差分が生じると、データの整合性が崩れてしまいます（※）。
※ DDLは non-transactional な処理であり、途中で失敗した時にロールバックなどが難しいため
そのため、PXCでは2つの実行形式が存在し、wsrep_osu_method変数で制御できます。
（OSUは Online Schema Upgradeの略です）
- wsrep_OSU_method = TOI
TOI（Total Order Isolation）は、PXC（Galera）のデフォルト方式です。この設定が有効な場合、DDLを実行した時に全ノードで整合性を保つために、DDLが実行される順番（GTID）が同じになるように調整されます。
つまりは全ノードで同時にDDLが実行されるわけですが、この代償としてDDLが完了するまでの間は全てのトランザクションが待ち状態（※）になります。これはMySQLでは「オンラインDDL」に該当する操作であっても同じ挙動になります。
※ より正確には commit の完了が待たされます
そのため、実行に長時間かかることが予想されるDDL（巨大テーブルへのALTER TABLEなど）は、サービス稼働中には実施しない、メンテナンス期間を設けるなどの工夫が必要となります。
- wsrep_OSU_method = RSU
RSU（Rolling Schema Upgrade）はもう1つのDDL実行方式です。同設定を有効にすると、DDLが実行されている間のみ、ノードがクラスターから切り離され、他ノードへの更新クエリの送受信を受付しなくなります。そのため、DDLを実行してもそれが他ノードに伝播せず、該当ノードのみに反映します。その後、RSUを無効にするとクラスタから切り離されていた間実行された更新差分が適用され、クラスタに復帰します（※）。
※ この時の挙動はIST方式と同じであるため、Galera Cacheが十分なサイズ用意されていない場合はSSTが作動し変更が上書きされます
DDLが反映されるのは、RSUを実行したノードのみとなりますのでDDLの内容によっては、ノード間の整合性が保てなくなる可能性があります。特に「カラム追加」などの場合は、クエリの書き方によってはエラーとなる可能性があります。
[code lang=text] 例：id, col1, col2 という3つのカラムを持つテーブルに対して、new_colカラムを追加する場合、以下のようなINSERTはカラムの数が一致しないエラーとなります。
|
1 |
mysql> INSERT INTO test_table VALUES (100, "AAA", "2019-08-01"); |
一方、以下のようにカラムを明示的に指定していればエラーは起きません。
|
1 |
mysql> INSERT INTO test_table (col1, col2) VALUES ("AAA", "2019-08-01"); |
また、カラムのデータ型を変更した場合はslave_type_conversions変数の制限にひっかかり、Galeraレプリケーションが停止してしまうこともあります。
TOI と RSU の使い分け
メンテナンス時間などPXC側で更新クエリが実行できない時間帯があっても許容されるケース、もしくはDDL自体が短時間で完了することが分かるケースでは、デフォルトの wsrep_osu_method = TOI でDDLを実行して問題ありません（ただし、完了するのに長時間かかるALTER TABLEには注意が必要です）。
一方、更新クエリの実行件数が多く、かつ停止時間が許容できないような場合は、RSUを使って1ノードずつDDLを発行していくことが推奨されます。なお、作業中はクラスタからノードが1台減った状態（3ノード構成なら2ノード）になるので、この時にノード障害が発生すると復旧が少し複雑になる可能性があります。
pt-online-schema-change について
上記の TOI / RSU 以外に、pt-online-schema-change（以下、pt-osc）を利用する方法もあります。
pt-osc は、その名の通り、更新クエリをブロックすることなくオンラインでDDLを実行することができるツールで、Percona社が開発する Percona Toolkitに含まれています。
pt-osc の仕組みは以下の通りです。
- pt-oscの対象となるテーブルをコピーする
→ 対象テーブルを TABLE01 、コピーテーブルを TABLE01_new とする - TABLE01_newに対して、DDLを実行する（カラム追加、インデックス追加など）
- TABLE01 に対する INSERT / DELETE / UPDATE の内容を TABLE01_new にトレースするトリガを作成する
- TABLE01_new でDDLが実行している間に、TABLE01で更新されたレコードの情報をコピーする
- DDLが完了したら、RENAMEコマンドで TABLE01 と TABLE01_new の名前を入れ替える
- 旧TABLE01 とトリガを削除する
pt-osc の特徴は、オンラインDDLが実装されていないMySQL5.5以前でも「オンラインDDLと同等の効果」が実現できることです。ただし、必ずテーブルコピーが必要な点、またトリガーによるレコード更新が常に発生する点などから、サーバ負荷は通常のDDLよりも高くなってしまいます。
pt-osc にできること/できないこと
【できること】
- 更新トランザクションをブロックしないDDL実行
- DDLの制御（–max-load や –max-flow-ctl オプションなど）
【できないこと】
- DDL実行中の高負荷回避（テーブルコピー、トリガー）
- 失敗したDDLを途中から再開
PXC と pt-osc の組み合わせ
PXCに対してDDLを実行したいときに、TOI によるトランザクションのブロックも、RSU によるテーブル互換性が崩れることも許容できない場合は、pt-osc を検討すべきです。
pt-oscによる処理は全てバックグラウンドで行われ、他ノードにも伝播するため整合性も保たれます。また、元テーブルでは処理が行われないため、トランザクションがブロックされることもありません。
ただし、以下のような注意点（制限）があります。
- pt-osc は必ずテーブルコピーを伴うため、テーブルサイズと同程度のディスク空き領域が必要です
- wsrep_max_ws_sizeの設定がpt-oscが必要とする更新サイズを下回る場合、pt-oscがエラーとなる可能性があります
- 元テーブルで既に別のトリガーが定義されている場合、原則としてpt-oscは実行できません
- サーバへの負荷が高くなるため、クエリのパフォーマンスが全体的に落ちる可能性があります
- 上記に起因して、Flow Controlが発生する可能性があります
→ pt-osc実行中のFlow Controlを制御するため、–max-flow-ctl オプションが用意されています
なお、MySQLで実行する場合とPXCで実行する場合とで大きな違いはありません。
まとめ
PXCでDDLを実行する場合は3つの選択肢があり、それぞれ以下のようなメリット・デメリットがあります。
| TOI | RSU | pt-osc | |
|---|---|---|---|
| 利点 | 手順が簡単で、エラーが起こってもリカバリが容易 | ダウンタイムが発生しない | ダウンタイムが発生しない |
| 欠点 | トランザクションがブロックされる時間（ダウンタイム）が発生する | 後方互換性が担保されていないDDLが実行できない | DBへの負荷が大きい |
| 適した操作 | 短時間で完了するDDL メンテナンス時間中に実行するDDL |
インデックス追加などクエリの互換性に影響を与えないDDL | ダウンタイムが許容できないDDL |
デモ
以下のデモでは、dbdeployerで構築したPXC3ノード環境を利用します。
★TOI のデモ
1. 大きなテストテーブルを作成する
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
node1> CREATE TABLE test.large_tbl (id int primary key auto_increment, col1 int, col2 varchar(30), col3 text); node1> INSERT INTO test.large_tbl (col1, col2, col3) SELECT CEIL(RAND() * 10000), SUBSTRING(MD5(RAND()), 1, 30), SUBSTRING(MD5(RAND()), 1, 50); node1> INSERT INTO test.large_tbl (col1, col2, col3) SELECT CEIL(RAND() * 10000), SUBSTRING(MD5(RAND()), 1, 30), SUBSTRING(MD5(RAND()), 1, 50) FROM test.large_tbl; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 ... 繰り返し node1> INSERT INTO test.large_tbl (col1, col2, col3) SELECT CEIL(RAND() * 10000), SUBSTRING(MD5(RAND()), 1, 30), SUBSTRING(MD5(RAND()), 1, 50) FROM test.large_tbl; Query OK, 524288 rows affected (26.92 sec) Records: 524288 Duplicates: 0 Warnings: 0 node1> select count(*) from test.large_tbl; +----------+ | count(*) | +----------+ | 1048576 | +----------+ 1 row in set (2.39 sec) node1> select * from test.large_tbl limit 3; +----+------+--------------------------------+----------------------------------+ | id | col1 | col2 | col3 | +----+------+--------------------------------+----------------------------------+ | 2 | 1294 | 84fb7bdf93c13a4d2d5715b183b125 | 04a51feb180bbb9c84e53e50dd86f5db | | 5 | 1879 | cf7df40cf630b98b4520010e3d8825 | da9c6fcc5d3651a4fdfb4141203480fb | | 8 | 5400 | 5efc0f2786a17dce67aeb2ae9fcbaa | 36b40aeb446c9da1618aaa88f4f1d2c4 | +----+------+--------------------------------+----------------------------------+ 3 rows in set (0.00 sec) |
2. もう1つ同様のテストテーブルを作成する
|
1 2 3 4 5 |
node1> CREATE TABLE test.large_tbl_2 LIKE test.large_tbl; node1> INSERT INTO test.large_tbl_2 SELECT * FROM test.large_tbl LIMIT 1000; Query OK, 1000 rows affected (0.08 sec) Records: 1000 Duplicates: 0 Warnings: 0 |
4. wsrep_osu_method=TOIの状態で、インデックスを追加するDDLを実行
ADD INDEXするだけであれば、通常のMySQLの場合はオンラインDDLとなり、更新クエリ / 参照クエリが両方とも実行できますが、PXCではオンラインDDLにはなりません。そのため、DDLの実行中はすべてのトランザクションがCOMMIT待機状態になります。
|
1 2 3 4 5 6 7 8 9 10 |
node1> SHOW GLOBAL VARIABLES LIKE "wsrep_osu_method"; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | wsrep_OSU_method | TOI | +------------------+-------+ 1 row in set (0.01 sec) node1> ALTER TABLE test.large_tbl ADD INDEX new_idx (col2); ... |
5. ALTER実行中に新しいクライアントを開いて、large_tblに対して SELECT / UPDATE を実行
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
node1> SELECT * FROM test.large_tbl LIMIT 5; +----+------+--------------------------------+----------------------------------+ | id | col1 | col2 | col3 | +----+------+--------------------------------+----------------------------------+ | 2 | 1294 | 84fb7bdf93c13a4d2d5715b183b125 | 04a51feb180bbb9c84e53e50dd86f5db | | 5 | 1879 | cf7df40cf630b98b4520010e3d8825 | da9c6fcc5d3651a4fdfb4141203480fb | | 8 | 5400 | 5efc0f2786a17dce67aeb2ae9fcbaa | 36b40aeb446c9da1618aaa88f4f1d2c4 | | 11 | 1257 | 41f5ee376ee2b7578590cd2b99442c | 69b37639b74db55459c4e8bb2d765084 | | 17 | 4685 | 8dfb6268dd1fa18251063c9e6c5dd1 | 661d788e53321da6c32ea33ef48250e6 | +----+------+--------------------------------+----------------------------------+ 5 rows in set (0.02 sec) node1> UPDATE test.large_tbl SET col3 = "updated" LIMIT 1; → 待ち状態になる |
6. ALTER実行中に新しいクライアントを開いて、large_tbl_2に対して SELECT / UPDATE を実行
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
node1> SELECT * FROM test.large_tbl_2 LIMIT 5; +----+------+--------------------------------+----------------------------------+ | id | col1 | col2 | col3 | +----+------+--------------------------------+----------------------------------+ | 2 | 1294 | 84fb7bdf93c13a4d2d5715b183b125 | 04a51feb180bbb9c84e53e50dd86f5db | | 5 | 1879 | cf7df40cf630b98b4520010e3d8825 | da9c6fcc5d3651a4fdfb4141203480fb | | 8 | 5400 | 5efc0f2786a17dce67aeb2ae9fcbaa | 36b40aeb446c9da1618aaa88f4f1d2c4 | | 11 | 1257 | 41f5ee376ee2b7578590cd2b99442c | 69b37639b74db55459c4e8bb2d765084 | | 17 | 4685 | 8dfb6268dd1fa18251063c9e6c5dd1 | 661d788e53321da6c32ea33ef48250e6 | +----+------+--------------------------------+----------------------------------+ 5 rows in set (0.02 sec) node1> UPDATE test.large_tbl_2 SET col3 = "updated"; → DDL実行中のテーブル以外へのトランザクションであっても待ち状態になる |
この時、PROCESSLISTを見ると “wsrep: initiating pre-commit for write set”状態でトランザクションが待たされているのが分かります。
|
1 2 3 4 5 6 7 8 9 |
node1> show processlist; +----+-------------+-----------+------+---------+---------+-------------------------------------------------+---------------------------------------------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +----+-------------+-----------+------+---------+---------+-------------------------------------------------+---------------------------------------------------------+-----------+---------------+ ... | 12 | root | localhost | NULL | Query | 13 | altering table | ALTER TABLE test.large_tbl ADD INDEX new_idx (col2) | 0 | 0 | | 13 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 | | 14 | root | localhost | NULL | Query | 10 | wsrep: initiating pre-commit for write set (86) | UPDATE test.large_tbl_2 SET col3 = "updated" | 0 | 1000 | +----+-------------+-----------+------+---------+---------+-------------------------------------------------+---------------------------------------------------------+-----------+---------------+ |
※ トランザクションがブロックされるのは「DDLの実行中」のみです
※ DDLの完了後、自動で待ち状態だったトランザクションが適用されます
★RSU のデモ
1. 新しいテストデータを作成
RSU用に新しいテストテーブルを作成します。
|
1 2 |
node1> CREATE TABLE test.rsu_tbl LIKE test.large_tbl; node1> INSERT INTO test.rsu_tbl VALUE (10, 200, "new_data", "TEST DATA"), (11, 300, "new_data_2", "TEST DATA_2"),(12, 400, "new_data_3", "TEST DATA_3"); |
2. Node2で、RSU形式のDDLを実行しnew_colカラムを追加
|
1 2 3 4 5 6 7 8 |
node2> SET GLOBAL wsrep_OSU_method=RSU; node2> EXIT; ※node2のmysqldに再接続 node2> ALTER TABLE test.rsu_tbl ADD COLUMN new_col int; node2> SET GLOBAL wsrep_OSU_method=TOI; node2> EXIT; |
3. Node1とNode2でテーブル定義に差分ができてることを確認
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
node1> DESC test.rsu_tbl; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | col1 | int(11) | YES | | NULL | | | col2 | varchar(30) | YES | | NULL | | | col3 | text | YES | MUL | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) node2> DESC test.rsu_tbl; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | col1 | int(11) | YES | | NULL | | | col2 | varchar(30) | YES | | NULL | | | col3 | text | YES | MUL | NULL | | | new_col | int(11) | YES | | NULL | | +---------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) |
4. 互換性のないINSERTを実行するとエラーとなることを確認
|
1 2 3 4 5 |
node2> INSERT INTO test.rsu_tbl VALUE (15, 1000, "add_data", "ADD TEST DATA"); ERROR 1136 (21S01): Column count doesn't match value count at row 1 以下のようにカラム名を明示的に指定したクエリは実行できる node2> INSERT INTO test.rsu_tbl(id, col1, col2, col3) VALUE (15, 1000, "add_data", "ADD TEST DATA"); |
★pt-osc のデモ
1. テストデータを準備
wsrep_osu_method=TOI のデモで使ったテーブルと同じものを準備する
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
node1> TRUNCATE test.large_tbl; node1> INSERT INTO test.large_tbl (col1, col2, col3) SELECT CEIL(RAND() * 10000), SUBSTRING(MD5(RAND()), 1, 30), SUBSTRING(MD5(RAND()), 1, 50); node1> INSERT INTO test.large_tbl (col1, col2, col3) SELECT CEIL(RAND() * 10000), SUBSTRING(MD5(RAND()), 1, 30), SUBSTRING(MD5(RAND()), 1, 50) FROM test.large_tbl; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 ... node1> INSERT INTO test.large_tbl (col1, col2, col3) SELECT CEIL(RAND() * 10000), SUBSTRING(MD5(RAND()), 1, 30), SUBSTRING(MD5(RAND()), 1, 50) FROM test.large_tbl; Query OK, 524288 rows affected (26.92 sec) Records: 524288 Duplicates: 0 Warnings: 0 node1> select count(*) from test.large_tbl; +----------+ | count(*) | +----------+ | 1048576 | +----------+ |
2. pt-oscをセットアップ
Percona Toolkit をインストールする、もしくはpt-oscのみをダウンロードするの2通りの方法があります。
ここでは、後者を採用しています。
|
1 2 3 4 5 |
$ wget percona.com/get/pt-online-schema-change $ chmod +x ./pt-online-schema-change ※ pt-oscはperl-DBD-MySQLパッケージを必要とします $ sudo yum install -y perl-DBD-MySQL |
3. pt-oscでPKの削除・再作成を行う
上記マニュアルの通り、「Primary Keyの削除」はMySQLであってもオンラインDDLには該当しないため、更新トランザクションはブロックされます。
しかし、pt-oscであればその制限を回避することができます。
|
1 |
$ ./pt-online-schema-change --user=root --password=msandbox --host=127.0.0.1 --port=26327 --alter "DROP PRIMARY KEY, ADD PRIMARY KEY(id, col1)" --execute D=test,t=large_tbl --no-check-alter |
4. 別クライアントを開き、テーブルを更新できることを確認
|
1 2 3 |
node1> INSERT INTO test.large_tbl (col1, col2, col3) SELECT CEIL(RAND() * 10000), SUBSTRING(MD5(RAND()), 1, 30), SUBSTRING(MD5(RAND()), 1, 50) FROM test.large_tbl LIMIT 1; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 |
まとめ
PXC（Galera Cluster）は原則としてMySQLとの互換性が担保されており、MySQLと同じ操作性で利用できます。
しかし、いくつかの細かい挙動の違いはあり、DDLもその一つです。本番稼働中のPXCでDDLを実行する場合は注意してください。
