DBCP pooling not fully configurable (was Re: DBCP pooling unusable with Oracle)

[Martin Kalén <mk...@apache.org>]

delbd wrote:
> We are sure there is a timeout, because we had problem with a commercial tool 
> before. But now i think about it, we had to restart it only after week-ends. 
> Maybe the timeout is something like 24 hours or alike...
> 
> I was just noting this combinaison had no problems here. However, as long as 
> store is still useable after that i have nothing against your suggested 
> change  ;). Of course, the fact 'it works here' does not mean you can't 
> correct problems for those having problems! It's just you said default is to 
> 8 hours and, as our DBA looked surprised when i asked for the idle connection 
> timeout on the server, i concluded they never changed the default value and 
> didn't even know it existed.

Hello again David,
  as you probably just noticed I am (after a some time without any chance
to work on this!) done with a DBCP patch for the JDBCStore.

If the patch does not get committed anytime soon, it would be great if you
could at least apply it locally and verify that it works at least as well
as your previous setup.

If you do, make sure to set a validation query and tune when you want
to use this query to verify that the Connection instances in the DBCP
pool are alive. You can test on borrow (ie get connection from pool),
test on return (ie put connection back into pool) and test on idle
(ie DBCP has a separate "evictor thread" running at a configurable
interval to check if a (again configurable) number of idle Connection
instances sitting in the pool are evictable for destruction).

An example for Oracle with max 50 concurrent active Connection instances
and max 25 connected sitting idle in the pool (no evictor thread testing
while idle, only running validation query on borrow from pool):

  <nodestore classname="org.apache.slide.store.impl.rdbms.JDBCStore">
    <parameter name="adapter">org.apache.slide.store.impl.rdbms.OracleRDBMSAdapter</parameter>
    <parameter name="driver">oracle.jdbc.OracleDriver</parameter>
    <parameter name="url">jdbc:oracle:thin:@127.0.0.1:1521:ORCL</parameter>
    <parameter name="user">(user)</parameter>
    <parameter name="password">(password)</parameter>
    <parameter name="isolation">READ_COMMITTED</parameter>
    <parameter name="compress">false</parameter>
    <parameter name="dbcpPooling">true</parameter>
    <parameter name="dbcp.maxWait">5000</parameter>
    <parameter name="dbcp.maxActive">50</parameter>
    <parameter name="dbcp.maxIdle">25</parameter>
    <parameter name="dbcp.validationQuery">SELECT 1 FROM DUAL</parameter>
    <parameter name="dbcp.testOnBorrow">true</parameter>
    <parameter name="dbcp.testOnReturn">false</parameter>
    <parameter name="dbcp.testWhileIdle">false</parameter>
    <parameter name="dbcp.connectionProperties">defaultRowPrefetch=50</parameter>
   </nodestore>

(I have debugged that the default Oracle row prefetch parameter is successfully
set to 50 on each created OracleConnection through the "dbcp.connectionProperties"
Slide configuration setting.)

Once again, thanks for you feedback on the list - it is always valuable
to get more people's opinion on things before starting an implementation
change.

Cheers,
  Martin

P.S. I changed the subject since my wording ("unusable") was a bit drastic. ;)
Feel free to drop the "was" bit...


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