外部キーから参照されているテーブルに対して pt-online-schema-change を使う

pt-online-schema-change では外部キーから参照されているテーブルに対して使用する場合に --alter-foreign-keys-method を指定する必要があります。
--alter-foreign-keys-method には rebuild_constraints を指定することが推奨されているんですが、子テーブルの ALTER に時間がかからなければという但し書きがあります。

This is the preferred technique, unless one or more of the “child” tables is so large that the ALTER would take too long.

cf. https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-alter-foreign-keys-method

じゃあ子テーブルが巨大なテーブルの場合にどうすればいいのかって話です。以下、MySQL 5.7.25 を前提とします。1

結論

plugin を使って before_update_foreign_keysforeign_key_checks を無効にすれば良いです。外部キー制約を満たしているかは pt-online-schema-change が終わった後で手動でチェックすると良いでしょう。
before_update_foreign_keys でググったら id:winebarrel さんが正に求めていたものを作ってました。

cf. https://github.com/winebarrel/pt-online-schema-change-fast-rebuild-constraints

解説

実例として https://github.com/abicky/sample-data-railsdm-2018/tree/7531a4ca24fcd99fefa9f2fe2fbffad504b077e1 に対して次の変更を加えることを考えます。

diff --git a/Schemafile b/Schemafile
index 509572e..a031015 100644
--- a/Schemafile
+++ b/Schemafile
@@ -12,6 +12,7 @@ create_table 'products', unsigned: true, force: :cascade do |t|
   t.integer    'price',      unsigned: true, null: false
   t.datetime   'started_at',                 null: false
   t.datetime   'ended_at',                   null: false
+  t.datetime   'deleted_at'
 end
 add_index 'products', ['shop_id', 'ended_at'], name: 'ix_shop_id_ended_at'
 add_index 'products', ['ended_at'],            name: 'ix_ended_at'

migrate しようとすると次のようなプランになります。

% rake db:migrate DRY_RUN=1
bundle exec ridgepole -c database.yml --table-options 'ENGINE=InnoDB DEFAULT CHARSET=utf8' --apply --dry-run
Apply `Schemafile` (dry-run)
add_column("products", "deleted_at", :datetime, {:after=>"ended_at"})

# ALTER TABLE `products` ADD `deleted_at` datetime AFTER `ended_at`

これを pt-online-schema-change を使って変更しようとしてみます。

% pt-online-schema-change --dry-run --print \
  --alter 'ADD `deleted_at` datetime AFTER `ended_at`' \
  --alter-foreign-keys-method "rebuild_constraints" \
  D=railsdm2018,t=products,h=localhost,u=root
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:
  `railsdm2018`.`orders` (approx. 10000 rows)
Will use the rebuild_constraints method to update foreign keys.
Starting a dry run.  `railsdm2018`.`products` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
CREATE TABLE `railsdm2018`.`_products_new` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` int(10) unsigned NOT NULL,
  `started_at` datetime NOT NULL,
  `ended_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_shop_id_ended_at` (`shop_id`,`ended_at`),
  KEY `ix_ended_at` (`ended_at`),
    CONSTRAINT `_fk_products_shops` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5001 DEFAULT CHARSET=utf8
Created new table railsdm2018._products_new OK.
Altering new table...
ALTER TABLE `railsdm2018`.`_products_new` ADD `deleted_at` datetime AFTER `ended_at`
Altered `railsdm2018`.`_products_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
INSERT LOW_PRIORITY IGNORE INTO `railsdm2018`.`_products_new` (`id`, `shop_id`, `name`, `price`, `started_at`, `ended_at`) SELECT `id`, `shop_id`, `name`, `price`, `started_at`, `ended_at` FROM `railsdm2018`.`products` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 21221 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `railsdm2018`.`products` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Not swapping tables because this is a dry run.
Not rebuilding foreign key constraints because this is a dry run.
ALTER TABLE `railsdm2018`.`orders` DROP FOREIGN KEY `fk_orders_products`, ADD CONSTRAINT `_fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `railsdm2018`.`products` (`id`)
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
DROP TRIGGER IF EXISTS `railsdm2018`.`pt_osc_railsdm2018_products_del`
DROP TRIGGER IF EXISTS `railsdm2018`.`pt_osc_railsdm2018_products_upd`
DROP TRIGGER IF EXISTS `railsdm2018`.`pt_osc_railsdm2018_products_ins`
2019-05-13T07:27:57 Dropping new table...
DROP TABLE IF EXISTS `railsdm2018`.`_products_new`;
2019-05-13T07:27:57 Dropped new table OK.
Dry run complete.  `railsdm2018`.`products` was not altered.

問題は最後に次のような SQL が発行されることです。

ALTER TABLE `railsdm2018`.`orders`
  DROP FOREIGN KEY `fk_orders_products`,
  ADD CONSTRAINT `_fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `railsdm2018`.`products` (`id`)

productsorders は 1 対多の関係にあるので、productspt-online-schema-change を使う必要のある規模のテーブルである場合、orders もまた pt-online-schema-change で変更が必要である可能性は高いでしょう。
外部キーの追加はドキュメントに書いてあるとおり、foreign_key_checks が無効であればメタデータの更新だけの軽い処理ですが、有効であれば COPY アルゴリズムが使われます。

foreign_key_checks が有効な場合、次の ALTER を発行することになります。つまり、ALTER が完了するまで orders テーブルは読み取り可能ですが、書き込めません。

