Best practices for multi million row alter table operation on Aurora MySQL

Question

A customer is using Aurora MySQL. He has a table with several million entries where we need to ALTER the primary key / auto_increment column from INT(11) to BIGINT(20).

He tried Percona's pt-online-schema-change, which works very well on a standalone Aurora instance. However, Aurora read replicas cannot be discovered by this tool and he is worried in general to bring something out of order when this tool executes a rename of the tables and if this is correctly propagated to all readers.

See https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-max-lag for details on the replica lag detection feature from pt-online-schema-change.

Are there any Aurora specific recommendations to make such a change?

Thanks

Accepted Answer

I suspect your customer is thinking that aurora read replicas are like regular mysql read replicas and have a separate physical copy of the database.  They do not, they share a common storage network/volume.  Therefore, the read replicas can only reflect what has happened on the writer instance, and there should be no concern about the read replicas not acknowledging/reflecting the changes.

Best practices for multi million row alter table operation on Aurora MySQL

Relevanter Inhalt