Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How is maxIdleTimeExcessConnections different from maxIdleTime in c3p0?

I want to configure my c3p0 connection pool so that at least 2 connections are always active, at most 5, and idle connections past the first 2 will expire in a reasonable amount of time (say an hour).

This all sounds straightforward except the documentation seems to imply that there is no difference between the functions maxIdleTime and maxIdleTimeExcessConnections, which is confusing me.

Basic pool configuration states:

Within the range between minPoolSize and maxPoolSize, the number of Connections in a pool varies according to usage patterns. The number of Connections increases whenever a Connection is requested by a user, no Connections are available, and the pool has not yet reached maxPoolSize in the number of Connections managed. Since Connection acquisition is very slow, it is almost always useful to increase the number of Connections eagerly, in batches, rather than forcing each client to wait for a new Connection to provoke a single acquisition when the load is increasing. acquireIncrement determines how many Connections a c3p0 pool will attempt to acquire when the pool has run out of Connections. (Regardless of acquireIncrement, the pool will never allow maxPoolSize to be exceeded.)

And minPoolSize usage:

Minimum number of Connections a pool will maintain at any given time.

Okay, great. And for configuring connection time:

maxIdleTimeExcessConnections is about minimizing the number of Connections held by c3p0 pools when the pool is not under load. By default, c3p0 pools grow under load, but only shrink if Connections fail a Connection test or are expired away via the parameters described above. Some users want their pools to quickly release unnecessary Connections after a spike in usage that forces a large pool size. You can achieve this by setting maxIdleTimeExcessConnections to a value much shorter than maxIdleTime, forcing Connections beyond your set minimum size to be released if they sit idle for more than a short period of time.

So it's implied that minPoolSize only matters when used in conjunction with maxIdleTimeExcessConnections, otherwise, it'll just be ignored entirely.

Corroborating the documentation for maxIdleTime makes no mention of minPoolSize:

Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.

And maxIdleTimeExcessConnections makes sense:

Number of seconds that Connections in excess of minPoolSize should be permitted to remain idle in the pool before being culled. Intended for applications that wish to aggressively minimize the number of open Connections, shrinking the pool back towards minPoolSize if, following a spike, the load level diminishes and Connections acquired are no longer needed. If maxIdleTime is set, maxIdleTimeExcessConnections should be smaller if the parameter is to have any effect. Zero means no enforcement, excess Connections are not idled out.

I find it really odd that minPoolSize, a basic feature, only matters when used with what seems to me to be a more advanced feature. Is this all correct?

like image 832
djechlin Avatar asked Mar 27 '14 17:03

djechlin


People also ask

How does c3p0 connection pool work?

c3p0 is a Java library that provides a convenient way for managing database connections. In short, it achieves this by creating a pool of connections. It also effectively handles the cleanup of Statements and ResultSets after use.

What is Mchange c3p0?

a mature, highly concurrent JDBC Connection pooling library, with support for caching and reuse of PreparedStatements. www.mchange.com/projects/c3p0.

What is acquireIncrement?

acquireIncrement determines how many Connections a c3p0 pool will attempt to acquire when the pool has run out of Connections. (Regardless of acquireIncrement, the pool will never allow maxPoolSize to be exceeded.)

What is Max idle time?

This attribute accepts a value (in minutes) equal to the maximum amount of time without activity before a session expires and the user must reauthenticate to regain access.


2 Answers

what a careful and lawyerly read!

but no, it's not correct.

there are several ways a Connection can die. as you quote:

c3p0 pools...shrink if Connections fail a Connection test or are expired away via the parameters described above.

the "parameters described above" include maxConnectionAge, maxIdleTime, and maxIdleTimeExcessConnections. Connections can also be removed from the pool because they fail Connection tests while idle (see idleConnectionTestPeriod), because they fail tests on check-in or on check-out (testConnectionOnCheckin, testConnectionOnCheckout), or because they fail tests triggered by an Exception in the course of client use.

however a pool shrinks, minPoolSize matters, because if the pool shrinks below minPoolSize, destroyed Connections will be replaced until minPoolSize is restored.

what is unique about maxIdleTimeExcessConnections is that it's behavior is directly contingent upon the size of the pool relative to minPoolSize. all the other parameters and tests just do their thing. if their thing happens to bring the pool to something lower than minPoolSize, then c3p0 will automatically bring the pool back to minPoolSize. but maxIdleTimeExcessConnections is different. it only has any effect when the pool is larger than minPoolSize.

as you say, maxIdleTimeExcessConnections is an advanced feature. most users do never and need never use it. it was added because some users wanted to aggressively force pools to shrink back to minPoolSize, but doing that with a very short unconditional maxIdleTime causes unnecessarily churn through Connections, as Connections even in a minPoolSize pool are constantly expired and replaced. setting a long or nonexistent maxIdleTime, while setting a short maxIdleTimeExcessConnections yields the desired result of fast, aggressive shrinking without churning through Connections once the pool hits minPoolSize.

but even without maxIdleTimeExcessConnections set, minPoolSize matters very much. Connections do get destroyed and expunged from the pool, and minPoolSize determines a threshhold below which destroyed Connections will be automatically replaced, even if no client load comes to provoke pool expansion.

i hope this makes sense!

like image 88
Steve Waldman Avatar answered Oct 21 '22 02:10

Steve Waldman


Good answer by Steve Walkdman, I just want to add a short answer:

The difference is that maxIdleTime will replace idle connections even in minPoolSize-d pools, while maxIdleTimeExcessConnections won't.

like image 21
Georgii Oleinikov Avatar answered Oct 21 '22 00:10

Georgii Oleinikov