DBCP issuing more sessions than maxActive sessions in Oracle

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

DBCP issuing more sessions than maxActive sessions in Oracle

Romero, René
Hello guys.

Under what circumstances could we get more than MaxActive sessions in the database if DBCP is our only means of access to it?

This is our current configuration and it's surpassing 160 Oracle sessions at times when it shouldn't go beyond 32 (our current value for MaxActive).


DB = Oracle 11g

Data access via spring famework.


Thanks in advance.


  <bean id="dwDataSource" class="org.apache.commons.dbcp.BasicDataSource" p:url="${dw.db.url}" p:username="${dw.db.username}"
    p:password="${dw.db.password}" p:driverClassName="${dw.db.driverClassName}" init-method="createDataSource" destroy-method="close">
    <property name="initialSize" value="${dw.db.initial.connection.size}" />
    <property name="maxActive" value="${dw.db.max.connection.size}" />
    <!--  As part of perf testing, saw a bottleneck in the dw hence commiting these two params
    <property name="testOnBorrow" value="true" />
    <property name="validationQuery" value="${dw.db.validation.query}" /> -->
    <property name="minEvictableIdleTimeMillis" value="900000" />
    <property name="minIdle" value="${dw.db.minIdle.connections}" />
    <property name="maxIdle" value="${dw.db.maxIdle.connections}" />
    <property name="timeBetweenEvictionRunsMillis" value="1200000" />
  </bean>


dw.db.url=jdbc:oracle:thin:someDB/somePass@someHost:1522:SomeInstance
dw.db.username=someUser
dw.db.password=somePass
dw.db.driverClassName=oracle.jdbc.OracleDriver
dw.db.allowed.operations=select
dw.db.schema=1|COMMON_APP_VIEWS_01, 81|COMMON_APP_VIEWS_81
dw.db.maxRows=500
dw.db.type=ORACLE
dw.db.validation.query=select 1 from dual
dw.db.initial.connection.size=4
dw.db.max.connection.size=32
dw.db.minIdle.connections=4
dw.db.maxIdle.connections=4


What other info would you need?


Rene Romero Benavides
Administrador de Bases de Datos para Command Center
+52(55) 11020460 Ext. 6432 |  Office

+52 1 5545664012 |  Mobile

www.omnitracs.com/mx<http://www.omnitracs.com/mx>

Register today!  www.omnitracs.com/outlook<http://www.omnitracs.com/outlook>

<https://outlook.office.com/owa/service.svc/s/GetFileAttachment?id=AAMkAGM2NWIyZGFmLWZhMTYtNDUzNC04YzQ5LWEwOTA1Zjk4MTY3ZgBGAAAAAABp9rw%2F5DvqQ5VNu%2FP5FANvBwCC6JFKXcS6RJy5Q76IhGGqAAAAAAEMAACC6JFKXcS6RJy5Q76IhGGqAACGtmA4AAABEgAQANT3RQAH0VJIrveZGngUUTQ%3D&X-OWA-CANARY=zgTt0gEIPEaHc-Q35K2xD7ADDSE3yNIYLGPJUeTkGumastS1fdcpigPbw4ASyo9SSzPAsJtdi6o>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DBCP issuing more sessions than maxActive sessions in Oracle

Phil Steitz
What versions of dbcp and pool are you running?

Looks like 1.x from the package names.  The first thing to look at
is the changelogs[1] for releases since the pool and dbcp versions
that you are running.

Phil

[1] for dbcp this is
https://commons.apache.org/proper/commons-dbcp/changes-report.html
There is a similar page on the pool site.

On 1/24/17 3:02 PM, Romero, René wrote:

