Tag Archives: Java

Connector/J extension points – Load Balancing Strategies

Java, MySQL, , , , , ,

A fourth and final Connector/J extension point I covered in my JavaOne and Silicon Valley Code Camp presentations is load-balancing strategies.  This exists in order to allow you to define behavior for balancing load across multiple back-end MySQL server instances.  MySQL Connector/J’s load-balancing implementation is a simple internal connection pool.  What appears to your application as a single Connection object can actually have multiple physical connections to MySQL servers underneath (one per configured host/port pair).  At specific points, Connector/J will re-balance and choose another host to interface with.  This extension point allows you to define how Connector/J determines which host it should pick next.

Unlike the previous extension points, my demo code does not contain examples of this.  In this case, though, there are some standard implementations provided with Connector/J that we can look at, instead.  The two implementations shipped with Connector/J today implement a “best response time” strategy and a “random” strategy.  The default behavior when using load-balanced deployments is “random”, and the Connector/J configuration properties documentation describes their use cases.  Below are the actual classes which define the behavior:

  • com.mysql.jdbc.RandomBalanceStrategy
  • com.mysql.jdbc.BestResponseTimeBalanceStrategy

These classes – and any user-implemented load-balancing strategy – implements the com.mysql.jdbc.BalanceStrategy interface.  As the purpose of this extension point is to define how load is balanced across MySQL instances, there’s really only one key method you need to focus on:  pickConnection().  The purpose of this method is to return a Connection (more specifically, a com.mysql.jdbc.ConnectionImpl object).  Looking at the RandomLoadBalacneStrategy code, you will see the logic that’s involved.

The first parameter to pickConnection() is a LoadBalancingConnectionProxy object.  This is the object that does much of the load-balancing work.  It also contains a few callback methods you will want to consider:

  1. getGlobalBlacklist() – this method returns a Map<String, Long> of hosts that have been identified as unavailable.  The String key is the host/port, while the Long is the time that the blacklist entry should expire.  Inside the proxy, this global blacklist is defined as a static Map, meaning that Host X will be found in the blacklist by one Connection if another Connection object put it there after experiencing problems.  Access to the static variable is synchronized, and the Map returned from this method is a local copy.
  2. shouldExceptionTriggerFailover() – this method takes a SQLException and determines whether such an Exception should trigger a failover.  This, too, is user-configurable, although the defaults are usually sufficient for most deployments.  A previous post contains detailed information on how to customize this behavior.
  3. addToGlobalBlacklist() – this is the method you want to call if you want to add a host to the global blacklist.
  4. createConnectionForHost() – this is a utility method that handles creation of a new ConnectionImpl object based on the host/port String, so that you don’t have to wire up ConnectionImpl objects directly.  If you look at the method implementation, you will see the work that goes into setting up a properly-configured ConnectionImpl.

The remaining parameters are, in order:

  • List<String> – a list of configured hosts involved in load-balancing
  • Map<String, ConnectionImpl> – a Map of “live” connections already established, accessed through the host/port key.  Thinking of this as a connection pool, these are the cached connections which can be reused if the host/port pair is chosen, instead of doing the additional work of setting up a new physical connection.
  • long[] – an array of response times in the same order as the List<String> of configured hosts.  This is used in BestResponseTimeBalanceStrategy.
  • int – number of retries that should be attempted before giving up on finding a new connection.

So, what can you do with this?  People frequently ask for a true round-robin load-balancer.  Our experience has been that RandomBalanceStrategy is far better, but if you really need a true round-robin load-balancing algorithm, you could implement it here.

 

Connector/J extension points – exception interceptors

Java, MySQL, , , ,

A third built-in extension point for MySQL Connector/J is the ExceptionInterceptor interface.  This is the third extension point covered in my recent JavaOne and Silicon Valley Code Camp presentations, and is very useful for diagnosing specific Exceptions encountered without modifying application-side code. This corresponds to slide #60 in my slide deck, and there are two Java files we’ll reference from my demo code:

  • demo.connectorj.ExceptionInterceptorExample
  • demo.connectork.plugins.ExampleExceptionInterceptor

