Exploring Amazon RDS MySQL Second Tier Read Replica

AWS recently introduced Second Tier Replica for RDS MySQL.You can use this feature to shift the load from primary master DB to the replica in first tier and prepare your application for handling extremely high level of read traffic.  In this post we are going to explore this feature in following aspects :

• Steps to configure Multi-Tiered Amazon RDS MySQL Read replicas
• Second Tier Read Replica Deployment architectures
• Important Points to note

Amazon RDS MySQL version 5.6.12 on AWS WEST region was used. 

Steps to configure Multi- Tiered Amazon RDS MySQL Read Replicas

Configuration steps for the following architecture is given in this post :

Step 1: Creating Read Replica from Master and Place at Tier 1:

To create RDS MySQL Read replica navigate to the dashboard of Amazon RDS, select the Amazon RDS MySQL Master(named "sampledb") and use the option of “Create Read Replica”. 

Name the newly created Amazon RDS Read replica as "sampledb-level1" and place it in Tier 1. The Tier 1 Amazon RDS MySQL read Replica can be created in same AZ of Master or in a different AZ for High Availability. When the Tier replica is placed in Different AZ , you should factor few extra milliseconds of latency during replication.

Explore the status from Master DB: Post successful creation of the Tier 1 Read Replica, You can see the Read Replica Id's from exploring the details of the Master DB. Illustrated in Below Screen shot :

Explore the status from Tier 1 Read Replica : When you explore the Tier 1 Read Replica details, you will find it is pointing to the to Master DB. Illustrated in Below Screen Shot :

Step 2: Creating Second Tier Read Replica from Tier 1 Read replica :
To create Second Tier Read replica navigate to the dashboard of Amazon RDS, select the Amazon RDS Tier 1 Read Replica (sampledb-leve1) as the source and use the option of “Create Read Replica”.

Name the newly created Amazon RDS Read replica as "sampledb-level2" and place it in Second Tier 2. The Second Tier -2 Amazon RDS MySQL read Replica can be created in same AZ of Tier 1/Master or in a different AZ for High Availability. 

Explore the status from Tier 2 Read Replica : When you explore the Tier 2 Read Replica details, you will find it is pointing to the to Tier 1 - sampledb-level1 as replication source. Illustrated in Below Screen Shot :

To Load Balance AWS RDS Read Replica's Refer this article : http://harish11g.blogspot.com/2013/08/Load-balancing-Amazon-RDS-MySQL-read-replica-slaves-using-HAProxy.html

Second Tier Read Replica Deployment Architectures

One of the main complexity behind Multi-Level replication is that, if Tier 1 Read Replica if not properly architected/placed, it can be a single point of failure. Imagine a case where you need 4 Read Replicas for your Master DB. You can take following approaches as illustrated below while designing your infrastructure for this requirement.

In Architecture-1, Tier-1 Read replica is a Single Point of Failure. Instead if you split Tier-1 itself into two separate fleets it offers better availability than architecture-1. Since both Tier-1 RR put replication load on Master DB , you can by pass this using Hot Standby instance. Benefits of this approach is explained below in the best practice architecture. 

Best Practice High Availability Architecture for Second Tier Read Replica:

Condition 1 - Master DB Failure : Hot Standby becomes new master and Tier-1 Read Replicas points automatically to new source
Condition 2- Tier-1 Read Replica Failure : Entire Tier-1 and Associated Tier-2 has to be recreated. The Alternate Active Fleet of Tier-1+Tier-2 will serve the requests for high availability.
Condition 3 - Tier-2 Read Replica Failure : Only the non performing Tier-2 Read Replica instance has to be recreated
Condition 4- AZ NW problem: In event AZ-3 is failed, Requests are served by alternate fleet of Tier-1+Tier-2 in another AZ(AZ-4).

Multi-AZ Hot Standby Implementation is a recommended best practice when it comes to Multi-tiered Read replica implementation

