Managing RDS Read Replicas on AWS

Read replicas in Amazon RDS allows to create read-only copy of the primary or the Source DB instance. The Read Replicas in Amazon RDS are created using the In-Built functionality of the various DB engines like MariaDB, MySQL, PostgreSQL, Oracle and SQL Server. The Read Replicas are created by taking the snapshot of the Source DB instance which eventually creates a Read-Only instance of the Source DB.

The purpose of using Read Replicas is that it can help to minimize the workload coming from the applications on the Source DB by routing the .read queries to the read DB instance. In case the database is having read-heavy workloads then read replicas can be used for scaling out the horizontally as well. Once the snapshot is taken from the Source DB and the Read replica is created, the changes that are then carried out on the source DB are updated asynchronously on the Read Replicas. In order to connect to the Read Replica the endpoint needs to be specified is specific traffic needs to be routed for read queries from the applications.

The Read replicas in Amazon RDS can be setup only for the specified RDBMS engines mentioned earlier. These can be setup within the same AWS Region as that of the source DB or it can be in some other AWS region which is often termed as Cross AWS region read replicas. However there are limitations with respect to setting up cross AWS region read replicas based on the RDBMS engine since not all the Amazon RDS DB engines supports it.
One of the major differences between Read Replicas and Multi A-Z deployments is that the Read Replicas uses asynchronous method for applying the changes while Multi A-Z deployments are synchronous in nature. The writes will always happen to the Primary\Source DB Instance in case of Read Replicas.

Overview of Amazon RDS Read Replicas

There are multiple benefits of using Read Replicas:

• Minimizing downtime - Multi A-Z deployments are often used for Disaster Recovery, while Read Replicas are suggested for Scalability. A well-designed and tested DR plan is critical for maintaining business continuity after a disaster. However in case of Database failure, one of the slaves as part of the Read Replicas can be promoted as master while the application will continue to run smoothly.
• Split up reads between databases - If the application workload consists of more Read queries compared to transactional queries, then the number of read replicas can be increased and the load on the master DB can be reduced by redirecting the traffic to the read instance.
• Enhanced security – All traffic between the source and destination database is encrypted irrespective whether the Read replica is in the same AWS region or cross AWS region. In case of cross region, Amazon RDS will setup the required secured communications including the security groups permissions.
• Read Replica can be manually promoted as a standalone database instance.
• Read Replicas can also be used to take logical backups, if the backups needs to be stored externally to RDS.
• Read Replicas of the Read Replica Instances can also be created except for Oracle and SQL Server.
• The maximum number of Read Replicas that can be created to one Master DB instance is 5 each with its own DNS endpoint.

By default, a read replica is created with the same storage type as the source DB instance. However, the read replica can be created that has a different storage type from the source DB instance based on the options listed in the following table.

Differences between Read Replicas for different DB Engines

Although Amazon RDS uses the Inbuilt Replication functionality of the DB engines there are significant differences in terms of the way it is implemented.

Please refer to AWS Documentation for complete details.

Creating a Read Replica

The Read Replica can be created from existing MySQL, MariaDB, Oracle, PostgreSQL DB, or SQL Server instance using the AWS Management Console, AWS CLI, or RDS API calls by specifying the SourceDBInstanceIdentifier parameter which is the DB Instance Identifier from which the Read replica needs to be created.

While creating the Read replica the snapshot of the Source DB Instance is taken and then replication is enabled. During this timeframe there will be slight I/O suspension that might happen on the Source DB which can typically last up to one minute. If the Source DB already exists in a Multi A-Z deployment then the Read Replica would be created from the Secondary Database which is part of the Multi A-Z and not from the primary DB instance. As is the case usually with Database if there are long running queries already running on the database then the process of creating the read replicas will be slightly longer than the usual recommended one. Hence it is advisable to wait for the long running or active transactions to complete before proceeding with the process of creating the read Replicas.

If more than One Read Replica is created at the same time then only one DB snapshot will be taken. However if multiple Read Replicas are created at different point of time then every time a Difference snapshot will be taken during that time frame so that the newly created Read Replicas has the latest snapshot thereby reducing the time required to sync with the primary one.

While creating read replicas there are certain prerequisites that needs to be taken into consideration.

1. The automatic backups must be enabled on the source DB instance by setting the backup retention period to a value other than 0. This requirement also applies to a read replica that is the source DB instance for another read replica. For MySQL DB instances, automatic backups are supported only for read replicas running MySQL 5.6 and later, but not for MySQL versions 5.5. To enable automatic backups on an Amazon RDS MySQL version 5.6 and later read replica, first create the read replica, then modify the read replica to enable automatic backups.
2. Within an AWS Region, all read replicas must be created in the same virtual private cloud (VPC) based on Amazon VPC as the source DB instance. This requirement applies even if VPC peering is configured in the AWS Region.
3. Another thing that needs to be considered is that if the Read Replicas are configured in a separate VPC or region there will be data transfer charges that will be applied for the same.

