Search code examples
mysqldatabasedatabase-migrationpt-online-schema-change

Pt-online-schema-change - Huge replication lag when renaming tables

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

We are using the following command and it does pause the copying of data when it encounters a replication lag, but near the end of the process, presumably when renaming the table, we do get a huge replication lag.

This here 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
Solution

  • We probably have found the issue. We are using the drop_swap method for foreign keys.

    While the renaming goes through quite fast, it needs to wait for the table to be dropped, which takes some time on a big table. Therefore the whole "switching-over" process does take way longer.

    For us the solution is too move away from FKs, and take the downtime into account when doing migrations on them.