Load Balancing ProxySQL in AWSThere are several ways to deploy ProxySQL between your applications and the database servers. A common approach is to have a floating virtual IP (VIP) managed by keepalived as the application endpoint. The proxies have to be strategically provisioned to improve the resiliency of the solution (different hardware, network segments, etc,).

When we consider cloud environments, spreading instances across many availability zones (AZ) is considered a best practice, but that presents a problem regarding VIP handling.

Per definition, VPC subnets have to be created in a specific AZ, and subnet IP ranges can’t overlap with one another. An IP address cannot simply be moved to an instance on a different AZ, as it would end up in a subnet that doesn’t include it.

So in order to use the VIP method, we would need to keep all our proxies in a single AZ. This clearly is not the best idea. In addition to this, the regular VIP method doesn’t work, due to the fact that broadcast is not allowed in AWS.

Let’s instead see how to overcome this by putting ProxySQL instances behind a Network Load Balancer (NLB) instead.

Creating a Load Balancer

1. Create an NLB, specifying the subnets where you launched the ProxySQL instances:

With the above command, the LB internal endpoints will automatically pick an available IP address on each subnet. Alternatively, if you want to specify the IP addresses yourself, you can run the following:

The output of the above includes the Amazon Resource Name (ARN) of the load balancer, with the following format:

Let’s save the value on a variable for later use:

Adding the ProxySQL Targets

2. Create a target group, specifying the same VPC that you used for your ProxySQL instances:

The output should include the ARN of the target group with this format:

We can store the value for later use:

3. Register your ProxySQL instances with the target group:

Creating the LB Listener

4. Create a listener for your load balancer with a default rule to forward requests to your target group:

The output contains the ARN of the listener, with the following format:

5. You can verify the health of the registered targets using the following​ command:

Be aware it takes a few minutes for the health to go green.

Testing Access

6. Now let’s get the DNS name of the load balancer:

7. Test access to the load balancer itself:

8. Finally, test the connection to the database through the load balancer:

Final Considerations

For this example, I am using a simple TCP connection to ProxySQL’s admin port as the health check. Another option would be to expose a separate HTTP service that queries ProxySQL to handle more complex health check logic.

It is also important to mention the difference between target-type:instance and target-type:ip for the target group. In the latter, if you check the client connections on the Proxy side (stats_mysql_processlist table) you will see they all come from the load balancer address instead of the actual client. Hence it is more desirable to use instance, to see the real client IP.

4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Javier Zon

As usual.. amazing!!

0
Ivan Groenewold

Sorry Javier I had missed your comment. Thanks for stopping by! glad it was useful

0
Rajiv

Thanks Ivan for the amazing article. Few things I wanted to clarify, NLB has connection timeout of 350 seconds, so how does it impacts ProxySql connections, can something be done to avoind that? Also, do you need to use sticky sessions to make sure that queries do not route to other connections?

0
Tyler Hains

This is great! I’ve gotten this far. Now I need to setup SSL end-to-end. I’m getting weird errors when I have different certs across the different servers. Do I need to somehow synchronize all of the SSL certs on the NLB, ProxySQL and DB nodes?

0