テーブルにカラムやインデックスを追加するといった、いわゆるスキーマの変更を行うときは、通常、サービスをメンテナンスに入れてから作業をしなくてはいけません。理由は、ALTER TABLE実行中は共有ロックがかかってしまうため、更新クエリを実行しても即座に完了しなくなるからです。そうすると、アプリからみればおそらく更新クエリを発行するページではHTTPタイムアウトになりますし、参照だけのページでもかなり遅くなることでしょう。
サービスの改良をすると必ずスキーマ変更が必要になりますが、しかしサービスは可能な限り24時間365日提供したいもの。Percona Toolkitの pt-online-schema-change はそんな悩みを払拭し、サービスを停止すること無くスキーマの変更を可能にしてくれます。
概要と仕組み
Percona Toolkitは全てPerlスクリプトなので、/usr/bin/pt-online-schema-change を見れば詳細を確認できます。仕組みを簡単に説明すると、- 既存テーブルのスキーマをコピーして新規テーブルを作成
- 新規テーブルにスキーマ変更を適用
- トリガーを作成して既存テーブルへの変更を新規テーブルに反映される状態に
- 既存テーブルから新規テーブルへデータをコピー
- コピーが完了したらRENAMEにより既存テーブルをどけて新規テーブルを正規の名前にする
- 旧テーブルとトリガーを削除する
この手順でやればオンラインで実行できますよ、という手順を１つのスクリプトにしてくれただけなので、XtraDBである必要はありません。軽くまとめると、
メリット
デメリット
実行実績
実行時間
1GB / 440万行 のテストデータに対する ADD COLUMN で、他クエリの実行なしでとある本番サーバの約1億行に対する実行では、
サービスへの影響
オンラインと銘打っても、重たい処理に変わりはないのでサービスへの影響はあります。CPUはもちろん、特にDiskI/Oが高くなり、その度合は後述する実際の実行クエリで感触を掴んでもらえればと思います。具体的には、ある本番サーバでの例として、
もちろん、現象や度合いについては環境次第になるでしょうが、影響は少なからずあることを知り、影響をより少なくする努力をすること。そして場合によっては無理せずメンテナンス状態で実行するという判断をすることが大切になります。
使い方
このsysbenchで作成したテーブルの、Field c の後ろに Field new_column を追加するとします。|
1 2 3 4 5 6 7 8 9 |
mysql> desc sbtest; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | k | int(10) unsigned | NO | MUL | 0 | | | c | char(120) | NO | | | | | pad | char(60) | NO | | | | +-------+------------------+------+-----+---------+----------------+ |
接続情報と、D=dbname, t=tablename、そして –after にALTER TABLE tablename に続くSQLを書きます。
そして最初は –dry-run でテストします。
これは CRAETE TABLE と ALTER TABLE まで確かめて終了するオプションで、既に存在するカラム名やインデックスといった場合にエラーを返してくれます。
|
1 2 3 4 |
pt-online-schema-change \ --host=localhost -u root --ask-pass \ D=sbtest,t=sbtest --charset=utf8 \ --alter "ADD COLUMN new_column INTEGER DEFAULT NULL AFTER c" --dry-run |
問題なければ、–dry-run を –execure に変更して実行します。
|
1 2 3 4 |
pt-online-schema-change \ --host=localhost -u root --ask-pass \ D=sbtest,t=sbtest --charset=utf8 \ --alter "ADD COLUMN new_column INTEGER DEFAULT NULL AFTER c" --execute |
完了したら、スキーマの確認をします。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> desc sbtest; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | k | int(10) unsigned | NO | MUL | 0 | | | c | char(120) | NO | | | | | new_column | int(11) | YES | | NULL | | | pad | char(60) | NO | | | | +------------+------------------+------+-----+---------+----------------+ |
実行内容の確認
何が実行されたかは、スクリプトを読むよりも、general_log ON にしてから実行する方が手っ取り早いです。これは 1GB 440万行 のテストデータに対するログです。
|
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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |
Connect root@localhost on sbtest Query set autocommit=1 Query SELECT @@SQL_MODE Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/ Query /*!40101 SET NAMES "utf8"*/ Query SET wait_timeout=10000 Query SELECT @@hostname, @@server_id Query SET SESSION innodb_lock_wait_timeout=1 Query SHOW VARIABLES LIKE 'version%' Query SHOW ENGINES Query SHOW VARIABLES LIKE 'innodb_version' Query SELECT @@SERVER_ID Query SHOW GRANTS FOR CURRENT_USER() Query SHOW PROCESSLIST Query SHOW SLAVE HOSTS Query SHOW GLOBAL STATUS LIKE 'Threads_running' Query SHOW GLOBAL STATUS LIKE 'Threads_running' Query SHOW TABLES FROM `sbtest` LIKE 'sbtest' Query SHOW TRIGGERS FROM `sbtest` LIKE 'sbtest' Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ Query USE `sbtest` Query SHOW CREATE TABLE `sbtest`.`sbtest` Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ Query EXPLAIN SELECT * FROM `sbtest`.`sbtest` WHERE 1=1 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='sbtest' AND referenced_table_name='sbtest' Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ Query USE `sbtest` Query SHOW CREATE TABLE `sbtest`.`sbtest` Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ # 新規テーブル作成 Query CREATE TABLE `sbtest`.`_sbtest_new` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=4400001 DEFAULT CHARSET=utf8 # 新規テーブル編集 Query ALTER TABLE `sbtest`.`_sbtest_new` ADD COLUMN new_column INTEGER DEFAULT NULL AFTER c Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */ Query USE `sbtest` Query SHOW CREATE TABLE `sbtest`.`_sbtest_new` Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */ # トリガー作成 Query CREATE TRIGGER `pt_osc_sbtest_sbtest_del` AFTER DELETE ON `sbtest`.`sbtest` FOR EACH ROW DELETE IGNORE FROM `sbtest`.`_sbtest_new` WHERE `sbtest`.`_sbtest_new`.`id` <=> OLD.`id` Query CREATE TRIGGER `pt_osc_sbtest_sbtest_upd` AFTER UPDATE ON `sbtest`.`sbtest` FOR EACH ROW REPLACE INTO `sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) Query CREATE TRIGGER `pt_osc_sbtest_sbtest_ins` AFTER INSERT ON `sbtest`.`sbtest` FOR EACH ROW REPLACE INTO `sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`) # 以下データのコピー Query EXPLAIN SELECT * FROM `sbtest`.`sbtest` WHERE 1=1 Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lower boundary*/ Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) WHERE `id` IS NOT NULL ORDER BY `id` LIMIT 1 /*key_len*/ Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `sbtest`.`sbtest` FORCE INDEX (`PRIMARY`) WHERE `id` >= '1' /*key_len*/ Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 1000, 2 /*next chunk boundary*/ Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/ Query EXPLAIN SELECT `id`, `k`, `c`, `pad` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '1000')) /*explain pt-online-schema-change 25649 copy nibble*/ Query INSERT LOW_PRIORITY IGNORE INTO `sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '1000')) /*pt-online-schema-change 25649 copy nibble*/ Query SHOW WARNINGS Query SHOW GLOBAL STATUS LIKE 'Threads_running' Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) ORDER BY `id` LIMIT 22405, 2 /*next chunk boundary*/ Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) ORDER BY `id` LIMIT 22404, 2 /*next chunk boundary*/ Query EXPLAIN SELECT `id`, `k`, `c`, `pad` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) AND ((`id` <= '23405')) /*explain pt-online-schema-change 25649 copy nibble*/ Query INSERT LOW_PRIORITY IGNORE INTO `sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1001')) AND ((`id` <= '23405')) /*pt-online-schema-change 25649 copy nibble*/ Query SHOW WARNINGS Query SHOW GLOBAL STATUS LIKE 'Threads_running' # ~snip~ ↑のINSERT含む下6行のループ107回分を省略 Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4323651')) ORDER BY `id` LIMIT 39855, 2 /*next chunk boundary*/ Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4323651')) ORDER BY `id` LIMIT 39854, 2 /*next chunk boundary*/ Query EXPLAIN SELECT `id`, `k`, `c`, `pad` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4323651')) AND ((`id` <= '4363505')) /*explain pt-online-schema-change 25649 copy nibble*/ Query INSERT LOW_PRIORITY IGNORE INTO `sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4323651')) AND ((`id` <= '4363505')) /*pt-online-schema-change 25649 copy nibble*/ Query SHOW WARNINGS Query SHOW GLOBAL STATUS LIKE 'Threads_running' Query EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4363506')) ORDER BY `id` LIMIT 40224, 2 /*next chunk boundary*/ Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4363506')) ORDER BY `id` LIMIT 40223, 2 /*next chunk boundary*/ Query SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) ORDER BY `id` DESC LIMIT 1 /*last upper boundary*/ Query EXPLAIN SELECT `id`, `k`, `c`, `pad` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4363506')) AND ((`id` <= '4400000')) /*explain pt-online-schema-change 25649 copy nibble*/ Query INSERT LOW_PRIORITY IGNORE INTO `sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '4363506')) AND ((`id` <= '4400000')) /*pt-online-schema-change 25649 copy nibble*/ Query SHOW WARNINGS Query SHOW GLOBAL STATUS LIKE 'Threads_running' # テーブル入れ替え Query RENAME TABLE `sbtest`.`sbtest` TO `sbtest`.`_sbtest_old`, `sbtest`.`_sbtest_new` TO `sbtest`.`sbtest` # お掃除 Query DROP TABLE IF EXISTS `sbtest`.`_sbtest_old` Query DROP TRIGGER IF EXISTS `sbtest`.`pt_osc_sbtest_sbtest_del` Query DROP TRIGGER IF EXISTS `sbtest`.`pt_osc_sbtest_sbtest_upd` Query DROP TRIGGER IF EXISTS `sbtest`.`pt_osc_sbtest_sbtest_ins` Query SHOW TABLES FROM `sbtest` LIKE '\_sbtest\_new' Quit |
ロックの確認
通常の ALTER TABLE と pt-online-schema-change の処理中それぞれで発行した更新クエリがどうなっているか確認しておきます。まず、直接ALTER TABLEを実行しつつ更新クエリを発行すると
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# 1つ目のシェルでスキーマ変更 mysql> ALTER TABLE sbtest ADD COLUMN new_column INTEGER DEFAULT NULL AFTER c; # 2, 3つ目のシェルでINSERT, UPDATEを実行しつつ # 4つ目で実行中クエリを確認 # 共有ロックにより待機中となっている mysql> SHOW PROCESSLIST; +----+---------+------+---------------------------------+-----------------------------------------------------------------------+-----------+---------------+-----------+ | Id | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read | +----+---------+------+---------------------------------+-----------------------------------------------------------------------+-----------+---------------+-----------+ | 55 | Query | 0 | NULL | SHOW PROCESSLIST | 0 | 0 | 6 | | 71 | Query | 40 | copy to tmp table | ALTER TABLE sbtest ADD COLUMN new_column INTEGER DEFAULT NULL AFTER c | 0 | 0 | 3056182 | | 73 | Execute | 38 | Waiting for table metadata lock | UPDATE sbtest set c='949692053-612769513-998075866-57522370-805218651 | 0 | 0 | 1 | | 74 | Query | 12 | Waiting for table metadata lock | INSERT INTO sbtest (k, c, pad) VALUES (101, 'A', 'B') | 0 | 0 | 1 | +----+---------+------+---------------------------------+-----------------------------------------------------------------------+-----------+---------------+-----------+ |
次に、pt-online-schema-change 実行中に更新クエリを発行
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
# 1つ目のシェルでPT実行 pt-online-schema-change \ --host=localhost -u root --ask-pass \ D=sbtest,t=sbtest --charset=utf8 \ --alter "ADD COLUMN new_column INTEGER DEFAULT NULL AFTER c" --execute # 2つ目のシェルで更新クエリ発行しまくり # 3つ目で確認 # トリガーによる REPLACE INTO が通り、ロックされない mysql> SHOW PROCESSLIST; +----+---------+------+--------------+---------------------------------------------------------------------------------------+-----------+---------------+-----------+ | Id | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read | +----+---------+------+--------------+---------------------------------------------------------------------------------------+-----------+---------------+-----------+ | 55 | Query | 0 | NULL | SHOW PROCESSLIST | 0 | 0 | 6 | | 65 | Query | 1 | Sending data | INSERT LOW_PRIORITY IGNORE INTO `sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`) SELECT | 0 | 0 | 23393 | | 67 | Execute | 1 | update | REPLACE INTO `sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k` | 0 | 0 | 1 | +----+---------+------+--------------+---------------------------------------------------------------------------------------+-----------+---------------+-----------+ |
更新クエリが通ることが確認できました。
あとは変更内容に応じて、RENAME の後でAPサーバからのクエリがエラーにならないような内容にしておけばOKです。
実行するのは簡単なツールですが、内容を正しく理解しておかないと予期せぬ状態になったり、不測の事態に対応できないので、おいそれと利用するものではないです。でもこのツールを通じてSQLの理解を深められるし、便利だしなので、なんにせよ使ってみたらいいツールだと思います。
先日の、MySQL Connect 2012で ALTER TABLE がオンラインでできるようになる、と発表されたのでネタが腐る前に掲載してみました。
仕組みとしては似ているようですが、どんなものか楽しみですね。
1) Wait for all transactions that have modified the table to complete.
• Block any transactions that try to modify the table after the ALTER begins.
• Allow SELECTs from the table, though.
2) Create a temporary table with the new definition, and copy data from the old table to the new table.
3) Wait for all remaining transactions against the table to complete, and swap the old and the new table.
• Block any transactions that try to modify the table after the ALTER begins.
• Allow SELECTs from the table, though.
2) Create a temporary table with the new definition, and copy data from the old table to the new table.
3) Wait for all remaining transactions against the table to complete, and swap the old and the new table.
