You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Madan KN <ma...@gmail.com> on 2013/04/08 09:23:44 UTC
Tomcat JDBC Pooling - Connections
*Hi All,*
Currently we are using tomcat jdbc pooling for oracle 11g R12. The problem
we are facing is during the peak traffic (less than %2) of time the
connections spike up to more than 100 sessions & never comes back to the
normal / desired pool size. Due to the connections are returned either in
Round-Robin/Random to the application.
Is there a way we can try LIFO so that connections to evict idle / unwanted
pool size beyond certain limit?
*Madan KN*
RE: Tomcat JDBC Pooling - Connections
Posted by Jeffrey Janner <Je...@PolyDyne.com>.
> -----Original Message-----
> From: Madan KN [mailto:madan.kn@gmail.com]
> Sent: Monday, April 08, 2013 9:29 AM
> To: Tomcat Users List
> Subject: Re: Tomcat JDBC Pooling - Connections
>
> # 99% percent of the time we do have need for 200 active sessions
> across our application cluster. But due to some high spikes we end up
> with 2000 sessions which is ending up in the pool with out getting
> evicted.*
>
> *
> # I think once the application is out of peak the connections are
> returned in either Round Robin/Random order, which is not making the
> condition for connections inactivity timeout and sweeper is failing to
> evict those connections.
> *
> *
> # How do we set something like LIFO, so that we can control and use
> only required connections during normal operating scenarios & evict
> which are not required.*
> *
> *
> --------------------------------*-------------------
> *Configuration Being Used*
> ---------------------------------------------------
> <Resource name="jdbc/GlobalHotelsDS" auth="Container"
> type="oracle.jdbc.pool.OracleDataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver"
> factory="oracle.jdbc.pool.OracleDataSourceFactory"
> queryTimeout="50"
>
> _connectionProperties="oracle.jdbc.ReadTimeout=30000,oracle.net.CONNECT
> _TIMEOUT=30000"
> url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=
> test.dev.com)(PORT=1522))(ADDRESS=(PROTOCOL=TCP)(HOST=test.dev.com)(POR
> T=1522))(LOAD_BALANCE=yes)(CONNECT_DATA=
> (SERVER=DEDICATED)(SERVICE_NAME=test.service.com)))"
> user="xxxxxx"
> password="xxxxxxx"
> connectionCachingEnabled="true"
> connectionCacheName="testPool"
>
> connectionCacheProperties="MaxLimit=20,InitialLimit=10,MaxStatementsLim
> it=80,InactivityTimeout=1800"/>
>
>
> *Thanks*,
> Madan KN
>
The Oracle pool is working as configured. Once your DB query rate drops below 20 within 30 minutes, it will start closing physical connections. You could try lowering the InactivityTimeout setting to have that start sooner.
In you OP you mentioned 200 and 2000 connections, but obviously here you are limiting it to 20 connections, which is a perfectly reasonable size IMHO, in fact maybe a little small for peak work depending on your app.
Otherwise, as others have stated, please see the Oracle Documentation on the pool that you are using.
Jeff
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tomcat JDBC Pooling - Connections
Posted by Jose María Zaragoza <de...@gmail.com>.
Hi:
But I can see that you aren't using Tomcat JDBC pool, but oracle.jdbc.pool.
OracleDataSource , right ?
And I don't know how you set up your datasource to more than 200 maxActive
connections
Indeed, I don't know how you define the size of connection pool
Regards
2013/4/8 Madan KN <ma...@gmail.com>
> # 99% percent of the time we do have need for 200 active sessions across
> our application cluster. But due to some high spikes we end up with 2000
> sessions which is ending up in the pool with out getting evicted.*
>
> *
> # I think once the application is out of peak the connections are returned
> in either Round Robin/Random order, which is not making the condition for
> connections inactivity timeout and sweeper is failing to evict those
> connections.
> *
> *
> # How do we set something like LIFO, so that we can control and use only
> required connections during normal operating scenarios & evict which are
> not required.*
> *
> *
> --------------------------------*-------------------
> *Configuration Being Used*
> ---------------------------------------------------
> <Resource name="jdbc/GlobalHotelsDS" auth="Container"
> type="oracle.jdbc.pool.OracleDataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver"
> factory="oracle.jdbc.pool.OracleDataSourceFactory"
> queryTimeout="50"
>
>
> _connectionProperties="oracle.jdbc.ReadTimeout=30000,oracle.net.CONNECT_TIMEOUT=30000"
> url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=
> test.dev.com)(PORT=1522))(ADDRESS=(PROTOCOL=TCP)(HOST=test.dev.com
> )(PORT=1522))(LOAD_BALANCE=yes)(CONNECT_DATA=
> (SERVER=DEDICATED)(SERVICE_NAME=test.service.com)))"
> user="xxxxxx"
> password="xxxxxxx"
> connectionCachingEnabled="true"
> connectionCacheName="testPool"
>
>
> connectionCacheProperties="MaxLimit=20,InitialLimit=10,MaxStatementsLimit=80,InactivityTimeout=1800"/>
>
>
> *Thanks*,
> Madan KN
>
>
>
> On Mon, Apr 8, 2013 at 6:15 PM, Daniel Mikusa <dm...@vmware.com> wrote:
>
> > On Apr 8, 2013, at 3:23 AM, Madan KN wrote:
> >
> > > *Hi All,*
> > >
> > > Currently we are using tomcat jdbc pooling for oracle 11g R12.
> >
> > What version of Tomcat are you using? If you are using the pool directly
> > outside of Tomcat, what version of it are you using?
> >
> > > The problem
> > > we are facing is during the peak traffic (less than %2) of time the
> > > connections spike up to more than 100 sessions & never comes back to
> the
> > > normal / desired pool size.
> >
> > It would be helpful to see your configuration. Please post it here,
> minus
> > comments.
> >
> > > Due to the connections are returned either in
> > > Round-Robin/Random to the application.
> >
> > How are you determining this? Can you provide some background on why you
> > think this is causing a problem?
> >
> > >
> > > Is there a way we can try LIFO so that connections to evict idle /
> > unwanted
> > > pool size beyond certain limit?
> >
> > Not sure exactly what you mean here. Is the "fairQueue" option what you
> > are looking for?
> >
> > https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
> >
> > Dan
> >
> >
> > >
> > > *Madan KN*
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> > For additional commands, e-mail: users-help@tomcat.apache.org
> >
> >
>
Re: Tomcat JDBC Pooling - Connections
Posted by Daniel Mikusa <dm...@vmware.com>.
On Apr 8, 2013, at 10:28 AM, Madan KN wrote:
> # 99% percent of the time we do have need for 200 active sessions across
> our application cluster. But due to some high spikes we end up with 2000
> sessions which is ending up in the pool with out getting evicted.*
>
> *
> # I think once the application is out of peak the connections are returned
> in either Round Robin/Random order, which is not making the condition for
> connections inactivity timeout and sweeper is failing to evict those
> connections.
> *
> *
> # How do we set something like LIFO, so that we can control and use only
> required connections during normal operating scenarios & evict which are
> not required.*
> *
> *
> --------------------------------*-------------------
> *Configuration Being Used*
> ---------------------------------------------------
> <Resource name="jdbc/GlobalHotelsDS" auth="Container"
> type="oracle.jdbc.pool.OracleDataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver"
> factory="oracle.jdbc.pool.OracleDataSourceFactory"
This is not a Tomcat issue, but an Oracle issue. You are using the "oracle.jdbc.pool.OracleDataSourceFactory" to create your DataSource, which means that you're not using DBCP or the Tomcat jdbc-pool.
You should probably try posting to an Oracle forum or contacting their support department. There's not much we can help you with here, unless you want to switch to DBCP or Tomcat jdbc-pool.
Dan
> queryTimeout="50"
>
> _connectionProperties="oracle.jdbc.ReadTimeout=30000,oracle.net.CONNECT_TIMEOUT=30000"
> url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=
> test.dev.com)(PORT=1522))(ADDRESS=(PROTOCOL=TCP)(HOST=test.dev.com)(PORT=1522))(LOAD_BALANCE=yes)(CONNECT_DATA=
> (SERVER=DEDICATED)(SERVICE_NAME=test.service.com)))"
> user="xxxxxx"
> password="xxxxxxx"
> connectionCachingEnabled="true"
> connectionCacheName="testPool"
>
> connectionCacheProperties="MaxLimit=20,InitialLimit=10,MaxStatementsLimit=80,InactivityTimeout=1800"/>
>
>
> *Thanks*,
> Madan KN
>
>
>
> On Mon, Apr 8, 2013 at 6:15 PM, Daniel Mikusa <dm...@vmware.com> wrote:
>
>> On Apr 8, 2013, at 3:23 AM, Madan KN wrote:
>>
>>> *Hi All,*
>>>
>>> Currently we are using tomcat jdbc pooling for oracle 11g R12.
>>
>> What version of Tomcat are you using? If you are using the pool directly
>> outside of Tomcat, what version of it are you using?
>>
>>> The problem
>>> we are facing is during the peak traffic (less than %2) of time the
>>> connections spike up to more than 100 sessions & never comes back to the
>>> normal / desired pool size.
>>
>> It would be helpful to see your configuration. Please post it here, minus
>> comments.
>>
>>> Due to the connections are returned either in
>>> Round-Robin/Random to the application.
>>
>> How are you determining this? Can you provide some background on why you
>> think this is causing a problem?
>>
>>>
>>> Is there a way we can try LIFO so that connections to evict idle /
>> unwanted
>>> pool size beyond certain limit?
>>
>> Not sure exactly what you mean here. Is the "fairQueue" option what you
>> are looking for?
>>
>> https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
>>
>> Dan
>>
>>
>>>
>>> *Madan KN*
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tomcat JDBC Pooling - Connections
Posted by Madan KN <ma...@gmail.com>.
# 99% percent of the time we do have need for 200 active sessions across
our application cluster. But due to some high spikes we end up with 2000
sessions which is ending up in the pool with out getting evicted.*
*
# I think once the application is out of peak the connections are returned
in either Round Robin/Random order, which is not making the condition for
connections inactivity timeout and sweeper is failing to evict those
connections.
*
*
# How do we set something like LIFO, so that we can control and use only
required connections during normal operating scenarios & evict which are
not required.*
*
*
--------------------------------*-------------------
*Configuration Being Used*
---------------------------------------------------
<Resource name="jdbc/GlobalHotelsDS" auth="Container"
type="oracle.jdbc.pool.OracleDataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
queryTimeout="50"
_connectionProperties="oracle.jdbc.ReadTimeout=30000,oracle.net.CONNECT_TIMEOUT=30000"
url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=
test.dev.com)(PORT=1522))(ADDRESS=(PROTOCOL=TCP)(HOST=test.dev.com)(PORT=1522))(LOAD_BALANCE=yes)(CONNECT_DATA=
(SERVER=DEDICATED)(SERVICE_NAME=test.service.com)))"
user="xxxxxx"
password="xxxxxxx"
connectionCachingEnabled="true"
connectionCacheName="testPool"
connectionCacheProperties="MaxLimit=20,InitialLimit=10,MaxStatementsLimit=80,InactivityTimeout=1800"/>
*Thanks*,
Madan KN
On Mon, Apr 8, 2013 at 6:15 PM, Daniel Mikusa <dm...@vmware.com> wrote:
> On Apr 8, 2013, at 3:23 AM, Madan KN wrote:
>
> > *Hi All,*
> >
> > Currently we are using tomcat jdbc pooling for oracle 11g R12.
>
> What version of Tomcat are you using? If you are using the pool directly
> outside of Tomcat, what version of it are you using?
>
> > The problem
> > we are facing is during the peak traffic (less than %2) of time the
> > connections spike up to more than 100 sessions & never comes back to the
> > normal / desired pool size.
>
> It would be helpful to see your configuration. Please post it here, minus
> comments.
>
> > Due to the connections are returned either in
> > Round-Robin/Random to the application.
>
> How are you determining this? Can you provide some background on why you
> think this is causing a problem?
>
> >
> > Is there a way we can try LIFO so that connections to evict idle /
> unwanted
> > pool size beyond certain limit?
>
> Not sure exactly what you mean here. Is the "fairQueue" option what you
> are looking for?
>
> https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
>
> Dan
>
>
> >
> > *Madan KN*
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: Tomcat JDBC Pooling - Connections
Posted by Daniel Mikusa <dm...@vmware.com>.
On Apr 8, 2013, at 3:23 AM, Madan KN wrote:
> *Hi All,*
>
> Currently we are using tomcat jdbc pooling for oracle 11g R12.
What version of Tomcat are you using? If you are using the pool directly outside of Tomcat, what version of it are you using?
> The problem
> we are facing is during the peak traffic (less than %2) of time the
> connections spike up to more than 100 sessions & never comes back to the
> normal / desired pool size.
It would be helpful to see your configuration. Please post it here, minus comments.
> Due to the connections are returned either in
> Round-Robin/Random to the application.
How are you determining this? Can you provide some background on why you think this is causing a problem?
>
> Is there a way we can try LIFO so that connections to evict idle / unwanted
> pool size beyond certain limit?
Not sure exactly what you mean here. Is the "fairQueue" option what you are looking for?
https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
Dan
>
> *Madan KN*
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org