> Hello guys.
>
> Under what circumstances could we get more than MaxActive sessions in the database if DBCP is our only means of access to it?
>
> This is our current configuration and it's surpassing 160 Oracle sessions at times when it shouldn't go beyond 32 (our current value for MaxActive).
>
>
> DB = Oracle 11g
>
> Data access via spring famework.
>
>
> Thanks in advance.
>
>
>   <bean id="dwDataSource" class="org.apache.commons.dbcp.BasicDataSource" p:url="${dw.db.url}" p:username="${dw.db.username}"
>     p:password="${dw.db.password}" p:driverClassName="${dw.db.driverClassName}" init-method="createDataSource" destroy-method="close">
>     <property name="initialSize" value="${dw.db.initial.connection.size}" />
>     <property name="maxActive" value="${dw.db.max.connection.size}" />
>     <!--  As part of perf testing, saw a bottleneck in the dw hence commiting these two params
>     <property name="testOnBorrow" value="true" />
>     <property name="validationQuery" value="${dw.db.validation.query}" /> -->
>     <property name="minEvictableIdleTimeMillis" value="900000" />
>     <property name="minIdle" value="${dw.db.minIdle.connections}" />
>     <property name="maxIdle" value="${dw.db.maxIdle.connections}" />
>     <property name="timeBetweenEvictionRunsMillis" value="1200000" />
>   </bean>
>
>
> dw.db.url=jdbc:oracle:thin:someDB/somePass@someHost:1522:SomeInstance
> dw.db.username=someUser
> dw.db.password=somePass
> dw.db.driverClassName=oracle.jdbc.OracleDriver
> dw.db.allowed.operations=select
> dw.db.schema=1|COMMON_APP_VIEWS_01, 81|COMMON_APP_VIEWS_81
> dw.db.maxRows=500
> dw.db.type=ORACLE
> dw.db.validation.query=select 1 from dual
> dw.db.initial.connection.size=4
> dw.db.max.connection.size=32
> dw.db.minIdle.connections=4
> dw.db.maxIdle.connections=4
>
>
> What other info would you need?
>
>
> Rene Romero Benavides
> Administrador de Bases de Datos para Command Center
> +52(55) 11020460 Ext. 6432 |  Office
>
> +52 1 5545664012 |  Mobile
>
> www.omnitracs.com/mx<http://www.omnitracs.com/mx>
>
> Register today!  www.omnitracs.com/outlook<http://www.omnitracs.com/outlook>
>
> <https://outlook.office.com/owa/service.svc/s/GetFileAttachment?id=AAMkAGM2NWIyZGFmLWZhMTYtNDUzNC04YzQ5LWEwOTA1Zjk4MTY3ZgBGAAAAAABp9rw%2F5DvqQ5VNu%2FP5FANvBwCC6JFKXcS6RJy5Q76IhGGqAAAAAAEMAACC6JFKXcS6RJy5Q76IhGGqAACGtmA4AAABEgAQANT3RQAH0VJIrveZGngUUTQ%3D&X-OWA-CANARY=zgTt0gEIPEaHc-Q35K2xD7ADDSE3yNIYLGPJUeTkGumastS1fdcpigPbw4ASyo9SSzPAsJtdi6o>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle

Romero, René

We're running  commons-dbcp-1.4 and commons-pool-1.6. To me, it sounds like we're being impacted by this bug:

https://issues.apache.org/jira/browse/DBCP-379

[DBCP-379] number of connections created has crossed more ...<https://issues.apache.org/jira/browse/DBCP-379>
issues.apache.org
I found that when the minIdle is configured then during loads, common pool creates more number of connections it will be greater than maxActive.

Is it transparent application-wise to jump from these versions to the latests? Or what upgrading path do you suggest we take?


Thanks again.



Rene Romero Benavides
Administrador de Bases de Datos para Command Center
+52(55) 11020460 Ext. 6432 |  Office

+52 1 5545664012 |  Mobile

www.omnitracs.com/mx<http://www.omnitracs.com/mx>

Register today!  www.omnitracs.com/outlook<http://www.omnitracs.com/outlook>

<https://outlook.office.com/owa/service.svc/s/GetFileAttachment?id=AAMkAGM2NWIyZGFmLWZhMTYtNDUzNC04YzQ5LWEwOTA1Zjk4MTY3ZgBGAAAAAABp9rw%2F5DvqQ5VNu%2FP5FANvBwCC6JFKXcS6RJy5Q76IhGGqAAAAAAEMAACC6JFKXcS6RJy5Q76IhGGqAACGtmA4AAABEgAQANT3RQAH0VJIrveZGngUUTQ%3D&X-OWA-CANARY=zgTt0gEIPEaHc-Q35K2xD7ADDSE3yNIYLGPJUeTkGumastS1fdcpigPbw4ASyo9SSzPAsJtdi6o>
________________________________
From: Phil Steitz <[hidden email]>
Sent: Tuesday, January 24, 2017 3:43:29 PM
To: Commons Users List
Subject: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle

What versions of dbcp and pool are you running?

Looks like 1.x from the package names.  The first thing to look at
is the changelogs[1] for releases since the pool and dbcp versions
that you are running.

Phil

[1] for dbcp this is
https://commons.apache.org/proper/commons-dbcp/changes-report.html
There is a similar page on the pool site.

On 1/24/17 3:02 PM, Romero, René wrote:

