Preamble

After having tested load balancing and failover scenario around Connector/J I wanted to test another well used principle that is called JDBC pooling. In few word JDBC pooling aim at maintaining a pool of permanent connections to your MySQL instance and share them when needed by multiple threads.

The benefit of this is obviously:

• Reduced connection time
• Better resource control and usage as number of connection to your instance will be reduced

First thing I noticed is that the feature is not natively implemented in Connector/J so you have to use another library to implement it. So the next question: where do I find such library ? Till a bit before this post has been written even the official Connector/J documentation (till version 5.1.27 of Connector/J if I’m not wrong) was mentioning a JDBC pooling library called mysql-lbpool. Issue is that there is no jar file to download and insert in your Java program and since Connector/J 5.1.28 the documentation has been updated to mention that those libraries are implemented in most application server programs:

Sun has standardized the concept of connection pooling in JDBC through the JDBC 2.0 Optional interfaces, and all major application servers have implementations of these APIs that work with MySQL Connector/J.

If you dog a bit on Internet you will (non exhaustively) see below libraries often coming in search results:

• Apache Commons DBCP
• The Tomcat JDBC Connection Pool
• c3p0 – JDBC3 Connection and Statement Pooling
• BoneCP – The fast Java JDBC Connection Pool

I have obviously not tested all and I first started by the one that is more logical in my opinion i.e. the one coming from Apache foundation called DBCP. All testing went well even if I’m using Java 1.7 (JDK 1.7.0_51) and noticed that DBCP 1.4 was only supported under JDK 1.6 (JDBC 4) and has not been updated since a while. Then I found the Tomcat JDBC Connection Pool library and it becames much clearer:

The JDBC Connection Pool org.apache.tomcat.jdbc.pool is a replacement or an alternative to the Apache Commons DBCP connection pool.

I let you read the added features in official documentation, the nice thing is that all is straightforward and a Java code written for Apache Commons DBCP is portable to the Tomcat JDBC Connection Pool with minor changes. In below code I have let in comment the original code for Apache Commons DBCP pool. In clear they kept all original procedures names and added new ones when needed.

For this testing I have used Java 1.7.0_51 and MariaDB Galera Cluster 5.5.34 running on Oracle Linux Server release 6.5 64 bits. The Java application is running on my Windows 7 desktop under Eclipse development tool, the used version of Connector/J is 5.1.28.

My first Galera node is called server1.domain.com with 192.168.56.101 as non-routable IP address and MySQL listening port is 3316. My second Galera node is called server2.domain.com with 192.168.56.102 as non-routable IP address and MySQL listening port is also 3316.

Testing

In source code I have registered the JDBC pool in MBeanServer to be able to access its functions/procedures/variables in Jconsole.

package jdbcdemo3;
 
//import org.apache.commons.dbcp.BasicDataSource;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;
 
//MBean registration of the JDBC pool
import javax.management.MBeanServer;
import javax.management.ObjectName;
import java.lang.management.ManagementFactory;
 
 
import com.mysql.jdbc.jmx.LoadBalanceConnectionGroupManager;
 
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
 
