Load Balancing AWS RDS MySQL read traffic using Route53 Weighted Routing

[Update]
As of Sep 2016, AWS Aurora has the load balancing built-in — By using the cluster endpoint, you can load-balance connections across the replicas in the DB cluster. As of Nov 2017, Auto-scaling is available for Aurora read-replicas.
You may need to consider these options instead the solutions described in this post.

If you do have any read heavy applications hopefully you must have experienced some sudden CPU spikes on your RDS MySQL read replicas.

If you’re running on a single read replica, chances are your read replica may not respond when the CPU hits 100%. The solution is to add more read replicas to your stack and distribute the read traffic evenly by enabling load-balancing. As of this writing MySQL RDS allows maximum of 5 read replicas per single master rds.

In general you can load-balance your MySQL read replicas from these logical layers.

  • Application layer — Create multiple connection pools and distribute the read traffic between one or more read replicas.
  • External proxy — Configure HAProxy[1] or MySQLProxy[2]

[1] http://www.haproxy.org
[2] https://dev.mysql.com/downloads/mysql-proxy/

Both these options requires some code level and server level configuration work. And these are the most well known methods to setup load-balanced environments.

There is however a quick and easy way — that is using the Route53 Weighted Routing. Let’s see how — the diagram below shows how the setup would look like once completed.

As per the diagram we have 2 application instances (EC2) and both these apps configured to connect with read replicas using a CNAME — read.db.yourdomain.com. We also have our master RDS with its 2 read replicas R1, R2 under same VPC.

For this we will be using a Private hosted zone for VPC on Route53, so the routing queries will not be public.

A private hosted zone determines how traffic is routed within an Amazon VPC. Your resources are not accessible outside the VPC. You can use any domain name.

Go to your Route53 on aws console and setup a new private hosted zone — example: db.yourdomain.com.

On your newly created hosted zone, create these CNAME records sets as described below.

Name: read.db.yourdomain.com
Type: CNAME
Value: my-rds-read1.us-east-1.rds.amazonaws.com
TTL: 0
Routing policy: Weighted
Weight: 0
SetId: read1

That’s it — Route53 will now load-balance the DNS queries received to read.db.yourdomain.com between the 2 read replicas. You can also add more read replicas (up to 5) without changing your database connection endpoint on your apps.

Randika Rathugamage

Written by

Architect | Coder | Microservice, Serverless Enthusiasts | AWS Certified Solutions Architect (Professional)— Connect at https://www.linkedin.com/in/randika/