> Hello guys.
>
> Under what circumstances could we get more than MaxActive sessions in the database if DBCP is our only means of access to it?
>
> This is our current configuration and it's surpassing 160 Oracle sessions at times when it shouldn't go beyond 32 (our current value for MaxActive).
>
>
> DB = Oracle 11g
>
> Data access via spring famework.
>
>
> Thanks in advance.
>
>
>   <bean id="dwDataSource" class="org.apache.commons.dbcp.BasicDataSource" p:url="${dw.db.url}" p:username="${dw.db.username}"
>     p:password="${dw.db.password}" p:driverClassName="${dw.db.driverClassName}" init-method="createDataSource" destroy-method="close">
>     <property name="initialSize" value="${dw.db.initial.connection.size}" />
>     <property name="maxActive" value="${dw.db.max.connection.size}" />
>     <!--  As part of perf testing, saw a bottleneck in the dw hence commiting these two params
>     <property name="testOnBorrow" value="true" />
>     <property name="validationQuery" value="${dw.db.validation.query}" /> -->
>     <property name="minEvictableIdleTimeMillis" value="900000" />
>     <property name="minIdle" value="${dw.db.minIdle.connections}" />
>     <property name="maxIdle" value="${dw.db.maxIdle.connections}" />
>     <property name="timeBetweenEvictionRunsMillis" value="1200000" />
>   </bean>
>
>
> dw.db.url=jdbc:oracle:thin:someDB/somePass@someHost:1522:SomeInstance
> dw.db.username=someUser
> dw.db.password=somePass
> dw.db.driverClassName=oracle.jdbc.OracleDriver
> dw.db.allowed.operations=select
> dw.db.schema=1|COMMON_APP_VIEWS_01, 81|COMMON_APP_VIEWS_81
> dw.db.maxRows=500
> dw.db.type=ORACLE
> dw.db.validation.query=select 1 from dual
> dw.db.initial.connection.size=4
> dw.db.max.connection.size=32
> dw.db.minIdle.connections=4
> dw.db.maxIdle.connections=4
>
>
> What other info would you need?
>
>
> Rene Romero Benavides
> Administrador de Bases de Datos para Command Center
> +52(55) 11020460 Ext. 6432 |  Office
>
> +52 1 5545664012 |  Mobile
>
> www.omnitracs.com/mx<http://www.omnitracs.com/mx<http://www.omnitracs.com/mx<http://www.omnitracs.com/mx>>
>
> Register today!  www.omnitracs.com/outlook<http://www.omnitracs.com/outlook<http://www.omnitracs.com/outlook<http://www.omnitracs.com/outlook>>
>
> <https://outlook.office.com/owa/service.svc/s/GetFileAttachment?id=AAMkAGM2NWIyZGFmLWZhMTYtNDUzNC04YzQ5LWEwOTA1Zjk4MTY3ZgBGAAAAAABp9rw%2F5DvqQ5VNu%2FP5FANvBwCC6JFKXcS6RJy5Q76IhGGqAAAAAAEMAACC6JFKXcS6RJy5Q76IhGGqAACGtmA4AAABEgAQANT3RQAH0VJIrveZGngUUTQ%3D&X-OWA-CANARY=zgTt0gEIPEaHc-Q35K2xD7ADDSE3yNIYLGPJUeTkGumastS1fdcpigPbw4ASyo9SSzPAsJtdi6o>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

This message was received from outside of the Omnitracs network
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle

Shawn Heisey
On 1/24/2017 3:55 PM, Romero, René wrote:
> We're running  commons-dbcp-1.4 and commons-pool-1.6. To me, it sounds like we're being impacted by this bug:
>
> https://issues.apache.org/jira/browse/DBCP-379
>
> [DBCP-379] number of connections created has crossed more ...<https://issues.apache.org/jira/browse/DBCP-379>
> issues.apache.org
> I found that when the minIdle is configured then during loads, common pool creates more number of connections it will be greater than maxActive.
>
> Is it transparent application-wise to jump from these versions to the latests? Or what upgrading path do you suggest we take?

I don't know anything about that bug or whether that is what you are
encountering, but I can discuss upgrading.

You seem to be on the latest 1.x versions.  Upgrading to the 2.x
versions will require code changes, because the package names have
changed.  A few other things have changed as well.

When I did the upgrade, I found it to be pretty easy to make the
conversion.  Changing the imports was a big part of the job, which my
IDE (eclipse) basically did for me.  A few changes were also required
when building the datasource objects.

Here's a slightly redacted patch that shows the code changes I made when
I upgraded POOL and DBCP from 1.x to 2.x:

https://www.dropbox.com/s/de1hpa767i8tg5s/dbcp2-upgrade.patch?dl=0

This upgrade was done nearly two years ago, but I think I remember
enough about it that I may be able to answer questions on the patch.

