Auto Scaling a MySQL Database to Meet Fluctuating Application Demands with Amazon Aurora
TUTORIAL
Overview
This tutorial is not within the free tier and will cost you less than $1 provided you follow the steps in the tutorial and terminate your resources at the close of the tutorial.
What you will accomplish
In this tutorial, you will:
- create an Amazon Aurora database
- configure it to scale automatically to meet fluctuating demand by adding or removing read replicas
Prerequisites
Before starting this tutorial, you will need:
- An AWS account: If you don't already have an account, follow the Setting Up Your AWS Environment guide for a quick overview.
AWS experience
Time to complete
10-20 minutes
Cost to complete
Requires
AWS account*
*Accounts created within the past 24 hours might not yet have access to the services required for this tutorial.
Services used
Last updated
February 3, 2023
Step 1: Create an Aurora DB cluster
a. Open a browser and navigate to the Amazon RDS console. If you already have an AWS account, log in to the console. Otherwise, create a new AWS account to get started.
Already have an account? Sign in
c. Scroll down and choose Create database.
d. On the Create database page, choose Standard create.
e. For Engine type, select Amazon Aurora; for Edition, select Amazon Aurora MySQL-Compatible Edition; and under Available versions, select the latest version of Aurora MySQL. As of the publication date of this tutorial, the latest version is Aurora (MySQL 5.7) 2.10.2.
f. Choose the Production sample template.
g. Under Settings, choose an identifier for your Aurora DB cluster, such as database-1. Open Credentials Settings and then set a Master username and password. For this tutorial, retain the default Master username of admin and choose Auto generate a password.
h. For the DB instance class, retain the default selection for Memory optimized classes and for size, select a large instance (ending with .large).
i. Under Availability & durability, select Create an Aurora Replica or Reader in a different AZ (recommended for scaled availability).
j. Under the Connectivity section, select Don’t connect to an EC2 compute resource.
k. Select the VPC where you want to create the database. For this tutorial, select the Default VPC. Note that once created, a database can't be migrated to a different VPC. Then choose the default value for the DB Subnet group.
l. For Public access, select No. This means you will have to connect to the database from an EC2 instance within the same VPC.
m. In the VPC security group section, select Create new. If you happen to have a security group that allows incoming TCP connections on port 3306, you can choose it instead. This security group will control ingress to your Aurora cluster.
In New VPC security group name, enter aurora-tutorial.
n. Leave the default value for Database authentication and default values for Monitoring.
o. Open Additional configuration. For Initial database name enter aurora_tutorial. Then scroll down to the Deletion protection section. The best practice is to select Enable deletion protection. However, if you want to delete the database at the end of the tutorial, you will need to leave this unselected.
p. After a quick review of all the fields in the form, you can proceed. Choose Create database.
Step 2: Save credentials
a. Choose View connection details.
b. Save the Master username, Master password, and the Endpoint.
c. After closing the Connection details popup, select the name of the database you created from the DB identifier list.
d. Copy the Writer and Reader endpoints. You can direct any read/write traffic to the Writer endpoint, but it's a good practice to direct read-only traffic to the Reader endpoint.
Step 3: Add a scaling policy
a. Select your Aurora DB cluster. Choose Actions and then select Add replica auto scaling.
b. Under Policy name, enter policy-1.
There are two Target metrics you can use: Average CPU utilization of Aurora Replicas and Average connections of Aurora Replicas. Aurora Auto Scaling creates and manages CloudWatch alarms that trigger the scaling policy and calculates the scaling adjustment based on the metric and target value. The scaling policy adds or removes Aurora Replicas as required to keep the metric close to the specified target value.
Which metric to use depends on the architecture and workload of your application. If you have to run CPU-intensive database queries, measuring the CPU utilization can be a good idea. If your queries are simple but you need to scale reads and writes, then measuring the number of connections could be the way to go.
Keep in mind that an Auto Scaling policy can be based only on one metric, but you can create more than one scaling policy. For this tutorial, you can select Average connections of Aurora Replicas.
This means that Aurora Auto Scaling will add Aurora Replicas if the number of connections reaches the target value of 20, and will remove extra Replicas if the number of connections falls below that target value. In all cases, Aurora Auto Scaling only removes Aurora Replicas that it created—never those created by you.
e. For Minimum capacity, enter 1. For Maximum capacity, enter 2.
The numbers for minimum and maximum capacity can be modified later. Which values to use in a production environment will depend on your estimates for workload, number of connections, and budget. The Aurora Replicas created by Aurora Auto Scaling are the same DB instance class as the one used for the primary instance.
f. Choose Add policy.
Step 4: Modify a scaling policy
b. Select Logs & events.
c. Select your Auto scaling policy and choose Edit.
d. Change the Maximum capacity to 4 and choose Save.
Step 5: Delete a scaling policy
b. Select Logs & events.
c. Select your Auto scaling policy and choose Delete.
Step 6: Delete your cluster
To finish this tutorial, you will learn how to delete your Aurora DB cluster when it's not needed anymore. To delete your Aurora DB cluster, go to the Amazon RDS console and follow these instructions:
This will show you a list of all your Aurora DB clusters.
b. Select the Reader instance of the Aurora DB cluster you created for this tutorial. Choose Actions and select Delete.
c. You will be asked to confirm the action. Enter delete me and choose Delete.
d. Select the Writer instance of the Aurora DB cluster you created for this tutorial. Choose Actions and select Delete.
The status of your cluster will change to Deleting. At this point, you can also delete the security group you created if you determine you won't need it anymore.
Conclusion
Congratulations! You have created an Aurora DB cluster with auto scaling. You have learned how to automatically adjust the capacity of the Aurora DB cluster by adding or removing read replicas based on the needs of your application.
Next steps
To learn more about Amazon Aurora and auto scaling, visit the following resources.