Blog

Make It Smarter: Tuning MySQL Client Request Routing for Tungsten Connector

Overview

The Skinny

In this blog post we explore various options for tuning MySQL traffic routing in the Tungsten Connector for better control of the distribution.

A Tungsten Cluster relies upon the Tungsten Connector to route client requests to the master node or optionally to the slaves. The Connector makes decisions about where to route requests based on a number of factors.

This blog post will focus on the Load Balancer algorithms available via configuration that allow you to adjust the routing behavior of the Connector, along with ways to debug the Connector Load Balancer's routing decisions.

The Question

Recently, a customer asked us:

How do I know which load balancer algorithm is in use by the Connector? And how do we enable debug logging for the Connector load balancer?

The Answers

Grep and Ye Shall Find

Let's start off with the discovery process - how do I know which load balancer is in use for which QoS?

First, you may simply grep for the values in the `router.properties` file:


shell> grep dataSourceLoadBalancer $CONTINUENT_ROOT/tungsten/cluster-home/conf/router.properties

dataSourceLoadBalancer_RO_RELAXED=com.continuent.tungsten.router.resource.loadbalancer.MostAdvancedSlaveLoadBalancer
dataSourceLoadBalancer_RW_STRICT=com.continuent.tungsten.router.resource.loadbalancer.DefaultLoadBalancer
dataSourceLoadBalancer_RW_SESSION=com.continuent.tungsten.router.resource.loadbalancer.HighWaterSlaveLoadBalancer

You can also locate the maxAppliedLatency the same way, just use grep:


shell> grep 'maxAppliedLatency=' $CONTINUENT_ROOT/tungsten/cluster-home/conf/router.properties
maxAppliedLatency=-1

If your Connector is running in Proxy mode, you can also get the information via the Connector's enhanced MySQL CLI:


shell> tpm connector
mysql> tungsten show variables like '%dataSourceLoadBalancer%';
+-------------------+-----------------------------------+-------------------------------+
| Variable_Type     | Variable_name                     | Value                         |
+-------------------+-----------------------------------+-------------------------------+
| router.properties | dataSourceLoadBalancer_RO_RELAXED | MostAdvancedSlaveLoadBalancer |
| router.properties | dataSourceLoadBalancer_RW_SESSION | HighWaterSlaveLoadBalancer    |
| router.properties | dataSourceLoadBalancer_RW_STRICT  | DefaultLoadBalancer           |
+-------------------+-----------------------------------+-------------------------------+
3 rows in set (0.01 sec)

Singing a Better Tune

How to Change the Connector's Load Balancer Algorithm

The default Connector Load balancer in v6.x is the MostAdvancedSlaveLoadBalancer which selects the slave that has replicated the most events, by comparing data sources "high water" marks. If no slave is available, the master will be returned.

This means that even if a slave is behind the master, this Load Balancer will select the most advanced slave, while still eliminating any slave that is more than maxAppliedLatency seconds latent.

You may wish to use one of the other algorithms to adjust the behavior, such as round-robin or lowest-latency slave:

LOAD BALANCER DEFAULT QOS DESCRIPTION
DefaultLoadBalancer RW_STRICT Always selects the master data source
MostAdvancedSlaveLoadBalancer RO_RELAXED Selects the slave that has replicated the most events, by comparing data sources "high water" marks. If no slave is available, the master will be returned.
LowestLatencySlaveLoadBalancer   Selects the slave data source that has the lowest replication lag, or appliedLatency in ls -l within cctrl output. If no slave data source is eligible, the master data source will be selected.
RoundRobinSlaveLoadBalancer   Selects a slave in a round robin manner, by iterating through them using internal index. Returns the master if no slave is found online
HighWaterSlaveLoadBalancer RW_SESSION Given a session high water (usually the high water mark of the update event), selects the first slave that has higher or equal high water, or the master if no slave is online or has replicated the given session event. This is the default used when SmartScale is enabled.

To change the Connector load balancer, specify the property in the configuration, i.e to use Round Robin:


shell> vi /etc/tungsten/tungsten.ini

[alpha]
property=dataSourceLoadBalancer_RO_RELAXED=com.continuent.tungsten.router.resource.loadbalancer.RoundRobinSlaveLoadBalancer
...

shell> tpm update

Here are the docs for a closer look - http://docs.continuent.com/tungsten-clustering-6.1/connector-routing-loadbalancers.html

The Nitty-Gritty

Debug Logging to the Rescue!

For in-depth debugging, enable trace logging for the Connector load balancer. Perform the procedure on one or more Connector nodes to see what the load balancer algorithm is thinking.

Warning

Enabling Connector debug logging will decrease performance dramatically. Disk writes will increase as will disk space consumption. Do not use in production environments unless instructed to do so by Continuent support. In any case, run in this mode for as short a period of time as possible - just long enough to gather the needed debug information. After that is done, disable debug logging.

To enable trace logging for the load balancer, edit the Connector configuration file tungsten-connector/conf/log4j.properties and uncomment two lines. For example:


shell> su - tungsten
shell> vi /opt/continuent/tungsten/tungsten-connector/conf/log4j.properties

Uncomment these two lines:


#log4j.logger.com.continuent.tungsten.router.resource.loadbalancer=debug, stdout
#log4j.additivity.com.continuent.tungsten.router.resource.loadbalancer=false

so they look like this:


log4j.logger.com.continuent.tungsten.router.resource.loadbalancer=debug, stdout
log4j.additivity.com.continuent.tungsten.router.resource.loadbalancer=false

Then signal the Connector to reread the config files:


shell> connector reconfigure
Warning

When disabling debug logging, DO NOT comment the lines out! Instead replace debug with info.

To disable debug logging, edit the Connector configuration file tungsten-connector/conf/log4j.properties and change the keyword debug to info.

For example, this is how it should look when the edit to disable is completed:


shell> vi /opt/continuent/tungsten/tungsten-connector/conf/log4j.properties

log4j.logger.com.continuent.tungsten.router.resource.loadbalancer=info, stdout
log4j.additivity.com.continuent.tungsten.router.resource.loadbalancer=false

shell> connector reconfigure

The Library

Please read the docs!

This is another Blog post about load balancing: How to use Round-Robin Load Balancing with the Tungsten Connector

Here are the Connector Load Balancer docs for a closer look - http://docs.continuent.com/tungsten-clustering-6.1/connector-routing-loadbalancers.html

For additional understanding of maxAppliedLatency, please visit here: http://docs.continuent.com/tungsten-clustering-6.1/connector-routing-latency.html

For more information about the generalized debug procedures: https://docs.continuent.com/tungsten-clustering-6.1/troubleshooting-support.html#troubleshooting-support-advanced
Scroll down to "Configuring Connector Debug Logging"

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

Summary

The Wrap-Up

Clearly, there are many, many things to think about when it comes to Tungsten Connector tuning - this blog post barely scratches the surface of the subject. Remember, tuning is all about iterative effort over time!

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.

About the Author

Eric M. Stone
COO

Eric is a veteran of fast-paced, large-scale enterprise environments with 35 years of Information Technology experience. With a focus on HA/DR, from building data centers and trading floors to world-wide deployments, Eric has architected, coded, deployed and administered systems for a wide variety of disparate customers, from Fortune 500 financial institutions to SMB’s.

Add new comment