The code went through a whole bunch of changes after the upgrade.  I
changed "replica" to "main" throughout the class.  The "replica database
server" section that you can see in the patch now looks like the following:

  /*
   * Create a datasource (connection pool) for the main database server.
   */
  ConnectionFactory cfMain = new DriverManagerConnectionFactory(mainUrl,
dbUser, dbPass);
  PoolableConnectionFactory pcfMain = new
PoolableConnectionFactory(cfMain, null);
  pcfMain.setValidationQuery(validationQuery);
  pcfMain.setValidationQueryTimeout(Const.FIVE_SECONDS / 1000);
  opMain = new GenericObjectPool<>(pcfMain);
  opMain.setMaxWaitMillis(Const.THIRTY_SECONDS);
  opMain.setMaxIdle(numShards);
  opMain.setMaxTotal(numShards * 5);
  opMain.setNumTestsPerEvictionRun(numShards * 5);
  opMain.setTimeBetweenEvictionRunsMillis(Const.ONE_MINUTE);
  opMain.setMinEvictableIdleTimeMillis(Const.ONE_MINUTE * 5);
  pcfMain.setPool(opMain);
  dsMain = new PoolingDataSource<>(opMain);

Thanks,
Shawn


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle

Romero, René
Thank you very much Shawn for sharing your experience and code on this, we'll certainly use it.

Guys, do you think dbcp-1.4.1 is going to come out soon?


Rene Romero Benavides
Administrador de Bases de Datos para Command Center
+52(55) 11020460 Ext. 6432 |  Office

+52 1 5545664012 |  Mobile

www.omnitracs.com/mx<http://www.omnitracs.com/mx>

Register today!  www.omnitracs.com/outlook<http://www.omnitracs.com/outlook>

<https://outlook.office.com/owa/service.svc/s/GetFileAttachment?id=AAMkAGM2NWIyZGFmLWZhMTYtNDUzNC04YzQ5LWEwOTA1Zjk4MTY3ZgBGAAAAAABp9rw%2F5DvqQ5VNu%2FP5FANvBwCC6JFKXcS6RJy5Q76IhGGqAAAAAAEMAACC6JFKXcS6RJy5Q76IhGGqAACGtmA4AAABEgAQANT3RQAH0VJIrveZGngUUTQ%3D&X-OWA-CANARY=zgTt0gEIPEaHc-Q35K2xD7ADDSE3yNIYLGPJUeTkGumastS1fdcpigPbw4ASyo9SSzPAsJtdi6o>
________________________________
From: Shawn Heisey <[hidden email]>
Sent: Tuesday, January 24, 2017 6:29:34 PM
To: Commons Users List
Subject: Re: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle

On 1/24/2017 3:55 PM, Romero, René wrote:
> We're running  commons-dbcp-1.4 and commons-pool-1.6. To me, it sounds like we're being impacted by this bug:
>
> https://issues.apache.org/jira/browse/DBCP-379
>
> [DBCP-379] number of connections created has crossed more ...<https://issues.apache.org/jira/browse/DBCP-379>
> issues.apache.org
> I found that when the minIdle is configured then during loads, common pool creates more number of connections it will be greater than maxActive.
>
> Is it transparent application-wise to jump from these versions to the latests? Or what upgrading path do you suggest we take?

I don't know anything about that bug or whether that is what you are
encountering, but I can discuss upgrading.

You seem to be on the latest 1.x versions.  Upgrading to the 2.x
versions will require code changes, because the package names have
changed.  A few other things have changed as well.

When I did the upgrade, I found it to be pretty easy to make the
conversion.  Changing the imports was a big part of the job, which my
IDE (eclipse) basically did for me.  A few changes were also required
when building the datasource objects.

Here's a slightly redacted patch that shows the code changes I made when
I upgraded POOL and DBCP from 1.x to 2.x:

https://www.dropbox.com/s/de1hpa767i8tg5s/dbcp2-upgrade.patch?dl=0

This upgrade was done nearly two years ago, but I think I remember
enough about it that I may be able to answer questions on the patch.

The code went through a whole bunch of changes after the upgrade.  I
changed "replica" to "main" throughout the class.  The "replica database
server" section that you can see in the patch now looks like the following:

  /*
   * Create a datasource (connection pool) for the main database server.
   */
  ConnectionFactory cfMain = new DriverManagerConnectionFactory(mainUrl,
dbUser, dbPass);
  PoolableConnectionFactory pcfMain = new