public class jdbcdemo3 {
	public static void main(String[] args) throws Exception {
		// Create a DataSource object and configure database, second row if for Apache Commons DBCP
		DataSource datasource = new DataSource();
		//BasicDataSource datasource = new BasicDataSource();
		PoolProperties props = new PoolProperties();
		LoadBalanceConnectionGroupManager jmx1 = new LoadBalanceConnectionGroupManager();
		ResultSet rs;
		Connection conn = null;
		String JDBC_URL = "jdbc:mysql:loadbalance://192.168.56.101:3316,192.168.56.102:3316";
		String objectName = "jdbcdemo3:type=JDBC pool";
		MBeanServer server = ManagementFactory.getPlatformMBeanServer();
		ObjectName mbeanName = new ObjectName(objectName);
 
		props.setUrl(JDBC_URL);
		props.setDriverClassName("com.mysql.jdbc.Driver");
		props.setUsername("yjaquier");
		props.setPassword("secure_password");
		props.setInitialSize(20);
		props.setMinIdle(20);
		props.setMaxIdle(20);
		props.setConnectionProperties("loadBalanceEnableJMX=true;loadBalanceConnectionGroup=galera_cluster01");
		props.setDefaultAutoCommit(false);
 
		System.out.println("PoolConfiguration.isPoolSweeperEnabled(): "+props.isPoolSweeperEnabled()+"\n");
 
		datasource.setPoolProperties(props);
		datasource.createPool();
 
		server.registerMBean(datasource.getPool().getJmxPool(),mbeanName);
 
		System.out.println("------------ MySQL Connector/J JDBC pooling testing ------------\n");
		System.out.println("Trying connection...\n");
 
	  // Get JDBC pool connection
	  try {
		  conn = datasource.getConnection();
	  }
	  catch (SQLException e) {
		  e.printStackTrace();
		  return;
	  }
 
	  System.out.println("Number of servers in cluster: "+jmx1.getActiveHostCount("galera_cluster01"));
	  System.out.println("Server names(s) in cluster: "+jmx1.getActiveHostsList("galera_cluster01"));
 
	  // Looping and executing a query to test failover scenario
	  for(int i=1; i <= 1000; i++) {
		  System.out.println("\nQuery "+i+": ");
		  try {
			  rs = conn.createStatement().executeQuery("SELECT variable_value FROM information_schema.global_variables where variable_name='hostname'");
			  while (rs.next()) {
			  System.out.println("Working on node : " + rs.getString("variable_value"));
			  }
		  }
		  catch (SQLException e) {
			  e.printStackTrace();
			  continue;
		  }
 
		  try {
			  rs = conn.createStatement().executeQuery("select count(*) as nb_user from information_schema.PROCESSLIST where user='yjaquier' order by id;");
			  while (rs.next()) {
				  System.out.println("This node handles "+rs.getString("nb_user")+" connections");
			  }
		  }
		  catch (SQLException e) {
			  e.printStackTrace();
			  continue;
		  }
 
		  try {
			  conn.commit();
		  }
		  catch (SQLException e) {
			  e.printStackTrace();
			  continue;
		  }
 
		  if (conn != null) {
			  conn.close();
			  System.out.println("datasource.getIdle(): "+datasource.getIdle());
			  conn = datasource.getConnection();
		  }
 
		  Thread.sleep(2000);
	  }
 
	  System.out.println("\nExiting...");
	  if (conn != null) {
		  conn.close();
	  }
  }
}

What I’m doing is basic: I create a JDBC pool on my two cluster nodes, then loop picking a connection in the pool and doing a simple query to check on which cluster node I land and how many connection is handle by this node.

Under Eclipse running application is allocate around 10 connections per cluster node (not always so perfectly balanced, look 10 and 11 in my case even if initial size is set to 20) and use cluster nodes in a round robin fashion:

If you connect to your application in Jconsole you can see your pool properties and even access to few methods:

Conclusion

If all my Galera cluster nodes are up and running all is working fine, the pool is equally divided (20 divided by 2 almost) and the Galera cluster nodes are round robin used (randomly I should say). This either using Apache Commons DBCP pool or the Tomcat JDBC Connection Pool. Well, working perfectly till you start testing failover scenario…

I’m a bit disappointed but if I stop one cluster nodes the pool is not automatically re-sized on surviving node(s) and when I restart the failed node, at least it used again but the number of connections created on it is equal to one and never re-sized. I have been able to see this under Eclipse console:

I have tried playing with below options with no success and the methods exposed in Jconsole are also not giving any opportunity to change this:

props.setTestOnBorrow(true);
props.setValidationQuery("select 1");
props.setValidationInterval(1000);
props.setTimeBetweenEvictionRunsMillis(1000);
props.setRemoveAbandoned(true);
props.setLogAbandoned(true);
props.setMaxWait(1000);
props.setTestOnReturn(true);
props.setRemoveAbandonedTimeout(1000);*/
props.setConnectionProperties("autoReconnect=true;autoReconnectForPools=true;loadBalanceEnableJMX=true;loadBalanceConnectionGroup=galera_cluster01");
props.setTestWhileIdle(true);

Looking at few web sites (see references) I thought the pool sweeper was in charge of taking care of testing idle connections and re-sizing the pool while the pool is active. This is why I display the result of props.isPoolSweeperEnabled() procedure and even if output is true I do not see this famous pool sweeper entering in action and re-sizing the pool after one cluster fall down or is back to life…

So far I have not find any trick to make this working. The only option I see is to put another component in the architecture like a F5 box or similar or a software component like HAProxy. If somone has found something interesting feel free to share in comments…

References

• Using the Tomcat 7 JDBC Connection Pool in Production
• Commons DBCP
• The Tomcat JDBC Connection Pool
• Create and register MBean in MBeanServer

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts