• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

connection pooling issue in production environment -

 
Ranch Hand
Posts: 782
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,

Our application servers are expecting to receive 800 concurrent request and for each request we have to fetch data from oracle db.

Oracle DB is configured such that it can give application server maximum of 40 connections from its connection pool

My question is how we can handle 800 concurrent request with available connections (40) from pool ?

Right now, our JDBC code gets connection in every method and closes it in same method in any case (closing connection in finally block - implemented proper connection closing pattern)

Please guide for this scenario.

Bye,
Viki.

 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You will be able to handle 40 concurrent requests (if there are no other limiting pieces in your application and architecture). The other requests will wait until connections are returned to the pool.
Did you get a requirement of how soon the 800 concurrent requests have to be finished?
 
Vikrama Sanjeeva
Ranch Hand
Posts: 782
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Jan Cumps wrote:You will be able to handle 40 concurrent requests (if there are no other limiting pieces in your application and architecture). The other requests will wait until connections are returned to the pool.
Did you get a requirement of how soon the 800 concurrent requests have to be finished?



The other requests will wait until connections are returned to the pool.

I want to know how request will wait ? Means
1: user has submitted request
2: application received request, but no connection available

Now how long user/request will wait ? Where we can set the waiting time ? In my case, I am getting ReadTimeOut error when no connections available.

Did you get a requirement of how soon the 800 concurrent requests have to be finished?
This is good idea. I will try to find average time spend on each request.


Bye,
Viki.
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Vikrama Sanjeeva wrote:... I want to know how request will wait ? Means
1: user has submitted request
2: application received request, but no connection available

Now how long user/request will wait ? Where we can set the waiting time ? ....

Depends on the connection pool library you are using. For the Apache Commons DBCP, this is set by the maxWait parameter.



Vikrama Sanjeeva wrote:... In my case, I am getting ReadTimeOut error when no connections available....

Can you give us the details on that error?
 
Vikrama Sanjeeva
Ranch Hand
Posts: 782
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Jan Cumps wrote:Depends on the connection pool library you are using. For the Apache Commons DBCP, this is set by the maxWait parameter.



We are not using any specific library at application level for connection pooling. We are using JDBC and doing Lookup on database which is maintaining a pool of connections.


Jan Cumps wrote:Can you give us the details on that error?



Ok, Its my mistake. Actually for testing purpose we made 2 connections available in db connection pool and simulated 3 simultaneous request. 3rd request got error: SEVERE: java.sql.SQLException: Attempt to use a closed handle : 'oracle_jdbc_driver_LogicalConnection_Proxy@aaa205'.
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Vikrama Sanjeeva wrote:...We are not using any specific library at application level for connection pooling. We are using JDBC and doing Lookup on database which is maintaining a pool of connections.

That lookup you do is to find a database connection pool that is configured somewhere (in your application sever maybe?) .


Vikrama Sanjeeva wrote:...3rd request got error: SEVERE: java.sql.SQLException: Attempt to use a closed handle : 'oracle_jdbc_driver_LogicalConnection_Proxy@aaa205'. ...

Can you show us the outline of how you get the connection, what you do with it, and how you return it to the pool?
 
Vikrama Sanjeeva
Ranch Hand
Posts: 782
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Jan Cumps wrote:That lookup you do is to find a database connection pool that is configured somewhere (in your application sever maybe?)



We are using oracle application server and I see there is a place where timeout settings can be set. Please see snapshot.


Jan Cumps wrote:Can you show us the outline of how you get the connection, what you do with it, and how you return it to the pool?



Ok I am mentioning here code snippets from our different classes. It not exact copy paste so ignore compile time errors:

app_settings.png
[Thumbnail for app_settings.png]
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Do you hold a reference to the pooled connection somewhere in your class?

In that case, if you call: if (connection == null || connection.isClosed() ...

The connection will not be null, because it still exists in the pool, and you don't set the variable to null after closing..
The connection is not closed, because calling close() on a pooled connection does not close the connection, but returns the still open connection to the pool.


 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You have a finally block without a try block. Does that ever execute?
 
Vikrama Sanjeeva
Ranch Hand
Posts: 782
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Jan Cumps wrote:Do you hold a reference to the pooled connection somewhere in your class?

In that case, if you call: if (connection == null || connection.isClosed() ...

The connection will not be null, because it still exists in the pool, and you don't set the variable to null after closing..
The connection is not closed, because calling close() on a pooled connection does not close the connection, but returns the still open connection to the pool.

You have a finally block without a try block. Does that ever execute?



We have different methods for DB accessing. Each method gets new connection by calling:


and closes connection by calling:

where:

connection: is local variable of each method, therefore its scope is finished as soon as method finishes and thus object connection is available for GC (my understanding)
JDBCCloser.close(connection): is suppose to close connection and return the connection back to the pool. This is JDBCCloser.close(con...) code: Please see note.



Yes, we are calling try before finally. I just skipped try part and highlighted finally to emphasize that all resources are ensured to be closed by finally block.
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

...

  • connection is available for GC (my understanding)
  • JDBCCloser.close(connection): is suppose to close connection and return the connection back to the pool. This is JDBCCloser.close(con...)
  • ...

    Answer to both points is: no.

    1: The connection is not available for garbage collection. It is returned to the pool, and will remain a living object.

    2: calling close() on a pooled connection does *not* close it. It sends the connection back to the pool, without closing it.
     
    Vikrama Sanjeeva
    Ranch Hand
    Posts: 782
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Jan Cumps wrote:

    ...

  • connection is available for GC (my understanding)
  • JDBCCloser.close(connection): is suppose to close connection and return the connection back to the pool. This is JDBCCloser.close(con...)
  • ...

    Answer to both points is: no.

    1: The connection is not available for garbage collection. It is returned to the pool, and will remain a living object.

    2: calling close() on a pooled connection does *not* close it. It sends the connection back to the pool, without closing it.



    Do you think connection = null after connection.close() will close the connection instead of sending it back to the pool ?
    If yes, then, what is the better strategy w.r.t performance (i) do connection = null after connection.close() (ii) just do connection.close()


     
    Jan Cumps
    Bartender
    Posts: 2661
    19
    Netbeans IDE C++ Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    No. You don't want to *really* close the connection. That is the whole point of a connection pool. It is a pool of open connections.
     
    Vikrama Sanjeeva
    Ranch Hand
    Posts: 782
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Jan Cumps wrote:No. You don't want to *really* close the connection. That is the whole point of a connection pool. It is a pool of open connections.



    In other words, you are saying in connection pooling one should not do connection=null and connection.close() is enough ?
     
    I need a new interior decorator. This tiny ad just painted every room in my house purple.
    a bit of art, as a gift, the permaculture playing cards
    https://gardener-gift.com
    reply
      Bookmark Topic Watch Topic
    • New Topic