PoolableConnectionFactory(cfMain, null);
  pcfMain.setValidationQuery(validationQuery);
  pcfMain.setValidationQueryTimeout(Const.FIVE_SECONDS / 1000);
  opMain = new GenericObjectPool<>(pcfMain);
  opMain.setMaxWaitMillis(Const.THIRTY_SECONDS);
  opMain.setMaxIdle(numShards);
  opMain.setMaxTotal(numShards * 5);
  opMain.setNumTestsPerEvictionRun(numShards * 5);
  opMain.setTimeBetweenEvictionRunsMillis(Const.ONE_MINUTE);
  opMain.setMinEvictableIdleTimeMillis(Const.ONE_MINUTE * 5);
  pcfMain.setPool(opMain);
  dsMain = new PoolingDataSource<>(opMain);

Thanks,
Shawn


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle

Shawn Heisey
On 1/24/2017 5:36 PM, Romero, René wrote:
> Guys, do you think dbcp-1.4.1 is going to come out soon?

I'm more familiar with the Lucene/Solr project than Commons, but if I
had to guess, I would say that you're unlikely to see a new 1.x version
of dbcp or pool.  The 1.4 version of dbcp was released nearly six years
ago, the 1.6 version of pool was released five years ago.  They are
quite old, and if a new version were planned, it is likely that it would
have already been released.

Typically when a new major version of an Apache project is released, the
previous major version goes into maintenance mode, and the major version
before that goes completely dead.  In maintenance mode, only extremely
significant bugs with widespread effects are fixed.  After so much time,
it is unlikely that any bug like this will be found, so the 1.x versions
have become *effectively* dead, even though that is not technically
their status.

While it is possible that Commons works differently than a typical
Apache project where previous major versions are concerned, I would be a
little bit surprised if that were the case.

Thanks,
Shawn


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle

garydgregory
ATM & FWIW, the only push I see is to release new versions of [pool2] and
[dbcp2] out of the current branches.

Gary

On Tue, Jan 24, 2017 at 5:37 PM, Shawn Heisey <[hidden email]> wrote:

> On 1/24/2017 5:36 PM, Romero, René wrote:
> > Guys, do you think dbcp-1.4.1 is going to come out soon?
>
> I'm more familiar with the Lucene/Solr project than Commons, but if I
> had to guess, I would say that you're unlikely to see a new 1.x version
> of dbcp or pool.  The 1.4 version of dbcp was released nearly six years
> ago, the 1.6 version of pool was released five years ago.  They are
> quite old, and if a new version were planned, it is likely that it would
> have already been released.
>
> Typically when a new major version of an Apache project is released, the
> previous major version goes into maintenance mode, and the major version
> before that goes completely dead.  In maintenance mode, only extremely
> significant bugs with widespread effects are fixed.  After so much time,
> it is unlikely that any bug like this will be found, so the 1.x versions
> have become *effectively* dead, even though that is not technically
> their status.
>
> While it is possible that Commons works differently than a typical
> Apache project where previous major versions are concerned, I would be a
> little bit surprised if that were the case.
>
> Thanks,
> Shawn
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


--
E-Mail: [hidden email] | [hidden email]
Java Persistence with Hibernate, Second Edition
<https://www.amazon.com/gp/product/1617290459/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=1617290459&linkCode=as2&tag=garygregory-20&linkId=cadb800f39946ec62ea2b1af9fe6a2b8>

<http:////ir-na.amazon-adsystem.com/e/ir?t=garygregory-20&l=am2&o=1&a=1617290459>
JUnit in Action, Second Edition
<https://www.amazon.com/gp/product/1935182021/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=1935182021&linkCode=as2&tag=garygregory-20&linkId=31ecd1f6b6d1eaf8886ac902a24de418%22>

<http:////ir-na.amazon-adsystem.com/e/ir?t=garygregory-20&l=am2&o=1&a=1935182021>
Spring Batch in Action
<https://www.amazon.com/gp/product/1935182951/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=1935182951&linkCode=%7B%7BlinkCode%7D%7D&tag=garygregory-20&linkId=%7B%7Blink_id%7D%7D%22%3ESpring+Batch+in+Action>
<http:////ir-na.amazon-adsystem.com/e/ir?t=garygregory-20&l=am2&o=1&a=1935182951>
Blog: http://garygregory.wordpress.com
Home: http://garygregory.com/
Tweet! http://twitter.com/GaryGregory
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle

Romero, René
Got it.  Thanks a lot for the info. Won't put my hopes on the 1.x series. Greetings from Mexico.


