JDBC Connection Pooling for Oracle Databases

While Database Connection Pooling is not a new concept, it is becoming increasingly more important with the proliferation of both public and private web-based applications.  This post will cover using a JDBC connection pool against an Oracle database.  The general idea of Database Connection Pooling is universal and these concepts can be used with any app server / database combination.  If you have never used a database connection pool before, Apache has an open source product called DBCP which is fairly well documented for use with the Tomcat app server. 

What is Database Connection Pooling

Database Connection Pooling involves creating a persistent group of Oracle sessions, all waiting to fulfill database requests.  When a pooled connection is used, it is not destroyed like a regular database session.  It is returned to the pool so that it can be used to fulfill another request.  The reason for the existence of Database Connection Pools is to eliminate the process (and time required) to create and destroy database connections.  Because Oracle is a multi-process server, establishing a database session is considered expensive as it involves creating an operating system process and allocation of server memory.  A busy web-based applicaiton with a few hundred users can easily generate thousands of logins per minute (we’ve seen this situation more than once).  Although logins look like they happen pretty fast, the time spent waiting adds up very quickly.  The bottom line is that logins just burn CPU.

How is Connection Pooling enabled?

Most commercial application servers have their own connection pooling mechanisms built in.  Configuration of the connection pooling mechanism is usually very straightforward.  The best way to become familiar with your particular implementation is to spend some time with the manuals – oh the horror!  The most important settings of any database connection pool are as follows:

Number of Connections in the Pool

Determining the number of connections required is not an exact science.  You need to take into account the type of transactions (long or short) your application generates.  A good rule of thumb for setting the Max number of connections is to use 10% of the user population.  So if you have 300 users, set the Max connections in the pool to 30.  Every app is different, so getting this number right is a learning process.  It is generally recommended to go bigger rather than smaller since you’re only going to incurr the cost of logins one time and you dont want to lock users out of the system.

Minimum and Maximum Connections

The Min and Max values of any Database Connection Pool SHOULD BE SET TO THE SAME VALUE.  You can set the Min below the Max, but think about what you are doing.  You       are almost guaranteeing that at some point you will begin doing additional database logins.  As soon as the Min number of sessions are occupied, new logins will be done until the pool reaches the Max value.  If you think you will need 50 connections at some point, set the Min and Max to 50.  You’ll only pay for this once where you’ll keep paying over and over if the Min is less than the Max.

Maximum Idle Time

The Max Idle Time is VERY IMPORTANT as it determines when to discard a database connection from the pool.  "If the Min and Max values are set the same, why would this setting matter?" you ask.  Well, not all database connection pools work the same way.  Some will throw a connection away, reducing the number of connections below the Min value which then results in a new login to get the number of connections back up to the Min.  So, SET THE MAX IDLE TIME TO A HIGH VALUE – 24 hours is a good place to start.

 

What are the Gotcha’s?

• Turn off or slow down "Database Connection Validation" if the database connection pool supports it.  This feature causes the connection pool to execute a SQL statement (SELECT 1 FROM DUAL is really popular) to validate the connection before executing the requested query.  This results in a validation query for EVERY connection to the database.

• If you start all your connections at the same time (which you should), be aware that they may all get thrown away an re-created at the same time via the Max Idle  time parameter.  This value can be set so connections are recycled during low activity or to never recycle (until a failure or a manual process causes a change).

• ot all database connection pools are perfect.  Some of them, as we have seen, will let the number of connections drop below the minimum unexpectedly.

• Keep in mind that if you have multiple app servers, each can support a separate connection pool.  If you are load balancing between app servers, you need to be intimately familiar with what the load balancer is doing so you can appropriately size the connection pool on the individual servers.  For example, if the load balancer is biasing connections to one app server over another, the connection pool should be sized appropriately to handle the load.  Also, be prepared for the time when one of the app servers goes off line.  The remaining connection pool must be capable of handling the entire load.

• Although it is better to go bigger on the size of the connection pool, it is easy to go too big.  Each database connection costs server memory and, depending on how your database is configured (PGA Aggregate Target set or not), it could be a lot.  As I said earlier, setting the connection pool size is a learning process.

How to Test a Database Connection Pool?

Ensure that you know what your database connection pool is doing.  YOU MUST TEST THE CONNECTION POOL IN AN ISOLATED ENVIRONMENT.  When you start your app server / database connection pool (lets say the Min and Max connections are set to 40), you should see 40 JDBC connections in the database. 

1. Set the Max Idle Time parameter to a few minutes and see what happens. 
2. Leave the connection pool up for several hours and come back to check it.
3. Check the number of logins against the database to see if connections are being held open or are recycling