Achieve one second or less of downtime with ProxySQL when upgrading Amazon RDS Multi-AZ deployments with two readable standbys
In this post, we explore how to use ProxySQL to achieve a downtime of typically 1 second or less when performing a minor version upgrade on Amazon RDS for MySQL Multi-AZ deployments with two readable standbys (Amazon RDS Multi-AZ DB cluster). ProxySQL is an open source proxy for MySQL.
Currently, minor version upgrades or system maintenance updates for Amazon Relational Database Service (Amazon RDS) for MySQL databases can take several minutes and require a planned downtime. Custom-built upgrade solutions are not fully automated and are complex to use with database fleets because they need coordination among various application users, require additional infrastructure, are resource intensive, and are expensive to build.
You can now shorten the downtime required to perform minor version upgrades or system maintenance updates to typically 35 seconds or less by using Amazon RDS Multi-AZ DB cluster. When combined with ProxySQL, this downtime can be further reduced to typically 1 second or less.
When upgrading the minor version of Amazon RDS Multi-AZ DB cluster, the process begins by applying the patch to each of the readers in sequence. After both the readers are upgraded, one of them is promoted to be the new writer, followed by an upgrade of the old writer. This process of switching to the new writer takes approximately 1 second or less. However, applications can still see a downtime of at least 35 seconds. This is due to Domain Name System (DNS) propagation delays of the cluster endpoint because the cluster endpoint changes after the new writer is promoted during the upgrade process.
By setting up ProxySQL with your Amazon RDS Multi-AZ DB cluster, you can now reduce the downtime for minor version upgrades and system maintenance updates to typically 1 second or less. ProxySQL has the ability to monitor your database for changes in the writer and reroute traffic to the new writer. When configured properly for Amazon RDS Multi-AZ DB clusters, ProxySQL is not impacted by DNS propagation delays of the cluster endpoint, and quickly detects the change to the new writer to keep your application downtime to typically under 1 second during minor version upgrades.
In the following sections, we demonstrate how to configure ProxySQL for Amazon RDS for MySQL Multi-AZ DB clusters to reduce your application downtime to typically 1 second or less during minor version upgrades or system maintenance updates.
Before walking through the steps involved to set up ProxySQL, you need the following:
- An Amazon RDS for MySQL Multi-AZ DB cluster. You can use an existing cluster or create a new one. Remember to use a cluster running an older MySQL version, if you want to perform a minor version upgrade to test the downtime of 1 second or less.
- An Amazon Elastic Compute Cloud (Amazon EC2) instance for ProxySQL. The EC2 instance should be in the same region and VPC as your Amazon RDS Multi-AZ DB cluster. It’s recommended to use Amazon Linux 2 or higher for the EC2 instance. ProxySQL version 2.5.1 or higher is recommended. If you already have an EC2 instance with ProxySQL running in the same region and VPC as your Amazon RDS Multi-AZ DB cluster, you can use the same instance and follow along with this post to set it up. Otherwise, on the new EC2 instance, install ProxySQL and complete the initial setup for configuring ProxySQL. For more information, refer to Getting started.
Collect the endpoint and port of all instances in the Amazon RDS Multi-AZ DB cluster
An Amazon RDS Multi-AZ DB cluster has one writer instance and two reader instances. Each DB instance in the cluster has its own built-in endpoint. You can find the endpoint and port of all the cluster instances from the AWS Management Console, AWS Command Line Interface (AWS CLI), or Amazon RDS API.
Find the instance endpoint and port using the console
To use the console, complete the following steps:
- On the Amazon RDS console, choose the region in which DB cluster is created.
- In the navigation pane, choose Databases to display the list of all your DB instances.
- Choose the name of the DB cluster.
- Choose the name of
instance-1of the DB cluster to display all its details.
- On the Connectivity & security tab, note down the endpoint and port number of the instance.
- Repeat these steps for
instance-3of the DB cluster to collect their endpoint and port information.
In this example, our Amazon RDS Multi-AZ DB cluster
mycluster has the following instance endpoints and port.
Find the instance endpoint and port using the AWS CLI
To use the AWS CLI, use the describe-db-clusters command to find the
DBInstanceIdentifier of all three instances of the cluster. Then use the describe-db-instances command to find the instance endpoint and port of each instance.
Find the instance endpoint and port using the Amazon RDS API
To use the Amazon RDS API, call the DescribeDBClusters API to find the
DBInstanceIdentifier of all the instances of the cluster. Then call the DescribeDBInstances API to find the instance endpoint and port of each instance.
Onboard the Amazon RDS Multi-AZ DB cluster onto ProxySQL using instance endpoints
Use the instance endpoints and port information you retrieved in the previous step to onboard the Amazon RDS Multi-AZ DB cluster onto ProxySQL. This will prevent ProxySQL from being impacted by the DNS propagation delay issues described earlier in this post.
- Connect to the EC2 instance running ProxySQL.
- Connect to ProxySQL through the admin interface to configure it:
- Insert the endpoints and port of all the instances in the Amazon RDS Multi-AZ DB cluster you retrieved earlier to the mysql_servers table in the ProxySQL configuration. In the following example, we onboarded all the instances of the cluster with
hostgroup_id1. You can use a different ID if needed, but all the instances should be onboarded to the same
- Add the
- Add the
- Add the
- Add the
- Verify all the three instances of the cluster were added to the ProxySQL configuration:
- Load the
mysql_serverstable configuration to runtime and persist it to the disk:
Configure ProxySQL monitoring for the Amazon RDS Multi-AZ DB cluster
This helps ProxySQL monitor the Amazon RDS Multi-AZ DB cluster for changes in the writer, detect the new writer in 1 second or less, and reroute traffic to the new writer.
In this example, we use the default ProxySQL monitor user
monitor and password
monitor to configure monitoring. You can use a different monitor user and password if you want.
First, you configure the ProxySQL monitor user on the Amazon RDS Multi-AZ DB cluster.
- Log in to the Amazon RDS for MySQL Multi-AZ DB cluster database.
- Create the ProxySQL monitor user and grant it privileges to monitor:
Now you configure ProxySQL to monitor the Amazon RDS Multi-AZ DB cluster.
- Connect to the EC2 instance running ProxySQL.
- Connect to ProxySQL through the admin interface to configure:
- Configure the monitor user and password:
- Configure the monitoring intervals by setting mysql-monitor_read_only_interval to 100 milliseconds. This helps ProxySQL detect the change to the new writer within 100 milliseconds.
- Load the configuration to runtime and persist it to the disk:
- Verify ProxySQL can connect to the Amazon RDS Multi-AZ DB cluster and monitor it. There should be no connection or ping errors.
Configure ProxySQL MySQL replication hostgroups
ProxySQL uses a MySQL replication hostgroup to understand the replication topology, identify the reader and writer instances in the cluster, and monitor the cluster for topology changes.
Each row in mysql_replication_hostgroups table represents a pair of writer and reader hostgroups. ProxySQL monitors the read_only value for all the servers in a hostgroup pair and assigns each server to the writer or reader hostgroup.
In this example, we create
writer_hostgroup 1 and
reader_hostgroup 2 because we onboarded all the instances in the cluster with
hostgroup_id 1 in a previous step.
- Create the writer and reader replication hostgroups on ProxySQL:
- To enable the replication hostgroup, run the following:
- Verify both reader instances (
read_only=1) and one writer instance (
read_only=0) in the cluster have moved to the right host groups. There should be no errors in the log.
ProxySQL is now monitoring the
read_onlyvalue for all the instances in the cluster. It has created
hostgroup 2automatically, and moved the two reader instances from
hostgroup 1(writer hostgroup) to
hostgroup 2(reader hostgroup).
- As a final step, persist all config to disk:
Configure ProxySQL mysql_users
Next, we configure the MySQL users that client should use to connect to ProxySQL and in turn connect to the cluster DB instances to send traffic.
- Insert the user into the mysql_users table on ProxySQL:
Note that this user should exist on the cluster as well, otherwise ProxySQL can’t establish the connection to the cluster DB on behalf of the client to send traffic.
In this example, we specified the default_hostgroup as 1 because our
writer_hostgroupis 1 and we intend to send write traffic from this user.
- Load the configuration to runtime and disk:
We’re done configuring ProxySQL.
- Exit from the ProxySQL admin interface:
Validate ProxySQL connection to the Amazon RDS Multi-AZ DB cluster
ProxySQL is now ready to serve application traffic for the Amazon RDS Multi-AZ cluster. You can test this on the ProxySQL EC2 instance by running the following command and verifying it connects to the cluster. Update the arguments
-u <user>, and
-p<password> as applicable:
Test 1 second or less downtime
Now that you have successfully configured ProxySQL and connected it to your Amazon RDS for MySQL Multi-AZ DB cluster, let’s test for 1 second or less of downtime during a minor version upgrade of the cluster.
Perform a minor version upgrade. In this example, we upgrade the cluster from MySQL 8.0.33 to 8.0.34:
The upgrade itself may take time to complete. However, you can monitor the application downtime during the upgrade. The downtime is typically under 1 second, provided the replica lag is near zero when the new writer is elected during the upgrade process.
Here’s a simple bash you can run to measure the downtime:
Remember to update the arguments
-u <user>, and
-p<password> as applicable. This bash creates a table ‘testtable’ under a new database ‘testdb’ on your cluster via ProxySQL and keeps writing entries to the ‘testtable’ while also logging them to a log file
Once your upgrade is done, open the
downtime_test.txt file and find the timestamp at which the
server_id value changes. This is the timestamp at which the new writer first started handling write traffic and the timestamp before it is the timestamp at which the old writer handled the last write. Subtract the two timestamps, that’s your downtime.
In this example, the downtime is 730ms.
If you don’t plan on using the EC2 ProxySQL instance or the Amazon RDS Multi-AZ DB cluster beyond testing, delete the resources to avoid incurring future charges.
In this post, we examined in detail how you can configure open source ProxySQL to achieve typically under 1 second of downtime while upgrading Amazon RDS for MySQL Multi-AZ DB cluster. This is a significant improvement from the typical 35 seconds of downtime you will experience when you perform minor version upgrades or system maintenance updates on your Amazon RDS Multi-AZ DB cluster due to DNS propagation delay issues described earlier in the post.
Go try open source ProxySQL with Amazon RDS for MySQL Multi-AZ deployments with two readable standbys. Let me know how it works!
About the author
Meenakshi Garg is a software engineer at AWS working on building Amazon RDS for MySQL and MariaDB. She is passionate about technology and is based in California.