You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Vijay Anjur <vi...@gmail.com> on 2006/03/13 22:04:50 UTC
Tomcat: DBCP Connection Pooling issue
Hi,
I am having trouble with dbcp connection pooling with JTDS driver.
When I close the connection, pool never seem to shrink after the idle time
specified in server.xml has passed.
When I check using GetNumIdle() method, it always shows zero, even after the
connections are closed.Any Ideas?
Here is my server config & the code.
<Resource
name="jdbc/somedb"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
driverClassName="net.sourceforge.jtds.jdbc.Driver"
validationQuery="Select 1"
maxWait="6000"
username="username"
password="something"
testOnBorrow="true"
testOnReturn="false"
testWhileIdle="true"
url="jdbc:jtds:sqlserver://server:16778"
initialSize="8"
numTestsPerEvictionRun="6"
timeBetweenEvictionRunsMillis="10000"
minEvictableIdleTimeMillis="100000"
removeAbandoned="true"
removeAbandonedTimeout="300"
logAbandoned="true"
maxActive="20"
maxIdle="5"
minIdle="0"/>
Code snippet:
private InitialContext ic = null;
private Context envCtx = null;
private static DataSource dsINTLDB;
private static GenericObjectPool poolINTLDB;
private static PoolingDataSource pdsINTLDB;
private static BasicDataSource bdsINTLDB;
ic = new InitialContext();
envCtx = (Context) ic.lookup("java:comp/env");
dsINTLDB = (DataSource) envCtx.lookup("ds/somedb");
if (dsINTLDB != null)
{
bdsINTLDB = (BasicDataSource)dsINTLDB;
ConnectionFactory cf1 = new DataSourceConnectionFactory(dsINTLDB);
poolINTLDB = new GenericObjectPool(null,
bdsINTLDB.getMaxActive(),
(byte)1,
bdsINTLDB.getMaxWait(),
bdsINTLDB.getMaxIdle(),
bdsINTLDB.getMinIdle(),
bdsINTLDB.getTestOnBorrow(),
bdsINTLDB.getTestOnReturn(),
bdsINTLDB.getTimeBetweenEvictionRunsMillis(),
bdsINTLDB.getNumTestsPerEvictionRun(),
bdsINTLDB.getMinEvictableIdleTimeMillis(),
bdsINTLDB.getTestWhileIdle()
);
PoolableConnectionFactory pcf1 = new PoolableConnectionFactory(cf1,
poolINTLDB, null, null, false, true);
pcf1.setValidationQuery(bdsINTLDB.getValidationQuery());
pdsINTLDB = new PoolingDataSource(poolINTLDB);
}
Connection c = pdsINTLDB.getConnection();
//use connection.
c.close();
===============================================================================
Re: Tomcat: DBCP Connection Pooling issue
Posted by Vijay Anjur <vi...@gmail.com>.
When I did what you mentioned, the other parameters such as 'testOnBorrow',
'ValidationQuery' etc, were not set to the pool.
I just casted the DataSource to BasicDataSource so that I can ensure those
properties are set to the pool.
For example, testOnBorrow property was always false when I did what you
mentioned. Somehow it was defaulting to GenericObjectPool's default value…
On 3/13/06, Sandy McArthur <sa...@apache.org> wrote:
>
> Vijay,
>
> I don't get what you are trying to do.
>
> Why are you getting a DataSource from JNDI and then using the settings
> from it to create an ObjectPool? Shouldn't it be sufficient to simply
> do:
>
> Connection c = dsINTLDB.getConnection();
> // use connection.
> c.close();
>
> On 3/13/06, Vijay Anjur <vi...@gmail.com> wrote:
> > I am having trouble with dbcp connection pooling with JTDS driver.
> >
> > When I close the connection, pool never seem to shrink after the idle
> time
> > specified in server.xml has passed.
> > When I check using GetNumIdle() method, it always shows zero, even after
> the
> > connections are closed.Any Ideas?
> >
> > Here is my server config & the code.
> >
> > <Resource
> >
> > name="jdbc/somedb"
> >
> > auth="Container"
> >
> > type="javax.sql.DataSource"
> >
> > factory="org.apache.commons.dbcp.BasicDataSourceFactory"
> >
> > driverClassName="net.sourceforge.jtds.jdbc.Driver"
> >
> > validationQuery="Select 1"
> >
> > maxWait="6000"
> >
> > username="username"
> >
> > password="something"
> >
> > testOnBorrow="true"
> >
> > testOnReturn="false"
> >
> > testWhileIdle="true"
> >
> > url="jdbc:jtds:sqlserver://server:16778"
> >
> > initialSize="8"
> >
> > numTestsPerEvictionRun="6"
> >
> > timeBetweenEvictionRunsMillis="10000"
> >
> > minEvictableIdleTimeMillis="100000"
> >
> > removeAbandoned="true"
> >
> > removeAbandonedTimeout="300"
> >
> > logAbandoned="true"
> >
> > maxActive="20"
> >
> > maxIdle="5"
> >
> > minIdle="0"/>
> >
> >
> >
> > Code snippet:
> >
> > private InitialContext ic = null;
> >
> > private Context envCtx = null;
> >
> > private static DataSource dsINTLDB;
> >
> > private static GenericObjectPool poolINTLDB;
> >
> > private static PoolingDataSource pdsINTLDB;
> >
> > private static BasicDataSource bdsINTLDB;
> >
> >
> >
> > ic = new InitialContext();
> >
> > envCtx = (Context) ic.lookup("java:comp/env");
> >
> > dsINTLDB = (DataSource) envCtx.lookup("ds/somedb");
> >
> >
> >
> > if (dsINTLDB != null)
> >
> > {
> >
> > bdsINTLDB = (BasicDataSource)dsINTLDB;
> >
> > ConnectionFactory cf1 = new DataSourceConnectionFactory(dsINTLDB);
> >
> > poolINTLDB = new GenericObjectPool(null,
> >
> > bdsINTLDB.getMaxActive(),
> >
> > (byte)1,
> >
> > bdsINTLDB.getMaxWait(),
> >
> > bdsINTLDB.getMaxIdle(),
> >
> > bdsINTLDB.getMinIdle(),
> >
> > bdsINTLDB.getTestOnBorrow
> (),
> >
> > bdsINTLDB.getTestOnReturn
> (),
> >
> >
> > bdsINTLDB.getTimeBetweenEvictionRunsMillis(),
> >
> >
> > bdsINTLDB.getNumTestsPerEvictionRun(),
> >
> >
> > bdsINTLDB.getMinEvictableIdleTimeMillis(),
> >
> >
> bdsINTLDB.getTestWhileIdle()
> > );
> >
> >
> >
> > PoolableConnectionFactory pcf1 = new PoolableConnectionFactory(cf1,
> > poolINTLDB, null, null, false, true);
> >
> > pcf1.setValidationQuery(bdsINTLDB.getValidationQuery());
> >
> > pdsINTLDB = new PoolingDataSource(poolINTLDB);
> >
> > }
> >
> >
> >
> > Connection c = pdsINTLDB.getConnection();
> >
> > //use connection.
> >
> >
> >
> > c.close();
>
>
> --
> Sandy McArthur
>
> "He who dares not offend cannot be honest."
> - Thomas Paine
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
Re: Tomcat: DBCP Connection Pooling issue
Posted by Sandy McArthur <sa...@apache.org>.
On 3/15/06, Vijay Anjur <vi...@gmail.com> wrote:
> When I did what you mentioned, the other parameters such as 'testOnBorrow',
> 'ValidationQuery' etc, were not set to the pool.
>
> I just casted the DataSource to BasicDataSource so that I can ensure those
> properties are set to the pool.
>
> For example, testOnBorrow property was always false when I did what you
> mentioned. Somehow it was defaulting to GenericObjectPool's default value…
Yea, I agree this is a problem. It stems from Commons Pool not being
as well thought out as it should have been and the various included
implementations behaving inconsistently. I'm working hard to fix this
in Pool 2.0 . If you're willing to build Pool from source in the SVN
trunk at https://svn.apache.org/repos/asf/jakarta/commons/proper/pool/trunk/
I think it will fix your problem as the default behavior of
GenericObjectPool is more sane. Be aware that Pool 2.0 won't be 100%
backwards compatible. I tried to keep is as compatible as possible but
things that expect "broken" behavior may break when presented with
"correct" behavior.
> On 3/13/06, Sandy McArthur <sa...@apache.org> wrote:
> >
> > Vijay,
> >
> > I don't get what you are trying to do.
> >
> > Why are you getting a DataSource from JNDI and then using the settings
> > from it to create an ObjectPool? Shouldn't it be sufficient to simply
> > do:
> >
> > Connection c = dsINTLDB.getConnection();
> > // use connection.
> > c.close();
> >
> > On 3/13/06, Vijay Anjur <vi...@gmail.com> wrote:
> > > I am having trouble with dbcp connection pooling with JTDS driver.
> > >
> > > When I close the connection, pool never seem to shrink after the idle
> > time
> > > specified in server.xml has passed.
> > > When I check using GetNumIdle() method, it always shows zero, even after
> > the
> > > connections are closed.Any Ideas?
> > >
> > > Here is my server config & the code.
> > >
> > > <Resource
> > >
> > > name="jdbc/somedb"
> > >
> > > auth="Container"
> > >
> > > type="javax.sql.DataSource"
> > >
> > > factory="org.apache.commons.dbcp.BasicDataSourceFactory"
> > >
> > > driverClassName="net.sourceforge.jtds.jdbc.Driver"
> > >
> > > validationQuery="Select 1"
> > >
> > > maxWait="6000"
> > >
> > > username="username"
> > >
> > > password="something"
> > >
> > > testOnBorrow="true"
> > >
> > > testOnReturn="false"
> > >
> > > testWhileIdle="true"
> > >
> > > url="jdbc:jtds:sqlserver://server:16778"
> > >
> > > initialSize="8"
> > >
> > > numTestsPerEvictionRun="6"
> > >
> > > timeBetweenEvictionRunsMillis="10000"
> > >
> > > minEvictableIdleTimeMillis="100000"
> > >
> > > removeAbandoned="true"
> > >
> > > removeAbandonedTimeout="300"
> > >
> > > logAbandoned="true"
> > >
> > > maxActive="20"
> > >
> > > maxIdle="5"
> > >
> > > minIdle="0"/>
> > >
> > >
> > >
> > > Code snippet:
> > >
> > > private InitialContext ic = null;
> > >
> > > private Context envCtx = null;
> > >
> > > private static DataSource dsINTLDB;
> > >
> > > private static GenericObjectPool poolINTLDB;
> > >
> > > private static PoolingDataSource pdsINTLDB;
> > >
> > > private static BasicDataSource bdsINTLDB;
> > >
> > >
> > >
> > > ic = new InitialContext();
> > >
> > > envCtx = (Context) ic.lookup("java:comp/env");
> > >
> > > dsINTLDB = (DataSource) envCtx.lookup("ds/somedb");
> > >
> > >
> > >
> > > if (dsINTLDB != null)
> > >
> > > {
> > >
> > > bdsINTLDB = (BasicDataSource)dsINTLDB;
> > >
> > > ConnectionFactory cf1 = new DataSourceConnectionFactory(dsINTLDB);
> > >
> > > poolINTLDB = new GenericObjectPool(null,
> > >
> > > bdsINTLDB.getMaxActive(),
> > >
> > > (byte)1,
> > >
> > > bdsINTLDB.getMaxWait(),
> > >
> > > bdsINTLDB.getMaxIdle(),
> > >
> > > bdsINTLDB.getMinIdle(),
> > >
> > > bdsINTLDB.getTestOnBorrow
> > (),
> > >
> > > bdsINTLDB.getTestOnReturn
> > (),
> > >
> > >
> > > bdsINTLDB.getTimeBetweenEvictionRunsMillis(),
> > >
> > >
> > > bdsINTLDB.getNumTestsPerEvictionRun(),
> > >
> > >
> > > bdsINTLDB.getMinEvictableIdleTimeMillis(),
> > >
> > >
> > bdsINTLDB.getTestWhileIdle()
> > > );
> > >
> > >
> > >
> > > PoolableConnectionFactory pcf1 = new PoolableConnectionFactory(cf1,
> > > poolINTLDB, null, null, false, true);
> > >
> > > pcf1.setValidationQuery(bdsINTLDB.getValidationQuery());
> > >
> > > pdsINTLDB = new PoolingDataSource(poolINTLDB);
> > >
> > > }
> > >
> > >
> > >
> > > Connection c = pdsINTLDB.getConnection();
> > >
> > > //use connection.
> > >
> > >
> > >
> > > c.close();
> >
> >
> > --
> > Sandy McArthur
> >
> > "He who dares not offend cannot be honest."
> > - Thomas Paine
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: commons-user-help@jakarta.apache.org
> >
> >
>
>
--
Sandy McArthur
"He who dares not offend cannot be honest."
- Thomas Paine
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: Tomcat: DBCP Connection Pooling issue
Posted by Vijay Anjur <vi...@gmail.com>.
When I did what you mentioned, the other parameters such as 'testOnBorrow',
'ValidationQuery' etc, were not set to the pool.
I just casted the DataSource to BasicDataSource so that I can ensure those
properties are set to the pool.
For example, testOnBorrow property was always false when I did what you
mentioned. Somehow it was defaulting to GenericObjectPool's default value…
On 3/13/06, Sandy McArthur <sa...@apache.org> wrote:
>
> Vijay,
>
> I don't get what you are trying to do.
>
> Why are you getting a DataSource from JNDI and then using the settings
> from it to create an ObjectPool? Shouldn't it be sufficient to simply
> do:
>
> Connection c = dsINTLDB.getConnection();
> // use connection.
> c.close();
>
> On 3/13/06, Vijay Anjur <vi...@gmail.com> wrote:
> > I am having trouble with dbcp connection pooling with JTDS driver.
> >
> > When I close the connection, pool never seem to shrink after the idle
> time
> > specified in server.xml has passed.
> > When I check using GetNumIdle() method, it always shows zero, even after
> the
> > connections are closed.Any Ideas?
> >
> > Here is my server config & the code.
> >
> > <Resource
> >
> > name="jdbc/somedb"
> >
> > auth="Container"
> >
> > type="javax.sql.DataSource"
> >
> > factory="org.apache.commons.dbcp.BasicDataSourceFactory"
> >
> > driverClassName="net.sourceforge.jtds.jdbc.Driver"
> >
> > validationQuery="Select 1"
> >
> > maxWait="6000"
> >
> > username="username"
> >
> > password="something"
> >
> > testOnBorrow="true"
> >
> > testOnReturn="false"
> >
> > testWhileIdle="true"
> >
> > url="jdbc:jtds:sqlserver://server:16778"
> >
> > initialSize="8"
> >
> > numTestsPerEvictionRun="6"
> >
> > timeBetweenEvictionRunsMillis="10000"
> >
> > minEvictableIdleTimeMillis="100000"
> >
> > removeAbandoned="true"
> >
> > removeAbandonedTimeout="300"
> >
> > logAbandoned="true"
> >
> > maxActive="20"
> >
> > maxIdle="5"
> >
> > minIdle="0"/>
> >
> >
> >
> > Code snippet:
> >
> > private InitialContext ic = null;
> >
> > private Context envCtx = null;
> >
> > private static DataSource dsINTLDB;
> >
> > private static GenericObjectPool poolINTLDB;
> >
> > private static PoolingDataSource pdsINTLDB;
> >
> > private static BasicDataSource bdsINTLDB;
> >
> >
> >
> > ic = new InitialContext();
> >
> > envCtx = (Context) ic.lookup("java:comp/env");
> >
> > dsINTLDB = (DataSource) envCtx.lookup("ds/somedb");
> >
> >
> >
> > if (dsINTLDB != null)
> >
> > {
> >
> > bdsINTLDB = (BasicDataSource)dsINTLDB;
> >
> > ConnectionFactory cf1 = new DataSourceConnectionFactory(dsINTLDB);
> >
> > poolINTLDB = new GenericObjectPool(null,
> >
> > bdsINTLDB.getMaxActive(),
> >
> > (byte)1,
> >
> > bdsINTLDB.getMaxWait(),
> >
> > bdsINTLDB.getMaxIdle(),
> >
> > bdsINTLDB.getMinIdle(),
> >
> > bdsINTLDB.getTestOnBorrow
> (),
> >
> > bdsINTLDB.getTestOnReturn
> (),
> >
> >
> > bdsINTLDB.getTimeBetweenEvictionRunsMillis(),
> >
> >
> > bdsINTLDB.getNumTestsPerEvictionRun(),
> >
> >
> > bdsINTLDB.getMinEvictableIdleTimeMillis(),
> >
> >
> bdsINTLDB.getTestWhileIdle()
> > );
> >
> >
> >
> > PoolableConnectionFactory pcf1 = new PoolableConnectionFactory(cf1,
> > poolINTLDB, null, null, false, true);
> >
> > pcf1.setValidationQuery(bdsINTLDB.getValidationQuery());
> >
> > pdsINTLDB = new PoolingDataSource(poolINTLDB);
> >
> > }
> >
> >
> >
> > Connection c = pdsINTLDB.getConnection();
> >
> > //use connection.
> >
> >
> >
> > c.close();
>
>
> --
> Sandy McArthur
>
> "He who dares not offend cannot be honest."
> - Thomas Paine
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
Re: Tomcat: DBCP Connection Pooling issue
Posted by Sandy McArthur <sa...@apache.org>.
Vijay,
I don't get what you are trying to do.
Why are you getting a DataSource from JNDI and then using the settings
from it to create an ObjectPool? Shouldn't it be sufficient to simply
do:
Connection c = dsINTLDB.getConnection();
// use connection.
c.close();
On 3/13/06, Vijay Anjur <vi...@gmail.com> wrote:
> I am having trouble with dbcp connection pooling with JTDS driver.
>
> When I close the connection, pool never seem to shrink after the idle time
> specified in server.xml has passed.
> When I check using GetNumIdle() method, it always shows zero, even after the
> connections are closed.Any Ideas?
>
> Here is my server config & the code.
>
> <Resource
>
> name="jdbc/somedb"
>
> auth="Container"
>
> type="javax.sql.DataSource"
>
> factory="org.apache.commons.dbcp.BasicDataSourceFactory"
>
> driverClassName="net.sourceforge.jtds.jdbc.Driver"
>
> validationQuery="Select 1"
>
> maxWait="6000"
>
> username="username"
>
> password="something"
>
> testOnBorrow="true"
>
> testOnReturn="false"
>
> testWhileIdle="true"
>
> url="jdbc:jtds:sqlserver://server:16778"
>
> initialSize="8"
>
> numTestsPerEvictionRun="6"
>
> timeBetweenEvictionRunsMillis="10000"
>
> minEvictableIdleTimeMillis="100000"
>
> removeAbandoned="true"
>
> removeAbandonedTimeout="300"
>
> logAbandoned="true"
>
> maxActive="20"
>
> maxIdle="5"
>
> minIdle="0"/>
>
>
>
> Code snippet:
>
> private InitialContext ic = null;
>
> private Context envCtx = null;
>
> private static DataSource dsINTLDB;
>
> private static GenericObjectPool poolINTLDB;
>
> private static PoolingDataSource pdsINTLDB;
>
> private static BasicDataSource bdsINTLDB;
>
>
>
> ic = new InitialContext();
>
> envCtx = (Context) ic.lookup("java:comp/env");
>
> dsINTLDB = (DataSource) envCtx.lookup("ds/somedb");
>
>
>
> if (dsINTLDB != null)
>
> {
>
> bdsINTLDB = (BasicDataSource)dsINTLDB;
>
> ConnectionFactory cf1 = new DataSourceConnectionFactory(dsINTLDB);
>
> poolINTLDB = new GenericObjectPool(null,
>
> bdsINTLDB.getMaxActive(),
>
> (byte)1,
>
> bdsINTLDB.getMaxWait(),
>
> bdsINTLDB.getMaxIdle(),
>
> bdsINTLDB.getMinIdle(),
>
> bdsINTLDB.getTestOnBorrow(),
>
> bdsINTLDB.getTestOnReturn(),
>
>
> bdsINTLDB.getTimeBetweenEvictionRunsMillis(),
>
>
> bdsINTLDB.getNumTestsPerEvictionRun(),
>
>
> bdsINTLDB.getMinEvictableIdleTimeMillis(),
>
> bdsINTLDB.getTestWhileIdle()
> );
>
>
>
> PoolableConnectionFactory pcf1 = new PoolableConnectionFactory(cf1,
> poolINTLDB, null, null, false, true);
>
> pcf1.setValidationQuery(bdsINTLDB.getValidationQuery());
>
> pdsINTLDB = new PoolingDataSource(poolINTLDB);
>
> }
>
>
>
> Connection c = pdsINTLDB.getConnection();
>
> //use connection.
>
>
>
> c.close();
--
Sandy McArthur
"He who dares not offend cannot be honest."
- Thomas Paine
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org