How to use Round-Robin Load Balancing with the Tungsten Connector

Overview

The Skinny

Part of the power of Tungsten Clustering for MySQL / MariaDB is its intelligent MySQL Proxy, known as the Tungsten Connector. The Tungsten Connector has built-in read-write splitting capabilities, and it is also possible to configure different algorithms which select the appropriate slave (i.e. Round-Robin or Lowest-Latency).

The Question

Recently, a customer asked us:

How do we best share the load between read-only slaves? Currently, there appears to be an imbalance, with most of the read-only queries reaching just one slave. What may we do to improve this situation?

This customer noticed that a couple of long running, slow queries were running against one particular slave node.

Despite the increased CPU utilization caused by the expected, long running queries, the “quick” Read-Only requests would run against this same slave node causing what the customer referred to as "pilling up", otherwise known as a bottleneck.

The Background

What IS the default Load Balancer Model

• The default Load Balancer Model setting for the Tungsten Connector is MostAdvancedSlaveLoadBalancer.
• This means that Read-Only operations are always reading from the most up-to-date slave in the cluster.
• The MostAdvancedSlaveLoadBalancer Load Balancer Model setting selects the slave data source that has the lowest replication lag, or highWater in the ls -l output within cctrl.
• If no slave data source is eligible, the master data source will be selected.
• In a cluster experiencing rapid changes in latency, the 3-second (default) polling interval will be too low. This will result in higher CPU utilization and slower-running queries, which means that the MostAdvancedSlaveLoadBalancer can potentially create a bottleneck.

The Answer

Change the Load Balancer Model

It is possible to change the Tungsten Connector's Load Balancer Model.

To more evenly distribute the workload (therefore resolving the bottleneck), Continuent recommends changing the Load Balancer Model from MostAdvancedSlaveLoadBalancer to RoundRobinSlaveLoadBalancer.

Why choose round-robin instead?

• The RoundRobinSlaveLoadBalancer Load Balancer Model setting uses all available slave nodes, each in turn.
• When the end of the slave node list is reached, the load balancer starts from the beginning of the list and repeats the loop.

Why NOT choose round-robin?

• Consideration should be given to the fact that with the RoundRobinSlaveLoadBalancer, it is possible that a Read-Only request may read older/stale data from a slave that is not as caught-up as another slave.
• It is also possible for the calling client to see slower response times due to slave CPU load

To change the Connector Load Balancer model, simply specify the property in the [defaults] stanza of your tungsten.ini file:

property=dataSourceLoadBalancer_RO_RELAXED=com.continuent.tungsten.router.resource.loadbalancer.RoundRobinSlaveLoadBalancer

The Library

Please read the docs!

For more information the Load Balancer Model setting for the Tungsten Connector, please visit the docs page at https://docs.continuent.com/tungsten-clustering-6.0/connector-routing-loadbalancers.html

For more information about Tungsten clusters, please visit https://docs.continuent.com

Summary

The Wrap-Up

In this blog post we discussed changing the Load Balancer Model setting for the Tungsten Connector to a Round-Robin model, as well the behavioral changes that should be considered before making this change.

Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

For more information, please visit https://www.continuent.com/solutions

Want to learn more or run a POC? Contact us.