What really is connection pooling?

Question

I have heard the term connection pooling and looked for some references by googling it... But can't get the idea when to use it....

• When should i consider using connection pooling?

• What are the advantages and disadvantagesof connection pooling?

Any suggestion....

Answer 1

The idea is that you do not open and close a single connection to your database, instead you create a "pool" of open connections and then reuse them. Once a single thread or procedure is done, it puts the connection back into the pool and, so that it is available to other threads. The idea behind it is that typically you don't have more than some 50 parallel connections and that opening a connection is time- and resource- consuming.

Answer 2

When should i consider using connection pooling?

• Always for production system.

What are the advantages and disadvantages of connection pooling?

Advantages:

• Performance. Use a fixed pool of connection and avoid the costly creation and release of connections.
• Shared infrastructure. If your database is shared between several apps, you don't want one app to exhaust all connections. Pooling help to limit the number of connection per app.
• Licensing. Depending on your database license, the number of concurrent client is limited. You can set a pool with the number of authorized connections. If no connection is available, client waits until one is available, or times out.
• Connectivity issue. The connection pool that is between the client and the database, can provide handy features such as "ping" test, connection retry, etc. transparently for the client. In worse case, there is a time-out.
• Monitoring. You can monitor the pool, see the number of active connections, etc.

Disadvantage:

• You need to set it up and configure it, which is really peanuts usually.