You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by sic <si...@naver.com> on 2010/09/03 04:20:46 UTC
[DBCP] getConnection Timeout
I'm using dbcp on the web application server whose version is
commons-dbcp-1.3, commons-pool-1.5.4 on jdk1.5.
It can be used to execute some DML statements when receiving about 30 ~ 150
number of data(data is less than 3 KB) peridically per minute
Expected to do these simple works with no difficulties, it generated some
errors occasionally(50 ~ 150 per day).
org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool
error Timeout waiting for idle object
at
org.apache.commons.dbcp.PoolDataSource.getConnection(PoolingDataSource.java:114)
at
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
...
The web application server could use 30 threads at most, using oracle DB and
the configurations are following:
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="..." />
<property name="username" value="..." />
<property name="password" value="..." />
<property name="initialSize" value="5"/>
<property name="maxActive" value="30"/>
<property name="maxIdle" value="30"/>
<property name="minIdle" value="5"/>
<property name="maxWait" value="10000"/>
<property name="testWhileIdle" value="true"/>
<property name="validationQuery" value="select 1 from dual"/>
<property name="testOnReturn" value="true"/>
<property name="timeBetweenEvictionRunsMillis" value="10000"/>
</bean>
How can I prevent it from generating continuously?
I have a feeling of doubt about my maxWait and
timeBetweenEvictionRunsMillis.
Surely I guess, if I change the maxWait value more sufficiently, it would be
generated much less than the present, which is just a temporary resolution.
I'm not certain whether timeBetweenEvictionRunsMillis has influence on this
issue.
And I have to put a lot of effort as it has already run for a long time, not
easy to change something.
I appreciate somebody make a helpful advice for that issue.
regards,
sic
--
View this message in context: http://apache-commons.680414.n4.nabble.com/DBCP-getConnection-Timeout-tp2525029p2525029.html
Sent from the Commons - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] getConnection Timeout
Posted by Phil Steitz <ph...@gmail.com>.
On 9/2/10 10:20 PM, sic wrote:
>
> I'm using dbcp on the web application server whose version is
> commons-dbcp-1.3, commons-pool-1.5.4 on jdk1.5.
>
> It can be used to execute some DML statements when receiving about 30 ~ 150
> number of data(data is less than 3 KB) peridically per minute
>
> Expected to do these simple works with no difficulties, it generated some
> errors occasionally(50 ~ 150 per day).
>
> org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool
> error Timeout waiting for idle object
> at
> org.apache.commons.dbcp.PoolDataSource.getConnection(PoolingDataSource.java:114)
> at
> org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
> ...
>
> The web application server could use 30 threads at most, using oracle DB and
> the configurations are following:
>
> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
> destroy-method="close">
> <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
> <property name="url" value="..." />
> <property name="username" value="..." />
> <property name="password" value="..." />
> <property name="initialSize" value="5"/>
> <property name="maxActive" value="30"/>
> <property name="maxIdle" value="30"/>
> <property name="minIdle" value="5"/>
> <property name="maxWait" value="10000"/>
> <property name="testWhileIdle" value="true"/>
> <property name="validationQuery" value="select 1 from dual"/>
> <property name="testOnReturn" value="true"/>
> <property name="timeBetweenEvictionRunsMillis" value="10000"/>
> </bean>
>
> How can I prevent it from generating continuously?
>
> I have a feeling of doubt about my maxWait and
> timeBetweenEvictionRunsMillis.
> Surely I guess, if I change the maxWait value more sufficiently, it would be
> generated much less than the present, which is just a temporary resolution.
> I'm not certain whether timeBetweenEvictionRunsMillis has influence on this
> issue.
The timeBetweeenEvictionRunsMillis should not have any impact on the
issue, other than making it too short can reduce efficiency of the
pool. Unless idle connections are going bad, you can remove this
parameter altogether. All it is doing is triggering examination of
some (3) of the idle connections in the pool each time it runs.
What is going on is what the exception message indicates: you are
running out of available connections. When this exception occurs,
all 30 available connections are checked out and a thread has waited
more than 10 seconds for one of them to be returned. If there
really are only 30 app server threads active at a given time, this
may indicate that that on some control paths your code is not
closing connections. You should review your code to make sure that
there are no control paths that result in connections being
abandoned (i.e. borrowed using getConnection, but not returned via
Connection.close()). You can also set removeAbandoned=true and
configure an abandonedTimeOut setting to have the pool automatically
reclaim connections that appear to be abandoned. It is better,
however, to fix your code if this is what is happening.
Phil
>
> And I have to put a lot of effort as it has already run for a long time, not
> easy to change something.
>
> I appreciate somebody make a helpful advice for that issue.
>
> regards,
> sic
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] getConnection Timeout
Posted by Cyrille <cy...@free.fr>.
Hi Sic,
this can happen if your queries takes longer to execute.
General thoughts: It can show you that your db is getting bigger, or
your indexes are not up to date.
I think you are right with both of your feeling.
1. You should recycle your connections less frequently, i.e increase the
<property name="timeBetweenEvictionRunsMillis" value="10000"/>
In previous dbcp implementations, when evicting the connections, the
pool had to synchronise its access to the list of connections, so it was
preventing connections from being borrowed by the rest of the application.
I am not sure this is still true, but I guess so.
Trying to evict every 10 s seems quite agressive for me.
2. I would use testOnBorrow instead of testOnReturn, which will test if
connections are valid before using them.
If not, the pool will borrow another one.
I find this safer than using testOnReturn.
3. You can wait longer before having a timeout when no connection is
available by increasing the maxWait.
Note: waiting 10 s for a connection to be available seems long enough.
I would start with evicting less frequently.
I used this connection pool, configuring the testOnBorrow and not using
the evictor, ie removed those lines:
<property name="testWhileIdle" value="true"/>
<property name="timeBetweenEvictionRunsMillis" value="10000"/>
Regards,
Cyrille.
Le 03/09/10 04:20, sic a écrit :
> I'm using dbcp on the web application server whose version is
> commons-dbcp-1.3, commons-pool-1.5.4 on jdk1.5.
>
> It can be used to execute some DML statements when receiving about 30 ~ 150
> number of data(data is less than 3 KB) peridically per minute
>
> Expected to do these simple works with no difficulties, it generated some
> errors occasionally(50 ~ 150 per day).
>
> org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool
> error Timeout waiting for idle object
> at
> org.apache.commons.dbcp.PoolDataSource.getConnection(PoolingDataSource.java:114)
> at
> org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
> ...
>
> The web application server could use 30 threads at most, using oracle DB and
> the configurations are following:
>
> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
> destroy-method="close">
> <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
> <property name="url" value="..." />
> <property name="username" value="..." />
> <property name="password" value="..." />
> <property name="initialSize" value="5"/>
> <property name="maxActive" value="30"/>
> <property name="maxIdle" value="30"/>
> <property name="minIdle" value="5"/>
> <property name="maxWait" value="10000"/>
> <property name="testWhileIdle" value="true"/>
> <property name="validationQuery" value="select 1 from dual"/>
> <property name="testOnReturn" value="true"/>
> <property name="timeBetweenEvictionRunsMillis" value="10000"/>
> </bean>
>
> How can I prevent it from generating continuously?
>
> I have a feeling of doubt about my maxWait and
> timeBetweenEvictionRunsMillis.
> Surely I guess, if I change the maxWait value more sufficiently, it would be
> generated much less than the present, which is just a temporary resolution.
> I'm not certain whether timeBetweenEvictionRunsMillis has influence on this
> issue.
>
> And I have to put a lot of effort as it has already run for a long time, not
> easy to change something.
>
> I appreciate somebody make a helpful advice for that issue.
>
> regards,
> sic
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org