Database connections in depth / Sponsor

How Connection Pooling Works

In a pooled environment, once the initial physical connection is established, it will likely not be closed for the life of the environment. That is, when an application disconnects, the physical connection is not closed; instead, it is placed in the pool for reuse. Therefore, re-establishing the connection becomes one of the fastest operations instead of one of the slowest.

Here is a basic overview of how connection pooling works:

1. When the application or application server is started, the connection pool is typically populated with connections.
2. An application makes a connection request.
3. Either the driver or the Connection Pool Manager (depending on your architecture) assigns one of the pooled connections to the application instead of requesting that a new connection be established. This means that no network round trips occur between the driver and the database server for connection requests because a connection is available in the pool. The result: Your connection request is fast.
4. The application is connected to the database.
5. When the connection is closed, it is placed back into the pool.
Guidelines for Connection Pooling

Here are some general guidelines for using connection pooling. 

A perfect scenario for using connection pooling is when your applications reside on an application server, which implies multiple users using the applications. 

Consider using connection pooling if your application has multiple users and your database server has enough memory to manage the maximum number of connections that will be in the pool at any given time. In most connection pooling models, it is easy to calculate the maximum number of connections that will be in a pool because the connection pool implementation allows you to configure the maximum. If the implementation you are using does not support configuring the maximum number of connections in a pool, you must calculate how many connections will be in the pool during peak times to determine if your database server can handle the load. 

Determine whether the number of database licenses you have accommodates a connection pool. If you have limited licenses, answer the following questions to determine if you have enough licenses to support a connection pool:
a. ‑Will other applications use database licenses? If yes, take this into account when calculating how many licenses you need for your connection pool.
b. ‑Are you using a database that uses a streaming protocol, such as Sybase, Microsoft SQL Server, or MySQL? If yes, you may be using more database connections than you think. In streaming protocol databases, only one request can be processed at a time over a single connection; the other requests on the same connection must wait for the preceding request to complete before a subsequent request can be processed. Therefore, some database driver implementations duplicate connections (establish another connection) when multiple requests are sent over a single connection so that all requests can be processed in a timely manner. 

When you develop your application to use connection pooling, open connections just before the application needs them. Opening them earlier than necessary decreases the number of connections available to other users and can increase the demand for resources. Don’t forget to close them when the database work is complete so that the connection can return to the pool for reuse.

Next Page: When Not to Use Connection Pooling

Previous page