• It is recommended to create Tier 1 Read Replicas from Multi-AZ DB instance to offload read queries from the source master DB instance for high traffic sites. If the source instance of a Multi-AZ deployment fails over to the secondary, any associated read replicas will be switched to use the secondary as their replication source automatically. This model guarantees high availability.
• Also when you initiate the creation of a Tier 1 read replica, Amazon RDS takes the DB Snapshot of your Standby DB instance (instead of Source DB) and begins replication. This model saves I/O suspension on your source DB during the snapshot process 

Other Points to Note:

Note : 
To Load Balance AWS RDS Read Replica's Refer this article : http://harish11g.blogspot.com/2013/08/Load-balancing-Amazon-RDS-MySQL-read-replica-slaves-using-HAProxy.html

P1) Circular replication are not allowed in this tiered replica creation process. 
P2)Third Tier cannot be created. Actually in production, very rarely we need third tier and it is not important feature
P3) When the "X" Tier replica is placed in Different AZ , you should factor few extra milliseconds of latency during replication.
P4) Before a DB instance (Master or Tier 1) can serve as a replication source, you must enable automatic backups on the source DB instance by setting the backup retention period to a value other than 0. This requirement does not apply to second tier replica as they are not source DB instance for another read replica.So create “Read Replica” option will appear only when the Backup Retention is set to minimum of 1 day for any level Read Replica creation. When we "create Read Replica" by default RR is created with Backup Retention set to 0. Use the modify option Illustrated in below screenshot and set the  Backup Retention Period.  :

P5) We have used t1.micro as RDS DB for article explanatory purpose. For production use cases please use proper instance types after proper capacity planning.
P6) AWS has released Parallel replica creation process, where you can create multiple Tier 1 and Tier 2 replicas in parallel. Since you no longer need to wait for one replica creation before starting the next one, it becomes easy to create multiple RR quickly. Without this feature, it would take hours to create a large Multi-tiered Replica setup.
P7) If a replica lags too far behind for your environment, the normal practice is to scale up or consider deleting and recreating the read replica. Imagine you have architecture where there are two Tier 1 and three Second Tier RR as illustrated in below architecture:

Now since Tier -1 RR is lagging , you are planning to delete and recreate the same. When you delete the Tier-1 RR, All the Second Tier Read Replica's will now become Standalone , Single AZ DB's. Once you have recreated the Tier -1 RR again and managed to retain the original end point as well, you cannot re-point these Second Tier RR to Tier -1 again. You need to recreate all 3 second tier from the Tier -1 again. For a application with heavy DB dependency and read traffic, it means 1/2 of the fleet is now down. This can lead to uncomfortable performance situation. This is not a ideal condition and AWS RDS team can take this in their Road Map. For such cases, it is better to create a new Tier -1 and Second tier fleet first , update your LB/App configs and then delete the old lagging fleet 
P8)Why it is better to have Multi-tiered Read Replica as the last resort in your architecture ?
8.a)Currently 30 Read Replica can be created overall for a master in two tiers. 30 Read replica is more than sufficient and usually turns out to be costly architecture approach. Use this approach only for cases, which demand heavy read and when application code cannot accommodate changes and are highly DB dependent.
8.b)It is recommended in DB world to stay away from Multi-level replication as much possible. Your architecture will be much simpler with one master and "X" replica slaves, rather than having tiered replica's. As we observed in above deployment architectures, the second tier replica slave will be a trouble to manage in event of replication delay, crashes and network problems affecting the Tier-1 RR or the Master DB. 
8.c)In case your application code can be redesigned, it is recommended to take following approaches before resorting to Second Tier replicas architecture

• Functional partition the RDS MySQL with Hot Stand By and Read Replica's
• Re-balance the DB load by using alternate data stores provided by AWS like DynamoDB, ElastiCache, CloudSearch etc.

If the above methods does not work for you, take the Second tier Read replica approach.

Thanks Senthil and Ram of 8KMiles for taking part in this analysis.