Questions on DBCP Config

["Holly, Michael" <mh...@talisentech.com>]

Hi
 
I'm using JDK 1.4.1_06  on Solaris 9.  My application is run on Tomcat
4.1.24 and makes connections to a MySQL 3.23.53 db and an Oracle 8i DB
via the DBCP 1.0. 
 
I have had excellent performanace using DBCP but on this project I am
hitting a snag.  I use the following configuration for my oracle DB
connection pool.
 
   <Resource name="jdbc/oracle_abraxxas" auth="Container"
type="javax.sql.DataSource"/> 
 
   <ResourceParams name="jdbc/oracle_abraxxas">
      <parameter>
         <name>factory</name>
         <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
      </parameter>
      <parameter>
         <name>driverClassName</name>
         <value>oracle.jdbc.OracleDriver</value>
      </parameter>
      <parameter>
         <name>url</name>
         <value>jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = snooker.myco.com)(PORT = 1521)))
(CONNECT_DATA = (SERVER = DEDICATED) (SID = ABRAXXAS)))</value>      
      </parameter>
      <parameter>
         <name>username</name>
         <value>abraxxas</value>
      </parameter>
      <parameter>
         <name>password</name>
         <value>#########</value>
      </parameter>
      <parameter>
         <name>maxActive</name>
         <value>20</value>
      </parameter>
      <parameter>
         <name>maxIdle</name>
         <value>10</value>
      </parameter>
      <parameter>
         <name>maxWait</name>
         <value>-1</value>
      </parameter>
      <parameter>
         <name>removeAbandoned</name>
         <value>true</value>
      </parameter>
      <parameter>
         <name>removeAbandonedTimeout</name>
         <value>300</value>
      </parameter>
      <parameter>
         <name>logAbandoned</name>
         <value>true</value>
      </parameter>      
      <parameter>
         <name>validationQuery</name>
         <value>select 'validationQuery' from dual</value>
      </parameter>
      <parameter>
        <name>testOnBorrow</name>
        <value>true</value>
      </parameter>    
   </ResourceParams>
 
 
My problem is that there is a firewall between the DB and my tomcat
server.  On the production system firewall connections have 60 min TTL.
Right now when I come in the morning and run the page that performs the
query to the Oracle DB it hangs. Eventually, the page times out.  
 
My questions are:
1. Can I utilize a 'testOnBorrow' parameter and a 'testWhileIdle'
parameter at the same time?
2. Will utitlizing the 'testWhileIdle' and a
'timeBetweenEvictionRunsMillis' = 600000 (10min) and a
'minEvictableIdleTimeMillis' = 1800000 (30 min) solve my problem?
3. Could I set the 'MaxIdle' parameter down to 0 so that all connections
are eventually dropped from inactivity?
 
I do not have the same network configuration in my Test otherwise I
would just test it there.  
 
Thanks for the help
 
Michael
 
 

Re: Questions on DBCP Config

[Dirk Verbeeck <di...@pandora.be>]

Hi Michael,
see inline

Holly, Michael wrote:
> Hi
>  
> I'm using JDK 1.4.1_06  on Solaris 9.  My application is run on Tomcat
> 4.1.24 and makes connections to a MySQL 3.23.53 db and an Oracle 8i DB
> via the DBCP 1.0. 
>  
> I have had excellent performanace using DBCP but on this project I am
> hitting a snag.  I use the following configuration for my oracle DB
> connection pool.

config removed

> My problem is that there is a firewall between the DB and my tomcat
> server.  On the production system firewall connections have 60 min TTL.
> Right now when I come in the morning and run the page that performs the
> query to the Oracle DB it hangs. Eventually, the page times out.  
>  
> My questions are:
> 1. Can I utilize a 'testOnBorrow' parameter and a 'testWhileIdle'
> parameter at the same time?
yes, you can combine multiple test* parameters

> 2. Will utitlizing the 'testWhileIdle' and a
> 'timeBetweenEvictionRunsMillis' = 600000 (10min) and a
> 'minEvictableIdleTimeMillis' = 1800000 (30 min) solve my problem?
The testWhileIdle alone will solve your problem, the firewall sees the 
test query and will not close the connection.  The 
minEvictableIdleTimeMillis is optional but you have to configure 
timeBetweenEvictionRunsMillis and numTestsPerEvictionRun.

> 3. Could I set the 'MaxIdle' parameter down to 0 so that all connections
> are eventually dropped from inactivity?
It you set maxIdle=0 then no connection will be allowed to remain idle 
  in the pool. (idle meaning not active, not being used by the 
application). This effectively disables the pooling as all connections 
are closed immediately.


There is also another solution.
You can prevent that your firewall from dropping the connection using 
a oracle technique.
SQL*Net has a parameter which defines time interval to send a probe 
message to identify if the client process is still alive
- SQLNET.EXPIRE_TIME, sqlnet.ora file on the server side. 
SQLNET.EXPIRE_TIME = <your_value>
Set it to something like 30 when your firewall drops connection after 
60 minutes.
See: 
http://download-west.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/params.htm#437583

We use this in our server environment and it works perfectly.

-- Dirk



---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org