Best practices for multi million row alter table operation on Aurora MySQL
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
- 최신
- 최다 투표
- 가장 많은 댓글
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.
