You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Shaan <sh...@em-t.com> on 2004/10/10 10:34:57 UTC
[dbcp] removing idle connections.
Hi,
My problem is not new. But i need your suggestion.
I have java based application which is connecting to oracle database.
There is firewall in between Application and Database. Physical distance
between 2 servers also considerable more. So it takes around 3-4 seconds to
initiate any connection. I was using Oracle thin driver before. 3-4 seconds
time gap forced me to use Oracle's connection caching. which worked well but
that does not have any mechanism to remove connections from pool which are
closed by firewall after some certain idle timeout. This firewall behavior
is now creating problem for my application. Oracle driver has solution for
this but that works only with Oracle 10g. And i am using Oracle 9i. I cant
change it to 10g.
Well then i found DBCP. I made it working on my test samples but i have few
queries as I am new to it.
1) What settings i have to bring in for DBCP to remove these types of
connections?
2) Can i set some time interval for DBCP to remove it automatically from
pool before firewall close them?
3) Can DBCP remove broken connections by itself? As connections can be
broken by any reason by any party at any time.
I gone through parameters of DBCP but i became confused, which one is
useful in my case and how to use them in following my sample code (copied
from ManualPoolingDriverExample :p )?
thanks
regards,
Shahnaz Ali.
======================== Sample Code ========================
public class ManualPoolingDriverExample
{
public static void main(String[] args) throws Exception
{
System.out.println("Loading underlying JDBC driver.");
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
System.out.println("Setting up driver.");
try
{
setupDriver( "jdbc:oracle:thin:ET002/ET002@10.0.0
6:1521:EngApps" );
}
catch (Exception e)
{
e.printStackTrace();
}
for (int j=1; j<=10 ; j++)
{
System.out.println ( j + "
===================================================== " ) ;
for (int i=1; i<=10 ; i++)
{
new NeedData().start() ;
}
Thread.sleep ( 15000) ;
}
System.out.println ( " over " ) ;
try
{
shutdownDriver();
}
catch (Exception e)
{
e.printStackTrace();
}
}
public static void setupDriver(String connectURI) throws Exception
{
ObjectPool connectionPool = new GenericObjectPool(null);
ConnectionFactory connectionFactory = new
DriverManagerConnectionFactory(connectURI,null);
PoolableConnectionFactory poolableConnectionFactory = new
PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false
true);
Class.forName("org.apache.commons.dbcp.PoolingDriver");
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver(
jdbc:apache:commons:dbcp:");
driver.registerPool("example",connectionPool);
}
public static void printDriverStats() throws Exception
{
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver(
jdbc:apache:commons:dbcp:");
ObjectPool connectionPool = driver.getConnectionPool("example");
System.out.println("NumActive: " + connectionPool.getNumActive());
System.out.println("NumIdle: " + connectionPool.getNumIdle());
}
public static void shutdownDriver() throws Exception
{
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver(
jdbc:apache:commons:dbcp:");
driver.closePool("example");
}
}
class NeedData extends Thread
{
public NeedData()
{
}
public void run ()
{
try
{
getTableNameList( ) ;
}
catch (Exception e)
{
e.printStackTrace() ;
}
}
public ArrayList getTableNameList( ) throws Exception
{
ArrayList prodList = new ArrayList();
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try
{
// Get Connection from the Cache
java.util.Date d1 = new java.util.Date () ;
conn = DriverManager.getConnection(
jdbc:apache:commons:dbcp:example");
stmt = conn.createStatement();
rset = stmt.executeQuery(" SELECT * from tab ");
java.util.Date d2 = new java.util.Date () ;
System.out.println ( "Time taken :" + (d2.getTime() - d1.getTime())
);
}
catch (SQLException ex)
{
throw new Exception("SQL Error : \n" + ex.toString());
}
finally
{
try
{
rset.close();
stmt.close();
conn.close();
}
catch (Exception ex)
{
throw new Exception("SQL Error while closing objects = " +
ex.toString());
}
}
return prodList;
}
}
============================================================
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
[dbcp] BasicDataSource Hangs
Posted by MC Sekhar <mc...@medicomsoft.com>.
Hi,
We have a product in healthcare completely written using J2EE. We are
currently running the product on Oracle 9iAS and Oracle 8.1.7.4 database
enterprise edition. We are using the Thin driver to connect to the database.
Due to teh problems with Oracle's connection cache implementations, wer
switched to DBCP for the connection pooling. At our development labs, we
donot have any issues and the pooling works fine. But when we put DBCP in
our production sites we are having a major problem, even with a concurrent
user load of 20 to 50(we are unable to simulate this in our development
labs).
From the feedback we have got from the customer, when there is a resource
intensive query, taking up much of the resources on the database server,
DBCP refuses any new connection requests. This has been reported even when
there are about 10 Db connections in the pool. But they are able to connect
to the database using the SQLPlus Client. The following is the datasource
definition,
<data-source
name = "BasicDataSource"
class = "org.apache.commons.dbcp.BasicDataSource"
connection-driver = "oracle.jdbc.driver.OracleDriver"
url = "jdbc:oracle:thin:@<machine_ip>:<port_no>:<sid>"
connection-retry-interval = "1"
location = "jdbc/OracleDS"
username="<user_id>"
password="<password>"
>
<property name="username" value="<user_id>" />
<property name="password" value="<password>" />
<property name="url"
value="jdbc:oracle:thin:@<machine_ip>:<port_no>:<sid>" />
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"
/>
<property name="defaultAutoCommit" value="true" />
<property name="initialSize" value="10" />
<property name="maxActive" value="0" />
<property name="maxIdle" value="0" />
<property name="minIdle" value="0" />
<property name="maxWait" value="60" />
<property name="validationQuery" value="Select 1 from dual" />
<property name="testOnBorrow" value="true" />
<property name="testOnReturn" value="true" />
<property name="testWhileIdle" value="true" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<property name="numTestsPerEvictionRun" value="5" />
<property name="minEvictableIdleTimeMillis" value="600000" />
<property name="poolPreparedStatements" value="false" />
<property name="maxOpenPreparedStatements" value="100" />
<property name="accessToUnderlyingConnectionAllowed" value="false" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="180" />
<property name="logAbandoned" value="true" />
</data-source>
Any suggestions ??
Thanks in advance.
Regards
Chandra Sekhar
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [dbcp] removing idle connections.
Posted by "David Tonhofer, m-plify S.A." <d....@m-plify.com>.
Hmmm...I don't time to look at you code but here are some hints:
> 1) What settings i have to bring in for DBCP to remove these types of
> connections?
Dead connections are automatically removed/recreated if you use
GenericObjectPool and switch on all validations. Check out my notes in
<http://wiki.apache.org/jakarta-commons/DBCP>
> 2) Can i set some time interval for DBCP to remove it automatically from
> pool before firewall close them?
You are asking for a Pool from Jakarta Pooling (not Jakarta DBCP) that does
asynchronous validation. AFAIK there is no such thing yet, all verifications
are done synchronously. But that should be enough.
> 3) Can DBCP remove broken connections by itself? As connections can be
> broken by any reason by any party at any time.
Yes, see above.
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org