Load balancer for hosts

Managed Service for MySQL® allows you to use an internal network balancer to distribute the load among hosts. This load balancer operates on layer 4 of the OSI network model but uses layer 3 technologies to speed up packet processing.

Using a load balancer gives you the following advantages:

• A single entry point for connecting to the master host.
• Even distribution of read requests among replicas that factors in their load.
• Choice of load balancing policy. For example, you can set Least connections to distribute load among replicas with the fewest connections.
• Minimization of cluster unavailability during master failover or changes in the replica composition.
• To switch the application to a new cluster when restoring from a backup, you just need to replace a single FQDN.

The load balancer automatically accounts for changes in cluster topology: new replicas are included in load balancing, and removed ones are excluded.

In the cluster architecture, the load balancer is implemented as a separate service that can be enabled when creating a cluster. The load balancer is assigned a single FQDN common to all availability zones, which serves as a single entry point for all database requests. A load balancer instance is provisioned in every availability zone where your cluster hosts reside.

The load balancer stops and starts simultaneously with the cluster stop and start. The load balancer distributes incoming traffic across all hosts in the cluster. It automatically adds or removes hosts from the load balancing pool as your cluster scales up or down.

To get the load balancer’s FQDN, follow this guide.

The network traffic regulation schema for the cluster using a load balancer is shown below:

When the load balancer is enabled, it remains possible to connect directly to the database via the host FQDN, bypassing the load balancer.

A separate proxy service based on HAProxy is used to analyze the state and load of hosts. The proxy service is deployed on each host the following functions:

• Monitoring host availability.
• Checking replica lag.
• Verifying host role.

The load balancer provides several special ports for target connections:

Traffic from each open port of the load balancer is directed to the proxy service and then distributed to target connections depending on the port number.

For example, to distribute read requests only among replicas with a lag of no more than 30 seconds:

1. Create a cluster with the load balancer enabled and specify a replica lag value of 30 seconds in the 4306 port settings.
2. Specify the database connection string with the load balancer FQDN and port 4306 in your application settings.

Balancing policiesBalancing policies

Using balancing policies, you can configure a balancer's behavior when distributing queries across cluster replicas. A policy is specified for all types of target connections to replicas when creating a cluster.

The following policies are available:

• Round Robin: Selects a replica according to the Round-Robin policy.
• Least connections: Selects the replica with the fewest connections.
• Localized Round Robin: When selecting a replica according to the round-robin policy, priority is given to replicas in the same availability zone as the balancer's instance receiving the query.