Rene Romero Benavides
Administrador de Bases de Datos para Command Center
<https://outlook.office.com/owa/service.svc/s/GetFileAttachment?id=AAMkAGM2NWIyZGFmLWZhMTYtNDUzNC04YzQ5LWEwOTA1Zjk4MTY3ZgBGAAAAAABp9rw%2F5DvqQ5VNu%2FP5FANvBwCC6JFKXcS6RJy5Q76IhGGqAAAAAAEMAACC6JFKXcS6RJy5Q76IhGGqAACGtmA4AAABEgAQANT3RQAH0VJIrveZGngUUTQ%3D&X-OWA-CANARY=zgTt0gEIPEaHc-Q35K2xD7ADDSE3yNIYLGPJUeTkGumastS1fdcpigPbw4ASyo9SSzPAsJtdi6o>
________________________________
From: Gary Gregory <[hidden email]>
Sent: Tuesday, January 24, 2017 7:58:59 PM
To: Commons Users List
Subject: Re: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle

ATM & FWIW, the only push I see is to release new versions of [pool2] and
[dbcp2] out of the current branches.

Gary

On Tue, Jan 24, 2017 at 5:37 PM, Shawn Heisey <[hidden email]> wrote:

> On 1/24/2017 5:36 PM, Romero, René wrote:
> > Guys, do you think dbcp-1.4.1 is going to come out soon?
>
> I'm more familiar with the Lucene/Solr project than Commons, but if I
> had to guess, I would say that you're unlikely to see a new 1.x version
> of dbcp or pool.  The 1.4 version of dbcp was released nearly six years
> ago, the 1.6 version of pool was released five years ago.  They are
> quite old, and if a new version were planned, it is likely that it would
> have already been released.
>
> Typically when a new major version of an Apache project is released, the
> previous major version goes into maintenance mode, and the major version
> before that goes completely dead.  In maintenance mode, only extremely
> significant bugs with widespread effects are fixed.  After so much time,
> it is unlikely that any bug like this will be found, so the 1.x versions
> have become *effectively* dead, even though that is not technically
> their status.
>
> While it is possible that Commons works differently than a typical
> Apache project where previous major versions are concerned, I would be a
> little bit surprised if that were the case.
>
> Thanks,
> Shawn
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


--
E-Mail: [hidden email] | [hidden email]
Java Persistence with Hibernate, Second Edition
<https://www.amazon.com/gp/product/1617290459/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=1617290459&linkCode=as2&tag=garygregory-20&linkId=cadb800f39946ec62ea2b1af9fe6a2b8>

<http:////ir-na.amazon-adsystem.com/e/ir?t=garygregory-20&l=am2&o=1&a=1617290459>
JUnit in Action, Second Edition
<https://www.amazon.com/gp/product/1935182021/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=1935182021&linkCode=as2&tag=garygregory-20&linkId=31ecd1f6b6d1eaf8886ac902a24de418%22>

<http:////ir-na.amazon-adsystem.com/e/ir?t=garygregory-20&l=am2&o=1&a=1935182021>
Spring Batch in Action
<https://www.amazon.com/gp/product/1935182951/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=1935182951&linkCode=%7B%7BlinkCode%7D%7D&tag=garygregory-20&linkId=%7B%7Blink_id%7D%7D%22%3ESpring+Batch+in+Action>
<http:////ir-na.amazon-adsystem.com/e/ir?t=garygregory-20&l=am2&o=1&a=1935182951>
Blog: http://garygregory.wordpress.com
Home: http://garygregory.com/
Tweet! http://twitter.com/GaryGregory
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle

Phil Steitz
In reply to this post by Romero, René
On 1/24/17 4:55 PM, Romero, René wrote:

