ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

Database Connection Pooling with Tomcat
Pages: 1, 2, 3

Connections issued from the JNDI DataSource factory will be returned to the pool when closed. Clients use a connection pool by borrowing a connection object, using it, and then returning it to the pool by closing it. We have to make sure that after we are done with the Connection, we close it. If a Connection is not closed, it will never be returned to the pool and become available for reuse. Of course, that would tie up resources. The finally block guarantees that used ResultSet, PreparedStatement, and Connection objects are closed and prevents any connection pool leaks, as shown below:




finally {
      try {if (rs != null) rs.close();} catch (SQLException e) {}
      try {if (pstmt != null) pstmt.close();} catch (SQLException e) {}
      try {if (connection != null) connection.close();} catch (SQLException e) {}
    }

Performance Measurement

Before our application hits the ground running, we would like to stress test the app, evaluate its performance, and compare the results between the cached set of pooled connection objects and the non-pooling alternative. For this, we'll rely on JMeter, an open source tool for load testing with a drag-and-drop-style GUI. I have written a test plan for stress testing the web app. I have set up JMeter to stimulate 50 concurrent users, accessing a common servlet two times without any interval. The results are pretty apparent after looking at the JMeter graph results shown in Figures 1 and 2, below.

Thumbnail, click for full-size image.
Figure 1. Pooled DB connections deviation (click for full-size image)

Thumbnail, click for full-size image.
Figure 2. Non-pooled DB connections deviation (click for full-size image)

After several test runs, it turned out that connection pooling is at least four times faster than explicitly creating a DB connection object from the ground up. Admittedly, to get more accurate results, JMeter should run on a different machine. The ramp-up period, which describes the amount of time for creating the total number of threads, should be carefully chosen. It's not considered to be a good idea to set it to zero if you have a large number of threads, because that would create all of the threads at once and send out requests immediately. At the same time, a higher ramp-up period is also not appropriate, as it might underestimate the results.

Conclusion

Connection pooling is a technique used for sharing a cached set of open database connections among several requesting clients. It doesn't require you to modify your code significantly; rather, it provides enhanced performance benefits. Object pooling should be used with care. It does require additional overhead for such tasks as managing the state of the object pool, issuing objects to the application, and recycling used objects. Pooling is best suited for objects that have a short lifetime. If you are already working in a rich Java EE environment, then most likely you would be using an out-of-box connection pooling facility provided by your app server, and your applications' use of connection pooling is almost transparent.

Resources

Kunal Jaggi is an independent Java consultant, primarily focused on enterprise solutions with Java-based technologies.


Return to ONJava.com.