Connection pooling with MySQL Connector/J

In last month's article, "Does MySQL Connector/J Support Multithreaded Programming?"), I covered the problems with sharing a JDBC connection between multiple threads.

Most of these problems can be overcome with a technique called "connection pooling." In addition to fixing the problems mentioned in the previous article, connection pooling generally leads to more predictable resource usage under load, and allows deployment of applications on smaller or less-powerful hardware than would be normally required.

What is Connection Pooling?

Connection pooling is a technique of creating and managing a pool of connections that are ready for use by any thread that needs them.

This technique of "pooling" connections is based on the fact that most applications only need a thread to have access to a JDBC connection when they are actively processing a transaction, which usually take only milliseconds to complete. When not processing a transaction, the connection would otherwise sit idle. Instead, connection pooling allows the idle connection to be used by some other thread to do useful work.

In practice, when a thread needs to do work against a MySQL or other database with JDBC, it requests a connection from the pool. When the thread is finished using the connection, it returns it to the pool, so that it may be used by any other threads that want to use it.

When the connection is "loaned out" from the pool, it is used exclusively by the thread that requested it. From a programming point of view, it is the same as if your thread called DriverManager.getConnection() every time it needed a JDBC connection, however with connection pooling, your thread may end up using either a new, or already-existing connection.

What Benefits are there to Connection Pooling?

Connection pooling can greatly increase the performance of your Java application, while reducing overall resource usage. The main benefits to connection pooling are:

Reduced connection creation time

While this is not usually an issue with the quick connection setup that MySQL offers compared to other databases, creating connections still has networking and JDBC driver overhead that will be avoided if connections are "recycled."

Simplified programming model

When using connection pooling, each individual thread can act as though it has created its own JDBC connection, allowing you to use straight-forward JDBC programming techniques.

Controlled resource usage

If you don't use connection pooling, and instead create a new connection every time a thread needs one, your application's resource usage can be quite wasteful and lead to unpredictable behavior under load.

Remember that each connection to MySQL has overhead (memory, CPU, context switches, etc) on both the client and server side. Every connection limits how many resources there are available to your application as well as the MySQL server. Many of these resources will be used whether or not the connection is actually doing any useful work!

Connection pools can be tuned to maximize performance, while keeping resource utilization below the point where your application will start to fail rather than just run slower.

How Do I Implement Connection Pooling?

Luckily, 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 fine with MySQL Connector/J.

Generally, you configure a connection pool in your application server configuration files, and access it via the Java Naming and Directory Interface (JNDI). The following code shows how you might use a connection pool:

import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;import javax.naming.InitialContext;import javax.sql.DataSource;public class MyServletJspOrEjb {public void doSomething() throws Exception {/** Create a JNDI Initial context to be able to*  lookup  the DataSource** In production-level code, this should be cached as* an instance or static variable, as it can* be quite expensive to create a JNDI context.** Note: This code only works when you are using servlets* or EJBs in a J2EE application server. If you are* using connection pooling in standalone Java code, you* will have to create/configure datasources using whatever* mechanisms your particular connection pooling library* provides.*/InitialContext ctx = new InitialContext();/** Lookup the DataSource, which will be backed by a pool* that the application server provides. DataSource instances* are also a good candidate for caching as an instance* variable, as JNDI lookups can be expensive as well.*/DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/MySQLDB");/** The following code is what would actually be in your* Servlet, JSP or EJB 'service' method...where you need* to work with a JDBC connection.*/Connection conn = null;Statement stmt = null;try {conn = ds.getConnection();/** Now, use normal JDBC programming to work with* MySQL, making sure to close each resource when you're* finished with it, which allows the connection pool* resources to be recovered as quickly as possible*/stmt = conn.createStatement();stmt.execute("SOME SQL QUERY");stmt.close();stmt = null;conn.close();conn = null;} finally {/** close any jdbc instances here that weren't* explicitly closed during normal code path, so* that we don't 'leak' resources...*/if (stmt != null) {try {stmt.close();} catch (sqlexception sqlex) {// ignore -- as we can't do anything about it here}stmt = null;}if (conn != null) {try {conn.close();} catch (sqlexception sqlex) {// ignore -- as we can't do anything about it here}conn = null;}}}}

As shown in the example above, after obtaining the JNDI InitialContext, and looking up the DataSource, the rest of the code should look familiar to anyone who has done JDBC programming in the past.

The most important thing to remember when using connection pooling is to make sure that no matter what happens in your code (exceptions, flow-of-control, etc), connections, and anything created by them (statements, result sets, etc) are closed, so that they may be re-used, otherwise they will be "stranded," which in the best case means that the MySQL server resources they represent (buffers, locks, sockets, etc) may be tied up for some time, or worst case, may be tied up forever.

What's the Best Size for my Connection Pool?

As with all other configuration rules-of-thumb, the answer is "It depends." While the optimal size depends on anticipated load and average database transaction time, the optimum connection pool size is smaller than you might expect. If you take Sun's Java Petstore blueprint application for example, a connection pool of 15-20 connections can serve a relatively moderate load (600 concurrent users) using MySQL and Tomcat with response times that are acceptable.

To correctly size a connection pool for your application, you should create load test scripts with tools such as Apache JMeter or The Grinder, and load test your application.

An easy way to determine a starting point is to configure your connection pool's maximum number of connections to be "unbounded," run a load test, and measure the largest amount of concurrently used connections. You can then work backwards from there to determine what values of minimum and maximum pooled connections give the best performance for your particular application.

Connection Pooling Configuration Documentation

Since the object of this article is not to discuss the specific connection pool configurations of various application servers, I will leave you with the following pointers to read about configuring connection pools with the most common open source application servers.

Until next time, have fun trying out connection pooling with Connector/J!

P.S. I m always on the lookout for article ideas—If there's a particular Connector/J topic you would like to see covered, please e-mail me at mark@mysql.com!

Apache Tomcat 4.1

Place a copy of mysql-connector-java-[version]-bin.jar in $CATALINA_HOME/common/lib/. Then, follow the instructions in the section MySQL DBCP Example of the Tomcat documentation.

Notice that Connector/J 3.0 and newer work with the same settings: http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html

Tomcat uses the Apache Software Foundation's DBCP libraries, which you can also use in your own non-J2EE code: http://jakarta.apache.org/commons/dbcp/

JBoss

Place a copy of mysql-connector-java-[version]-bin.jar in $JBOSS_HOME/server/all/lib. Then, follow the example configuration file named mysql-ds.xml in the $JBOSS_HOME/docs/examples/jca directory that comes with a JBoss binary installation. To activate your DataSource, place an xml file that follows the format of mysql-ds.xml in the deploy subdirectory in either $JBOSS_HOME/server/all, $JBOSS_HOME/server/default, or $JBOSS_HOME/server/[yourconfig] as appropriate.

Jetty with the "Plus" package

Put mysql-connector-java-[version]-bin.jar in $JETTY_HOME/lib/extra/ext, and then follow the directions located at http://jetty.mortbay.org/jetty/plus/ to configure a DataSource. Use com.mysql.jdbc.Driver for the JDBC driver classname, and follow the JDBC URL format that is given in the README that comes with Connector/J.

OrionServer

To use connection pooling with OrionServer, put mysql-connector-java-[version]-bin.jar in $ORION_HOME/lib and add the following to data-sources.xml:

<data-sourcename="MySqlDS" location="jdbc/MySqlPooledDS"class="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource"max-connections="100"min-connections="5"inactivity-timeout="200"wait-timeout="10"username="USERNAME"password="PASSWORD"url="jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=Cp1251"/>