ALTER TABLE `railsdm2018`.`orders`
  ADD CONSTRAINT `_fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `railsdm2018`.`products` (`id`),
  ALGORITHM=COPY,
  LOCK=SHARED

そんなわけで、pt-online-schema-change によって作成された新しいテーブルに対する外部キーを追加する直前に foreign_key_checks を無効にするよう plugin を利用すればメタデータの更新だけで済みます。

% pt-online-schema-change --execute --print \
  --alter 'ADD `deleted_at` datetime AFTER `ended_at`' \
  --alter-foreign-keys-method "rebuild_constraints" \
  --plugin /path/to/pt-online-schema-change-fast-rebuild-constraints.pl \
  D=railsdm2018,t=products,h=localhost,u=root
-- snip --
2019-05-13T07:26:17 Copied rows OK.
2019-05-13T07:26:17 Analyzing new table...
2019-05-13T07:26:17 Swapping tables...
RENAME TABLE `railsdm2018`.`products` TO `railsdm2018`.`_products_old`, `railsdm2018`.`_products_new` TO `railsdm2018`.`products`
2019-05-13T07:26:17 Swapped original and new tables OK.
Disable foreign key checks
2019-05-13T07:26:17 Rebuilding foreign key constraints...
ALTER TABLE `railsdm2018`.`orders` DROP FOREIGN KEY `fk_orders_products`, ADD CONSTRAINT `_fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `railsdm2018`.`products` (`id`)
2019-05-13T07:26:17 Rebuilt foreign key constraints OK.
Enable foreign key checks
2019-05-13T07:26:17 Dropping old table...
DROP TABLE IF EXISTS `railsdm2018`.`_products_old`
2019-05-13T07:26:17 Dropped old table `railsdm2018`.`_products_old` OK.
2019-05-13T07:26:17 Dropping triggers...
DROP TRIGGER IF EXISTS `railsdm2018`.`pt_osc_railsdm2018_products_del`
DROP TRIGGER IF EXISTS `railsdm2018`.`pt_osc_railsdm2018_products_upd`
DROP TRIGGER IF EXISTS `railsdm2018`.`pt_osc_railsdm2018_products_ins`
2019-05-13T07:26:17 Dropped triggers OK.
Successfully altered `railsdm2018`.`products`.

foreign_key_cheks とは

FOREIGN KEY constraint の説明に書いてあるとおりですが、check_foreigns で grep すると次のような時に使われていることがわかります。

  • インデックスレコードの追加・更新・削除の際に制約を満たしているかチェックする
    • 場合によっては子テーブルのレコードを更新・削除する
    • cf. row_ins_check_foreign_constraint
  • テーブル作成の際に親テーブルが存在するかチェックする
    • cf. dict_create_foreign_constraints_low
  • テーブル削除の際に子テーブルが存在するかチェックする
    • cf. row_drop_table_for_mysql
  • テーブルを truncate する際に子テーブルが存在するかチェックする
    • cf. row_truncate_foreign_key_checks

foreign_key_checks が有効であれば、次のように products テーブルに存在しない product_id のレコードを挿入しようとするとエラーになります。

mysql> INSERT INTO `orders` (`user_id`, `product_id`, `count`, `created_at`) VALUES (1, 10000, 1, NOW());
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`railsdm2018`.`orders`, CONSTRAINT `fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`))

foreign_key_checks が無効であれば挿入可能です。

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `orders` (`user_id`, `product_id`, `count`, `created_at`) VALUES (1, 10000, 1, NOW());
Query OK, 1 row affected (0.00 sec)

有効にした後も、次のように外部キーの値が更新されるまでは不整合な状態でもエラーになることはありません。

mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM `orders` ORDER BY `id` DESC LIMIT 1;
+-------+---------+------------+-------+---------------------+
| id    | user_id | product_id | count | created_at          |
+-------+---------+------------+-------+---------------------+
| 10002 |       1 |      10000 |     1 | 2019-05-13 06:42:52 |
+-------+---------+------------+-------+---------------------+
1 row in set (0.01 sec)

mysql> UPDATE `orders` SET `product_id` = 10000, `count` = 2 WHERE `id` = 10002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE `orders` SET `product_id` = 10001 WHERE `id` = 10002;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`railsdm2018`.`orders`, CONSTRAINT `fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`))

その他の選択肢について

--alter-foreign-keys-method には auto, rebuild_constraints, drop_swap, none があります。auto は外部キーの追加に時間がかかりそうだと判断したら drop_swap、そうでなければ rebuild_constraints を自動で選択します。

drop_swap の場合、最後に次のようにテーブルを差し替えます。これをするぐらいなら rebuild_constraints と plugin を組み合わせる方が断然良いでしょう。

SET foreign_key_checks=0
DROP TABLE IF EXISTS `railsdm2018`.`products`
RENAME TABLE `railsdm2018`.`_products_new` TO `railsdm2018`.`products`

none の場合、最後に次のようにテーブルを差し替えます。

RENAME TABLE `railsdm2018`.`products` TO `railsdm2018`.`_products_old`, `railsdm2018`.`_products_new` TO `railsdm2018`.`products`
SET foreign_key_checks=0
DROP TABLE IF EXISTS `railsdm2018`.`_products_old`

外部キーを作り直していないので、orders テーブルは削除した _products_old をずっと参照し続けることになります。

mysql> show create table orders \G
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `count` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_orders_on_user_id` (`user_id`),
  KEY `index_orders_on_product_id` (`product_id`),
  CONSTRAINT `fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `_products_old` (`id`),
  CONSTRAINT `fk_orders_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

  1. Aurora で早く MySQL 8 互換のものを使いたいですね…。