• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Need help on optimizing JDBC configurations

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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

 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
here client not ready to spend a penny as recession continues.... we are working on it, any suggestion or solutions higly appreciated
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic