How to use JDBC (Connector/J) with MySQL Cluster

Last week I helped a customer setup a JBoss application against MySQL Cluster. It turns out it is not immediately obvious how you should setup our JDBC connector to do loadbalancing and failover. For instance, setting the connector up for an Master-Slave setup (with MySQL Enterprise) is well documented, but not doing the same with MySQL Cluster.

It's not really properly documented in the manual part, but I found in the changelogs, and confirmed on IRC that to do load-balancing across the SQL nodes in MySQL Cluster, you would use a different JDBC connection string with the "loadbalance" keyword added...


jdbc:mysql:loadbalance://host-1,host-2,...host-n/database

That does indeed loadbalance, however it didn't properly address failover. When an SQL node is killed, it still tries to round-robin queries to all specified hosts, resulting in exceptions 50% of the time (with 2 nodes, that is).

After further digging (in fact, my collague got this from Mark Matthews himself) I finally found out that the correct string to use is:


jdbc:mysql:loadbalance://host-1,host-2,...host-n/database?loadBalanceBlacklistTimeout=5000

The "loadBalanceBlacklistTimeout" adds the needed feature that failed connections in a connection pool are put aside for the specified time, and only working connections are utilized.

That's all that is needed. It is simple and beautiful once you get it to work!

Update: I should add that transactions that are running while a node crashes will still rollback and return an exception. This is by design and it is then up to the application to decide whether to give up or retry. If you retry the transaction, the JDBC driver will pick a working SQL node for the second try.

Comments:

A few quick notes:

1. You probably shouldn't be getting an Exception 50% of the time, when one of two nodes is down and the global blackist is disabled. I believe that the loadbalancing code swallows the Exception and moves on to the next node in the list silently. Of course, if you already have an existing connection and the node goes down, you'll get an Exception. But it shouldn't happen when the loadbalancer selects the node up front.

2. There are a couple of loadbalancing strategies, the default is "random". Another available strategy is "bestResponseTime", which might prove useful ("loadBalanceStrategy=bestResponseTime").

3. You may find the "retriesAllDown" (currently undocumented :( ) parameter useful - it defines the maximum number of times the list of all nodes will be iterated while trying to establish a successful connection before an Exception is thrown.

Hope that helps!

Posted by Todd Farmer on November 28, 2008 at 08:49 PM EET #

I've been trying to get attention on this issue for years....

you guys should just be using our JDBC driver (lbpool).

Just check it out of SVN and build:

http://code.google.com/p/mysql-lbpool/

It's been used in production at Spinn3r for more than two years now.

It's a complete project........ MySQL AB just hasn't done any work promoting it even though they own the code.

That, and I'm a horrible marketer of my open source projects :)

Posted by Kevin Burton on November 28, 2008 at 11:56 PM EET #

@Todd:

1. I don't know if it's 50 % of the time, but it didn't work before adding the loadBalanceBlacklistTimeout parameter. In other words, also new transactions would generate exceptions, not just the once in progress at the time of failover. I also tried it with a simple non-J2EE Java program, there in particular it didn't work without the parameter.

2. Yes. I was happy when we got it to work at all, but thanks for adding this.

3. Cool, wasn't aware of it.

@Kevin:

The main reason was that I cannot find a download of it, only an svn checkout. In this particular case the customer is explicitly wanting to get rid of some 3rd party Open Source non-mysql stuff, so it wouldn't have been a solution in any case, but in general lbpool sounds interesting indeed.

Something I read about it tells me I should ask: Does it support the MySQL Cluster use case where all SQL nodes are working on the same database - e.g. not Master-Slave but rather pretty much the same as Active-Active (like Oracle RAC, say)?

Posted by Henrik Ingo on November 30, 2008 at 12:32 PM EET #

Post a Comment:
Comments are closed for this entry.
About

The people of the MySQL Telecom team writes about developments around MySQL and MySQL Cluster and how these products are used by our Communitcations industry customers. (Image jasmic@Flickr)

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today