読者です 読者をやめる 読者になる 読者になる

pt-online-schema-change-fast-rebuild-constraints

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のせいで、制約違反になる」というタイミングがあるのではないかなぁ、と思ってたりしてます。

なので、

  1. FKを無効化
  2. 子テーブルのFKを削除
  3. 親テーブルをスワップ
  4. 子テーブルのFKを再作成
  5. FKを有効化

という手順がベストではないかと考えているのですが、プラグインだけでそこまでやるのは手間がかかりそうです。

*1:employees sample databaseを使ってます