Solved

A good resource for optimizing JDBC connections?

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 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