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/03/08 11:31:07 UTC
Oracle session excess over maxActive
I'm using commons-pool-1.3, commons-dbcp-1.2.2
Our business is quite simple. Using quartz, 30 simpleTriggers(5sec
repeatInterval) are triggered by 10 thread on quartz threadPool.
When any simpleTrigger is fired, it executes a job of doing DB(oracle)
query(select several rows and then update for them).
Configuration is as below :
<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
<property name="quartzProperties">
<props>
<prop key="org.quartz.threadPool.threadCount">10</prop>
</props>
</property>
<property name="triggers">
<list>
... 30 simple triggers ...
</list>
</property>
</bean>
<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="defaultAutoCommit" value="false"/>
<property name="initialSize" value="10"/>
<property name="maxActive" value="15"/>
<property name="maxIdle" value="10"/>
<property name="minIdle" value="10"/>
<property name="maxWait" value="60000"/>
<property name="testWhileIdle" value="false"/>
<property name="validationQuery" value="select 1 from dual"/>
<property name="testOnReturn" value="true"/>
<property name="timeBetweenEvictionRunsMillis" value="10000"/>
<property name="removeAbandoned" value="true"/>
<property name="removeAbandonedTimeout" value="10"/>
<property name="logAbandoned" value="true"/>
</bean>
by this configuration, 10 threads are executed by using 10~15 connections
after firing at 30 triggers.
On rare occasion, however, DB sessions(confusing connection and session) are
not closed normally and the result is that they occupy a tremendous amount
of sessions.
As a result of inquiring of DB "select * from v$session", it presents the
maximum number of session(more than 1000) allowed to this user and remains
INACTIVE status. And this status cannot be resolved until we shutdown this
process.
What causes this abnormal states? We certainly expected that DB pool can
limit the number of Active session by the "maxActive" property no matter
what may happen, but it seems not to work occasionally.
Any advice about this matter..
regards,
sic
--
View this message in context: http://n4.nabble.com/Oracle-session-excess-over-maxActive-tp1584311p1584311.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] Re: Oracle session excess over maxActive
Posted by James Carman <ja...@carmanconsulting.com>.
I would read this:
http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html
That should give you a good idea of what it provides. Spring is
popular for a reason. Enjoy.
On Thu, Mar 11, 2010 at 8:40 PM, sic <si...@naver.com> wrote:
>
> I didn't know spring supports to manage the connections, etc by JdbcTemplate.
>
> I'll contemplate using JdbcTemplate after knowing how to use it and then
> considering whether it is suitable for my case.
> --
> View this message in context: http://n4.nabble.com/Oracle-session-excess-over-maxActive-tp1584311p1590016.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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [dbcp] Re: Oracle session excess over maxActive
Posted by sic <si...@naver.com>.
I didn't know spring supports to manage the connections, etc by JdbcTemplate.
I'll contemplate using JdbcTemplate after knowing how to use it and then
considering whether it is suitable for my case.
--
View this message in context: http://n4.nabble.com/Oracle-session-excess-over-maxActive-tp1584311p1590016.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] Re: Oracle session excess over maxActive
Posted by James Carman <ja...@carmanconsulting.com>.
I was thinking more along the lines of using JdbcTemplate, which means
you don't manage the connections (or statements or result sets)
yourself. You let Spring do that.
On Thu, Mar 11, 2010 at 1:33 PM, <cy...@free.fr> wrote:
> looks good.
> Don't forget the rollback in the catch and it should work.
>
> Cyrille
>
> ----- Mail Original -----
> De: "sic" <si...@naver.com>
> À: user@commons.apache.org
> Envoyé: Jeudi 11 Mars 2010 04h56:47 GMT +01:00 Amsterdam / Berlin / Berne / Rome / Stockholm / Vienne
> Objet: Re: [dbcp] Re: Oracle session excess over maxActive
>
>
> I just manage connections by raw code.
>
> simple pseudo-code is that :
>
> private DataSource ds;
>
> public void test() {
> Connection conn = null;
> PreparedStatement pstmt = null;
> ResultSet rs = null;
> try {
> conn = ds.getConnection();
> pstmt = conn.prepareStatement(sql);
> rs = pstmt.executeQuery();
> ...
> conn.commit();
> } catch (Exception e) {
> ...
> } finally {
> if (rs != null) { try { rs.close(); } catch (SQLException e) { } }
> if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { } }
> if (conn != null) { try { conn.close(); } catch (SQLException e) { } }
> }
> }
>
> if using Spring's JDBC stuff as you mentioned, pseudo is following in my
> thought :
>
> try {
> conn = DataSourceUtils.getConnection(ds);
> pstmt = conn.prepareStatement(sql);
> rs = pstmt.executeQuery();
> ...
> conn.commit();
> } catch (Exception e) {
> ...
> } finally {
> JdbcUtils.closeResultSet(rs);
> JdbcUtils.closeStatement(pstmt);
> DataSourceUtils.releaseConnection(connection, ds);
> }
>
> If understanding ur advice correctly, I'll use this and observe the
> situation whereafter.
> --
> View this message in context: http://n4.nabble.com/Oracle-session-excess-over-maxActive-tp1584311p1588407.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
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [dbcp] Re: Oracle session excess over maxActive
Posted by cy...@free.fr.
looks good.
Don't forget the rollback in the catch and it should work.
Cyrille
----- Mail Original -----
De: "sic" <si...@naver.com>
À: user@commons.apache.org
Envoyé: Jeudi 11 Mars 2010 04h56:47 GMT +01:00 Amsterdam / Berlin / Berne / Rome / Stockholm / Vienne
Objet: Re: [dbcp] Re: Oracle session excess over maxActive
I just manage connections by raw code.
simple pseudo-code is that :
private DataSource ds;
public void test() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = ds.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
...
conn.commit();
} catch (Exception e) {
...
} finally {
if (rs != null) { try { rs.close(); } catch (SQLException e) { } }
if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { } }
if (conn != null) { try { conn.close(); } catch (SQLException e) { } }
}
}
if using Spring's JDBC stuff as you mentioned, pseudo is following in my
thought :
try {
conn = DataSourceUtils.getConnection(ds);
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
...
conn.commit();
} catch (Exception e) {
...
} finally {
JdbcUtils.closeResultSet(rs);
JdbcUtils.closeStatement(pstmt);
DataSourceUtils.releaseConnection(connection, ds);
}
If understanding ur advice correctly, I'll use this and observe the
situation whereafter.
--
View this message in context: http://n4.nabble.com/Oracle-session-excess-over-maxActive-tp1584311p1588407.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
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [dbcp] Re: Oracle session excess over maxActive
Posted by sic <si...@naver.com>.
I just manage connections by raw code.
simple pseudo-code is that :
private DataSource ds;
public void test() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = ds.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
...
conn.commit();
} catch (Exception e) {
...
} finally {
if (rs != null) { try { rs.close(); } catch (SQLException e) { } }
if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { } }
if (conn != null) { try { conn.close(); } catch (SQLException e) { } }
}
}
if using Spring's JDBC stuff as you mentioned, pseudo is following in my
thought :
try {
conn = DataSourceUtils.getConnection(ds);
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
...
conn.commit();
} catch (Exception e) {
...
} finally {
JdbcUtils.closeResultSet(rs);
JdbcUtils.closeStatement(pstmt);
DataSourceUtils.releaseConnection(connection, ds);
}
If understanding ur advice correctly, I'll use this and observe the
situation whereafter.
--
View this message in context: http://n4.nabble.com/Oracle-session-excess-over-maxActive-tp1584311p1588407.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] Re: Oracle session excess over maxActive
Posted by James Carman <ja...@carmanconsulting.com>.
Have you considered using Spring's JDBC support, or something like it?
What you need to do is write/use one piece of code that makes sure
you manage your connections, etc. correctly and use that wherever you
want to do JDBC "stuff."
On Wed, Mar 10, 2010 at 12:10 AM, sic <si...@naver.com> wrote:
>
> Yeah, I'll check whether I close PreparedStatements, Statements and ResultSet
> objects properly on my code.
> Then I think I've already use testOnBorrow without stating clearly <property
> name="testOnBorrow" value="true"/> since it's default value is true.
> If it occurs again, I'll monitor the number of opened connections by the
> netstat command, thanks.
>
> And I'm addressing the appropriate configuration in my case.
> I can understand that 5 connections(maxActive(=15) - maxIdle(same as
> minIdle=10)) are destroyed and created continuously.
> So I'm going to set "maxIdle" property to a negative value(or more than
> maxActive value) in order not to destroy connections.
>
> However dropping both of these(e.g. maxIdle = 5, minIdle = 2) as you
> remarked above, it's likely to force the pool to destroy excess connections
> and create them more extremely.
> I wonder what means "dropping both of these" and want to point out my
> misunderstanding.
>
> BTW, I'll observe the DB session states after upgrading to the latest
> version and eliminating the aggressive abandoned connection cleanup,
> checking my closing code.
>
> regards,
> sic
> --
> View this message in context: http://n4.nabble.com/Oracle-session-excess-over-maxActive-tp1584311p1586964.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
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [dbcp] Re: Oracle session excess over maxActive
Posted by sic <si...@naver.com>.
According to your advice, I just try to patch the previous contents.
But certain process cannot be configured completely for some reasons so it
is running with this configurations now.
This process is just using 1 thread, commons-dbcp-1.3, commons-pool-1.5.4
and jdk 1.5.
<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="1"/>
<property name="testWhileIdle" value="true"/>
<property name="validationQuery" value="select 1 from dual"/>
<property name="testOnReturn" value="true"/>
<property name="timeBetweenEvictionRunsMillis" value="10000"/>
<property name="defaultAutoCommit" value="false"/>
</bean>
And I'm using a raw code( e.g. conn = ds.getConnection())
In this situation, the following error message occurred.
java.sql.SQLException: Io exception: Connection reset
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334)
at
oracle.jdbc.ttc7.TTC7Protocol.handleIOException(TTC7Protocol.java:3678)
at oracle.jdbc.ttc7.TTC7Protocol.logon(TTC7Protocol.java:352)
at
oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:365)
at
oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:547)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:347)
at
org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at
org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at
org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1148)
at
org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
at
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at TargetSource ...
at java.lang.Thread.run(Thread.java:801)
I have already known that running the evictor every 10 seconds is
improper(will be removed) and using the Spring's JDBC stuff is more
efficient(will use such as conn = DataSourceUtils.getConnection(ds);).
Nonetheless I wonder it(modify the evictor conf and use spring jdbc) would
solve this problem.
regards,
sic
--
View this message in context: http://n4.nabble.com/DBCP-Oracle-session-excess-over-maxActive-tp1584311p1748785.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] Re: Oracle session excess over maxActive
Posted by Phil Steitz <ph...@gmail.com>.
sic wrote:
> Yeah, I'll check whether I close PreparedStatements, Statements and ResultSet
> objects properly on my code.
And connections.
> Then I think I've already use testOnBorrow without stating clearly <property
> name="testOnBorrow" value="true"/> since it's default value is true.
> If it occurs again, I'll monitor the number of opened connections by the
> netstat command, thanks.
>
> And I'm addressing the appropriate configuration in my case.
> I can understand that 5 connections(maxActive(=15) - maxIdle(same as
> minIdle=10)) are destroyed and created continuously.
> So I'm going to set "maxIdle" property to a negative value(or more than
> maxActive value) in order not to destroy connections.
Good idea.
>
> However dropping both of these(e.g. maxIdle = 5, minIdle = 2) as you
> remarked above, it's likely to force the pool to destroy excess connections
> and create them more extremely.
> I wonder what means "dropping both of these" and want to point out my
> misunderstanding.
Sorry, I should have been more clear. What I mean by "dropping"
them was removing them from your config, resulting in both
properties being set to default values, which are 8 for maxIdle (a
reasonable value in general, but in your use case, I would set to a
negative value for no limit as suggested above) and 0 for minIdle.
Phil
>
> BTW, I'll observe the DB session states after upgrading to the latest
> version and eliminating the aggressive abandoned connection cleanup,
> checking my closing code.
>
> regards,
> sic
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [dbcp] Re: Oracle session excess over maxActive
Posted by sic <si...@naver.com>.
Yeah, I'll check whether I close PreparedStatements, Statements and ResultSet
objects properly on my code.
Then I think I've already use testOnBorrow without stating clearly <property
name="testOnBorrow" value="true"/> since it's default value is true.
If it occurs again, I'll monitor the number of opened connections by the
netstat command, thanks.
And I'm addressing the appropriate configuration in my case.
I can understand that 5 connections(maxActive(=15) - maxIdle(same as
minIdle=10)) are destroyed and created continuously.
So I'm going to set "maxIdle" property to a negative value(or more than
maxActive value) in order not to destroy connections.
However dropping both of these(e.g. maxIdle = 5, minIdle = 2) as you
remarked above, it's likely to force the pool to destroy excess connections
and create them more extremely.
I wonder what means "dropping both of these" and want to point out my
misunderstanding.
BTW, I'll observe the DB session states after upgrading to the latest
version and eliminating the aggressive abandoned connection cleanup,
checking my closing code.
regards,
sic
--
View this message in context: http://n4.nabble.com/Oracle-session-excess-over-maxActive-tp1584311p1586964.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] Re: Oracle session excess over maxActive
Posted by cy...@free.fr.
Hi,
make sure you close also PreparedStatements, Statements and ResultSet objects.
Also, you might want to use testOnBorrow instead of testOnReturn
==> <property name="testOnReturn" value="true"/>
<property name="testOnBorrow" value="true"/>
You can monitor number of opened connections through sudo netstat -apn |grep <java_pid> command.
Regards,
Cyrille.
----- Mail Original -----
De: "Phil Steitz" <ph...@gmail.com>
À: "Commons Users List" <us...@commons.apache.org>
Envoyé: Mardi 9 Mars 2010 15h06:56 GMT +01:00 Amsterdam / Berlin / Berne / Rome / Stockholm / Vienne
Objet: [dbcp] Re: Oracle session excess over maxActive
sic wrote:
> I'm using commons-pool-1.3, commons-dbcp-1.2.2
>
> Our business is quite simple. Using quartz, 30 simpleTriggers(5sec
> repeatInterval) are triggered by 10 thread on quartz threadPool.
> When any simpleTrigger is fired, it executes a job of doing DB(oracle)
> query(select several rows and then update for them).
> Configuration is as below :
> <bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
> <property name="quartzProperties">
> <props>
> <prop key="org.quartz.threadPool.threadCount">10</prop>
> </props>
> </property>
> <property name="triggers">
> <list>
> ... 30 simple triggers ...
> </list>
> </property>
> </bean>
> <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="defaultAutoCommit" value="false"/>
> <property name="initialSize" value="10"/>
> <property name="maxActive" value="15"/>
> <property name="maxIdle" value="10"/>
> <property name="minIdle" value="10"/>
> <property name="maxWait" value="60000"/>
> <property name="testWhileIdle" value="false"/>
> <property name="validationQuery" value="select 1 from dual"/>
> <property name="testOnReturn" value="true"/>
> <property name="timeBetweenEvictionRunsMillis" value="10000"/>
> <property name="removeAbandoned" value="true"/>
> <property name="removeAbandonedTimeout" value="10"/>
> <property name="logAbandoned" value="true"/>
> </bean>
>
> by this configuration, 10 threads are executed by using 10~15 connections
> after firing at 30 triggers.
> On rare occasion, however, DB sessions(confusing connection and session) are
> not closed normally and the result is that they occupy a tremendous amount
> of sessions.
>
> As a result of inquiring of DB "select * from v$session", it presents the
> maximum number of session(more than 1000) allowed to this user and remains
> INACTIVE status. And this status cannot be resolved until we shutdown this
> process.
>
> What causes this abnormal states? We certainly expected that DB pool can
> limit the number of Active session by the "maxActive" property no matter
> what may happen, but it seems not to work occasionally.
>
> Any advice about this matter..
When posting to this list, please prefix the subject line with the
name of the component, in this case [dbcp]. The list is shared by
all commons components and it makes it easier for us to find and
respond to posts if they do this. Thanks!
Now to your question.
1) Your configuration will likely lead to a lot of connection churn.
Setting maxIdle = minIdle = 10 when maxActive = 15 will force the
pool to be constantly destroying excess connections and then adding
new ones. You would likely be better off dropping both of these or
at least setting maxIdle to a negative value (no limit). Your
removeAbandonedTimeout is also set to a very low value. If your
code is closing all connections (something you should check in any
case), you should not need to use abandoned connection cleanup and
in any case running the evictor every 10 seconds and timing out idle
connections every 10 seconds is extreme. Your code should close its
own connections and not require such aggressive cleanup. In pool
1.3, evictor runs lock the pool, so eliminating this if you can
clean up the client code will improve throughput.
2) The latest versions of dbcp and pool are dbcp 1.3 (JDK 1.4-1.5)
or dbcp 1.4 (JDK 1.6+) and pool 1.5.4. Upgrading to the latest
versions may resolve your problem. You should address 1) in any case.
Phil
>
> regards,
>
> sic
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
[dbcp] Re: Oracle session excess over maxActive
Posted by Phil Steitz <ph...@gmail.com>.
sic wrote:
> I'm using commons-pool-1.3, commons-dbcp-1.2.2
>
> Our business is quite simple. Using quartz, 30 simpleTriggers(5sec
> repeatInterval) are triggered by 10 thread on quartz threadPool.
> When any simpleTrigger is fired, it executes a job of doing DB(oracle)
> query(select several rows and then update for them).
> Configuration is as below :
> <bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
> <property name="quartzProperties">
> <props>
> <prop key="org.quartz.threadPool.threadCount">10</prop>
> </props>
> </property>
> <property name="triggers">
> <list>
> ... 30 simple triggers ...
> </list>
> </property>
> </bean>
> <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="defaultAutoCommit" value="false"/>
> <property name="initialSize" value="10"/>
> <property name="maxActive" value="15"/>
> <property name="maxIdle" value="10"/>
> <property name="minIdle" value="10"/>
> <property name="maxWait" value="60000"/>
> <property name="testWhileIdle" value="false"/>
> <property name="validationQuery" value="select 1 from dual"/>
> <property name="testOnReturn" value="true"/>
> <property name="timeBetweenEvictionRunsMillis" value="10000"/>
> <property name="removeAbandoned" value="true"/>
> <property name="removeAbandonedTimeout" value="10"/>
> <property name="logAbandoned" value="true"/>
> </bean>
>
> by this configuration, 10 threads are executed by using 10~15 connections
> after firing at 30 triggers.
> On rare occasion, however, DB sessions(confusing connection and session) are
> not closed normally and the result is that they occupy a tremendous amount
> of sessions.
>
> As a result of inquiring of DB "select * from v$session", it presents the
> maximum number of session(more than 1000) allowed to this user and remains
> INACTIVE status. And this status cannot be resolved until we shutdown this
> process.
>
> What causes this abnormal states? We certainly expected that DB pool can
> limit the number of Active session by the "maxActive" property no matter
> what may happen, but it seems not to work occasionally.
>
> Any advice about this matter..
When posting to this list, please prefix the subject line with the
name of the component, in this case [dbcp]. The list is shared by
all commons components and it makes it easier for us to find and
respond to posts if they do this. Thanks!
Now to your question.
1) Your configuration will likely lead to a lot of connection churn.
Setting maxIdle = minIdle = 10 when maxActive = 15 will force the
pool to be constantly destroying excess connections and then adding
new ones. You would likely be better off dropping both of these or
at least setting maxIdle to a negative value (no limit). Your
removeAbandonedTimeout is also set to a very low value. If your
code is closing all connections (something you should check in any
case), you should not need to use abandoned connection cleanup and
in any case running the evictor every 10 seconds and timing out idle
connections every 10 seconds is extreme. Your code should close its
own connections and not require such aggressive cleanup. In pool
1.3, evictor runs lock the pool, so eliminating this if you can
clean up the client code will improve throughput.
2) The latest versions of dbcp and pool are dbcp 1.3 (JDK 1.4-1.5)
or dbcp 1.4 (JDK 1.6+) and pool 1.5.4. Upgrading to the latest
versions may resolve your problem. You should address 1) in any case.
Phil
>
> regards,
>
> sic
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org