Connector/J has long provided an effective means to distribute read/write load across multiple MySQL server instances for Cluster or master-master replication deployments, but until version 5.1.13, managing such deployments frequently required a service outage to redeploy a new configuration. Given that ease of scaling out by adding additional MySQL Cluster (server) instances is a key element in that product offering, which is also naturally targeted at deployments with very strict availability requirements, we had to add support for online changes of this nature. It’s also critical for online upgrades – the other option is to take a MySQL Cluster server instance down hard, which loses any in-process transactions and generates application exceptions, if any application is trying to use that particular server instance.
Mark Matthews and I first presented this at the MySQL Conference and Expo 2010, but the live demo we had planned didn’t work out (buggy video adapter, not product, really!). This is my belated attempt to rectify that situation by walking through configuration, deployment and management of load-balancing configurations.
First, let’s talk about the configuration properties associated with this new functionality:
- loadBalanceConnectionGroup – This is somewhat tricky to explain – imagine a scenario where you have an application which pulls JDBC connections from three different sources. They may be dedicated resources for specific components – one for your JMS, one for your web app, etc. This allows you to manage these JDBC sources within a single class-loader in any combination you choose. If you have different servers for JMS than for your web app, you would define two different connection groups. If they use the same configuration, and you want to manage them as a logical single group, give them the same name. This is the key property for management, though – if you don’t define a name (string) for loadBalanceConnectionGroup, you cannot manage the connections.
- loadBalanceEnableJMX – The ability to manage the connections is exposed when you define a loadBalanceConnectionGroup, but if you want to manage this externally, you will want to enable JMX by setting this property to true. This enables a JMX implementation which exposes the management and monitoring operations of a connection group. To expose this externally, you need to start your application with the -Dcom.sun.management.jmxremote JVM flag. You can then perform connect and perform operations using a JMX client such as jconsole.
Now that you have the properties set, what operations can you perform? First, there are a few monitoring details exposed:
- Current active host count
- Current active physical connection count
- Current active logical connection count
- Total logical connections created
- Total transaction count
The following management operations are also exposed:
- Add host
- Remove host
The JMX interface (com.mysql.jdbc.jmx.LoadBalanceConnectionGroupManagerMBean) has the following methods:
int getTotalHostCount(String group);
long getTotalLogicalConnectionCount(String group);
long getActiveLogicalConnectionCount(String group);
long getActivePhysicalConnectionCount(String group);
long getTotalPhysicalConnectionCount(String group);
long getTotalTransactionCount(String group);
void removeHost(String group, String host) throws SQLException;
void stopNewConnectionsToHost(String group, String host) throws SQLException;
void addHost(String group, String host, boolean forExisting);
String getActiveHostsList(String group);
String getRegisteredConnectionGroups();
The getRegisteredConnectionGroups() method will return the names of all connection groups defined in that class-loader.
So, what does this look like when running? Let’s take a look at the long-delayed demo!
Here’s the code for my test application:
public class ToddTest {
private static String URL = "jdbc:mysql:loadbalance://" +
"localhost:3306,localhost:3310/test?" +
"loadBalanceConnectionGroup=first&loadBalanceEnableJMX=true";
public static void main(String[] args) throws Exception {
new Thread(new Repeater()).start();
new Thread(new Repeater()).start();
new Thread(new Repeater()).start();
}
static Connection getNewConnection() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection(URL, "root", "");
}
static void executeSimpleTransaction(Connection c, int conn, int trans){
try {
c.setAutoCommit(false);
Statement s = c.createStatement();
s.executeQuery("SELECT SLEEP(1) /* Connection: " + conn + ", transaction: " + trans + " */");
c.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static class Repeater implements Runnable {
public void run() {
for(int i=0; i < 100; i++){
try {
Connection c = getNewConnection();
for(int j=0; j < 10; j++){
executeSimpleTransaction(c, i, j);
Thread.sleep(Math.round(100 * Math.random()));
}
c.close();
Thread.sleep(100);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
I start this (with -Dcom.sun.management.jmxremote flag), then start jconsole:
There's the testsuite.ToddTest main class listed there I select that and navigate to the com.mysql.jdbc.jmx.LoadBalanceConnectionGroupManager bean:
Great, let's look at the methods exposed:
Let's get the number of total physical connections established:
So, 27 physical connections have been established. Now, let's see what the currently-configured hosts are:
So the connections are being balanced against instances running on ports 3306 and 3310. Now, I've started up an instance on port 3309, and I want to tell Connector/J to start using it. I do this with the addHost() method:
That worked, and here's how I can tell - the queries start showing up in SHOW PROCESSLIST output on port 3309:
Not quite as riveting as a live demo, I'm sure, but the instructions and code necessary to reproduce this quickly on your own local machine are above. Hopefully this will prove useful to users who want to scale out, maintain and reconfigure Connector/J-driven applications without downtime.
