Solved

A good resource for optimizing JDBC connections?

Posted on 2003-11-21
4
603 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 300 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Most of the developers using Tomcat find it easy to configure the datasource in Server.xml and use the JNDI name in the code to get the connection.  So the default connection pool using DBCP (or any other framework) is made available and the life go…
There are numerous questions about how to setup an IBM HTTP Server to be administered from WebSphere Application Server administrative console. I do hope this article will wrap things up and become a reference for this task. You need three things…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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