Fog Creek Software
Discussion Board




Database Connection Pooling

When would be a good time to decide if it is good to add database connection pooling to an application?

This is a question I have from a college course - so a disclaimer, what you answer could theoretically assist me in getting a better mark.

I have a simple web application that we developed for couese work - it has a web interface (JSP,Servlets), a middle layer with business logic and some exposed APIs (exposed to allow other software applications to use) and the database.

For connections to the database - I have a constant database connection.  It can handle loads of up to 6 queries a second - this limit is self-imposed from the software side.

What is the benefit of adding connection pooling? Is it  purely to give scalability?

Wierd Al Yankovic
Tuesday, July 13, 2004

I can't think of many situations where connection pooling wouldn't help performance. 

Let's say it takes .1 seconds (totally random estimate) to initiate a connection, but only point .01 (also totally random) seconds to retrieve a connection from the pool. 

Every time you want a connection to talk to your database, you're seeing a .09 second benefit.  This is on top of the scalability issue of having few connections open.

Brian
Tuesday, July 13, 2004

Sorry, didn't notice that you said you're keeping a constant connection open.  In a single threaded scenario where you are sure you'll never lose your connection, you wouldn't really need the pooling, but as soon as you leave this academically simple model, you're in for problems.

Brian
Tuesday, July 13, 2004

Your setup sounds like a pretty normal sort of J2EE system, presumably using EJBs for the business logic part. This being so, then you are probably using an EJB container, almost all of these have built-in connection pooling because it is such a common need. Even if doing JDBC inside such a container, there is usually an API to get a connection from the pool and/or it picks up the standard JDBC request and redirects it through the pool.

Its an interesting question academically, but not necessarily one that you need to worry about at the practical level.

WhatTimeIsItEccles
Tuesday, July 13, 2004

While your limit of 6 queries/second may be self imposed, a large-scale real world application would run up against a wall in performance if utilizing only a single connection to the DB. 

This comes from the fact that while a query is executing on a given connection, no other activity can occur on that connection (ie, each connection is like a single thread).  Adding more concurrent connections means more queries can be executed simultaneously at any given moment.

Using connection pooling allows your app to maintain all these extra DB connections efficiently and (almost) effortlessly, which increases scalability and performance dramatically in high-volume scenarios.

Joe
Tuesday, July 13, 2004

Just in repsonse to brian's post.  There are situations where pooling doesn't help perfirmance.  If you have an application that only uses connection from time to time, the outcome is that it costs more to instantiate and maintain the pool than just allowing one conncetion to database.  However, in the case of this web application, then I agree with Brian, connection pooling will pretty much increase your performance.
My question is that, what type of data are you querying?  If all the users have similar request and similar data result, caching might give you better performance.

Unix2M$
Tuesday, July 13, 2004

You might find some of the docs and info from http://sqlrelay.sourceforge.net/ informative in learning more about the subject.

Jeff
Tuesday, July 13, 2004

Not that I disagree entirely, but:

> If you have an application that only uses connection from
> time to time, the outcome is that it costs more to
> instantiate and maintain the pool than just allowing one
> conncetion to database.

This may be considered premature optimization, but I would also look at the future cost of adding pooling to an existing application that didn't use it.  Most of the time it's just a small change in a connection string somewhere, but if you rely on things like MS SQL Server Application Roles (which don't play nice w/ pooling), then it may be worth it to weigh the costs, in case someday your app does need to be more performant.

Generally, I go w/ pooling by default, just like I avoid web app session state by default (in case someone wants to install the app on a cluster or web farm later).  Unless you have a specific reason to avoid it, the performance hit caused by using the connection pool vs 1 static connection is really quite minimal vs the costs of having to implement it later underneath a design that made assumptions about not having a pool.

Joe
Tuesday, July 13, 2004

*  Recent Topics

*  Fog Creek Home