To implement an exception interceptor, you need to do the following:

  1. Create a Java class which implements com.mysql.jdbc.ExceptionInterceptor
  2. Configure Connector/J to use your exception interceptor by passing the fully-qualified class name as the value for the “exceptionInterceptors” property.

Like statement interceptors, this extension point is stackable – you can create multiple exception interceptors, passing them in as a comma-delimited list of fully-qualified class names.  The exception interceptors are executed in order in which they are defined in the connection property.

The demo here simply demonstrates how this work, but doesn’t give much of an idea of the power behind this interface.  The demo code executes a command that’s not valid SQL syntax – which will trigger a server-side error – catches the normal exception, wraps it with some additional text in the message before returning it to the application (where it is hopefully logged).  So what can you do with this?

There are certain errors where diagnosis requires additional information about either connection or server state – at the time the exception is raised.  This gives you a hook to enable collection of this data.  For example, certain NDB (Cluster) errors map to the same MySQL Server error code and message, and you can get more information from the NDB problems by issuing SHOW WARNINGS immediately after the error.  Unless you want to recode your application, there’s a lot of value in the ability to add an ExceptionInterceptor which looks for such errors, executes the SHOW WARNINGS, takes the details returned and shoves them into the Exception message text.

Another example might be when you get a server error that indicates the connection character set is set to something other what it should be set to, based on the configuration settings, and you want to know what session character set or collation is in use.  Unless you change your application code, you can only assume what it should be – unless you implement an ExceptionInterceptor that collects that data when the problem is encountered and logs that information for you.

Generally speaking, this is an extension point that you probably won’t leverage for normal operations, but can be very useful for diagnosing Exceptions that are difficult to reproduce outside the context of your Java application deployment.  Because ExceptionInterceptors are only called when handling a SQLException thrown from Connector/J code, you don’t have to worry too much about performance penalties in production deployments.

 

Connector/J extension points – statement interceptors

Java, MySQL, , , ,

Continuing the review of MySQL Connector/J’s built-in extension points from my recent JavaOne and Silicon Valley Code Camp presentations, this blog posting will focus on the StatementInterceptor extension point.  As the name suggests, this allows you to hook into statement execution and alter behavior – without changing application-side code.  This corresponds to slide #59 in my slide deck, and there are two Java files we’ll reference:

  • demo.connectorj.StatementInterceptorExample
  • demo.connectork.plugins.ExampleStatementInterceptor

To implement a statement interceptor, you need to do the following:

  1. Create a Java class which implements com.mysql.jdbc.StatementInterceptorV2
  2. Configure Connector/J to use your statement interceptor by passing the fully-qualified class name as the value for the “statementInterceptors” property.

This extension point is stackable – you can create multiple statement interceptors, passing them in as a comma-delimited list of fully-qualified class names.

The example provided in the demo code is pretty bland, but illustrates what can be done.  In the demo code, we’ve implemented the preProcess() method to check for a certain trigger (“/* test */” in this case), which triggers entirely different behavior than what would normally transpire.  This code simply returns the result of  “SELECT NOW()” when triggered, instead of whatever would normally be executed on the server.  You’ll probably never need this particularly functionality, but there’s other interesting stuff you could do:

  • Add memcached without changing a line of application code, by checking memcached before executing a query, and caching the results after retrieving non-cached data from MySQL.
  • Work around MySQL server problem areas without changing application code.  Maybe you have slow-performing subqueries in an application you cannot change?  Use statement interceptors to rewrite them to more better-performing JOIN syntax equivalents.
  • Perform some standard result set transformation by wrapping ResultSetInternalMethods returned object.
  • Add fine-grained conditional audit logging or access control without changing application code.
  • Track down problematic statements that you suspect may be issued by the application, but not logged anywhere else.
  • Shard your data, and use statement interceptors to route statements to appropriate MySQL instances (or combine results fetched from multiple MySQL servers)  – again without changing application code.
  • Implement low-overhead “ping” operation for connection pools that don’t allow you to define your own validation query.

There’s a lot that can be done with statement interceptors, and they are very easy to wire up – there’s really only five methods you need to implement:

  • init()  – You can set up state variables here, if needed.  Returns void, so leaving this empty is fine.
  • preProcess() – This is where you return a ResultSetInternalMethods object if you want to bypass the normal operation of the statement.  This is called before the statement is sent to the server, so you can change what is sent – or even bypass the sending – here.  If you return null, the driver executes the statement as it normally would.  If you return a non-null object, further execution is bypassed.  Note that the SQL String argument will be null for PreparedStatement objects; you’ll need to handle Statement and PreparedStatement executions differently in order to examine the SQL being sent.
  • postProcess() – Like preProcess(), but invoked by the driver after the server has returned a result set.  This allows you to change the results returned or wrap them with some sort of custom decorator.
  • executeTopLevelOnly() – Return true if you are issuing queries inside preProcess() or postProcess() that could cause infinite recursion.
  • destroy() – Clean up any local references you created in init() here.

The demo code provided will give you a simple example of how to implement statement interceptors, but what you do with them is really limited only by your imagination.

 

Connector/J extension points – lifecycle interceptors

Java, MySQL, , , ,

This is the first of a handful of posts to augment the presentations I gave at Java One and Silicon Valley Code Camp earlier this month.  It seems I significantly overestimated how much content I could effectively deliver in the time allotted, and left a few of my major points untouched.  These blog posts will try to rectify that.

The first major area I failed to cover in depth was really “Extension Points”, starting from slide #56.  There are four major extension points in Connector/J:

  • Lifecycle Interceptors
  • Statement Interceptors
  • Exception Interceptors
  • Loadbalancing Strategies

We’ll look at the first in this post.

Connection lifecycle events can be useful for instrumenting or debugging application database behavior, without changing application code.  In Connector/J, you can intercept the following lifecycle events by implementing com.mysql.jdbc.ConnectionLifecycleInterceptor:

  • Connection creation (via the init() method)
  • Connection.commit()
  • Connection.rollback()
  • Connection.setAutoCommit()
  • Connection.close()
  • Connection.setCatalog()
  • Transaction start/end (via transactionBegun() and transactionCompleted() methods)
  • Connection.close()
  • Connection object destruction (via destroy() method)

So, how might this be useful?  In the demo code provided, I implemented code that prints the stack trace when Connection.rollback() is called.  Perhaps you are trying to understand where rollbacks are coming from in your application – the demo code lets you do just that.  The steps are fully illustrated in the demo code:

  1. Create a lifecycle interceptor that implements com.mysql.jdbc.ConnectionLifecycleInterceptor.
  2. Add logging in the rollback() method to track whatever you require.
  3. Start the application and list the fully-qualified class name of the lifecycle interceptor you created in step #1 as the value for property, “connectionLifecycleInterceptors”.  In the demo code, this is found in demo.connectorj.LifecycleInterceptorExample at line 23:

props.setProperty(“connectionLifecycleInterceptors”, “demo.connectorj.plugins.ExampleLifecycleInterceptor”);

That’s it!

What other lifecycle events might you be interested in tracking?  Well, you might be interested in tracking the time between transaction start and end times.  You might be interested to know how many times setAutoCommit() is called.  It’s convenient that you can do all of this without modifying application code.

Connector/J Presentation at JavaOne and SVCC

Java, MySQL, , , ,

I’ve uploaded both the presentation materials and demo code used in my JavaOne and Silicon Valley Code Camp presentations. Since I ran out of time at JavaOne, I’ll be writing blog posts later this coming week to cover the material I didn’t get a chance to complete there.

UPDATE:  I’ve started adding posts fleshing out the presentation materials, which I will index below:

 

Debugging Communication Link Failure exceptions in Connector/J

Java, MySQL, ,

Have you seen error messages similar to the following:

Communications link failure – Last packet sent to the server was X ms ago.

Judging from the forums, many people have had problems with this.  Here’s a brief overview of the causes, and potential solutions.

Generally speaking, this error suggests that the network connection has been closed. There can be several root causes:

  • Firewalls or routers may clamp down on idle connections (the MySQL client/server protocol doesn’t ping).
  • The MySQL Server may be closing idle connections which exceed the wait_timeout or interactive_timeout threshold

