How is maxIdleTimeExcessConnections different from maxIdleTime in c3p0?

Question

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?

Isoelectronic

Answer 1

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!

Oshea

So if I have min = 2, maxIdleTime = an hour, and 2 connections, one of which has gone unused for an hour, it will be killed because it's idle, then immediately replaced to maintain the minimum? – Isoelectronic

yup. (now i have to type 11 characters. wait, now that's too many.) – Oshea

So funny thing is it sounds like I want to use maxIdleTimeExcessConnections. There's no reason whatsoever to kill a connection at the minimum even if it's unused and force rotation. Only want to kill if they're above minimum. Isn't that a more sensible way to use this? – Isoelectronic

sure. that's a fine approach. the unconditional maxIdleTime is a nice way of preventing Connections from timing out, as many dbms/jdbc-drivers don't permit Connections to be held open forever. but if that's not an issue for you (or if you are happy using tests to clean away expired Connections lazily), you needn't set maxIdleTime. normally, i'd set it to a value of several hours to prevent material churn. if the goal is to keep the pool's footprint small, maxIdleTimeExcessConnections is a much better choice. – Oshea

@SteveWaldman: Thanks for your answer! How does c3p0 behaves when there are stale connections in the pool and client calls fail with error "Connection Already Closed" etc. Does it makes c3p0 connection pool shrink or these stale connections remain in the pool (even after client call has failed)? TIA. – Sebastiansebastiano

@Sebastiansebastiano Hopefully you have set up Connection testing in c3p0. If you have, broken Connections will be pruned from the pool by the tests you have set up. Even if you have not, c3p0 quietly tests Connections when they generate Exceptions, so a broken client Connection will usually be excluded from the pool. But it's always best to set up sane, explicit Connection testing. – Oshea

Answer 2

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.

Scheffler

Hi,Oleninikov, What is the difference between 'replace idle connections' and 'break idle connections', 'replace idle connections' means to create a new connection object and removes the old one? when break the idle connection, the num of connection pool will be reduced one ? Thanks in advance. – Ennius

Answer 3

springboot - 1.5 x - application.properties -

server.session.timeout=3600

Younglove