pt-online-schema-changeについて
CyberXのengineer、K.Mです。
MySQLのTableにカラムやインデックスの追加、修正する際にメンテナンスを入れることがあると思います。
AlterTable実行中は書き込みができないため、普通はメンテナンスを入れる必要があるのですが、
pt-online-schema-changeを使うとAlterTable実行中でも書き込みが可能になります。
本日はこのツールを紹介します。
pt-online-schema-changeはPercona Toolkitの1機能です。
Percona ToolkitはMaatkitとAspersaが元になっています。
実は似たツールが複数あり、米Facebook、MySQL向けのオンラインスキーマ変更ユーティリティ「OSC(Online Schema Change for MySQL)」もその一つです。
http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/tools/files
http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932
pt-online-schema-changeはこのツールを改良したものだそうです。
なお、AlterTable実行時の処理の流れは以下の通りとなります。※InnoDBの場合
・テーブルをTL_WRITE_ALLOW_READロックする。（他のセッションからのREADを許可し、WRITEをブロックする。）
・新しいテーブル定義を使って空のテンポラリテーブルを作成する。
・古いテーブルから新しいテーブルへデータをコピーする。
・テーブルの名前を付け替えて、新しいテーブルを古いテーブルと同じ名前にする。古いテーブルは破棄する。
・新しいテーブルへブロックしていたWRITEをリダイレクトする。
http://nippondanji.blogspot.jp/2009/05/alter-table.html
ALTER TABLEも内部的に作業用テーブルへのINSERT処理を実行するのですね。
以下はpt-online-schema-changeを実行した際のsqlログとなります。
Connect root@localhost on db_name
・オートコミット機能を有効にする
Query set autocommit=1
Query SELECT @@SQL_MODE
・SHOW CREATE TABLE と SHOW CREATE DATABASEでテーブル名とカラム名は引用符で囲う
Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/
・待機状態（アイドル状態）になって○秒経過したら接続を切る。
Query SET wait_timeout=10000
・hostname, server_id取得
Query SELECT @@hostname, @@server_id
・行ロック待ちの秒数をセッション単位で指定
Query SET SESSION innodb_lock_wait_timeout=1
・versionなど取得
Query SHOW VARIABLES LIKE 'version%'
・使用可能なエンジン一覧取得
Query SHOW ENGINES
・innodbのversion取得
Query SHOW VARIABLES LIKE 'innodb_version'
・server_id取得
Query SELECT @@SERVER_ID
・accountに付与されている権限取得
Query SHOW GRANTS FOR CURRENT_USER()
・processlist確認
Query SHOW PROCESSLIST
・複製スレーブのリスト取得
Query SHOW SLAVE HOSTS
・スリープ状態になっていないスレッドの数 (1回目)
Query SHOW GLOBAL STATUS LIKE 'Threads_running'
・スリープ状態になっていないスレッドの数 (2回目)
Query SHOW GLOBAL STATUS LIKE 'Threads_running'
・指定したTableの存在確認
Query SHOW TABLES FROM `db_name` LIKE 'test\_table'
・指定したTableのトリガ一覧取得
Query SHOW TRIGGERS FROM `db_name` LIKE 'test\_table’
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 */
・DB選択
Query USE `db_name`
・変更対象Tableのcreate文取得
Query SHOW CREATE TABLE `db_name`.`table_name`
Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
・select
Query EXPLAIN SELECT * FROM `db_name`.`table_name` WHERE 1=1
・外部キーを張っているテーブルを調査
Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='db_name' AND referenced_table_name='table_name’
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 */
・DB選択(2回目)
Query USE `db_name`
・変更対象Tableのcreate文取得
Query SHOW CREATE TABLE `db_name`.`table_name`
Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
Query CREATE TABLE `db_name`.`_table_name_new` (
`id` int(11) NOT NULL DEFAULT '0',
`foo` int(11) DEFAULT NULL COMMENT 'testdao',
`category_id` int(11) DEFAULT NULL,
`title` varchar(64) DEFAULT NULL,
`author_name` varchar(32) DEFAULT NULL,
`detail` text,
`price` int(11) DEFAULT NULL,
`image` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `foo` (`foo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
・新しいTableにカラム追加
Query ALTER TABLE `db_name`.`_table_name_new` ADD foo2 INT( 11 ) NULL COMMENT 'testdao2' AFTER foo
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 */
・DB選択(3回目)
Query USE `db_name`
・移行用Tableのcreate文参照 (table生成確認)
Query SHOW CREATE TABLE `db_name`.`_table_name_new`
・SQL_MODE設定
Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
・deleteのtrigger/レコードの削除中にすべてのエラーを無視
Query CREATE TRIGGER `pt_osc_db_name_table_name_del` AFTER DELETE ON `db_name`.`table_name` FOR EACH ROW DELETE IGNORE FROM `db_name`.`_table_name_new` WHERE `db_name`.`_table_name_new`.`id` <=> OLD.`id`
・updateのtrigger
Query CREATE TRIGGER `pt_osc_db_name_table_name_upd` AFTER UPDATE ON `db_name`.`table_name` FOR EACH ROW REPLACE INTO `db_name`.`_table_name_new` (`id`, `foo`, `category_id`, `title`, `author_name`, `detail`, `price`, `image`) VALUES (NEW.`id`, NEW.`foo`, NEW.`category_id`, NEW.`title`, NEW.`author_name`, NEW.`detail`, NEW.`price`, NEW.`image`)
・insertのtrigger
Query CREATE TRIGGER `pt_osc_db_name_table_name_ins` AFTER INSERT ON `db_name`.`table_name` FOR EACH ROW REPLACE INTO `db_name`.`_table_name_new` (`id`, `foo`, `category_id`, `title`, `author_name`, `detail`, `price`, `image`) VALUES (NEW.`id`, NEW.`foo`, NEW.`category_id`, NEW.`title`, NEW.`author_name`, NEW.`detail`, NEW.`price`, NEW.`image`)
・explain
Query EXPLAIN SELECT * FROM `db_name`.`table_name` WHERE 1=1
・explain
Query EXPLAIN SELECT `id`, `foo`, `category_id`, `title`, `author_name`, `detail`, `price`, `image` FROM `db_name`.`table_name` /*explain pt-online-schema-change 6557 copy table*/
・旧Tableから新Tableに優先度低めでInsert & 実行中に起きたエラーは警告扱い
Query INSERT LOW_PRIORITY IGNORE INTO `db_name`.`_table_name_new` (`id`, `foo`, `category_id`, `title`, `author_name`, `detail`, `price`, `image`) SELECT `id`, `foo`, `category_id`, `title`, `author_name`, `detail`, `price`, `image` FROM `db_name`.`table_name` /*pt-online-schema-change 6557 copy table*/
・最後のステートメントから生じたエラー、警告など取得
Query SHOW WARNINGS
・スリープ状態になっていないスレッドの数
Query SHOW GLOBAL STATUS LIKE 'Threads_running'
・リネーム
Query RENAME TABLE `db_name`.`table_name` TO `db_name`.`_table_name_old`, `db_name`.`_table_name_new` TO `db_name`.`table_name`
・古いTable削除
Query DROP TABLE IF EXISTS `db_name`.`_table_name_old`
・triggerの削除
Query DROP TRIGGER IF EXISTS `db_name`.`pt_osc_db_name_table_name_del`
Query DROP TRIGGER IF EXISTS `db_name`.`pt_osc_db_name_table_name_upd`
Query DROP TRIGGER IF EXISTS `db_name`.`pt_osc_db_name_table_name_ins`
・移行用Tableの存在確認
Query SHOW TABLES FROM `db_name` LIKE '\_table\_name\_new'
Quit
実際実行すると、通常のAlterTableより時間がかかる傾向があります。
処理中に対象Tableの書き込みが発生しても、insert/updateのtriggerで旧Tableのデータが新Tableに反映される仕組みになっています。
なお、MySQL 5.6ではALTER TABLE...ADD INDEX/DROP INDEX中であっても参照・更新共に実行可能になる模様です。
実行する場合、バックアップした上で、似た環境で事前検証が必要です。
問題なければ、MySQL5.5以下はもちろん、5.6以上でもカラム追加、削除用途で使えると思います。
公式マニュアル
http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html
バグ情報
https://bugs.launchpad.net/percona-toolkit/+bugs?field.tag=pt-online-schema-change
MySQLのTableにカラムやインデックスの追加、修正する際にメンテナンスを入れることがあると思います。
AlterTable実行中は書き込みができないため、普通はメンテナンスを入れる必要があるのですが、
pt-online-schema-changeを使うとAlterTable実行中でも書き込みが可能になります。
本日はこのツールを紹介します。
pt-online-schema-changeはPercona Toolkitの1機能です。
Percona ToolkitはMaatkitとAspersaが元になっています。
実は似たツールが複数あり、米Facebook、MySQL向けのオンラインスキーマ変更ユーティリティ「OSC(Online Schema Change for MySQL)」もその一つです。
http://bazaar.launchpad.net/~mysqlatfacebook/mysqlatfacebook/tools/files
http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932
pt-online-schema-changeはこのツールを改良したものだそうです。
なお、AlterTable実行時の処理の流れは以下の通りとなります。※InnoDBの場合
・テーブルをTL_WRITE_ALLOW_READロックする。（他のセッションからのREADを許可し、WRITEをブロックする。）
・新しいテーブル定義を使って空のテンポラリテーブルを作成する。
・古いテーブルから新しいテーブルへデータをコピーする。
・テーブルの名前を付け替えて、新しいテーブルを古いテーブルと同じ名前にする。古いテーブルは破棄する。
・新しいテーブルへブロックしていたWRITEをリダイレクトする。
http://nippondanji.blogspot.jp/2009/05/alter-table.html
ALTER TABLEも内部的に作業用テーブルへのINSERT処理を実行するのですね。
以下はpt-online-schema-changeを実行した際のsqlログとなります。
Connect root@localhost on db_name
・オートコミット機能を有効にする
Query set autocommit=1
Query SELECT @@SQL_MODE
・SHOW CREATE TABLE と SHOW CREATE DATABASEでテーブル名とカラム名は引用符で囲う
Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/
・待機状態（アイドル状態）になって○秒経過したら接続を切る。
Query SET wait_timeout=10000
・hostname, server_id取得
Query SELECT @@hostname, @@server_id
・行ロック待ちの秒数をセッション単位で指定
Query SET SESSION innodb_lock_wait_timeout=1
・versionなど取得
Query SHOW VARIABLES LIKE 'version%'
・使用可能なエンジン一覧取得
Query SHOW ENGINES
・innodbのversion取得
Query SHOW VARIABLES LIKE 'innodb_version'
・server_id取得
Query SELECT @@SERVER_ID
・accountに付与されている権限取得
Query SHOW GRANTS FOR CURRENT_USER()
・processlist確認
Query SHOW PROCESSLIST
・複製スレーブのリスト取得
Query SHOW SLAVE HOSTS
・スリープ状態になっていないスレッドの数 (1回目)
Query SHOW GLOBAL STATUS LIKE 'Threads_running'
・スリープ状態になっていないスレッドの数 (2回目)
Query SHOW GLOBAL STATUS LIKE 'Threads_running'
・指定したTableの存在確認
Query SHOW TABLES FROM `db_name` LIKE 'test\_table'
・指定したTableのトリガ一覧取得
Query SHOW TRIGGERS FROM `db_name` LIKE 'test\_table’
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 */
・DB選択
Query USE `db_name`
・変更対象Tableのcreate文取得
Query SHOW CREATE TABLE `db_name`.`table_name`
Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
・select
Query EXPLAIN SELECT * FROM `db_name`.`table_name` WHERE 1=1
・外部キーを張っているテーブルを調査
Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='db_name' AND referenced_table_name='table_name’
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 */
・DB選択(2回目)
Query USE `db_name`
・変更対象Tableのcreate文取得
Query SHOW CREATE TABLE `db_name`.`table_name`
Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
Query CREATE TABLE `db_name`.`_table_name_new` (
`id` int(11) NOT NULL DEFAULT '0',
`foo` int(11) DEFAULT NULL COMMENT 'testdao',
`category_id` int(11) DEFAULT NULL,
`title` varchar(64) DEFAULT NULL,
`author_name` varchar(32) DEFAULT NULL,
`detail` text,
`price` int(11) DEFAULT NULL,
`image` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `foo` (`foo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
・新しいTableにカラム追加
Query ALTER TABLE `db_name`.`_table_name_new` ADD foo2 INT( 11 ) NULL COMMENT 'testdao2' AFTER foo
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 */
・DB選択(3回目)
Query USE `db_name`
・移行用Tableのcreate文参照 (table生成確認)
Query SHOW CREATE TABLE `db_name`.`_table_name_new`
・SQL_MODE設定
Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
・deleteのtrigger/レコードの削除中にすべてのエラーを無視
Query CREATE TRIGGER `pt_osc_db_name_table_name_del` AFTER DELETE ON `db_name`.`table_name` FOR EACH ROW DELETE IGNORE FROM `db_name`.`_table_name_new` WHERE `db_name`.`_table_name_new`.`id` <=> OLD.`id`
・updateのtrigger
Query CREATE TRIGGER `pt_osc_db_name_table_name_upd` AFTER UPDATE ON `db_name`.`table_name` FOR EACH ROW REPLACE INTO `db_name`.`_table_name_new` (`id`, `foo`, `category_id`, `title`, `author_name`, `detail`, `price`, `image`) VALUES (NEW.`id`, NEW.`foo`, NEW.`category_id`, NEW.`title`, NEW.`author_name`, NEW.`detail`, NEW.`price`, NEW.`image`)
・insertのtrigger
Query CREATE TRIGGER `pt_osc_db_name_table_name_ins` AFTER INSERT ON `db_name`.`table_name` FOR EACH ROW REPLACE INTO `db_name`.`_table_name_new` (`id`, `foo`, `category_id`, `title`, `author_name`, `detail`, `price`, `image`) VALUES (NEW.`id`, NEW.`foo`, NEW.`category_id`, NEW.`title`, NEW.`author_name`, NEW.`detail`, NEW.`price`, NEW.`image`)
・explain
Query EXPLAIN SELECT * FROM `db_name`.`table_name` WHERE 1=1
・explain
Query EXPLAIN SELECT `id`, `foo`, `category_id`, `title`, `author_name`, `detail`, `price`, `image` FROM `db_name`.`table_name` /*explain pt-online-schema-change 6557 copy table*/
・旧Tableから新Tableに優先度低めでInsert & 実行中に起きたエラーは警告扱い
Query INSERT LOW_PRIORITY IGNORE INTO `db_name`.`_table_name_new` (`id`, `foo`, `category_id`, `title`, `author_name`, `detail`, `price`, `image`) SELECT `id`, `foo`, `category_id`, `title`, `author_name`, `detail`, `price`, `image` FROM `db_name`.`table_name` /*pt-online-schema-change 6557 copy table*/
・最後のステートメントから生じたエラー、警告など取得
Query SHOW WARNINGS
・スリープ状態になっていないスレッドの数
Query SHOW GLOBAL STATUS LIKE 'Threads_running'
・リネーム
Query RENAME TABLE `db_name`.`table_name` TO `db_name`.`_table_name_old`, `db_name`.`_table_name_new` TO `db_name`.`table_name`
・古いTable削除
Query DROP TABLE IF EXISTS `db_name`.`_table_name_old`
・triggerの削除
Query DROP TRIGGER IF EXISTS `db_name`.`pt_osc_db_name_table_name_del`
Query DROP TRIGGER IF EXISTS `db_name`.`pt_osc_db_name_table_name_upd`
Query DROP TRIGGER IF EXISTS `db_name`.`pt_osc_db_name_table_name_ins`
・移行用Tableの存在確認
Query SHOW TABLES FROM `db_name` LIKE '\_table\_name\_new'
Quit
実際実行すると、通常のAlterTableより時間がかかる傾向があります。
処理中に対象Tableの書き込みが発生しても、insert/updateのtriggerで旧Tableのデータが新Tableに反映される仕組みになっています。
なお、MySQL 5.6ではALTER TABLE...ADD INDEX/DROP INDEX中であっても参照・更新共に実行可能になる模様です。
実行する場合、バックアップした上で、似た環境で事前検証が必要です。
問題なければ、MySQL5.5以下はもちろん、5.6以上でもカラム追加、削除用途で使えると思います。
公式マニュアル
http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html
バグ情報
https://bugs.launchpad.net/percona-toolkit/+bugs?field.tag=pt-online-schema-change