> We're running  commons-dbcp-1.4 and commons-pool-1.6. To me, it sounds like we're being impacted by this bug:
>
> https://issues.apache.org/jira/browse/DBCP-379
>
> [DBCP-379] number of connections created has crossed more ...<https://issues.apache.org/jira/browse/DBCP-379>
> issues.apache.org
> I found that when the minIdle is configured then during loads, common pool creates more number of connections it will be greater than maxActive.
>
> Is it transparent application-wise to jump from these versions to the latests? Or what upgrading path do you suggest we take?
>
>
> Thanks again.
>
>
>
> Rene Romero Benavides
> Administrador de Bases de Datos para Command Center
> +52(55) 11020460 Ext. 6432 |  Office
>
> +52 1 5545664012 |  Mobile
>
> www.omnitracs.com/mx<http://www.omnitracs.com/mx>
>
> Register today!  www.omnitracs.com/outlook<http://www.omnitracs.com/outlook>
>
> <https://outlook.office.com/owa/service.svc/s/GetFileAttachment?id=AAMkAGM2NWIyZGFmLWZhMTYtNDUzNC04YzQ5LWEwOTA1Zjk4MTY3ZgBGAAAAAABp9rw%2F5DvqQ5VNu%2FP5FANvBwCC6JFKXcS6RJy5Q76IhGGqAAAAAAEMAACC6JFKXcS6RJy5Q76IhGGqAACGtmA4AAABEgAQANT3RQAH0VJIrveZGngUUTQ%3D&X-OWA-CANARY=zgTt0gEIPEaHc-Q35K2xD7ADDSE3yNIYLGPJUeTkGumastS1fdcpigPbw4ASyo9SSzPAsJtdi6o>
> ________________________________
> From: Phil Steitz <[hidden email]>
> Sent: Tuesday, January 24, 2017 3:43:29 PM
> To: Commons Users List
> Subject: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle
>
> What versions of dbcp and pool are you running?
>
> Looks like 1.x from the package names.  The first thing to look at
> is the changelogs[1] for releases since the pool and dbcp versions
> that you are running.
>
> Phil
>
> [1] for dbcp this is
> https://commons.apache.org/proper/commons-dbcp/changes-report.html
> There is a similar page on the pool site.
>
> On 1/24/17 3:02 PM, Romero, René wrote:
>> Hello guys.
>>
>> Under what circumstances could we get more than MaxActive sessions in the database if DBCP is our only means of access to it?
>>
>> This is our current configuration and it's surpassing 160 Oracle sessions at times when it shouldn't go beyond 32 (our current value for MaxActive).
>>
>>
>> DB = Oracle 11g
>>
>> Data access via spring famework.
>>
>>
>> Thanks in advance.
>>
>>
>>   <bean id="dwDataSource" class="org.apache.commons.dbcp.BasicDataSource" p:url="${dw.db.url}" p:username="${dw.db.username}"
>>     p:password="${dw.db.password}" p:driverClassName="${dw.db.driverClassName}" init-method="createDataSource" destroy-method="close">
>>     <property name="initialSize" value="${dw.db.initial.connection.size}" />
>>     <property name="maxActive" value="${dw.db.max.connection.size}" />
>>     <!--  As part of perf testing, saw a bottleneck in the dw hence commiting these two params
>>     <property name="testOnBorrow" value="true" />
>>     <property name="validationQuery" value="${dw.db.validation.query}" /> -->
>>     <property name="minEvictableIdleTimeMillis" value="900000" />
>>     <property name="minIdle" value="${dw.db.minIdle.connections}" />
>>     <property name="maxIdle" value="${dw.db.maxIdle.connections}" />
>>     <property name="timeBetweenEvictionRunsMillis" value="1200000" />
>>   </bean>
>>
>>
>> dw.db.url=jdbc:oracle:thin:someDB/somePass@someHost:1522:SomeInstance
>> dw.db.username=someUser
>> dw.db.password=somePass
>> dw.db.driverClassName=oracle.jdbc.OracleDriver
>> dw.db.allowed.operations=select
>> dw.db.schema=1|COMMON_APP_VIEWS_01, 81|COMMON_APP_VIEWS_81
>> dw.db.maxRows=500
>> dw.db.type=ORACLE
>> dw.db.validation.query=select 1 from dual
>> dw.db.initial.connection.size=4
>> dw.db.max.connection.size=32
>> dw.db.minIdle.connections=4
>> dw.db.maxIdle.connections=4

The settings above will create a lot of connection churn and may be
what is causing you to hit an old pool or dbcp bug.  When you set
minIdle = maxIdle, you are telling the pool that it needs to try to
keep *exactly* that number (in your case 4) idle connections at all
times.  The maxIdle setting means that when there are already 4 idle
connections and a 5th is returned, it is destroyed.  If the idle
count subsequently drops below that, since you have the evictor
enabled, it will try to ensureMinIdle when it runs, creating new
ones.  In general, it is better to have maxIdle >> minIdle.  This
applies to dbcp2 as well.

Phil

>>
>>
>> What other info would you need?
>>
>>
>> Rene Romero Benavides
>> Administrador de Bases de Datos para Command Center
>> +52(55) 11020460 Ext. 6432 |  Office
>>
>> +52 1 5545664012 |  Mobile
>>
>> www.omnitracs.com/mx<http://www.omnitracs.com/mx<http://www.omnitracs.com/mx<http://www.omnitracs.com/mx>>
>>
>> Register today!  www.omnitracs.com/outlook<http://www.omnitracs.com/outlook<http://www.omnitracs.com/outlook<http://www.omnitracs.com/outlook>>
>>
>> <https://outlook.office.com/owa/service.svc/s/GetFileAttachment?id=AAMkAGM2NWIyZGFmLWZhMTYtNDUzNC04YzQ5LWEwOTA1Zjk4MTY3ZgBGAAAAAABp9rw%2F5DvqQ5VNu%2FP5FANvBwCC6JFKXcS6RJy5Q76IhGGqAAAAAAEMAACC6JFKXcS6RJy5Q76IhGGqAACGtmA4AAABEgAQANT3RQAH0VJIrveZGngUUTQ%3D&X-OWA-CANARY=zgTt0gEIPEaHc-Q35K2xD7ADDSE3yNIYLGPJUeTkGumastS1fdcpigPbw4ASyo9SSzPAsJtdi6o>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
> This message was received from outside of the Omnitracs network
>



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle

