Todd's MySQL Blog

Tiny tidbits of trivia from Todd

Connector/J’s load-balancing failover policies

Connector/J provides a useful load-balancing implementation for Cluster or multi-master deployments.  As of Connector/J 5.1.12, this same implementation is used under the hood for balancing load between read-only slaves with ReplicationDriver.  When trying to balance workload between multiple servers, though, the driver has to decide when it’s safe to swap servers – doing so in the middle of a transaction would not make applications very happy.  Many of the same principles which apply to autoReconnect also apply here – you don’t want to lose important state information.

As a result, Connector/J will only try to pick a new server when one of the following happen:

  1. At transaction boundaries (transactions are explicitly committed or rolled back)
  2. A communication exception (SQL State starting with “08″) is encountered
  3. When a SQLException matches conditions defined by user, using the extension points defined by the loadBalanceSQLStateFailover, loadBalanceSQLExceptionSubclassFailover or loadBalanceExceptionChecker properties.

The third condition is new, and revolves around three new properties introduced with Connector/J 5.1.13.  It allows you to control which SQLExceptions trigger failover. Let’s examine each of the new properties in detail.

loadBalanceExceptionChecker

The loadBalanceExceptionChecker property is really the key.  This takes a fully-qualified class name which implements the new com.mysql.jdbc.LoadBalanceExceptionChecker interface.  This interface is very simple, and you only need to implement the following method:

public boolean shouldExceptionTriggerFailover(SQLException ex)

In goes a SQLException, out comes a boolean.  True triggers a failover, false does not.  Easy!

You can use this to implement your own custom logic. An example where this might be useful is when dealing with transient errors with MySQL Cluster, where certain buffers may be overloaded. At the 2010 MySQL Conference, Mark Matthews and I presented a simple example during our tutorial which does this:

public class NdbLoadBalanceExceptionChecker
 extends StandardLoadBalanceExceptionChecker {

 public boolean shouldExceptionTriggerFailover(SQLException ex) {
  return super.shouldExceptionTriggerFailover(ex)
    ||  checkNdbException(ex);
 }

 private boolean checkNdbException(SQLException ex){
 // Have to parse the message since most NDB errors
 // are mapped to the same DEMC, sadly.
  return (ex.getMessage().startsWith("Lock wait timeout exceeded") ||
  (ex.getMessage().startsWith("Got temporary error")
  && ex.getMessage().endsWith("from NDB")));
 }
}

The code above extends com.mysql.jdbc.StandardLoadBalanceExceptionChecker, which is the default implementation. There’s a few convenient shortcuts built into this, for those who want to have some level of control using properties, without writing Java code. This default implementation uses the two remaining properties: loadBalanceSQLStateFailover and loadBalanceSQLExceptionSubclassFailover.

loadBalanceSQLStateFailover

The loadBalanceSQLStateFailover property allows you to define a comma-delimited list of SQLState code prefixes, against which a SQLException is compared. If the prefix matches, failover is triggered. So, for example, the following would trigger a failover if a given SQLException starts with “00″, or is “12345″:

loadBalanceSQLStateFailover=00,12345

loadBalanceSQLExceptionSubclassFailover

This property can be used in conjunction with loadBalanceSQLStateFailover or on it’s own. If you want certain subclasses of SQLException to trigger failover, simply provide a comma-delimited list of fully-qualified class or interface names to check against. For example, say you want all SQLTransientConnectionExceptions to trigger failover:

loadBalanceSQLExceptionSubclassFailover=java.sql.SQLTransientConnectionException

That’s all there is to it!

Single Post Navigation

One thought on “Connector/J’s load-balancing failover policies

  1. Pingback: » Multi-master support in MySQL Connector/Java Todd's MySQL Blog

Leave a Reply

Your email address will not be published. Required fields are marked *


6 − = five

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>