Readable MultiAZ Cluster with AWS RDS MySQL under the hood.

Amazon Web Services (AWS) very recently(March 02, 2022) announced the GA of its new RDS feature “Readable standby with Multi-AZ deployments” for MySQL. Yes !! you heard it right you can now use the standby instances created with Multi-AZ deployments for failover as well as for Read-scaling starting with version 8.0.26 and later for MySQL in RDS

Launching a MultiAZ Cluster

Now let us see how to launch this readable-Multi AZ cluster?

Region Availability: As this is a new feature now it is currently limited to the regions US-EAST-1 (N.Virginia), US-WEST-1 (Oregon), and EU-WEST-1 (Ireland), this list would be extended progressively

VPC requirement:

Before launching the instance, you should have SUBNET created for 3 AZ(Availability Zone) within the VPC since the cluster instances would be spawn across 3AZ by default

Hereunder the “Engine Option” am choosing “MySQL” and version as the latest “8.0.28”, Multi-AZ cluster is supported from version 8.0.26

Under “Availability & Durability” choose “MultiAZ DB cluster” as below

Instance type:

MultiAZ DB cluster is only supported with AWS Graviton2 family R6gd (Memory-optimized class ) and M6gd(standard class) DB instances

Am here choosing the lowest instance type for testing ie., db.r6gd.large(2vCPU & 16G RAM)

Storage:

MultiAZ DB cluster accepts only the “Provisioned IOPS SSD(i01)” with a minimum storage capacity of 100G and a minimum of 1000 IOPS

Another important thing to consider is “Storage autoscaling” is not supported in Readable MultiAZ

These are the main things to consider while launching the cluster other settings such as the backup, logs , and encryption remains similar to launching an RDS MySQL.

Architecture:

Below is the official deployment Architecture from AWS

Connectivity and End-points:

My cluster was available between 10 – 20 min, also you can see the 3 nodes are distributed in 3 availability zones. Similar to the Aurora cluster, the MultiAZ DB cluster also provides 3 end-points ie., Highly available cluster, read-only end-points, and individual instance end-point

Cluster endpoint

The cluster endpoint connects your application to the current primary DB instance for that Multi-AZ DB cluster. Your application can perform write operations such as DDL statements as well as read operations.

Reader endpoint

Each Multi-AZ DB cluster has a single built-in reader endpoint. You use the reader endpoint for read-only connections for your Multi-AZ DB cluster. The reader endpoint load balances connections across two readable standby DB instances to help your cluster handle a read-intensive workload

Instance endpoints

Each DB instance in a Multi-AZ DB cluster has its own unique instance endpoint.

You should always be mapping cluster and RO endpoints with applications for high availability.

How does this Cluster work under the hood?

I was very curious about the underlying tech that powers this cluster, on a high level it is a “GTID-based Replication”.

Below is the process list snap was taken from the cluster endpoint, wherein we can see two BINLOG DUMP threads exporting changelogs to the other nodes

 
+----+-----------------+--------------------+------+------------------+------+---------------------------------------------------------------+------------------+
| Id | User            | Host               | db   | Command          | Time | State                                                         | Info             |
+----+-----------------+--------------------+------+------------------+------+---------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost          | NULL | Daemon           | 3402 | Waiting on empty queue                                        | NULL             |
| 17 | rdsadmin        | localhost:63272    | NULL | Sleep            |    6 |                                                               | NULL             |
| 21 | rdsrepladmin    | 10.1.1.23:61334    | NULL | Binlog Dump GTID | 3026 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 22 | rdsrepladmin    | 10.1.4.8:26756     | NULL | Binlog Dump GTID | 3026 | Master has sent all binlog to slave; waiting for more updates | NULL             |
| 24 | rdstopmgr       | 10.1.1.23:61730    | NULL | Sleep            |    2 |                                                               | NULL             |
| 26 | rdstopmgr       | 10.1.1.23:61732    | NULL | Sleep            |    2 |                                                               | NULL             |
	

To make the replication robust and consistent AWS has implemented the following

GTID:

GTID is enabled by default with the cluster and its non-modifiable, which enforces better consistency and transaction tracking

Semi-Sync replication:

Semi-Sync replication is enabled which ensures that the committed transactions reach the relay log of at least one slave with the ACK mechanism. All Semi-Sync variables are again non-adjustable with the Readable Multi-AZ cluster, below is the trimmed out put from “Show plugins” showing active status of semi-sync plugin

mysql > show plugins;
+———————————-+———-+——————–+—————————–+———+

| Name | Status | Type | Library | License |

+———————————-+———-+——————–+—————————–+———+

| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |

| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |

+———————————-+———-+——————–+—————————–+———+

While doing a parallel load of 8 tables using sysbench i can observe the threads from processlist were waiting for Semi-Sync slave ack (Waiting for semi-sync ACK from slave) as below and obvious replication lag was seen at the other nodes

 
379 | admin           | 172.31.10.82:39138  | sbtest | Query            |     1 | Waiting for semi-sync ACK from slave                          | INSERT INTO sbtest8(k, c, pad) VALUES(563197, '97091453334-30970297900-36165130330-36020191197-44594 |
	

Below is the working block diagram illustrating working of Semi-sync replication

Faster replication:

In-order to make the replication sync faster between the source and replica, below are the variables added to the cluster configuration.

Binlog_group_commit_sync_delay:

The default value has to be set to 30, which adds a delay of 30 microseconds to writes for enabling more transactions to be synchronized together, This helps in faster sync of slave by slowing the master

Multi-Threaded Replication(MTR):

With LOGICAL_CLOCK as a parallelism type decides which transactions can be executed in parallel based on timestamps. Slave parallel worker set to 16 irrespective of the instance type helps in applying the transactions parallel.

WRITESET replication:

Along with these AWS has also enabled writeset based replication with Mysql 8, to know more on writeset based replication: https://www.mydbops.com/blog/fastest-parallel-replication-method-in-mysql-8/

Fail-over:

Now finally let’s discuss about the failover, By combining all the above discussed points fail-over is fairly simple, it just repointing the replication chain to new instance with auto-positioning and updating the end-points(route53).

With a multi-AZ cluster, RDS provides a faster fail-over time of 35 secs wherein the normal multi-AZ provides and fail-over time of 60-120secs

Below are the steps to initiate fail-over

Advantages:

• You can use the standby instances for read-scaling
• Enhanced availability with 2 readable stand-by
• Faster fail-over in needs of maintenance or disaster

Disadvantages:

• Transaction lag is possible during heavy writes due to default Async replication
• Unavailability of storage autoscaling lets you to plan capacity in prior also with active monitoring
• You cannot add more nodes to the cluster, it is limited to 3 nodes
• You cannot stop a node or the entire cluster, it is just either reboot or delete all
• Limited instance class types available.
• Enforcing the use of PIOPS with the cluster