You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Markus Reich <ma...@markusreich.at> on 2017/05/08 19:57:31 UTC
Oracle JNDI DataSource connections stay active
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
Re: Oracle JNDI DataSource connections stay active
Posted by Andrus Adamchik <an...@objectstyle.org>.
Ah cool. Yeah, pool management is often more tricky that it should be.
Andrus
> On May 11, 2017, at 10:50 AM, Markus Reich <ma...@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 <an...@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 <ma...@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 <an...@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 <an...@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
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
Re: Oracle JNDI DataSource connections stay active
Posted by Markus Reich <ma...@markusreich.at>.
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 <an...@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 <ma...@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 <an...@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 <an...@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
> >>>>
> >>>>
> >>
> >>
>
>
Re: Oracle JNDI DataSource connections stay active
Posted by Andrus Adamchik <an...@objectstyle.org>.
> 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 <ma...@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 <an...@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 <ma...@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 <an...@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
>>>>
>>>>
>>
>>
Re: Oracle JNDI DataSource connections stay active
Posted by Markus Reich <ma...@markusreich.at>.
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 <an...@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 <ma...@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 <an...@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
> >>
> >>
>
>
Re: Oracle JNDI DataSource connections stay active
Posted by Andrus Adamchik <an...@objectstyle.org>.
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 <ma...@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 <an...@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 <ma...@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
>>
>>
Re: Oracle JNDI DataSource connections stay active
Posted by Markus Reich <ma...@markusreich.at>.
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 <an...@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 <ma...@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
>
>
Re: Oracle JNDI DataSource connections stay active
Posted by Andrus Adamchik <an...@objectstyle.org>.
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 <ma...@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