Oracle JNDI DataSource connections stay active

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Oracle JNDI DataSource connections stay active

Markus Reich
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
Reply | Threaded
Open this post in threaded view
|

Re: Oracle JNDI DataSource connections stay active

Andrus Adamchik
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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Oracle JNDI DataSource connections stay active

Markus Reich
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 <[hidden email]> 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 <[hidden email]>
> 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
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Oracle JNDI DataSource connections stay active

Andrus Adamchik
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]>
>> 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
>>
>>

Reply | Threaded
Open this post in threaded view
|

Re: Oracle JNDI DataSource connections stay active

Markus Reich
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 <[hidden email]> 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 <[hidden email]>
> 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 <[hidden email]> 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 <[hidden email]
> >
> >> 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
> >>
> >>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Oracle JNDI DataSource connections stay active

Andrus Adamchik
>  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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]>
>> 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 <[hidden email]> 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 <[hidden email]
>>>
>>>> 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
>>>>
>>>>
>>
>>

Reply | Threaded
Open this post in threaded view
|

Re: Oracle JNDI DataSource connections stay active

Markus Reich
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 <[hidden email]> 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 <[hidden email]>
> 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 <[hidden email]> 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 <[hidden email]
> >
> >> 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 <[hidden email]> 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 <
> [hidden email]
> >>>
> >>>> 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
> >>>>
> >>>>
> >>
> >>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Oracle JNDI DataSource connections stay active

Andrus Adamchik
Ah cool. Yeah, pool management is often more tricky that it should be.

Andrus

> On May 11, 2017, at 10:50 AM, Markus Reich <[hidden email]> 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 <[hidden email]> 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 <[hidden email]>
>> 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 <[hidden email]> 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 <[hidden email]
>>>
>>>> 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 <[hidden email]> 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 <
>> [hidden email]
>>>>>
>>>>>> 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
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>