Replication lag & Long running alter queries

1

Hi All,

We have long running “ALTER TABLE … ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE = 4;” queries on source mysql instance. After the queries reach the replica mysql instance, over replication, we have noticed that the replication lag is getting increased continously. We also use MTR (Multi-threaded-replication) on the replica with the following settings:

slave_parallel_type=‘LOGICAL_CLOCK’
slave_parallel_workers=128

on Primary, we have also enabled “binlog_group_commit_sync_delay=10000;”

Kindly advise.

2

Yes, what you are experiencing with replica lag is correct and expected. ALTER TABLE is not written/replicated until after the ALTER completes and immediately lags the replica.

You should switch to using pt-online-schema-change for your ALTER TABLE. This will keep replication lag near 0 during the ALTER process.