Solved

A good resource for optimizing JDBC connections?

Posted on 2003-11-21
4
597 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
  • 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

-Xmx and -Xms are the two JVM options often used to tune JVM heap size.   Here are some common mistakes made when using them:   Assume BigApp is a java class file for the below examples. 1.         Missing m, M, g or G at the end …
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now