?
Solved

A good resource for optimizing JDBC connections?

Posted on 2003-11-21
4
Medium Priority
?
605 Views
Last Modified: 2013-12-10
Hi,

We have a fairly complex enterprise application running WebLogic 7.0sp2 with an Oracle 9i on Solaris backend.  We are trying to find the ideal DB_CONN_MIN and DB_CONN_MAX settings, but really using trial and error to get there, this is very time consuming...

Can anyone give some pointers to either resources or formulas that can be used to do this a bit more scientifically?

Thanks for any help!
-Brian
0
Comment
Question by:bgodden
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:egorelik
ID: 9812440
First of all , read JDBC performance tips here :

http://e-docs.bea.com/wls/docs70/perform/index.html

You want to set max number of connections equal to the maximum number of concurrent client connections required DB access through your application.
However, you have to make sure that your DB has enough memory to handle this number of connections.

In ideal sitiation , you would have set Initial DB connections = Max DB connections, however it can eat all your database memory if your max connections number is too high
So, you should ask DBA, how much memory you have available for JDBC connections and how much memory each JDBC connection utilize.
Based on information from DBA, you can calcutate Max number of connections you can set and Initial number of connections.
For example, If DBA says, that it's OK to utilize 1Gb of DB memory all the time and you know that each of your JDBC connections using 10 Mb of DB memory, then you can set Max conn. = Initial conn. = 1Gb/10Mb = 100.

If DBA says that he want to keep DB memory size utilized by Weblogic up to 1 Gb, , but in avarage 300 Mb, then set max connections to 100 and Init connections to 30.

After, you've set these settings, try to monitor number of connections using ( monitoring tab of JDBC connection in weblogic console ) for several days to make sure that initial maximum number of connections are sufficient and you don't need to change them.

If you see that maximum number of connections is less than you require for your app, then you have to negotiate with DBA to allow you to use more DB resources.

Also, it 's very important to make sure that application code is properly handling connections and closing them.

Also, if  


0
 

Author Comment

by:bgodden
ID: 9812991
Excellent, this is exactly the help I'm looking for!  So I'm going to ask you a couple more question if you don't mind, and I will up the points each time.  

I don't have access to the weblogic console right now...  When I do go int there for JDBC monitoring, which key values on that page will tell me how many connections are being required by the app?

Also, your answer got truncated at the end, can you finish that thought?  Thanks!  -Brian
0
 
LVL 2

Accepted Solution

by:
egorelik earned 1200 total points
ID: 9813629
I'd say that key data in JDBC connections monitoring table are:

Connections—Number of physical database connections from this instance of the connection pool that are currently in use.

Connections High—Highest number of active database connections in this instance of the connection pool since the connection pool was instantiated. Active connections are connections in use by an application.

If you see that Connections or Connections High are consistently close to maximum number of connections, then you probably need to increase max number of connections.

If JDBC pool will reach max number of connections then server most probably will run out of threads and die, so sometimes you can also go through that bad experience, before you understand what number of max connections you really need . ::)).

You can also keep your eye on :

Wait Seconds High—Highest number of seconds that an application waited for a connection from this instance of the connection pool since the connection pool was instantiated.

Waiters High—Highest number of application requests concurrently waiting for a connection from this instance of the connection pool.

If any of these numbers are consistently more than 0, then you have to improve DB performance or JDBC performance or may be application code.

I also wanted to say that don't forget to use test table in JDBC pool settings to verify connection to DB all the time.

You can also check "Test reserved connections" and "Test released connections" check boxes to make sure that there are no any connections hanging after client closed the sesion and that connections are not broken, however, it can also slow down JDBC performance a bit.

Eugene Gorelik.


 
0
 

Author Comment

by:bgodden
ID: 9813749
Thanks Eugene, this is a big help!!
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This exercise is about for the following scenario: Dmgr and One node with 2 application server. Each application server contains it owns application. Application server name as follows server1 contains app1 server2 contains app1 Prereq…
This article is about some of the basic and important steps to be used to improve the performance in web-sphere commerce application development. 1) Always leverage the Dyna-caching facility provided by the product 2) Remove the unwanted code …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question