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).
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.
What IS the default Load Balancer Model
- The default Load Balancer Model setting for the Tungsten Connector is
- This means that Read-Only operations are always reading from the most up-to-date slave in the cluster.
MostAdvancedSlaveLoadBalancerLoad Balancer Model setting selects the slave data source that has the lowest replication lag, or highWater in the
ls -loutput within
- 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
MostAdvancedSlaveLoadBalancercan potentially create a bottleneck.
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?
RoundRobinSlaveLoadBalancerLoad 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:
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
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.