This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Websphere and the fly likes Need help on optimizing JDBC configurations Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Websphere
Bookmark "Need help on optimizing JDBC configurations" Watch "Need help on optimizing JDBC configurations" New topic
Author

Need help on optimizing JDBC configurations

Anantha Reddy
Greenhorn

Joined: Jun 24, 2008
Posts: 12
Hi,

We have deployed an Enterprise application 4 application servers which are pointed to single Database Schema, load will be distributed on these application servers through netscalar.

Here problem is in Database statistics are showing too many inactive sessions around 1450 ( 1500 Max session limit set), once it reached to maximum limit DB not allowing to login/open a new session.

1. how to optimize JDBC configurations to reduce no of inactive sessions?
2. what all needs to be considered while configuring Datasources (JDBC connection pooling)?
3. what is the criteria to define each parameter?

Our application using both Datasource and Datasource(vesrion 4) JNDI names for connection pooling.

DataSource Settings
>Connection timeout : 600 sec
>Maximum connections : 500
>Minimum connections : 50
>Reap Time :180 sec
>Unused Timeout :600 sec
>Aged Timeout :900 sec
>Purge Policy : Entire pool

>Statement Cache Size: 300 ( is this create 300 sessions?? )

Datasource (Version 4) settings
>Minimum pool size 80
>Maximum pool size : 350
>Connection Timeout: 2000 sec
>Idel Timeout: 2000 sec
>Orphan Timeout: 2000 sec
>Statement Cache Size:300

Application user session timeout: 40mins (set explicitly).

Appserver: Websphere 6.0.2.21
JDK: IBMJDK 1.4.2


Application users are around: 2000
Daily usage: 1600 users
Concurrent users: 200 users approx..

Database Schema Profile Settings:
> Session_per_user : unlimited
> session time out : 10 mins.


I would appreciate based on above statistics how can I optimize JDBC configs for my application and how to avoid staled connection exception?.

This post contains many queries please answer every query if possible, it is very urgent as it is production issue.

One more issue:
other issue is though there are 4 application servers are going down due to Out Of Memory issue(n no of times), though there is relatively less load.

Environment: (3 servers out of 4)
Windows 2003 ent edition
32 bit
3 Gzs processor
4 GB RAM

Otherserver:
Windows 2003 ent edition
64 bit (how much maximum heap size we can set in tis en for application server?? )
3 Gzs processor
4 GB RAM

Server1 settings:
1. Transaction Service
> Total Transaction Life Timeout: 600 sec
> Client inactivity time out: 600 sec
> Maximum Transaction Timeout : 600 sec

2. JVM Settings:
> Min heap - 320
> Max Heap -1280


3. Thread Pools
Default:
>Minimum Size: 50 threads
>Maximum Size: 200 threads
>Thread inactivity Timeout: 2000 milli sec
Webcontainer:
>Minimum Size: 50 threads
>Maximum Size: 200 threads
>Thread inactivity Timeout: 3500 milli sec

Regarding Out Of Memory (OOM), we have raised n PMRs and had analyzed heap and thread dumps done optimization at application level as IBM said application creating large objects, requesting 400 mb to allocate heap for some requests etc.... we still working on it....


With above info can any one suggest what is the best configuration for the application/application server?,, how can come out of those issues or reduce up to some extent.. what else can be done in application to optimize further.

Technology used in application:
>HTML, CSS, Java Script (huge script is used)
>JSP (with huge scriptlets)
>Java
> Custom Tag libs
> EJB 2.0 (all are BMP)
>XML, XSL
> JDBC 2.0

Design Patterns: Singleton, Front Controller, Session Facade, Service Locator, DAO,VO,MVC ,Factories, Distributed Cache(one that come with websphere itself).


awaiting for your detail and valuable replies... thanks in advance.

Anantha
SCJP 1.5

Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

This post contains many queries please answer every query if possible, it is very urgent as it is production issue.


Yes, it does. Have you considered hiring a consultant to do a review of your system's architecture and configuration? Because it rather looks like that's what you are asking.
Anantha Reddy
Greenhorn

Joined: Jun 24, 2008
Posts: 12
here client not ready to spend a penny as recession continues.... we are working on it, any suggestion or solutions higly appreciated
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Need help on optimizing JDBC configurations
 
Similar Threads
Not able to generate xl sheet for 30k+ records
Test 252: Mock exam
000-341 sample test questions
Datasource connection timeouts
Performance tunning using J2EE