pt-oscおさらい
久々にpt-online-schema-change（以下pt-osc）を、手順通りコマンド叩くだけじゃなくて中身まで掘り下げる機会があったので書いていく。巨大な記事になってしまうと読むのも書くのもつらいので今日はおさらいを。
※ 今日はおさらいレベルなので、ググれば他の方が書かれた記事も見つかる内容です
pt-oscとは
https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html
Percona Toolkitという、主にMySQL向けに作られた運用ツール群があり、その中の1つとしてpt-oscがある。OSSとして公開されており自由に利用できる（ライセンスについては著作者の記載を参照のこと）。
pt-oscは、他セッションからそのテーブルへのread/writeを許容できる状態に保ったままALTER TABLEを実行するためのサポートツールである。
なぜpt-oscが必要か
古いMySQLでは、ALTER TABLE中は必ずwriteにロックがかかる仕様になっていた。そのためデータ量が多い場合、普通にALTER TABLEを発行してしまうと長時間writeできなくなり、DBを利用しているサービスが停止してしまう。
サービスを停止させないためには、いい感じに（後述）テーブルを運用し、長いwriteロックをかけないような手順でALTER TABLEを実行する必要がある。その手順を温かみのある手動コマンド実行ではなく、ツールにより1コマンドで実現できるようにしたのがpt-oscである。
なお、「古いMySQL」と書いたが、MySQL5.6からは「オンラインDDL」と呼ばれる機能が実装された。これにより、pt-oscの出番がなくなったケースもあるが、完全に不要になったわけではない。
オンラインDDLもpt-oscも、全てのALTER TABLEに対応しているわけではないのだが、pt-oscの方が対応しているALTER TABLEの種類が多いのでpt-oscが必要なケースは実際にある（あった）。
とりあえず使ってみる手順
インストール
AWS上で運用してたのでここにはAmazonLinuxのつもりで手順を書く。
本家サイトにもちゃんとドキュメントあるのでそちらも見て欲しい。
# リポジトリを登録
# ※ バージョン番号はそこそこの頻度で変わってるっぽいので注意
# 0.1-6 は2018-12-12時点での最新
sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
# インストール
sudo yum install percona-toolkit
実行例
■ 検証環境準備
create database testdb;
use testdb;
create table testtable (
id int not null auto_increment,
a int,
primary key(id)
);
■ カラムのデータタイプ変更はオンラインDDL対象外
alter table testtable modify column a bigint, lock=none;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK=SHARED.
■ pt-oscでデータタイプ変更
pt-online-schema-change --execute \
-h ${DATABASE_HOST} -P ${DATABASE_PORT} -u ${DATABASE_USERNAME} --ask-pass \
--alter "modify column a bigint" D=testdb,t=testtable
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `testdb`.`testtable`...
Creating new table...
Created new table testdb._testtable_new OK.
Altering new table...
Altered `testdb`.`_testtable_new` OK.
2018-12-12T04:00:56 Creating triggers...
2018-12-12T04:00:56 Created triggers OK.
2018-12-12T04:00:56 Copying approximately 1 rows...
2018-12-12T04:00:56 Copied rows OK.
2018-12-12T04:00:56 Analyzing new table...
2018-12-12T04:00:56 Swapping tables...
2018-12-12T04:00:56 Swapped original and new tables OK.
2018-12-12T04:00:56 Dropping old table...
2018-12-12T04:00:57 Dropped old table `testdb`.`_testtable_old` OK.
2018-12-12T04:00:57 Dropping triggers...
2018-12-12T04:00:57 Dropped triggers OK.
Successfully altered `testdb`.`testtable`.
pt-oscは何をやっているか
↑のログに書いてあるとおりなんだけど、日本語訳するとこんな感じ。
- 新しいテーブルを作る
- 作った新しいテーブルにalter tableを適用
- トリガーを作る
- 旧テーブルにINSERT/UPDATE/DELETEが来たときに新テーブルにも同じレコードを入れるようにする
- 旧テーブルのレコードを新テーブルにコピー
- 新旧テーブルを入れ替え
- 旧テーブルの削除
- 3で作ったトリガーを削除
図解しようかと思ったが、↓と全く同じ図を描くのはダルかったので、こちらを見て欲しい。
オンラインで安全にスキーマ変更可能なpt-online-schema-change - サイバーエージェント公式エンジニアブログ
せっかくなので、どんなトリガーを貼ってるか見てみよう。
--no-drop-triggersオプションを付けると、7の手順がスキップされるので、あとでどんなトリガーを貼っていたのか確認することができる。
MySQL [testdb]> show triggers\G
*************************** 1. row ***************************
Trigger: pt_osc_testdb_testtable_ins
Event: INSERT
Table: _testtable_old
Statement: REPLACE INTO `testdb`.`_testtable_new` (`id`, `a`) VALUES (NEW.`id`, NEW.`a`)
Timing: AFTER
Created: NULL
sql_mode: NO_AUTO_VALUE_ON_ZERO
Definer: uzo@%
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Trigger: pt_osc_testdb_testtable_upd
Event: UPDATE
Table: _testtable_old
Statement: BEGIN DELETE IGNORE FROM `testdb`.`_testtable_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `testdb`.`_testtable_new`.`id` <=> OLD.`id`;REPLACE INTO `testdb`.`_testtable_new` (`id`, `a`) VALUES (NEW.`id`, NEW.`a`);END
Timing: AFTER
Created: NULL
sql_mode: NO_AUTO_VALUE_ON_ZERO
Definer: uzo@%
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
*************************** 3. row ***************************
Trigger: pt_osc_testdb_testtable_del
Event: DELETE
Table: _testtable_old
Statement: DELETE IGNORE FROM `testdb`.`_testtable_new` WHERE `testdb`.`_testtable_new`.`id` <=> OLD.`id`
Timing: AFTER
Created: NULL
sql_mode: NO_AUTO_VALUE_ON_ZERO
Definer: uzo@%
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
update triggerだけ少し複雑な文になっているが、これはPrimary KeyやUnique Keyが変更されるようなupdateに対応するためのものである。それ以外はシンプル。
ちなみに<=>は「NULL安全等価演算子」と言い、比較対象にNULLがあってもTRUEかFALSEが返る。