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 #--execute
$ ./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を有効化
という手順がベストではないかと考えているのですが、プラグインだけでそこまでやるのは手間がかかりそうです。
*1:employees sample databaseを使ってます