There’s a couple of useful diagnostic details which can be useful.  For starters, when a recent (5.1.13) version of Connector/J is used, you should see additional details around both the last packet sent and received.  Older versions may simply indicate the last time a packet was sent to the server, which is frequently zero ms ago.  That’s not terribly useful, and it may be that you just sent a packet, but haven’t received a packet from the server for 12 hours.  Knowing how long it’s been since Connector/J last received a packet from the server is useful information, so if you are not seeing this in your exception message, update your driver.

The second useful diagnostic detail shows up when Connector/J notices that the time a packet was last sent/received exceeds the wait_timeout or interactive_timeout threshold.  It will attempt to notify you of this in the exception message.

The following can be helpful in avoiding such problems, but ultimately network connections can be volatile:

  • Ensure connections are valid when checked out of connection pool (use query which starts with “/* ping */” *exactly* to execute lightweight ping instead of full query)
  • Minimize duration a Connection object is left idle while other application logic is executed
  • Explicitly validate Connection before using after being left idle for extended period of time
  • Ensure wait_timeout and interactive_timeout are set sufficiently high
  • Ensure tcpKeepalive is enabled
  • Ensure that any configurable firewall or router timeout setting accounts for maximum expected idle connection time.
  • Make sure that you are not setting socketTimeout, or that it is set to a sufficiently high value to avoid socket timeouts.

I’ve seen exception messages which indicate Connections being used after sitting idle for hours – sometimes days.  If you do this, make sure that you are explicitly testing the connection before using it after lengthy idle periods.  Network connections fail, and applications need to be prepared to handle that.  But expecting connections to survive extended periods where left idle and work magically when used again hours later is just asking for trouble.

[Update 11 August 2015]  Combinations of specific versions of MySQL Server, Connector/Java and JREs may produce “communication link failure” messages when trying to negotiate SSL/TLS during the connection phase.  Further details are found here.

Connector/J ping (there will be a test on this)

Java, MySQL, , ,

MySQL Connector/J has a useful feature which executes a lightweight ping against a server (or, in the case of load-balanced connections, all active pooled internal connections that are retained) to validate the connection. As you might guess, this feature is useful for Java apps which use connection pools, so that the pool can validate the connection. Depending on your connection pool and configuration, this can be done at different times:

  • before the pool returns a connection to the application
  • when the application returns a connection to the pool
  • during periodic checks of idle connections

So, if you want to use this magic light-weight ping process, here’s how you do it:

Specifying a “validation query” in your connection pool that starts with “/* ping */” _exactly_ will cause the driver to instead send a ping to the server and return a fake result set (much lighter weight), and when using a ReplicationConnection or a LoadBalancedConnection, will send the ping across all active connections.

So says the relevant note in the changelogs.

Now for the test.  Which of the following Strings match the condition above?

  • sql = “/* PING */ SELECT 1”;
  • sql = “SELECT 1 /* ping*/”;
  • sql = “/*ping*/ SELECT 1”;
  • sql = ” /* ping */ SELECT 1″;
  • sql = “/*to ping or not to ping*/ SELECT 1”;

If you said “none of the above”, you pass the test.  The test is sensitive to whitespace, capitalization, and placement.  It’s this way for efficiency, as this test is done for every Statement that is executed:


