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....

Idiophone

@John: This question is fairly language agnostic. He is asking about the concept of connection pooling, not a specific implementation. – Infliction

@William: do you know the OP, or are you a mindreader? I wanted the OP to tell us which platform he had in mind, since he talks about using it. – Curve

@John: You could have done without your first sentence. If you would like to continue this without snide comments, let me know. – Infliction

@William: I note you didn't answer the question. If you'd like to continue this without telling me I can't read, and without telling me why you think I can't read, then you can go ahead without me. Otherwise, tell me how you decided the question was language-agnostic when the OP hasn't yet answered my question asking him whether or not the question is language agnostic. – Curve

@John: The OP asked some very general questions about what connection pooling is, when he should use it, and what the advantages of it are. The answers to those questions are the same regardless of the platform being used. That's why I said the question is language agnostic, and most people posting answers seem to agree with that assessment. Perhaps you're right and the OP left out the part where he really wanted to know how to use connection pooling with .NET or Java, but as his question is written, there is no indication of that. Now, I've explained myself, and I'm done thinking about it. – Infliction

@William: some of those answers are the same regardless of platform. Some are specific to the platform. That's why I asked what platform the OP had in mind, and did not assume that the question was language-agnostic. You're right that the OP didn't specify, but a large number of users of SO do not specify. These can be distinguished by the fact that they don't answer comments, so the fact that he didn't specify could mean either that the question is language-agnostic, or that he didn't think to specify and doesn't answer comments. – Curve

@William: it hasn't been 24 hours yet. Maybe the OP only has Internet access for an hour a day. It's too soon to make assumptions. – Curve

@Bala: What platform? .NET? Java? – Curve

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.

Wildman

It doesn't have to specifically be to databases. Connection pooling can be useful for anything that requires a connection that does not need to be closed at the end of a request. – Recede

True, the same applies to whatever resources where opening a connection takes a lot of time. – Wildman

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.

Tragedian

FYI, Connection pooling is on by default in ADO.NET - you pretty much have to go out of your way to prevent it. If you use a different username for each connection, for instance, then there will be no pooling. In the .NET case, there's no explicit pool management, nor limit to the number of connections per app, as the pool is per-app. – Curve

I think @Tragedian is right on the advantages, and you have a point with the credentials, but, in many cases, database user has no relevance because is not used more than to authenticate the application with the database, not directly the user with the database, the application takes care of authenticating the user using the database to do it. In that case, @Tragedian has totally the point. – Primavera

Answer 3

You should use connection pooling whenever the time to establish a connection is greater than zero (pretty much always) and when there is a sufficient average usage such that the connection is likely to be used again before it times out.

Advantages are it's much faster to open/close new connections as they're not really opened and closed, they're just checked out/in to a pool.

Disadvantage would be in some connection pools you'll get an error if all pooled connections are in use. This usually is a good thing as it indicates a problem with the calling code not closing connections, but if you legitimately need more connections than are in the pool and haven't configured it properly, you could get errors where you wouldn't otherwise.

And of course there will be other pros and cons depending on the specific environment you're working in and database.

Motherless

I don't get "average usage such that the connection is likely to be used again before it times out"? Connection pools usually validate connections that are served to the client, and recreate them transparently if they timed out. – Tragedian

@ewernli, if you have a connection pool with a very low-usage application such that a connection is only needed once every 10 minutes but it times out after 5 minutes, then the connection pool will never really be needed since the connections will usually be timedout. This isn't a common scenario, but can come up occasionally. – Motherless

Answer 4

connection pooling enables re-use of an existing, but not used database connection. by using it you eliminate the overhead of the connection/disconnection to the database server. it provides a significant performance boost in most cases. the only reason i can think of not to use it is if your software won't be connecting frequently enough to keep the connections alive or if there's some bug in the pooling layer.

Bethanybethe

Answer 5

In .NET, if you are using the same connection string for data access then you already have connection pooling. The concept is to reuse an idle connection without having to tear it down & recreate it, thereby saving server resources. This is of-course taking into consideration that you are closing open connections upon completion of your work.

Polyandrist

Answer 6

If we required to communicate with the database multiple times then it is not recommended to create a separate Connection Object every time, because creating and destroying connection object impacts performance.

To overcome this problem we should use a connection pool.

If we want to communicate with the database then we request a connection pool to provide a connection. Once we got the connection, by using it we can communicate with the database. After completing our work, we can return the connection object back to the pool instead of destroying it.

The main advantage of connection pooling is to reuse the same connection object multiple times.

Pathognomy