Please refer to the below steps for creating read replica from a source MySQL, MariaDB, Oracle, PostgreSQL, or SQL Server DB instance.

Using Console:

1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
2. In the navigation pane, choose Databases.
3. Choose the MySQL, MariaDB, Oracle, PostgreSQL, or SQL Server DB instance that you want to use as the source for a read replica. For Actions, choose Create read replica.
4. Choose the instance specifications as per the requirement. It is recommended to use the same DB instance class and storage type as the source DB instance for the read replica. For Multi-AZ deployment, choose Yes to create a standby of the replica in another Availability Zone for failover support for the replica. Creating the read replica as a Multi-AZ DB instance is independent of whether the source database is a Multi-AZ DB instance.
5. For DB instance identifier, enter a name for the read replica. Adjust other settings as needed. Choose the other settings that are required. Choose Create read replica.

Using AWS CLI:
To create a read replica from a source MySQL, MariaDB, Oracle, PostgreSQL, or SQL Server DB instance, use the AWS CLI command create-db-instance-read-replica.

Using RDS API:
To create a read replica from a source MySQL, MariaDB, Oracle, PostgreSQL, or SQL Server DB instance, invoke the Amazon RDS API function CreateDBInstanceReadReplica.

Promoting a Read Replica to Be a Standalone DB Instance

The Read Replica can also be promoted to be a Standalone DB Instance and when it is being used for standalone DB the DB instance is rebooted before getting promoted.

Performing DDL operations (MySQL and MariaDB only) :

Maintenance activities like carrying out index rebuilding can cause significant performance bottlenecks on the DB instance. Once the Read Replica is in sync these operations can be performed then on the Read Replica Instance post which the read replica can be promoted and the traffic from the applications can be redirected towards it.

Sharding:

Sharding also known as Horizontal Partitioning is often used in RDBMS for high scalability, high availability and fault tolerance for Data storage. Sharding is a technique that splits data into smaller subsets and distributes them across a number of physically separated database servers. Each server is referred to as a database shard. All database shards usually have the same type of hardware, database engine, and data structure to generate a similar level of performance.

Once the read replica is promoted to a standalone Instance, the new DB instance that is created retains the backup retention period, the backup window, the option group, and the parameter group of the source DB Instance. This read replica once promoted can no longer be used as target for replication. If there are multiple read replicas for a particular Source DB Instance and one of them is promoted to a standalone instance then it will not have any effect on the replication between the source and the remaining Read Replicas.

The promotion of Read replica can take certain duration depending upon the size of the Read Replica and also if the replica is in backing up status it cannot be promoted until the backup process is completed.
The following steps show the general process for promoting a read replica to a DB instance:

1. Stop any transactions from being written to the read replica source DB instance, and then wait for all updates to be made to the read replica. Database updates occur on the read replica after they have occurred on the source DB instance, and this replication lag can vary significantly. Use the Replica Lag metric to determine when all updates have been made to the read replica.
2. For MySQL and MariaDB only: If you need to make changes to the MySQL or MariaDB read replica, you must set the read-only parameter to 0 in the DB parameter group for the read replica. You can then perform all needed DDL operations, such as creating indexes, on the read replica. Actions taken on the read replica don't affect the performance of the source DB instance.
3. Promote the read replica by using the Promote option on the Amazon RDS console, the AWS CLI command promote-read-replica, or the PromoteReadReplica Amazon RDS API operation.

Using Console:

To promote a read replica to a DB instance

1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
2. In the Amazon RDS console, choose Databases. The Databases pane appears. Each read replica shows Replica in the Role column.
3. Choose the read replica that you want to promote. For Actions, choose Promote read replica.
4. On the Promote Read Replica page, enter the backup retention period and the backup window for the new promoted DB instance.
   When the settings are as you want them, choose Continue. On the acknowledgment page, choose Promote Read Replica.

Using AWS CLI:

To promote a read replica to a DB instance, use the AWS CLI promote-read-replica command.

Using RDS API:

To promote a read replica to a DB instance, call PromoteReadReplica.

Creating a Read Replica in a Different AWS Region

The Read Replica in Different AWS region usually referred to as Cross region Replica can be created using Amazon RDS for MySQL, PostgreSQL, Oracle and MariaDB. It is not yet supported for Amazon RDS on SQL Server.

