You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by M4N - Arjan Tijms <ar...@m4n.nl> on 2008/10/31 13:45:47 UTC
[dbcp] Connections closed and opened again when using fixed size
pool?
Hi,
I'm trying to use DBCP 1.2.1 and 1.2.2 (as supplied with Tomcat 6.0.14
resp 6.0.16). I've set the pool's configuration to be a fixed size. My
(test) config is this:
<Resource
name="jdbc/test_ds"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
type="javax.sql.DataSource"
scope="Shareable"
auth="Container"
driverClassName="org.postgresql.Driver"
username="*****" password="******"
url="*****"
poolPreparedStatements="true"
accessToUnderlyingConnectionAllowed="false"
defaultAutoCommit="true"
defaultReadOnly="false"
defaultTransactionIsolation="READ_COMMITTED"
validationQuery="select 1"
testOnBorrow="true"
testOnReturn="false"
testWhileIdle="false"
initialSize="5"
minIdle="5"
maxIdle="5"
maxActive="5"
maxWait="10"
removeAbandoned="true"
removeAbandonedTimeout="10"
logAbandoned="true"
timeBetweenEvictionRunsMillis="2000"
minEvictableIdleTimeMillis="10000"
numTestsPerEvictionRun="5"
/>
This seems to be a fixed size pool to me. However, in my postgresql log
I see that every ~10 seconds all 5 connections are closed and
immediately opened again:
2008-10-31 13:26:09 CET LOG: connection received: host=localhost port=38231
2008-10-31 13:26:09 CET LOG: connection received: host=localhost port=38232
2008-10-31 13:26:09 CET LOG: connection received: host=localhost port=38233
2008-10-31 13:26:09 CET LOG: connection received: host=localhost port=38234
2008-10-31 13:26:09 CET LOG: connection received: host=localhost port=38235
2008-10-31 13:26:09 CET LOG: connection received: host=localhost port=38236
2008-10-31 13:26:20 CET LOG: disconnection: session time: 0:00:10.690
user=test database=test_db host=localhost port=38232
2008-10-31 13:26:20 CET LOG: disconnection: session time: 0:00:10.686
user=test database=test_db host=localhost port=38233
2008-10-31 13:26:20 CET LOG: disconnection: session time: 0:00:10.668
user=test database=test_db host=localhost port=38236
2008-10-31 13:26:20 CET LOG: disconnection: session time: 0:00:10.675
user=test database=test_db host=localhost port=38235
2008-10-31 13:26:20 CET LOG: connection received: host=localhost port=38237
2008-10-31 13:26:20 CET LOG: disconnection: session time: 0:00:10.683
user=test database=test_db host=localhost port=38234
2008-10-31 13:26:20 CET LOG: connection received: host=localhost port=38238
2008-10-31 13:26:20 CET LOG: connection received: host=localhost port=38239
2008-10-31 13:26:20 CET LOG: connection received: host=localhost port=38240
2008-10-31 13:26:20 CET LOG: connection received: host=localhost port=38241
2008-10-31 13:26:31 CET LOG: disconnection: session time: 0:00:10.999
user=test database=test_db host=localhost port=38237
2008-10-31 13:26:31 CET LOG: disconnection: session time: 0:00:10.993
user=test database=test_db host=localhost port=38238
2008-10-31 13:26:31 CET LOG: disconnection: session time: 0:00:10.990
user=test database=test_db host=localhost port=38239
2008-10-31 13:26:31 CET LOG: disconnection: session time: 0:00:10.988
user=test database=test_db host=localhost port=38240
2008-10-31 13:26:31 CET LOG: disconnection: session time: 0:00:10.985
user=test database=test_db host=localhost port=38241
Of course, these timings are consistent with the time set for
"minEvictableIdleTimeMillis". Nevertheless, I don't really understand
why the pool closes all 5 and then immediately re-opens them. Since
minIdle is set to 5, even though connections have been idle for more
than 10 seconds, there seems to be no point in closing them. In general,
there seems to be no point in closing idle connections such that the
total number of them falls below minIdle.
Maybe I'm missing something though. Basically I only want to have an
evictor thread running for checking abandoned connections. Since I have
a fixed size pool, I don't care about any idle connections being closed.
Maybe related to this, on the documentation page
(http://commons.apache.org/dbcp/configuration.html), the term "idle"
seems to be used for both the condition that a connection sits in the
pool, waiting for being requested, and the condition that client code
has requested a connection and failed to return it to the pool within
some time frame. This slightly adds to the confusion I have with
understanding these settings.
Kind regards,
Arjan Tijms
--
It's a cult. If you've coded for any length of time, you've run across someone from this warped brotherhood. Their creed: if you can write complicated code, you must be good.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@commons.apache.org
For additional commands, e-mail: dev-help@commons.apache.org
Re: [dbcp] Connections closed and opened again when using fixed size
pool?
Posted by Phil Steitz <ph...@steitz.com>.
M4N - Arjan Tijms wrote:
> Hi,
>
> I'm trying to use DBCP 1.2.1 and 1.2.2 (as supplied with Tomcat 6.0.14
> resp 6.0.16). I've set the pool's configuration to be a fixed size. My
> (test) config is this:
>
> <Resource
> name="jdbc/test_ds"
> factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
> type="javax.sql.DataSource"
> scope="Shareable"
> auth="Container"
>
> driverClassName="org.postgresql.Driver"
> username="*****" password="******"
> url="*****"
>
> poolPreparedStatements="true"
> accessToUnderlyingConnectionAllowed="false"
> defaultAutoCommit="true"
> defaultReadOnly="false"
> defaultTransactionIsolation="READ_COMMITTED"
>
> validationQuery="select 1"
> testOnBorrow="true"
> testOnReturn="false"
> testWhileIdle="false"
>
> initialSize="5"
> minIdle="5"
> maxIdle="5"
> maxActive="5"
> maxWait="10"
>
> removeAbandoned="true"
> removeAbandonedTimeout="10"
> logAbandoned="true"
>
> timeBetweenEvictionRunsMillis="2000"
> minEvictableIdleTimeMillis="10000"
> numTestsPerEvictionRun="5"
> />
>
> This seems to be a fixed size pool to me. However, in my postgresql
> log I see that every ~10 seconds all 5 connections are closed and
> immediately opened again:
>
> 2008-10-31 13:26:09 CET LOG: connection received: host=localhost
> port=38231
> 2008-10-31 13:26:09 CET LOG: connection received: host=localhost
> port=38232
> 2008-10-31 13:26:09 CET LOG: connection received: host=localhost
> port=38233
> 2008-10-31 13:26:09 CET LOG: connection received: host=localhost
> port=38234
> 2008-10-31 13:26:09 CET LOG: connection received: host=localhost
> port=38235
> 2008-10-31 13:26:09 CET LOG: connection received: host=localhost
> port=38236
> 2008-10-31 13:26:20 CET LOG: disconnection: session time: 0:00:10.690
> user=test database=test_db host=localhost port=38232
> 2008-10-31 13:26:20 CET LOG: disconnection: session time: 0:00:10.686
> user=test database=test_db host=localhost port=38233
> 2008-10-31 13:26:20 CET LOG: disconnection: session time: 0:00:10.668
> user=test database=test_db host=localhost port=38236
> 2008-10-31 13:26:20 CET LOG: disconnection: session time: 0:00:10.675
> user=test database=test_db host=localhost port=38235
> 2008-10-31 13:26:20 CET LOG: connection received: host=localhost
> port=38237
> 2008-10-31 13:26:20 CET LOG: disconnection: session time: 0:00:10.683
> user=test database=test_db host=localhost port=38234
> 2008-10-31 13:26:20 CET LOG: connection received: host=localhost
> port=38238
> 2008-10-31 13:26:20 CET LOG: connection received: host=localhost
> port=38239
> 2008-10-31 13:26:20 CET LOG: connection received: host=localhost
> port=38240
> 2008-10-31 13:26:20 CET LOG: connection received: host=localhost
> port=38241
> 2008-10-31 13:26:31 CET LOG: disconnection: session time: 0:00:10.999
> user=test database=test_db host=localhost port=38237
> 2008-10-31 13:26:31 CET LOG: disconnection: session time: 0:00:10.993
> user=test database=test_db host=localhost port=38238
> 2008-10-31 13:26:31 CET LOG: disconnection: session time: 0:00:10.990
> user=test database=test_db host=localhost port=38239
> 2008-10-31 13:26:31 CET LOG: disconnection: session time: 0:00:10.988
> user=test database=test_db host=localhost port=38240
> 2008-10-31 13:26:31 CET LOG: disconnection: session time: 0:00:10.985
> user=test database=test_db host=localhost port=38241
>
> Of course, these timings are consistent with the time set for
> "minEvictableIdleTimeMillis". Nevertheless, I don't really understand
> why the pool closes all 5 and then immediately re-opens them. Since
> minIdle is set to 5, even though connections have been idle for more
> than 10 seconds, there seems to be no point in closing them. In
> general, there seems to be no point in closing idle connections such
> that the total number of them falls below minIdle.
>
> Maybe I'm missing something though. Basically I only want to have an
> evictor thread running for checking abandoned connections. Since I
> have a fixed size pool, I don't care about any idle connections being
> closed.
>
> Maybe related to this, on the documentation page
> (http://commons.apache.org/dbcp/configuration.html), the term "idle"
> seems to be used for both the condition that a connection sits in the
> pool, waiting for being requested, and the condition that client code
> has requested a connection and failed to return it to the pool within
> some time frame. This slightly adds to the confusion I have with
> understanding these settings.
The key thing to understand is that the minIdle and maxIdle settings
refer to idle objects in the pool, with idle defined to mean objects
that have not been checked out to clients. The
minEvictableIdleTimeMillis setting allows you to have the evictor
examine objects that are idle in this sense and destroy the ones that
have been sitting in the pool waiting to be checked out too long. Each
time it runs, it will look at numTestsPerEvictionRun idle objects and
destroy the connections that have been in the pool awaiting checkout too
long. This should not be used to remove abandoned connections, as these
will not be idle in this sense, since they will have been checked out by
the clients that abandoned them. That is what the removeAbandoned
settings are for.
One more comment. When you say you want a "fixed size pool", it sounds
to me like you want to have numIdle + numActive = a fixed number (i.e.,
the total number of connections idle plus checked out to be constant).
This is not strictly speaking possible with dbcp (at least I don't know
how to do it). What you can control is the total number of connections
attributable to the pool and its clients at a given time, with
maxActive, the maximum number of idle connections (in sense above), with
maxIdle, and the minimum number of idle connections, with minIdle.
Having maxIdle = minIdle is not generally a good idea, because this will
lead to lots of connection churn as the pool works to maintain exactly
the required number of idle connections. Having maxActive = maxIdle =
minIdle is even harder on the pool, since it can't actually maintain
this unless there are no connections checked out from the pool. In
this case, maxActive essentially trumps minIdle, so when the evictor
runs and tries to ensure minIdle are available, it will succeed only in
filling the idle object pool to maxActive - numActive.
Hope this helps. The documentation could certainly be improved and
documentation patches are welcome.
Phil
>
> Kind regards,
> Arjan Tijms
>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@commons.apache.org
For additional commands, e-mail: dev-help@commons.apache.org