cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Oracle JNDI DataSource connections stay active
Date Thu, 11 May 2017 08:19:07 GMT
Ah cool. Yeah, pool management is often more tricky that it should be.

Andrus

> On May 11, 2017, at 10:50 AM, Markus Reich <markus.reich@markusreich.at> wrote:
> 
> Hi,
> 
> I found the problem as I switchted to the Oracle UCP DataSource
> Implementation.
> We set the paramater inactiveConnectionTimeout to 90 seconds, this meant
> with a minPoolSize of 10, that every 90 seconds all IDLE conns which where
> not used, were physically closed and removed from the pool, but in the same
> moment the same amount of connections was created because to fullfill our
> minPoolSize.
> 
> I think this was also the problem with the Tomcats DBCP
> 
> Markus
> 
> Andrus Adamchik <andrus@objectstyle.org> schrieb am Mi., 10. Mai 2017 um
> 08:47 Uhr:
> 
>>> as I can see you use no validationQuery?
>> 
>> Actually I somehow missed it in my example. I do. But the DB is MySQL, so
>> the query is either this, or a select from a small table:
>> 
>>  validationQuery: select 1;
>> 
>> Andrus
>> 
>> 
>>> On May 9, 2017, at 12:11 PM, Markus Reich <markus.reich@markusreich.at>
>> wrote:
>>> 
>>> no everything's done by the framework, we can filter on user and machine
>> so
>>> no sidekicks :-/ as I can see you use no validationQuery?
>>> 
>>> Andrus Adamchik <andrus@objectstyle.org> schrieb am Di., 9. Mai 2017 um
>>> 10:37 Uhr:
>>> 
>>>> Looks pretty normal. I have no experience with ConnectionState and
>>>> StatementFinalizer interceptors though.
>>>> 
>>>> FWIW, my typical Bootique config looks like this:
>>>> 
>>>> nhldb:
>>>>   url: ..
>>>>   username: ..
>>>>   password: ..
>>>>   initialSize: 1
>>>>   maxActive: ..
>>>>   minIdle: ..
>>>>   maxIdle: ..
>>>>   testWhileIdle: true
>>>>   removeAbandoned: true
>>>>   abandonWhenPercentageFull: 80
>>>>   removeAbandonedTimeout: 300
>>>>   jdbcInterceptors: ResetAbandonedTimer
>>>>   rollbackOnReturn: true
>>>>   defaultAutoCommit: false
>>>> 
>>>> Also are there any external transaction managers involved, or are you
>> only
>>>> using implicit Cayenne-managed transactions?
>>>> 
>>>> Andrus
>>>> 
>>>>> On May 9, 2017, at 11:24 AM, Markus Reich <markus.reich@markusreich.at
>>> 
>>>> wrote:
>>>>> 
>>>>> thx Andrus, your answer give me some kind of hope :-)
>>>>> 
>>>>> here are my settings
>>>>> <?xml version='1.0' encoding='UTF-8'?>
>>>>> <Context cookies='false'>
>>>>> <Resource name="jdbc/mii"
>>>>>   auth="Container"
>>>>>        type="javax.sql.DataSource"
>>>>>        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
>>>>>        testWhileIdle="false"
>>>>>        testOnBorrow="true"
>>>>>        testOnReturn="false"
>>>>>        validationQuery="SELECT 1 FROM DUAL"
>>>>>        validationInterval="30000"
>>>>>        timeBetweenEvictionRunsMillis="30000"
>>>>>        maxActive="100"
>>>>>        minIdle="20"
>>>>>        maxIdle="20"
>>>>>        maxWait="10000"
>>>>>        initialSize="20"
>>>>>        suspectTimeout="0"
>>>>>        removeAbandoned="true"
>>>>>        logAbandoned="false"
>>>>>        abandonWhenPercentageFull="50"
>>>>>        minEvictableIdleTimeMillis="30000"
>>>>>        jmxEnabled="true"
>>>>> 
>>>>> 
>>>> 
>> jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
>>>>> 
>>>>> 
>>>> 
>> org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer;org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer"
>>>>>        username=""
>>>>>        password=""
>>>>>        driverClassName="oracle.jdbc.OracleDriver"
>>>>>        url="jdbc:oracle:thin:@xxx"/>
>>>>> </Context>
>>>>> 
>>>>> I also checked the sessions in v$session that are marked as active in
>> the
>>>>> pool, the last SQL they executed was definitily from cayenne framework,
>>>>> very strange and it are different statements so no hint on this ...
>>>>> 
>>>>> regards
>>>>> Markus
>>>>> 
>>>>> 
>>>>> Andrus Adamchik <andrus@objectstyle.org> schrieb am Di., 9. Mai
2017
>> um
>>>>> 10:05 Uhr:
>>>>> 
>>>>>> Regular Cayenne operations should not leak connections. Any
>> connections
>>>>>> Cayenne gets are closed (== returned to the pool) regardless of
>> whether
>>>> an
>>>>>> operation succeeds or fails. The only exception is iterated queries
>> that
>>>>>> require the caller to close Cayenne ResultIterator, so bugs in the
>> user
>>>>>> code can lead to connection leaks.
>>>>>> 
>>>>>> FWIW, I've also been using Tomcat connection pool for many years,
both
>>>> in
>>>>>> Bootique and more traditional Jetty apps. Never seen a problem that
>> you
>>>>>> describe. What are the connection pool settings?
>>>>>> 
>>>>>> Andrus
>>>>>> 
>>>>>>> On May 8, 2017, at 10:57 PM, Markus Reich <
>> markus.reich@markusreich.at
>>>>> 
>>>>>> wrote:
>>>>>>> 
>>>>>>> Hi,
>>>>>>> 
>>>>>>> I have a problem with our JNDI DataSource (Catalina Connection
Pool).
>>>>>>> After a while I got a lot of active conns in the Pool (I can
see them
>>>> in
>>>>>>> via JMX), when I take a look in Oracles v$session, all sessions/conns
>>>> are
>>>>>>> in IDLE state?
>>>>>>> The Problem is, that the pool thinks all sessions are active
so he
>>>>>> creates
>>>>>>> new ones, so we have a lot of sessions created in Oracle, nearly
>> about
>>>> 1
>>>>>>> session / per second!!!
>>>>>>> 
>>>>>>> Has anybody experiences with Oracle and Cayenne and Tomcat Connection
>>>>>>> Pooling?
>>>>>>> Is there any way I get more infos, logs, traces?
>>>>>>> 
>>>>>>> thx
>>>>>>> Markus
>>>>>> 
>>>>>> 
>>>> 
>>>> 
>> 
>> 


Mime
View raw message