New Amazon RDS for MySQL and PostgreSQL Multi-AZ Deployment Options

Today, we're announcing a new Amazon Relational Database Service (RDS) Multi-AZ deployment option with up to 2x faster transaction commit latency, automatic failover typically less than 35 seconds, and readable standby instances.

Amazon RDS offers two replication options to enhance availability and performance:

• Multi-AZ deployments provide high availability and automatic failover. Amazon RDS creates a storage-level replica of the database in the second Availability Zone. It then synchronously replicates the data from the primary DB instance to the standby DB instance for high availability. The primary database instance serves application requests, while the standby database instance takes over at any time in the event of a failure. Amazon RDS manages all aspects of failure detection, failover, and repair operations so that applications using the database are highly available.
• Read replicas allow applications to scale their read operations across multiple database instances. The database engine asynchronously replicates the data to the read replica. Applications send write requests ( INSERT , UPDATE and DELETE ) to the primary database, read requests ( SELECT ) can be implemented between read replicas load balancing. If the primary node fails, you can manually promote the read replica to be the new primary database .

Multi-AZ deployments and read replicas serve different purposes. Multi-AZ deployments provide high availability, durability, and automatic failover for your applications. Read replicas provide read scalability for your application.

But what if an application requires high availability, automatic failover, and read scalability at the same time?

Introducing the new Amazon RDS Multi-AZ deployment option with two readable standby instances.

Starting today, we will be adding a new option to deploy RDS databases. This option combines automatic failover with Read Replica: Amazon RDS Multi-AZ with two readable standby instances. This deployment option is available for MySQL and PostgreSQL databases. This is adatabase cluster with one primary and two readable standbys. It provides 2x faster transaction commit latency and automatic failover, typically in less than 35 seconds.

With the new Multi-AZ database cluster deployment option enabled, RDS will be configured with one primary database and two read replicas in three different Availability Zones. It then monitors and enables failover if the primary node fails.

Like a traditional read replica, the database engine replicates data between the primary and the read replica. Similar to the Multi-AZ single-standby deployment option, RDS automatically detects and manages failover for high availability.

You don't have to choose between high availability or scalability; a Multi-AZ database cluster with two readable standby replicas achieves both qualities.

What are the benefits?

This new deployment option brings you four major advantages over traditional Multi-AZ deployments: improved commit latency, faster failover, readable standby instances, and optimized replication.

First, write operations become faster when using a Multi-AZ database cluster. The new Multi-AZ DB cluster instances utilize the M6gd and R6gd instance types. These instances are powered by Amazon Graviton2 processors . Equipped with fast NVMe SSDs for local storage, they are ideal for high-speed and low-latency storage requirements. They offer up to 40% better price/performance and 50% more local storage capacity per vCPU than comparable x86-based instances.

Multi-AZ DB instances use Amazon Elastic Block Store (EBS) to store data and transaction logs. New Multi-AZ DB cluster instances use instance-provided local storage to store transaction logs. Local storage is optimized to provide applications with low latency and increased input/output operations per second (IOPS). Write operations are first written to the local storage transaction log and then flushed to persistent storage on the database storage volume.

Second, failover operations typically execute faster than in a multi-AZ DB instance scenario . Read replicas created by a new Multi-AZ DB cluster are full-fledged DB instances. Failover for this system can take up to 35 seconds, plus the time to apply any pending transaction logs. In the event of a failover, the system will fully automatically upgrade the new primary node and reconfigure the old primary node as the new reader instance.

Third, the two standby instances are hot standby instances . Your application can send its read requests ( SELECT ) to these standby instances using the cluster reader endpoint. It allows your application to evenly distribute the database read load among the instances of the database cluster.

Finally, processing the transaction log with local storage optimizes replication operations . Existing Multi-AZ DB instances replicate all changes at the storage level. The new Multi-AZ database cluster replicates only the transaction log and uses a quorum mechanism ) to confirm that at least one standby instance has acknowledged the change. Database transactions are committed synchronously when one of the secondary instances confirms that the transaction log has been written to its local disk.

Migrate an existing database

For those who have existing RDS databases and are willing to take advantage of this new Multi-AZ database cluster deployment option, you can take database snapshots to create storage-level backups of existing database instances. After the snapshot is ready, you can use the Multi-AZ DB cluster deployment option to create a new DB cluster based on this snapshot. Your new Multi-AZ database cluster will be a perfect replica of your existing database.

Let's see it in action

First, I pointed my browser to the Amazon Cloud Technology Management Console and navigated to RDS . The Multi-AZ DB cluster deployment option is available for MySQL 8.0.28 or later and PostgreSQL versions 13.4 R1 and 13.5 R1. I choose either database engine and make sure the version meets the minimum requirements. The rest of the process is the same as the standard Amazon RDS database launcher.

Under Deployment options, I choose PostgreSQL, version 13.4 R1, and under Availability and Durability, I choose Multi-AZ DB cluster.

The following diagram shows such a deployment service:

I can also choose the set of Availability Zones that RDS uses for the cluster if needed. To do this, I created a database subnet group and assigned the cluster to that subnet group.

After startup, I verify that three database instances have been created. I also noticed that Amazon RDS provides two endpoints: two primary endpoints for readable standby instances and a load balancing endpoint.

To test the new cluster, I created an Amazon Linux 2 EC2 instance in the same VPC in the same security group as the database, and made sure I attached an IAM role with the AmazonSSMManagedInstanceCore managed policy. This allows me to connect to the instance using SSM instead of SSH .

Once the instance is up, I will use SSM to connect to the instance. I have PostgreSQL client tools installed.

Zsh

 sudo amazon-linux-extras enable postgresql13
sudo yum clean metadata
sudo yum install postgresql

I connect to the main database. I create a table and insert a record.

Zsh

 psql -h awsnewsblog.cluster-c1234567890r.us-east-1.rds.amazonaws.com -U postgres

postgres=> create table awsnewsblogdemo (id int primary key, name varchar);
CREATE TABLE

postgres=> insert into awsnewsblogdemo (id,name) values (1, 'seb');
INSERT 0 1

postgres=> exit

To verify that replication is working as expected, I connect to the read replica. Note the -ro- in the endpoint name. I checked the table structure and entered a SELECT statement to confirm that the data was copied.

Zsh

 psql -h awsnewsblog.cluster-ro-c1234567890r.us-east-1.rds.amazonaws.com -U postgres

postgres=> \dt

              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | awsnewsblogdemo | table | postgres
(1 row)

postgres=> select * from awsnewsblogdemo;
 id | name
----+------
  1 | seb
(1 row)

postgres=> exit

In the event of a failover, the application will be disconnected from the primary database instance. In this case, it's important that the application-level code tries to re-establish the network connection. After a short time, the DNS name of the endpoint will point to the alternate instance and your application will be able to reconnect.

To learn more about Multi-AZ DB clusters, see ourdocumentation .

Pricing and Availability

Amazon RDS Multi-AZ deployments with two readable standby replicas are typically available in the following regions: US East (N. Virginia), US West (Oregon), and EU (Ireland). We will add more regions to this list.

You can use it with MySQL 8.0.28 or later and PostgreSQL version 13.4 R1 or 13.5 R1.

Pricing depends on instance type. In the US region, On-Demand pricing starts at $0.522 per hour for M6gd instances and $0.722 per hour for R6gd instances. As always, the Amazon RDS pricing page includes details for MySQL and PostgreSQL .

You can get started right away .

Click to learn more about Amazon cloud technology database products .