Load Balancing AWS RDS MySQL Read Replicas with Route 53 (No Auto Scaling) – by Khalid Rasool

#aws #loadbalancing #rds #programming

Scaling read-heavy workloads on Amazon RDS for MySQL can be a challenge when your application demands high performance without the complexity of auto scaling. By leveraging AWS Route 53’s weighted routing, you can distribute read traffic across multiple RDS read replicas effectively, ensuring better performance and reliability without auto scaling. In this article, I’ll walk you through how to set up load balancing for MySQL read replicas using Route 53.

Why Use Route 53 for RDS Read Replicas?

Amazon RDS for MySQL supports up to five read replicas per primary instance, which are ideal for offloading read queries from the primary database. However, without auto scaling, you need a manual yet efficient way to distribute traffic across these replicas. Route 53’s weighted routing policy offers a simple, DNS-based solution to balance read traffic without requiring complex proxies like HAProxy or code changes. Here’s why it’s a great fit:

Simplicity: No need for additional infrastructure or application-layer changes.
Flexibility: Easily adjust weights to prioritize certain replicas.
Cost-Effective: Uses existing AWS services without extra overhead.
No Auto Scaling Needed: Works within the fixed limit of read replicas.

Note: Unlike Amazon Aurora, which has built-in load balancing via cluster endpoints, RDS for MySQL requires manual configuration for load balancing, as auto scaling of read replicas is not supported.

Prerequisites

Before you begin, ensure you have:

An AWS account with an RDS MySQL instance and at least one read replica (up to five).
A Route 53 hosted zone for your domain (e.g., db.yourdomain.com).
Your application configured to use a single DNS endpoint for read queries.
Access to the RDS console to retrieve read replica endpoints.

Step 1: Set Up Your RDS Read Replicas

Create Read Replicas:
- In the AWS RDS Console, navigate to “Databases.”
- Select your primary MySQL instance, then choose “Actions” > “Create read replica.”
- Configure the replica (e.g., instance type, region) and repeat for up to five replicas.
- Note each replica’s DNS endpoint (e.g., replica1.xxxxx.ap-south-1.rds.amazonaws.com).
Verify Replicas:
- Connect to each read replica using a MySQL client and run SELECT * FROM your_table to confirm data consistency (replication is asynchronous, so expect slight lag).

Step 2: Configure Route 53 for Weighted Routing

Route 53’s weighted routing policy allows you to assign weights to each read replica, controlling how traffic is distributed. Here’s how to set it up:

Create a Hosted Zone (if not already done):
- In the Route 53 Console, go to “Hosted zones” and click “Create Hosted Zone.”
- Enter a domain name (e.g., db.yourdomain.com) and select “Private Hosted Zone” for the VPC where your RDS instances reside.
Add CNAME Records for Read Replicas:
- In the hosted zone, click “Create record.”
- Set the following for each read replica:
  - Record name: Use a common name, e.g., read.db.yourdomain.com.
  - Record type: CNAME.
  - Value: Enter the DNS endpoint of one read replica (e.g., replica1.xxxxx.ap-south-1.rds.amazonaws.com).
  - Routing policy: Weighted.
  - Weight: For equal distribution, divide 100 by the number of replicas (e.g., 50 for two replicas, 33 for three).
  - TTL: Set to 0 for quick DNS updates.
  - Record ID: Use a unique identifier (e.g., replica1-read).
- Repeat for each read replica, keeping the same record name (read.db.yourdomain.com) but different endpoints and record IDs.
Enable Health Checks (Optional):
- Create Route 53 health checks for each replica’s endpoint to ensure traffic isn’t sent to unavailable replicas. This requires a health check endpoint (e.g., a simple webpage returning “GOOD!”). Health checks cost ~$1/month per check.

Step 3: Update Your Application

Modify your application’s database connection to use the single Route 53 endpoint (read.db.yourdomain.com) for read queries.
Ensure write queries still go to the primary instance’s endpoint (e.g., master.xxxxx.ap-south-1.rds.amazonaws.com).
If your application uses connection pooling, test to avoid DNS caching issues, which can skew traffic distribution.

Step 4: Test and Monitor

Test Traffic Distribution: Run read queries (e.g., SELECT * FROM your_table) and verify they’re hitting different replicas by checking SHOW VARIABLES LIKE 'server_id'; on each replica.
Monitor Performance: Use AWS CloudWatch to track CPU utilization and read IOPS on each replica to ensure balanced load.
Adjust Weights: If one replica has more powerful hardware, assign it a higher weight (e.g., 50 for a high-capacity replica, 25 for others).

Considerations and Limitations

Replication Lag: MySQL read replicas use asynchronous replication, so there may be slight delays in data consistency. Ensure your application can tolerate this.
No Auto Scaling: RDS for MySQL doesn’t support automatic addition/removal of read replicas, so plan your replica count based on expected load.
DNS Caching: Some languages (e.g., Java) cache DNS resolutions, which can lead to uneven traffic distribution. Set a low TTL (e.g., 0 or 10 seconds) to mitigate this.
Alternative Approaches: For more control, consider proxies like ProxySQL or HAProxy, but these require additional setup compared to Route 53’s simplicity.

Why This Works

Route 53’s weighted routing provides a lightweight, DNS-based load balancing solution that evenly distributes read traffic across MySQL read replicas without auto scaling.This approach is cost-effective, easy to configure, and scales within the fixed replica limit of RDS MySQL.

Ready to boost your database performance? Set up Route 53 weighted routing for your RDS read replicas and share your results in the comments!

DEV Community