Auto scaling a mysql database for fluctuating application requirements

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 the fluctuating needs of your application.

this tutorial is not within the free quota and costs less than 1 USD, as long as you follow the steps in the tutorial and clear your resources at the end of the learning program.

About this tutorial

time	10-20 minutes
costs	less than 1 USD
use case	databases
products	amazon aurora, amazon RDS
target group	database administrators, developers
level	advanced
latest update	2. July 2019

Step 1: create aurora DB cluster

1.1: open a browser and navigate to the amazon RDS console. If you already have an AWS account, sign in to the console. otherwise, create a new AWS account to get started.

You already have an account? Log in to your account

1.2: select the region in which you want to start the aurora DB cluster in the top right-hand corner.

1.3: click on "create database" in the amazon aurora window.

Before proceeding, switch to the new database creation flow:

Engine options

1.4: under database engine, select "amazon aurora" from.

1.5: select "amazon aurora with mysql compatibility" as edition.

1.6: select the desired aurora version.

1.7: select "regional select as database location.

database features

1.8: select "one recorder and several readers from.

Templates

1.9: select "production from.

Settings

1.10: select an identifier for your aurora DB cluster, z. B. “Database-1”.

DB-instance-size

1.11: for DB instance size, select a large instance (the one marked with .large ends).

Availability and durability

1.12: select “create aurora replica/readers from.

Connectivity

1.13: select the VPC in which you want to create the database.

Note that once a database is created, it cannot be migrated to another VPC.

1.14: click on additional connectivity configuration.

1.15: select the default value for the subnet group.

1.16: select "publicly available the option "no.

This means that you will need to connect to the database from an EC2 instance within the same VPC.

1.17: select "rebuild" in the VPC security group from. If you have a security group that allows incoming TCP connections on port 3306, you can select that instead. This security group will control the input of your aurora cluster.

1.18: enter "aurora-tutorial" as the name of the new VPC security group a.

1.19: keep default value for database port.

Additional configuration

Keep the default values for "additional configuration" at.

The best practice is to enable erasure protection. If you want to delete the database at the end of the tutorial, you can leave this option disabled.

1.20: deactivate under "delete protection" the option "enable delete protection".

Verify and create

After a quick review of all the fields on the form, you can proceed to.

1.21: click create database.

During the creation of the instances, a banner will appear explaining how to obtain your credentials. this is a good opportunity to save the credentials, since you can only display the password this once.

1.22: click "view credentials.

1.23: save the username, password, and cluster endpoint.

1.24: when you have closed the login information popup, click on the name of your created database.

1.25: copy the writer and reader endpoints. You can route all read/write traffic to the writer endpoint, but it is a proven method to route read traffic to the reader endpoint.

Step 2: add a scaling policy

Aurora auto scaling can create and remove replicas based on the scaling policies you define. If the processing load or the number of connections to your database increases unexpectedly, aurora auto scaling can add aurora replicas. as soon as the processing load or the number of connections decreases again, aurora auto scaling removes the extra aurora replicas, so you don’t pay for unnecessary capacity.

2.1 – select your aurora DB cluster.

2.2 – click on "actions" and then select "add replica auto scaling".

Details of the directive

2.3 – select a policy name, z. B. "guideline-1".

2.4 – select a metric for auto scaling.

There are two target metrics you can use: "average CPU usage 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 needed 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 need to run CPU-intensive database queries, it may be a good idea to measure CPU usage. If your queries are simple but you need to scale reads and writes, it may be advisable to measure the number of connections.

note that a scaling policy can only be based on a single metric, but you can create multiple scaling policies. For this tutorial, you can select "average connections of aurora replicas".

2.5 – enter "20" as the target value.

This means that aurora auto scaling adds aurora replicas when the number of connections reaches the target value of 20, and removes additional replicas when it falls below this target value. In all cases, aurora auto scaling only removes the aurora replicas that it creates – never the ones that you create.

cluster capacity details

2.6 – enter "1" as minimum capacity.

2.7 – enter "1" as maximum capacity.

The numbers for minimum and maximum capacity can be changed at a later time. The values to use in a production environment will depend on your estimates for processing load, number of connections, and budget. aurora replicas created by aurora auto scaling belong to the same DB instance class as those used for the primary instance.

check and proceed

2.8 – check all fields and click on "add policy".