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
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
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)
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.
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
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.
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
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 is enabled by default with the cluster and its non-modifiable, which enforces better consistency and transaction tracking
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
In-order to make the replication sync faster between the source and replica, below are the variables added to the cluster configuration.
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
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.
Along with these AWS has also enabled writeset based replication with Mysql 8, to know more on writeset based replication: https://mydbops.wordpress.com/2021/02/14/writeset-replication-in-mysql-8/
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
- 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
- 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