pt-online-schema-change - Huge replication delay when renaming table

Question

We are using ptosc on a Percona MySQL 5.7 database and at the end of the process we are experiencing huge replication latency of 2-3 minutes on all replicas.

We use the following command and it does pause the copying of data when a replication delay is encountered, but towards the end of the process, presumably when the table is being renamed, we do experience a huge replication delay.

This is the command we are using:

pt-online-schema-change -u 'username' -p 'password' \
--max-lag 5 \
--max-load Threads_running=30 \
--critical-load Threads_running=200 \
--pause-file /tmp/pt-pause-file \
--alter-foreign-keys-method drop_swap \
--alter "ADD COLUMN test TINYINT(1) DEFAULT '0' NOT NULL" \
--recurse 1 \
D=db,t=table

Answer 1

We may have discovered the problem. We use the drop_swap method for foreign keys.

Although the rename process is very fast, it requires waiting for the table to be deleted, which takes some time on large tables. So the whole "switching" process does take longer.

The solution for us was to stay away from FK and account for downtime when migrating it.