Using Read Replica Promotion to Create Index On AWS RDS MySQL Database

3
votes

I would like to add an index to a MySQL table in a database on Amazon RDS, but I do not want to stop using the database while the index is created. This answer suggests using read replica promotion, first creating the index on the a read replica and then promoting the read replica. I created a read replica to try this approach, but when I try to change the index on the read replica I get ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement. How do I make it so that I can edit the read replica and will this prevent Amazon from continuing to update the read replica to match the master database?

2
mysqlamazon-web-servicesindexingserveramazon-rds
Is that index present on your master RDS? – error2007s
No, it is not on the master. – Michael
Not sure what version you are running but all this trouble might be entirely unnecessary... MySQL 5.6 allows the addition of indexes without locking the table. – Michael - sqlbot
@Michael-sqlbot is that the default behavior in 5.6? Can you point me to documentation. Everything I found said it wouldn't work while the database was running, but maybe this isn't an issue anymore in the newer version. Even on the replica it took 11 hours to create a fulltext index, but it would be nice not to have to go through the hassle of promoting the replica and repointing everything at the replica. – Michael
Nevermind, I found it. Does not look like the default, but it can be done: ALTER TABLE my_table ADD INDEX my_table__idx (my_column), ALGORITHM=INPLACE, LOCK=NONE; stackoverflow.com/a/21842589/2327821 – Michael

2 Answers

5
votes

Read replicas by default are read only until you customize them to be read write.

You can configure an Amazon RDS DB instance read replica to be read/write by setting the read_only parameter to false for the DB parameter group that you create for your DB instance(s). Follow this developers guide to achieve the read/write for you read replica. Then you will be able to achieve your use case.

https://aws.amazon.com/premiumsupport/knowledge-center/rds-read-replica/

4
votes

The pattern you are attempting is:

  1. While the app writes to current_db, next_db does the disruptive work of building a new index. Once the next_db catches up ...
  2. [tricky] Safely switch app traffic from current_db to next_db
  3. Take old master offline (or rebuild it, or add the index, or whatever, it's superfluous now)

With MySQL, you have to be really careful about step #2, especially if your app is running on 2+ instances. If part of your app tier writes to current_db and part of your app writes to next_db, bad things can happen if you aren't careful. Here's two ways to stay safe:

  • Three Second Cutover: Stop all writes to current_db, then verify that next_db is fully replicated, then start writes to next_db. If next_db is hot and ready, this cut-over interval results in 1-3 seconds of "downtime", depending on how fast you type.
  • Zero Downtime: With two db instances authoring writes, you need to be careful about DB design. The most important issue for MySQL is to use odd vs even numbering for auto-incrementing row IDs on the two db instances. Otherwise, both DB instances would be creating new DB rows with the same IDs! Badness! Data Corruption!

Unfortunately, RDS read-replica promotion doesn't seem sufficient for doing zero-downtime schema. Here's what the RDS docs say about read-replica promotion:

The promotion process takes a few minutes to complete. When you promote a Read Replica, replication is stopped and the Read Replica is rebooted. When the reboot is complete, the Read Replica is available as a single-AZ DB instance.

That doesn't really do the trick for zero-downtime. However, you could safely do "Three [Minute] Cutover" update using that mechanism. Sadly, because of the reboot, it will take minutes. Here's how:

  1. Add a read-replica and let it catch up to the master
  2. Mark read-replica as writable (link) and apply your forwards-compatible schema updates [DO NOT SEND DB ROW UPDATES!]
  3. STOP app (write) traffic to the old master (b/c replication is about to end). If your app supports it, it could run in read-only mode for this time, or spool writes into a queue.
  4. Promote read-replica (link) ... takes ~3 minutes.
  5. Point your app at the new DB instance that you've promoted.
  6. Verify that your app is working and healthy (might give it an hour; be paranoid).
  7. Destroy old DB instance.

If you truly want "Zero Downtime" updates, you'll need to investigate: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZ.html

Final word: Whatever you choose to do, I'd recommend practicing it once with mock version of your app before trying it on your live system! :-)