InnoDB Cluster ALTER large tables without causing lag

Question

We need to run an ALTER TABLE command to add a new modified column to all servers in a InnoDB ClusterSet (Two clusters)

Example:

ALTER TABLE DB.tableA add column modified varchar(30) GENERATED ALWAYS AS (json_unquote(json_extract(`_source`,_utf8mb4'$.modified'))) STORED;

Each clusters is 3 nodes, so there are 6 nodes total... 3 Nodes in one location as primary and 3 nodes in another location for DR.

On the primary site, of course we have the global primary node.

Since tableA from example above is over 100 GB, we cause very high lag in the cluster when we run from the global primary host, so we need a method to run this change without causing lag.

We have analyzed two options:

1. pt-online-schema with max-flow-ctl but the problem is this option works only for Percona XtraDB Cluster, not for InnoDB Cluster. Is there anything similar?
2. Run ALTER on each node with set sql_log_bin=0 but we don't know the implications for InnoDB cluster applier manager or overall cluster config.

Any ideas?

Answer 1

I would use pt-online-schema-change.

The --max-flow-ctl is specific to PXC, but --max-lag works on any replication setup.

Or don't use --max-lag either. One advantage of pt-osc is that the alter runs concurrently on all replicas.

The extra write load might result in a little replication lag, but not as much as if you use a direct ALTER TABLE statement, and it only starts on the replica after it finishes on the source.

Another alternative is to use a view instead of a generated column. That definitely won't cause replication lag, but it doesn't have the STORED property.