▸ Migration to Amazon RDS Multi-AZ Cluster

The primary node was configured for write operations, ensuring data consistency while offloading read traffic to read replicas in multiple Availability Zones (AZs). Automatic failover capabilities were implemented to minimize downtime by promoting a read replica to the primary role if the primary node failed.

‍

▸ Deployment of Read Replicas

Multiple read replicas were added in different AZs to handle read-heavy workloads, thus offloading the primary node. This setup allowed the system to scale horizontally by distributing read traffic across replicas, ensuring faster query responses during high-load periods.

‍

▸ Introduction of ProxySQL for Load Balancing

ProxySQL was implemented between the application and RDS nodes to intelligently route write traffic to the primary node and read traffic to the replicas. This ensured efficient traffic distribution, optimized resource usage, and prevented performance degradation of the primary node.

‍

▸ Failover Management with ProxySQL

ProxySQL’s failover management allowed for seamless traffic rerouting in case of a failover event, ensuring that the application would continue to function smoothly during node failures.

‍

Implementation

‍

Amazon RDS Multi-AZ Cluster (MySQL 8.0.32)

‍

Primary Node (Writer):

This is the main node responsible for handling all write operations.

Applications send write requests directly to this node to ensure data consistency.

In the event of a failure, Amazon RDS automatically fails over to a secondary node, minimizing downtime.

Read Replicas (Reader1 and Reader2):

These are additional nodes within the Multi-AZ setup that replicate data from the primary node.

They handle read-only queries, which offloads traffic from the primary node and increases read throughput.

By distributing read operations across multiple replicas, the system can scale horizontally to handle more read traffic.

Failover Capabilities:

Amazon RDS Multi-AZ offers built-in failover for high availability.If the primary node fails, one of the read replicas is promoted to the primary role, ensuring continuity of service.

‍

ProxySQL Cluster

‍

Load Balancing:

The ProxySQL cluster is placed between the application and the RDS nodes.

ProxySQL intelligently routes traffic to the appropriate node based on the type of operation: write traffic is directed to the primary node, while read traffic is load-balanced across the read replicas.

This ensures efficient use of resources, as read replicas are utilized for read-heavy workloads without impacting the primary node’s performance.

Clustering:

ProxySQL clustering provides redundancy and high availability for the proxy layer.

If one ProxySQL instance fails, others can continue handling traffic, reducing the risk of a single point of failure in traffic routing.

Failover Management:

ProxySQL is capable of detecting changes in the cluster (such as failovers) and automatically rerouting traffic to the new primary node.

This minimizes disruption during failover events, providing a seamless experience for applications and end-users.

‍

Architecture Benefits

‍

Performance:

Offloading reads to replicas prevents read-heavy workloads from overwhelming the primary node, resulting in faster query response times.

This also allows the primary node to focus on handling write operations, enhancing overall performance.

Scalability:

Read scalability is achieved by adding more read replicas as demand increases.

Horizontal scaling is possible for read traffic, allowing the system to handle more concurrent users and higher volumes of read requests.

High Availability and Fault Tolerance:

The Multi-AZ setup with automatic failover ensures minimal downtime in case of node failure.

The ProxySQL cluster adds another layer of resilience, allowing traffic to be routed seamlessly even if one proxy node goes down.

Improved Management:

ProxySQL provides advanced query routing, logging, and connection pooling, which helps manage database connections more effectively and reduce overhead on the RDS nodes.

The architecture also supports monitoring and tuning capabilities that can help in optimizing database performance and cost.