AWS Database Blog
Best strategies for achieving high performance and high availability on Amazon RDS for MySQL with Multi-AZ DB Clusters
In Amazon Relational Database Service (Amazon RDS), Multi-AZ deployments can have one or two standby DB instances. A Multi-AZ DB instance deployment has a single standby DB instance that provides failover support, but doesn’t serve read traffic. When the deployment has two readable standby DB instances, it’s called a Multi-AZ DB cluster deployment. A Multi-AZ DB cluster deployment has standby DB instances that provide failover support and can also serve read traffic.
Multi-AZ DB cluster deployment is best suited for application workloads that need additional read capacity or, for those workloads that are sensitive to transaction latencies and must be highly available. In addition to that, Multi-AZ DB cluster deployment includes new features such as Amazon RDS Optimized Writes, which delivers an improvement of up to 2x in write transaction throughput, and Amazon RDS Optimized Reads, which provides up to 2x faster complex query processing.
If you are currently in the process of planning a major version upgrade for your existing Amazon RDS for MySQL instances running MySQL 5.7.x, you have the opportunity to migrate to an Amazon RDS for MySQL 8.0.x Multi-AZ DB cluster deployment, which can effectively support your RDS for MySQL workloads. With Multi-AZ cluster deployments, Amazon RDS supports database workloads that require more read capacity and consistent transaction latency, while providing high availability and durability. Multi-AZ DB clusters are supported for Amazon RDS for MySQL 8.0.28 and higher, and can be utilized in all Regions where Amazon RDS for MySQL is supported.
In this post, we explore key considerations while upgrading Amazon RDS for MySQL 5.7.x to MySQL 8.0.x. We also discuss how to adopt a Multi-AZ DB cluster deployment for your existing RDS for MySQL workloads, and best practices associated with the migration.
Key new features introduced in Multi-AZ DB clusters
Amazon RDS Multi-AZ DB cluster deployment is ideal for production database workloads. Multi-AZ DB cluster deployments use semisynchronous replication, which requires acknowledgment from at least one reader DB instance in order for a change to be committed. This means that the primary will not acknowledge a commit to a client until one of the replicas has acknowledged receiving the transaction. In case of an infrastructure failure (for example, instance hardware failure, storage failure, or network disruption), of your writer DB instance in a Multi-AZ DB cluster, Amazon RDS automatically fails over to a reader DB instance in a different Availability Zone. Multi-AZ DB clusters support database workloads that require more read capacity and consistent transaction latency by provisioning one primary and two readable standby DB instances across three Availability Zones. The standby DB instances act as automatic failover targets and serve read traffic.
For read-heavy workloads, Multi-AZ DB cluster deployments also allow adding read replicas, to scale beyond the compute or I/O capacity of the cluster. This allows you to direct read traffic to one or more DB instance read replicas, which can be Single-AZ or can also be using Multi-AZ instance deployment. In addition to two readable standbys in the Multi-AZ DB cluster topology, you can create up to 15 read replicas from your Multi-AZ DB cluster.
Multi-AZ DB cluster deployment for Amazon RDS for MySQL supports Optimized Writes, where your RDS for MySQL databases write only once when flushing data to durable storage without the need for the doublewrite buffer. This provides up to two times improvement in write transaction throughput for your database. RDS Optimized Writes is available as a default option from Amazon RDS for MySQL version 8.0.30 and higher with specific instance classes. For more information, refer to Using RDS Optimized Writes.
In addition, on a Multi-AZ DB cluster deployment, the Optimized Reads feature is turned on by default on supported instances. An RDS for MySQL DB instance that uses Optimized Reads can achieve up to two times faster query processing compared to a DB instance that doesn’t use it.
Key considerations while moving to Multi-AZ DB cluster deployment
While planning to use RDS Multi-AZ DB clusters, there are a few things to keep in mind as you prepare the move. The first thing to keep in mind is the engine version. Multi-AZ DB clusters with Amazon RDS for MySQL require Amazon RDS for MySQL Version 8.0.28 and higher and RDS Optimized Writes requires version 8.0.30 and higher. Therefore, if you are performing a major version upgrade from Amazon RDS for MySQL 5.7.x versions, you can also use this time to upgrade to the latest Amazon RDS for MySQL version 8, which is version 8.0.33 as of this writing. For the latest version, refer to Supported MySQL minor versions on Amazon RDS. For reduced downtime and risk during a major version upgrade, you can also use RDS Blue/Green Deployments.
Another thing to keep in mind is the supported DB instance class for Multi-AZ DB cluster deployment. Currently, Multi-AZ DB clusters support “d” type instance class for example, db.m5d, db.m6gd, db.r5d, db.r6gd, and db.x2iedn. For the most up-to-date considerations, refer to Region and version availability and Instance class availability. You can also leverage Reserved DB instances with Multi-AZ DB cluster deployments. For more information, see Reserved DB instances for a Multi-AZ DB cluster. In addition, we recommend reviewing the current limitations while using Multi-AZ DB cluster deployments to ensure a smooth transition from your current architecture.
MySQL 8.0 introduces several changes when compared to MySQL 5.7, which may result in complications during the upgrade process from MySQL 5.7 to MySQL 8.0. When you start an upgrade, Amazon RDS runs prechecks automatically to detect these incompatibilities. If there are incompatibilities, Amazon RDS prevents the upgrade and provides a log for you to learn about them. You can then use the log to prepare your database for the upgrade to MySQL 8.0 by reducing the incompatibilities. Therefore, it is advisable to make necessary preparations on your databases, and thoroughly test to ensure a smooth and successful upgrade. You can use a DB snapshot to test the upgrade by running as many of your quality assurance tests against a restored instance that is upgraded. While upgrading, you can choose the latest MySQL 8.0 minor version supported on Amazon RDS for MySQL.
Migration options from an existing Single-AZ or Multi-AZ instance to Multi-AZ DB cluster deployment
Now that your DB instance is on a supported Multi-AZ DB cluster version and instance class, let’s review the migration options to move from an existing RDS for MySQL Single-AZ or RDS for MySQL Multi-AZ instance deployment.
Use a read replica with minimal downtime
As one of the most convenient options in terms of effort and minimal downtime, you can create a Multi-AZ DB cluster as a read replica from your existing Single-AZ or Multi-AZ DB instance deployment that will act as the replication source. This operation will take care of not only performing an initial load of your data, but also setting up replication for any changes, which means the DB instance can process write transactions during the migration to a Multi-AZ DB cluster.
For this migration method, as a prerequisite, configure gtid-mode, and enforce_gtid_consistency parameters to ON on your source DB instance. After you have upgraded your existing RDS for MySQL instances to version 8.0.28 or higher, you can set these parameters in the corresponding RDS parameter group. The apply type for both these parameters is static, so it would require a manual reboot before the parameters go in-sync. You can reboot immediately or wait until your maintenance window. After setting up the parameters, refer to Creating and promoting the Multi-AZ DB cluster read replica for complete steps on creating your Multi-AZ DB cluster read replica. Once the Multi-AZ DB replica cluster is in Available status, you can monitor the replication lag using the ReplicaLag CloudWatch metric and connect to it using the Multi-AZ DB cluster endpoint to perform any testing as replication from your existing RDS instance is happening in the backend.
When you’re ready, stop any transactions from being written to the source DB instance, and then wait for all updates to be made to the read replica. You can monitor the replication lag using the ReplicaLag metric. When the replica lag is a near-zero value, you can promote the read replica to be a standalone Multi-AZ DB cluster. You can create and promote a Multi-AZ DB cluster read replica using the AWS Management Console, AWS Command Line Interface (AWS CLI), or Amazon RDS API. For more details regarding creating and managing a Multi-AZ DB cluster read replica for the migration, refer to Migrating to a Multi-AZ DB cluster using a read replica.
The following diagram illustrates the architecture for this option.
Use an Amazon RDS snapshot
You can also migrate to a Multi-AZ DB cluster option using the Amazon RDS snapshot and restore option. Because you have upgraded your Amazon RDS for MySQL engine version to be using one of the supported engine versions and instances for Multi-AZ DB clusters, you can restore a snapshot of an existing Single-AZ DB instance deployment or Multi-AZ DB instance deployment to a Multi-AZ DB cluster.
You can create a DB snapshot or use an existing snapshot and then restore from it to form a new RDS instance. Because you’re restoring to an RDS Multi-AZ DB cluster deployment, review the available settings in Settings for creating Multi-AZ DB clusters. You can perform this operation through the console or using the AWS CLI or AWS SDK.
The restore operation can take time depending on the size of the database. As an optional step, if your workload can’t tolerate a longer downtime, you can configure binary log replication for new-zero migration downtime. You can enable binary logs on the source RDS for MySQL instance and run SHOW MASTER STATUS command to get the binary log position from which you can set up replication.
The following diagram illustrates the architecture for this option.
Use mysqldump and binary log replication
Whether you are migrating from self-managed MySQL or from self-managed MySQL on Amazon EC2 and would like to perform migration to a Multi-AZ DB cluster using MySQL native tools, you can also use popular logical backup tools like mysqldump. If you are migrating from an older DB instance class in an existing RDS for MySQL Single-AZ or Multi-AZ instance deployment, you can use this method to take advantage of the Optimized Writes feature in Multi-AZ DB clusters. With this tool, you can export schema definitions and data from your existing MySQL instances and restore the backup to the target Multi-AZ DB cluster. For larger datasets, if you require a near-zero downtime migration, you can use mysqldump to create backup files that include not only the data, but also the binary log position at the time when the consistent backup is taken using the parameter --master-data=2, as shown in the following example code:
Binary log replication would allow for continuous change data capture (CDC) and when the replica lag is near zero, you can perform the cutover. For step-by-step instructions on setting up this replication, refer to Replicate between your external database and new Amazon RDS DB instance.
You can also use mydumper and myloader for multithreaded migration. They offer advanced features such as dumping and loading data using parallel threads, and creating dump files in a file-per-table fashion.
The following diagram shows the architecture for this option.
Conclusion
When you decide to upgrade your existing RDS for MySQL resources to the latest version on Amazon RDS for MySQL 8.0.x, you can also use the RDS Multi-AZ DB cluster deployment option. This option supports workloads that require more read capacity and consistent transaction latency, while providing high availability and durability. In this post, we discussed the benefits and new enhancements of Multi-AZ DB clusters and possible minimal downtime migration methods.
You can start using the Multi-AZ DB cluster deployment with two readable standbys today by visiting the Amazon RDS console and creating a new RDS instance or migrating your existing RDS instance by upgrading the database engine version to a supported MySQL version.
About the Authors
Shagun Arora is a Database Specialist Solutions Architect at Amazon Web Services. She works with customers to design scalable, highly available, and secure solutions in the AWS Cloud.
Vijay Karumajji is a Principal Database Solutions Architect with Amazon Web Services. He works with our customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.