TomcatExpert

connection pool

Blog : Building extensions to jdbc-pool

posted by fhanik on April 19, 2010 06:08 AM

In our previous blog posts, we gave a short introduction to a new module,  jdbc-pool, currently being development inside of Apache Tomcat's subversion development branch as a high concurrency alternative to connection pooling. We also covered jdbc-pool performance and how to cconfigure jdbc-pool for optimal performance.

In this article we'll dig a bit deeper under the covers and take a look at how to build extensions to the jdbc-pool. To do this, we will build a prepared statement cache for the jdbc-pool using the interceptors as a base.

A note of caution

Personally, I don't believe that connection pools should perform any level of statement caching. Here are a few reasons why:

  • Statements represent resources on the database back end. Hence this cache leads to increased resources usage.
  • Statement caches belong on the database, in worst case in the JDBC driver. But not in a connection pool.
  • The overhead of preparing a statement can be cached and optimized on the database, rather than at a much higher level, since the overhead mainly is related to the parsing of the query.
  • Statements have to be cached on a per connection basis, not making it an optimal cache for a connection pool
  • There is an API to allow the underlying driver/database to handle it using the setPoolable call.
    If you take a look at a number of databases, these databases already have facilities for caching and optimizing statements, take DB2 dynamic statements as a simple example.

Setting up the interceptor

Most of the extensions in jdbc-pool can be accomplished using the JDBC interceptor base class. The interceptors all extend the invocation handler interface.

This means, that any method invocations on the Connection interface will pass through the

Object invoke(Object proxy, Method method, Object[] args) throws Throwable;

method. So if the user is calling javax.sql.PooledConnection.getConnection method we could implement that as:

1:  public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
2:    if (method.getDeclaringClass().equals(PooledConnection.class) &&
 
3:        compare(GETCONNECTION_VAL,method) { 
4:        return connection.getConnection();
5:    } else {
6:        return super.invoke(proxy,method,args);
7:    }
8:  }
  • Line 2 - We check that the interface that is being called is the javax.sql.PooledConnection.getConnection class
  • Line 3 - We check that the method being called is getConnection
  • Line 4 - We return the underlying connection
  • Line 6 - If the above conditions have not been met, pass on the request to the next interceptor.

Read More

5 comments   |  

3
Rating
  |  

Developers, Operations | connection pool, Tomcat 7, Tomcat Admin

Blog : Configuring jdbc-pool for high-concurrency

posted by fhanik on April 1, 2010 03:19 PM

In this article we will focus on configuration of the high-concurrency connection pool. For ease of migration for Tomcat users, the configuration has been written to mimic that of Commons DBCP.

The documentation of jdbc-pool covers all the attributes. Please note that these attributes are also available as direct setters on the org.apache.tomcat.jdbc.pool.DataSource bean if you're using a dependency injection framework. So in this article we will focus on use cases, and different configurations for Tomcat.

Simple Connection Pool for MySQL

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
/>

The first thing we notice is the factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" attribute.
When Tomcat reads the type="javax.sql.DataSource" it will automatically configure its repackaged DBCP, unless you specify a different factory. The factory object is what creates and configures the connection pool itself.

There are two ways to configure Resource elements in Apache Tomcat.

Read More

28 comments   |  

4
Rating
  |  

Developers, Operations | connection pool, DBCP, high-concurrency

Blog : Understanding the jdbc-pool performance improvements

posted by fhanik on March 24, 2010 12:12 PM

In our previous article we gave a short introduction to a new module,  jdbc-pool, currently being development inside of Apache Tomcat's subversion development branch as a high concurrency alternative to connection pooling.

One of the main reasons this module was started was performance. In this article we will show you how to benchmark your tests and also share some performance numbers on the high-concurrency connection pool.

You can run these tests yourself against an in-memory database. Assuming you have Java 6, Ant and SVN installed simply follow these steps:

svn co http://svn.apache.org/repos/asf/tomcat/trunk/modules/jdbc-pool
cd jdbc-pool
ant test

In our test runs we used a local MySQL database on a Solaris machine. While there is some overhead in creating a connection to MySQL, in our tests, this is a one-time affair, as we are testing pooled connections.

Read More

6 comments   |  

5
Rating
  |  

Developers, Operations | connection pool, DBCP, high concurrency

Blog : Explaining jdbc-pool, a High Concurrency Alternative to Connection Pooling Module

posted by fhanik on March 12, 2010 02:23 PM

Why another connection pool project

In this article we are going to explore one of the modules that is being developed in the Tomcat development trunk. The module named, jdbc-pool, is exactly that, an implementation of pooling of JDBC resources.

Connection pools have been around for a long time. I wrote one myself in 1997 when I first was introduced to Java, and used that for a long time in different projects I worked on. When JDBC came up, developers quickly realized that pooling JDBC connections would be beneficial for performance, and also a way to siphon connections and control the resource usage on the database server. This was especially important in horizontally scaled clusters where the database was always a risk of being overloaded. In those days we typically ran 1, 2 and sometimes 4 CPU machines for the application servers.

Connection pooling quickly became something that J2EE vendors developed in-house to integrate with their transaction services and became proprietary and integrated into the server itself. Outside of the J2EE containers a number of open source projects popped up. A few examples are Proxool, Apache Commons DBCP, C3P0 and Poolman. These are the most common ones we encounter in the field. Apache Tomcat has for very many years relied on DBCP to provide Tomcat users with a connection pooling implementation.

All of these open source projects have their pros and cons, but they all have something in common. They have become fairly stagnant and the code bases have not continued to evolve to meet new features in the Java language and are not optimized for multicore hardware that is increasingly becoming the common platform to deploy Java applications on. Some of the projects have also suffered from unneeded complexity in the source code, most likely one of the main reasons the project code is not moving forward, making it hard to maintain and easy to break.

Read More

17 comments   |  

4.666665
Rating
  |  

Developers | Commons DBCP, connection pool, JDBC

Syndicate content