Read Replica in a different AWS region helps to improve DR capabilities in case one of the AWS regions face an outage. In case the users are located in different regions then by creating a Read replica closest to the users AWS region the latency can be reduced for the read operations. It can serve as an alternate method of CDN but just for Database not from application perspective.

Creating a read replica in a different AWS Region from the source instance is similar to creating a replica in the same AWS Region. To create an encrypted read replica in a different AWS Region than the source DB instance, the source DB instance must be encrypted.

Cross-Region read replicas aren't supported in the following opt-in AWS Regions:

• Africa (Cape Town)
  Asia Pacific (Hong Kong)
  Europe (Milan)
  Middle East (Bahrain)

The following procedures show how to create a read replica from a source MariaDB, MySQL, Oracle, or PostgreSQL DB instance in a different AWS Region.

Using Console:

To create a read replica across AWS Regions with the console

1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
2. In the navigation pane, choose Databases.
3. Choose the MariaDB, MySQL, Oracle, or PostgreSQL DB instance that you want to use as the source for a read replica. For Actions, choose Create read replica. To create an encrypted read replica, the source DB instance must be encrypted.
4. Choose the instance specifications as per the requirements.
5. Choose the other settings as per the requirements.For DB instance identifier, enter a name for the read replica.
6. In the Network & Security section, choose a value for Destination region and Destination DB subnet group.
7. To create an encrypted read replica in another AWS Region, choose Enable Encryption, and then choose the Master key. For the Master key, choose the AWS Key Management Service (AWS KMS) key identifier of the destination AWS Region.
8. Choose the other settings that you want to use. Choose Create read replica.

Cross-Region Replication Considerations

The considerations that are used during replication for Read Replica in same region are same even Read Replica in different region. There are some other additional considerations which apply when replicating between AWS regions:-
The replication between AWS Regions is supported only while using the following Amazon RDS DB instances:

• MariaDB (all versions).
• MySQL version 5.6 and later.
• Oracle Enterprise Edition (EE) engine version 12.1.0.2.v10 and higher 12.1 versions, all versions of 12.2, and all versions of 18.0.An Active Data Guard license is required.
• PostgreSQL version 9.4.7 and later.

A source DB instance can have cross-Region read replicas in multiple AWS Regions.

The cross-region Amazon RDS read replica can be created from a source Amazon RDS DB Instance which is not a replica of another Amazon RDS DB Instance.

There is considerable amount of lagging in Cross region RDS DB Instance as compared to same region Read Replica. The lagging time will depend upon the network channels between regional data centres.

Within an AWS Region, all cross-Region read replicas created from the same source DB instance must either be in the same VPC or be outside of a VPC.

The read replica uses the default DB parameter group for the specified DB engine. The read replica uses the default security group.

For MariaDB, MySQL, and Oracle DB instances, when the source for a cross-Region read replica is deleted, the read replica is promoted.

For PostgreSQL DB instances, when the source for a cross-Region read replica is deleted, the replication status of the read replica is set to terminate. The read replica is not promoted.

Cross-Region Replication Costs

The data transferred for cross-Region replication incurs Amazon RDS data transfer charges. These cross-Region replication actions generate charges for the data transferred out of the source AWS Region.

Monitoring Read Replication

The status of Read Replication can be monitored in several ways. The Amazon RDS console shows the status of a read replica in the Availability and durability section of the read replica details. To view the details for a read replica, choose the name of the read replica in the list of instances in the Amazon RDS console. The status of a read replica can be one of the following:

• replicating – The read replica is replicating successfully.
• replication degraded (SQL Server only) – Replicas are receiving data from the master instance, but one or more databases might be not getting updates.
• error – An error has occurred with the replication. Check the Replication Error field in the Amazon RDS console or the event log to determine the exact error.
• terminated (MariaDB, MySQL, or PostgreSQL only) – Replication is terminated. This occurs if replication is stopped for more than 30 consecutive days, either manually or due to a replication error.
• stopped (MariaDB or MySQL only) – Replication has stopped because of a customer-initiated request.
• replication stop point set (MySQL only) – A customer-initiated stop point was set using the mysql.rds_start_replication_until stored procedure and the replication is in progress.
• replication stop point reached (MySQL only) – A customer-initiated stop point was set using the mysql.rds_start_replication_until stored procedure and replication is stopped because the stop point was reached.

The replication lag in Amazon CloudWatch can be monitored by viewing the Amazon RDS ReplicaLag metric. When the ReplicaLag metric reaches 0, the replica has caught up to the source DB instance. If the ReplicaLag metric returns -1, then replication is currently not active. ReplicaLag = -1 is equivalent to Seconds_Behind_Master = NULL. ReplicaLag returns -1 if RDS can't determine the lag, such as during replica setup, or when the read replica is in the error state.