Auto Scaling a MySQL Database to Meet Fluctuating Application Demands with Amazon Aurora

TUTORIAL

Overview

Amazon Aurora is a MySQL and PostgreSQL-compatible relational database that combines the performance and availability of traditional enterprise databases with the simplicity and cost-effectiveness of open source databases. In this tutorial, you will learn how to create an Amazon Aurora database and configure it to scale automatically, by adding or removing read replicas, to meet fluctuating demands of your application.

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:

 

 AWS experience

Intermediate

 Time to complete

10-20 minutes

 Cost to complete

Less than $1, provided you follow the steps to terminate your resources at the end of the tutorial

 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

b. In the top right corner, select the Region where you want to launch the Aurora DB cluster.

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

While the instances are being created, you will see a banner explaining how to obtain your credentials. This is a good opportunity to save the credentials somewhere, as this is the only time you will be able to view this password.

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

Aurora Auto Scaling can create and remove replicas based on the scaling policies you define. When the workload or the number of connections to your database suddenly increases, Aurora Auto Scaling can add Aurora Replicas. Once the workload or the number of connections decrease, Aurora Auto Scaling removes the extra Aurora Replicas so that you don't spend on extra capacity.

a. Select your Aurora DB cluster. Choose Actions and then select Add replica auto scaling.

b. Under Policy name, enter policy-1.

c. Choose a Target metric to use for the auto scaling.

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.

d. For Target value, enter 20.

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

a. Select your Aurora DB cluster.

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

a. Select your Aurora DB cluster.

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:

a. Select Databases in the left navigation pane.

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.

e. You will be asked if you want to create a final backup. That's usually a good idea, but it's not necessary for this tutorial.
 
Clear the box for Create final snapshot? and select I acknowledge..., then enter delete me and choose 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.

Was this page helpful?

Next steps

To learn more about Amazon Aurora and auto scaling, visit the following resources.