MySQLでサービス停止のないALTER TABLEの検討
MySQLでテーブルへのカラム追加、インデックス追加やテーブルの再編成などを行うと、その間テーブルに共有ロックがかかってしまいます。そのためこれらのメンテナンス処理は、通常利用者の少ない深夜早朝帯にサービスを止めて実施する必要があります。本日はそれを無停止、オンラインのままでできないかという話題です。
基本的なアイデア
- メンテナンス対象の元テーブルをコピーして、作業用の仮テーブルを作ります
- 仮テーブルに対して、カラム追加などの変更を加えます
- その間、元テーブルに対して行われる更新処理について差分を記録しておきます
- 仮テーブルの変更が終わったら、記録しておいた差分データを仮テーブルに反映します
- 差分データの反映が終わったら、元テーブルと仮テーブルを入れ替えます
これと似たようなことを考えた方は結構いらっしゃるのではないでしょうか。ただ、言うは易し、行うは難しです。整合性がきちんと取れるかどうか分からない、こんな危険なことはできないというのが普通の反応だと思います。
openark kit
このアイデアを実装したプログラムがあります。openark kitのoak-online-alter-tableです。
openark kitはイスラエル在住のMySQLコンサルタント、Shlomi Noach氏が開発したMySQLユーティリティ集です。10種類のプログラムから構成されており、すべてPythonで書かれています。ライセンスはBSDです。類似のMySQLユーティリティ集としてはMaatkitが有名ですが、Webで探すとそれ以外にもさまざまなソフトウェアが見つかります。
oak-online-alter-tableの仕組み
oak-online-alter-tableがどのようにしてオンラインのままテーブル定義を変更するのか、その仕組みを確認していきましょう。これはMySQLの一般クエリログをONにしてプログラムを実行することで、簡単に調べることができます。oak-online-alter-tableの仕組みは、実際には上で述べた基本的なアイデアよりも少し複雑になっています。
1. 空の仮テーブルを作成する
CREATE TABLE … LIKE文を用いて、元テーブルと同じ構造の仮テーブルを作成します。データのコピーはまだ行いません。
CREATE TABLE sbtest.__oak_sbtest LIKE sbtest.sbtest;
2. 仮テーブルに変更を加える
仮テーブルに対し、カラム追加、インデックス追加などの変更を加えます。
ALTER TABLE sbtest.__oak_sbtest ADD test VARCHAR(10);
3. 元テーブルにトリガを作成する
元テーブルに対してAFTER INSERT、AFTER UPDATE、AFTER DELETEの3つのトリガを作成します。これらのトリガによって、元テーブルに対する更新が仮テーブルに伝播するようになります。MySQLのトリガはそれぞれのイベントに対して一つしか作成できないため、すでにトリガの存在するテーブルに対してoak-online-alter-tableを使用することはできません。
CREATE TRIGGER sbtest.sbtest_AI_oak AFTER INSERT ON sbtest.sbtest FOR EACH ROW
REPLACE INTO sbtest.__oak_sbtest (c, pad, k, id) VALUES (NEW.c, NEW.pad, NEW.k, NEW.id);
//
CREATE TRIGGER sbtest.sbtest_AU_oak AFTER UPDATE ON sbtest.sbtest FOR EACH ROW
BEGIN
DELETE FROM sbtest.__oak_sbtest WHERE (id) = (OLD.id);
REPLACE INTO sbtest.__oak_sbtest (c, pad, k, id) VALUES (NEW.c, NEW.pad, NEW.k, NEW.id);
END;
//
CREATE TRIGGER sbtest.sbtest_AD_oak AFTER DELETE ON sbtest.sbtest FOR EACH ROW
DELETE FROM sbtest.__oak_sbtest WHERE (id) = (OLD.id);
//それぞれのトリガを見てみると、INSERTイベントに対してはREPLACE文、UPDATEイベントに対してはDELETEおよびREPLACE文、DELETEイベントに対してはDELETE文が発行されています。UPDATEイベントに対して一旦DELETEを行っているのは、主キーを更新するSQLに対応するためです。
4. 元テーブルから仮テーブルにレコードをコピーする
INSERT IGNORE文を用いて、元テーブルのレコードを仮テーブルにコピーします。仮テーブルにすでにトリガによってコピーされたレコードがある場合、INSERT IGNORE文はエラーを返すことなく単にそのレコードを無視します。
INSERT IGNORE INTO sbtest.__oak_sbtest (c, pad, k, id)
(SELECT c, pad, k, id FROM sbtest.sbtest
WHERE (((id > @unique_key_range_start_0) OR ((id = @unique_key_range_start_0)))
AND ((id < @unique_key_range_end_0) OR ((id = @unique_key_range_end_0))))
LOCK IN SHARE MODE)oak-online-alter-tableはすべてのレコードを一度にコピーするのではなく、デフォルトで1,000レコードずつコピーを行います。こうすることで、アプリケーション側の処理がロック待ちのタイムアウトで失敗することを防ごうとしています。また、システムが過負荷になることを防ぐため、1,000レコード処理するごとにスリープ時間を設けることもできるようになっています。
5. 元テーブルで削除されたレコードを、仮テーブルから削除する
INSERT IGNORE文によるレコードのコピーには、一つだけ元テーブルと仮テーブルの間に不整合を生じさせるパターンがあります。それは次の図に示すような、DELETE文とINSERT IGNORE文が同時に発生するパターンです。
DELETE文によるレコードの削除がCOMMITされる前にINSERT IGNORE文が発行されると、一度は削除されたレコードがINSERT IGNORE文によって復活してしまいます。これに対処するため、oak-online-alter-tableはレコードのコピー後にもう一度テーブルをスキャンし、元テーブルで削除されているレコードを仮テーブルからも削除します。
DELETE FROM sbtest.__oak_sbtest
WHERE (((id > @unique_key_range_start_0) OR ((id = @unique_key_range_start_0)))
AND
((id < @unique_key_range_end_0) OR ((id = @unique_key_range_end_0))))
AND (id) NOT IN
(SELECT id FROM sbtest.sbtest
WHERE (((id > @unique_key_range_start_0) OR ((id = @unique_key_range_start_0)))
AND
((id < @unique_key_range_end_0) OR ((id = @unique_key_range_end_0))))
)
6. 元テーブルと仮テーブルをリネームし、旧：元テーブルを削除する
RENAME TABLE文を用いてテーブルの名前を変更します。MySQLのRENAME TABLE文は、複数のテーブルに対してアトミックに名称変更を行うことができます。最後に旧：元テーブルを削除して、作業完了です。
RENAME TABLE sbtest.sbtest TO sbtest.__arc_sbtest,
sbtest.__oak_sbtest TO sbtest.sbtest;
DROP TABLE IF EXISTS sbtest.__arc_sbtest;
修正パッチ
現在公開されているopen arkバージョン111のoak-online-alter-tableには、テーブルの一意キーが数値型(TYNYINT、SMALLINT、INT、BIGINT)の場合に内部で型変換のエラーが発生してしまうという不具合があります。また、テーブルの情報を取得するために最初にごく短い時間テーブルロックをかけるのですが、更新負荷の高い状況ではまれにトリガの存在するテーブルに対するLOCK TABLESが失敗するという問題があります。これら二つの問題に対処する修正パッチを作成したので、オリジナル版が改修されるまでの間、当面このパッチをご利用いただければと思います。
*** oak-online-alter-table_org 2010-07-03 19:42:08.000000000 +0900
--- oak-online-alter-table 2010-07-03 20:04:25.000000000 +0900
***************
*** 726,732 ****
AS ratio_complete
"""
ratio_complete = float(get_row(ratio_complete_query)["ratio_complete"])
! verbose("%s range (%s), (%s), %s" % (description, ",".join(unique_key_range_start_values), ",".join(unique_key_range_end_values), get_progress_and_eta_presentation(elapsed_times, elapsed_time, ratio_complete)))
elif unique_key_type == "temporal":
ratio_complete_query = """
SELECT
--- 726,732 ----
AS ratio_complete
"""
ratio_complete = float(get_row(ratio_complete_query)["ratio_complete"])
! verbose("%s range (%s), (%s), %s" % (description, ",".join(map(str, unique_key_range_start_values)), ",".join(map(str, unique_key_range_end_values)), get_progress_and_eta_presentation(elapsed_times, elapsed_time, ratio_complete)))
elif unique_key_type == "temporal":
ratio_complete_query = """
SELECT
***************
*** 935,942 ****
shared_columns = get_shared_columns()
- create_custom_triggers()
lock_tables_write()
unique_key_min_values, unique_key_max_values, range_exists = get_unique_key_range()
unlock_tables()
--- 935,942 ----
shared_columns = get_shared_columns()
lock_tables_write()
+ create_custom_triggers()
unique_key_min_values, unique_key_max_values, range_exists = get_unique_key_range()
unlock_tables()
動作確認
実際に、oak-online-alter-tableの動作を確認してみましょう。
- 試験プログラム：JdbcRunner 1.1＋Tiny SysBench
- CPU：Pentium4 2.8GHz HTなし
- RAM：DDR-266 SDRAM 1GB
- HDD：IDE 3.5inch 7,200rpm 80GB
- OS：CentOS 5.5 32bit
- RDBMS：MySQL Community Server 5.1.48＋InnoDB Plugin 1.0.9
今回はまずSysBenchのテーブルを用い、これに対して参照のみを行うトランザクションを1スレッド、更新を行うトランザクションを1スレッド立ち上げて、50ミリ秒間隔で繰り返し負荷をかけます。そして負荷テストの途中でテーブルの再編成を行い、スループットがどのように変化するかを確認します。SysBenchのテストデータはデフォルトで1万レコードとなっていますが、このテストでは10倍の10万レコードを用意しています。
負荷テスト開始10秒後にALTER TABLE文によるテーブル再編成をかけます。
mysql> ALTER TABLE sbtest ENGINE = InnoDB; Query OK, 100000 rows affected (5.56 sec) Records: 100000 Duplicates: 0 Warnings: 0
この処理には5.56秒かかりました。
続いて、負荷テスト開始30秒後にoak-online-alter-tableによるテーブル再編成をかけます。
$ oak-online-alter-table -u sbtest --ask-pass -S /var/lib/mysql/mysql.sock -d sbtest -t sbtest --sleep=20 -- Connecting to MySQL Password: -- Table sbtest.sbtest is of engine innodb -- Checking for UNIQUE columns on sbtest.sbtest, by which to chunk -- Possible UNIQUE KEY column names in sbtest.sbtest: -- - id -- Table sbtest.__oak_sbtest has been created -- No ALTER statement provided -- Checking for UNIQUE columns on sbtest.__oak_sbtest, by which to chunk -- Possible UNIQUE KEY column names in sbtest.__oak_sbtest: -- - id -- Checking for UNIQUE columns on sbtest.sbtest, by which to chunk -- - Found following possible unique keys: -- - id (int) -- Chosen unique key is 'id' -- Shared columns: c, pad, k, id -- Attempting to lock tables -- Tables locked WRITE -- Created AD trigger -- Created AU trigger -- Created AI trigger -- id (min, max) values: ([1L], [100000L]) -- Tables unlocked -- Copying range (1), (1000), progress: 0% -- Will sleep for 0.02 seconds -- Copying range (1000), (2000), progress: 1% -- Will sleep for 0.02 seconds -- Copying range (2000), (3000), progress: 2% -- Will sleep for 0.02 seconds -- Copying range (3000), (4000), progress: 3% -- Will sleep for 0.02 seconds … -- Deleting range (96000), (97000), progress: 96% -- Will sleep for 0.02 seconds -- Deleting range (97000), (98000), progress: 97% -- Will sleep for 0.02 seconds -- Deleting range (98000), (99000), progress: 98% -- Will sleep for 0.02 seconds -- Deleting range (99000), (100000), progress: 99% -- Will sleep for 0.02 seconds -- Deleting range 100% complete. Number of rows: 0 -- Table sbtest.sbtest has been renamed to sbtest.__arc_sbtest, -- and table sbtest.__oak_sbtest has been renamed to sbtest.sbtest -- Table sbtest.__arc_sbtest was found and dropped -- ALTER TABLE completed
こちらの処理には23.06秒かかりました。
このときSysBenchのスループットは以下のように変化しました。
ALTER TABLE文によるテーブル再編成時はテーブルに共有ロックがかかるため、更新トランザクションが完全に止まってしまうことが分かると思います。一方oak-online-alter-tableによるテーブル再編成では、スループットこそ落ちるものの更新トランザクションが完全に止まることは避けられています。
CPU使用率は以下のように変化しました。
ALTER TABLE文が完全にCPUを使い切ってしまうのに比べ、oak-online-alter-tableはスリープ時間を設定することで負荷の高騰を抑えられていることが分かります。
まとめ
MySQLのALTER TABLE文は現状内部的にテーブルコピーを伴っており、テーブルへのカラム追加、インデックス追加やテーブルの再編成などを行うとテーブルに共有ロックがかかってしまいます。そのためこれら各種メンテナンス作業をオンラインのままで行うことは難しく、これがシステムの無停止運用を妨げる要因の一つとなっていました。こうしたオンラインメンテナンス機能は従来Oracleなど商用RDBMSが得意としてきたところですが、MySQLでもoak-online-alter-tableで示された考え方を応用することで、今後運用性を改善していけるのではないかと思います。
参考ですが、PostgreSQLにも同様の考え方に基づくpg_reorgというテーブル再編成ツールが用意されています。PostgreSQLは追記型アーキテクチャであることから、VACUUMというガベージコレクション処理を運用にどのように組み込むかがとても重要なのですが、pg_reorgによって運用性がかなり改善されました。こうした動きもおさえておきたいですね。