protected static final String PING_MARKER = "/* ping */";
...
if (sql.charAt(0) == '/') {
if (sql.startsWith(PING_MARKER)) {
doPingInstead();
...

All of these statements will issue normal SELECT statements and not get transformed into the lightweight ping.  More problematic, this means that loadbalanced connections only execute one statement against one physical connection in the internal pool, rather than validating the connection for each underlying physical connection.  So the non-active physical connections sit stale, and stale connections die, and then Connector/J re-balances and selects that stale dead connection, and suddenly you have an Exception pushed up to the application (loadBalanceValidateConnectionOnSwapServer can save you here).

If your Connector/J deployment uses a connection pool which allows you to specify a validation query, check it, and make sure that it starts with “/* ping */” – exactly.  This is especially true if you are using load-balancing or replication-aware features of Connector/J – it should help keep alive connections which otherwise will go stale and die, causing problems later.

Connector/J load-balancing for auto-commit-enabled deployments

Java, MySQL, , , ,

In a previous blog post, I wrote about Connector/J’s failover policies, I described three triggers which cause Connector/J to re-balance (potentially selecting a new physical connection to another host):

  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.

Those conditions fit most needs very well, but there are situations where people are running with auto-commit enabled (no explicit transaction commit or rollback), and the end result is that Connector/J never re-balances, and keeps chugging away using the same physical connection.  That’s a problem – particularly when load-balancing is being used under-the-hood to distribute read-only load across multiple slaves.  And as of today, there is new functionality which allows Connector/J to be configured to re-balance after a certain number of statements when auto-commit is enabled.  This functionality is dependent upon the following new properties:

  • loadBalanceAutoCommitStatementThreshold – defines the number of matching statements which will trigger the driver to (potentially) swap physical server connections.  The default value (0) retains the previously-established behavior that connections with auto-commit enabled are never balanced.
  • loadBalanceAutoCommitStatementRegex – the regular expression against which statements must match.  The default value (blank) matches all statements.

So, for example, using the following properties will cause Connector/J to re-balance after every third statement which contains the string “test”:

loadBalanceAutoCommitStatementThreshold=3

loadBalanceAutoCommitStatementRegex=.*test.*

Why does loadBalanceAutoCommitStatementRegex exist?  Maybe your application uses temporary tables.  Maybe it uses server-side variables.  Maybe it uses some other connection state that wouldn’t be good to lose by letting the driver arbitrarily swap physical connections before you are done with it (this post explores some of these scenarios).  This allows you to identify a trigger statement which is only executed at times where it is known to be safe to swap physical connections.  Generally, you likely won’t need to use it, but it may come in handy if you rely on server session-side state.

Load-balancing for MySQL Cluster

Java, MySQL, , , ,

Shortly after I wrote my last post regarding some advanced Connector/J load-balancing properties, Anirudh published a post describing configuration of RHEL LVS for load-balancing and failover of MySQL Cluster SQL nodes.  It’s an interesting post, and I admit I know very little about RHEL LVS, but it reminded me of problems I experienced when trying to set up load-balanced ColdFusion(!) servers at my last job, years back.  We ended up with a nice hardware load-balancer sitting in front of multiple ColdFusion web servers.  The problems we found were that our application depended upon session state, which was stored (of course) on a single web server.  The load-balancer allowed us to define sticky sessions, which is what we did, but it cost us.

We couldn’t really balance load – we could balance session counts, sort of.  Every time a new session started, the balancer would pick which server would handle that session – for the full duration of the session.  Some sessions might be short and little load, while others may be very long and represent a huge amount of load.

We also had a limited HA solution.  We implemented a heartbeat function so that when a web server went offline, the load-balancer would re-route affected users to an available server.  But because the session data was stored on the original server, the user had to log in again and recreate session data.  If the user was in the middle of a complex transaction, too bad.

The above problem also made maintenance a pain.  We could reconfigure the load-balancer on the fly to stop using a specific server for new sessions, but we couldn’t take that web server offline until all of the user sessions on that machine terminated.  That might take 5 minutes, or it might take 5 hours.

As I said, I’m no LVS expert, but I would expect similar problems when using it as a load-balancer for MySQL Cluster.  I suspect that only new connection requests are balanced, making persistent connections (like common Java connection pools) “sticky” to whatever machine the connection was originally assigned.  You probably cannot balance load at anything less than “connection” level, while Connector/J will rebalance after transactions or communications errors.  And anytime you lack the ability to redistribute load except at new connections, taking servers offline for maintenance will be problematic (Connector/J 5.1.13 provides a new mechanism to facilitate interruption-free maintenance, which I intend to blog about later).

This means that it probably works best when using other connectors which don’t support load-balancing, or with applications that don’t use persistent connections, but I wouldn’t use it instead of Connector/J’s load-balancing, and I definitely would not use it with Connector/J’s load-balancing – Connector/J won’t understand that multiple MySQL server instances live behind a single address, and won’t be able to coordinate load-balancing with LVS.

Connector/J’s load-balancing failover policies

Java, MySQL, , , ,

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!