Romero, René
I see, thanks a lot for shedding light on that misconfiguration Phil.

Fit mind in fit body.


From: Phil Steitz
Sent: Wednesday, January 25, 8:33 AM
Subject: Re: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle
To: Commons Users List

On 1/24/17 4:55 PM, Romero, René wrote: > We're running commons-dbcp-1.4 and commons-pool-1.6. To me, it sounds like we're being impacted by this bug: > > https://issues.apache.org/jira/browse/DBCP-379 > > [DBCP-379] number of connections created has crossed more ... > issues.apache.org > I found that when the minIdle is configured then during loads, common pool creates more number of connections it will be greater than maxActive. > > Is it transparent application-wise to jump from these versions to the latests? Or what upgrading path do you suggest we take? > > > Thanks again. > > > > Rene Romero Benavides > Administrador de Bases de Datos para Command Center > +52(55) 11020460 Ext. 6432 | Office > > +52 1 5545664012 | Mobile > > www.omnitracs.com/mx > > Register today! www.omnitracs.com/outlook > > > ________________________________ > From: Phil Steitz > Sent: Tuesday, January 24, 2017 3:43:29 PM > To: Commons Users List > Subject: [EXTERNAL]Re: DBCP issuing more sessions than maxActive sessions in Oracle > > What versions of dbcp and pool are you running? > > Looks like 1.x from the package names. The first thing to look at > is the changelogs[1] for releases since the pool and dbcp versions > that you are running. > > Phil > > [1] for dbcp this is > https://commons.apache.org/proper/commons-dbcp/changes-report.html > There is a similar page on the pool site. > > On 1/24/17 3:02 PM, Romero, René wrote: >> Hello guys. >> >> Under what circumstances could we get more than MaxActive sessions in the database if DBCP is our only means of access to it? >> >> This is our current configuration and it's surpassing 160 Oracle sessions at times when it shouldn't go beyond 32 (our current value for MaxActive). >> >> >> DB = Oracle 11g >> >> Data access via spring famework. >> >> >> Thanks in advance. >> >> >> > p:password="${dw.db.password}" p:driverClassName="${dw.db.driverClassName}" init-method="createDataSource" destroy-method="close"> >> >> >> >> >> >> >> >> >> >> >> dw.db.url=jdbc:oracle:thin:someDB/somePass@someHost:1522:SomeInstance >> dw.db.username=someUser >> dw.db.password=somePass >> dw.db.driverClassName=oracle.jdbc.OracleDriver >> dw.db.allowed.operations=select >> dw.db.schema=1|COMMON_APP_VIEWS_01, 81|COMMON_APP_VIEWS_81 >> dw.db.maxRows=500 >> dw.db.type=ORACLE >> dw.db.validation.query=select 1 from dual >> dw.db.initial.connection.size=4 >> dw.db.max.connection.size=32 >> dw.db.minIdle.connections=4 >> dw.db.maxIdle.connections=4 The settings above will create a lot of connection churn and may be what is causing you to hit an old pool or dbcp bug. When you set minIdle = maxIdle, you are telling the pool that it needs to try to keep *exactly* that number (in your case 4) idle connections at all times. The maxIdle setting means that when there are already 4 idle connections and a 5th is returned, it is destroyed. If the idle count subsequently drops below that, since you have the evictor enabled, it will try to ensureMinIdle when it runs, creating new ones. In general, it is better to have maxIdle >> minIdle. This applies to dbcp2 as well. Phil >> >> >> What other info would you need? >> >> >> Rene Romero Benavides >> Administrador de Bases de Datos para Command Center >> +52(55) 11020460 Ext. 6432 | Office >> >> +52 1 5545664012 | Mobile >> >> www.omnitracs.com/mx> >> >> Register today! www.omnitracs.com/outlook> >> >> >> > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [hidden email] > For additional commands, e-mail: [hidden email] > > This message was received from outside of the Omnitracs network > --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email]
Loading...