pt-online-schema-change-fast-rebuild-constraintsというpt-oscのプラグインを書きました。
これは--alter-foreign-keys-method= rebuild_constraints
を高速にするプラグインです。
通常、FKで参照されている親テーブルにpt-oscを実行しようとすると*1
pt-online-schema-change \
--alter 'ADD num int' \
h=localhost,u=root,D=employees,t=employees \
--dry-run
$ ./pt-osc.sh
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
Child tables:
`employees`.`dept_emp` (approx. 331143 rows)
`employees`.`dept_manager` (approx. 24 rows)
`employees`.`salaries` (approx. 2838426 rows)
`employees`.`titles` (approx. 442010 rows)
You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool's documentation carefully.
こんな感じで警告が出ます。
--alter-foreign-keys-method=rebuild_constraints
をつけてexecute
してみると
$ ./pt-osc.sh
...
2016-11-03T12:44:13 Copied rows OK.
2016-11-03T12:44:13 Swapping tables...
2016-11-03T12:44:13 Swapped original and new tables OK.
2016-11-03T12:44:13 Rebuilding foreign key constraints...
2016-11-03T12:44:35 Rebuilt foreign key constraints OK.
2016-11-03T12:44:35 Dropping old table...
2016-11-03T12:44:35 Dropped old table `employees`.`_employees_old` OK.
2016-11-03T12:44:35 Dropping triggers...
2016-11-03T12:44:35 Dropped triggers OK.
Successfully altered `employees`.`employees`.
と、FKの貼り替えに結構時間がかかってしまいます。
--alter-foreign-keys-method=drop_swap
だと速いは速いんですが
2016-11-03T12:47:13 Copied rows OK.
2016-11-03T12:47:13 Drop-swapping tables...
2016-11-03T12:47:13 Analyzing new table...
2016-11-03T12:47:13 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2016-11-03T12:47:13 Dropping triggers...
2016-11-03T12:47:13 Dropped triggers OK.
Successfully altered `employees`.`employees`.
テーブルが存在しない瞬間があるので、プロダクションでは使いづらいです。
pt-online-schema-change-fast-rebuild-constraints.pl
--plugin=pt-online-schema-change-fast-rebuild-constraints.pl
としてやると、--alter-foreign-keys-method=rebuild_constraints
でも一瞬でFKの貼り替えが終わります。
pt-online-schema-change \
--alter 'ADD num int' \
--alter-foreign-keys-method=rebuild_constraints \
--plugin=pt-online-schema-change-fast-rebuild-constraints.pl \
h=localhost,u=root,D=employees,t=employees \
--execute
$ ./pt-osc.sh
...
2016-11-03T12:56:53 Copied rows OK.
2016-11-03T12:56:53 Analyzing new table...
2016-11-03T12:56:53 Swapping tables...
2016-11-03T12:56:53 Swapped original and new tables OK.
Disable foreign key checks
2016-11-03T12:56:53 Rebuilding foreign key constraints...
2016-11-03T12:56:53 Rebuilt foreign key constraints OK.
Enable foreign key checks
2016-11-03T12:56:53 Dropping old table...
2016-11-03T12:56:53 Dropped old table `employees`.`_employees_old` OK.
2016-11-03T12:56:53 Dropping triggers...
2016-11-03T12:56:53 Dropped triggers OK.
Successfully altered `employees`.`employees`.
ログを見るとわかると思いますが、FKの貼り替えの前後でFKのチェックを無効化→有効化するようにしています。
これでFK追加・削除時のテーブルコピーを行わないようにして、高速化しています。
余談
「テーブルのスワップ+FKの貼り替え」はアトミックな操作じゃないので、タイミングによっては「子テーブルにデータを挿入しようとしたら旧親テーブルのFKのせいで、制約違反になる」というタイミングがあるのではないかなぁ、と思ってたりしてます。
なので、
- FKを無効化
- 子テーブルのFKを削除
- 親テーブルをスワップ
- 子テーブルのFKを再作成
- FKを有効化
という手順がベストではないかと考えているのですが、プラグインだけでそこまでやるのは手間がかかりそうです。