How do you setup your connection pool?

Question

What is the best way to setup your pool with respect to:-

1. When do you create connections?
2. When do you close connections, and would you close all of them?
3. Do you test connections are still good. When and how?
4. How do you figure out a good number for the maximum number of connections?
5. What sort of monitoring do you have in place to ensure users of the pool are well behaved? Can you stop one bad piece of code from taking out everything?
6. Have you written your own pool, or used a third-party library?

I believe this is an agnostic question, but comments about "features" of particular databases/languages are welcome. For example, it might be slower or more expensive to connect on some databases than others.

To clarify, I do not intend to write a pool from scratch, this question is more about how to configure an existing library that does pooling.

Answer 1

I wrote a connection pool for the database in Java when it was just a design pattern and not a common library. Now I use the one built into Tomcat.

I used a thread to monitor several aspects of the pool and several parameters to control its behavior...

1. minimumInPool="3"... These first three are created upon launch. The pool is never allowed to drop below three.
2. maximumIdleTimeBeforeRemoval="60"... If a connect is idle for an hour, then drop it and create a new one. Idle time probably means there is only the minimum of three in the pool.
3. maximumInUseTimeBeforeRemoval="30"... If a given connection has been checked out for over 30 minutes, then something is probably wrong. Recall it, and kill the connection.
4. maximumTimeBeforeRemoval="60"... Remove it if it is over 60 minutes old.
5. maximumUsageBeforeRemoval="1000"... Remove it if it has been checked out over 1000 times.
6. monitorInterval="15"... Check the above parameters every 15 minutes.

This served me very well for a couple of years. The highest I ever saw the pool was 151 connections during a wild peek. Usually the pool was at about a dozen during heavy usage and idled down to the minimum three in the early morning hours.

I used Oracle's JDBC thin drivers and connected to an Oracle database.