E812 not dropping unique DB connections

[KSI]

We just started with unique DB system users for every JDE user account. We are using OAS 10.1.3.4.0. E1 is not dropping the DB connection when users log out.

Result is that connections into SQL 2005 keep rising until the JVMs decide to restart (at about 4200 DB connections.) Currently, our 2 JVMs (75 max users each) are restarting once a shift (we are 24/7 follow the sun).

All of our tests could not get JDE to drop these connections upon user logout. Database folks say it is an application issue.

SM Tools 8.98.0.1 settings we've tried so far:
JDBj Connection Pools
Min connections=0
Pool Growth size=1
Initial connections=1 (this seemed to reduce the number of connections per user, but not persistance. Default was 5.)

Also tried KeepAlive=off in http config file.

HTTP, JAS, and JDBj files zipped and attached.

Any help is appreciated,
KSI

 

[cdawes]

This is working exactly as designed.

The Web Server maintain a connection "pool" and this pool is controlled at the level at which you assign database authentication.

If you use one proxy ID for all users then you will have one pool of "X" number of connections.

If you use one proxy id per user then you will have one pool to manage for each user. ie if you have 50 users then you will have 50 pools.

If you monitor a single login each user creates about 8 database connections when they log on so 50 users would create a minimum of 400 connections (most of which will be totally wasted).

Math comes into play when you look at the JDBJ initial connections:

EXAMPLE #1
JDBj Connection Pools
Min connections=0
Pool Growth size=1
Initial connections=1
Max connections=10

EXAMPLE #2
JDBj Connection Pools
Min connections=10
Pool Growth size=5
Initial connections=10
Max connection=150

For example #1 if you have 50 users using a SINGLE proxy account then you end up with:

JDBj Connection Pools
Min connections=0
Pool Growth size=1
Initial connections=1
Max connections=10 --> INCREASES AS MORE USERS COME ON THE SYSTEM

This may give users a slower initial response time as many connections will need to be created which takes time, memory and CPU

For example #2 if you have 50 users using a single proxy account then you end up with:

JDBj Connection Pools
Min connections=10 x 1 PROXY USER = 50 CONNECTIONS
Pool Growth size=5
Initial connections=10 x 1 PROXY USER = 50 CONNECTIONS
Max connection=150 x 1 PROXY USER = 150 CONNECTIONS

NOW LETS REPEAT USING ONE TO ONE PROXY ACCOUNTS:

For example #1 if you have 50 users using INDIVIDUAL proxy account then you end up with:

JDBj Connection Pools
Min connections=0 x 50 PROXY ACCOUNTS = 0 CONNECTIONS
Pool Growth size=1
Initial connections=1 x 50 PROXY ACCOUNTS = 50 CONNECTIONS
Max connections=10 x 50 PROXY ACCOUNTS = 50 CONNECTIONS

In the above example (similar to what you have) the only thing I've added is a max connection of 10. This is more than the 8 connections a user needs to log on but it sets an upper limit per user and forces connection reuse. This is the only way you can control this if using one to one proxy accounts.

For example #2 if you have 50 users using INDIVIDUAL proxy account then you end up with:

JDBj Connection Pools
Min connections=10 x 50 PROXY USER = 500 CONNECTIONS
Pool Growth size=5
Initial connections=10 x 50 PROXY USER = 500 CONNECTIONS
Max connection=150 x 50 PROXY USER = 7500 CONNECTIONS (only goes as high as needed)


My first question is why switch to individual proxy accounts (DB system users). Unless there is a really good reason it might be best to use shared connection pools instead of individual connection pools. This will conserve precious memory.


Colin

 

[klwong]

What is your setting for resultSetTimeout under [JDBj-RUNTIME PROPERTIES]? If it is -1, that will hold connections open forever.

 

[Joel]

KSI,
Have you created unique proxy id's for each user i.e. if u have 50 users then 50 proxy id ? If yes then the issue you are experiencing is possible because even though the users have logged out of JDE they the database connection is still maintained between the database server and OAS.

What is the reason behind this design ... if its SOX controls etc then there is a better way of doing this. From a future capacity planning perspective and managing security your design will cause problems

 

[KSI]

Thank you for the replies so far. We have a business need to use 1-to-1 connections.

Colin, Your math is accurate. Currently, users are connecting with min connections to each of 3 databases. Reducing the min connections lowers the total number, but not the persistance of those connections.
Your note on max connections is valuable, thanks.

Ken, our result set timeout on the web server is set to 60000 ms.

Joel, the reason is a business need. Prior solutions we investigated (including JDE's 21CFR11) were rejected for business reasons.

If I can get the unique connections to drop in a timely manner, our current DB capacity will meet our needs.

 

[klwong]

Next question: What Service Pack/Cumulative Patch level are you on with SQL Server 2005?

 

[Joel]

When you look into the JDBJ connection settings of your JAS server from server manager there is one called Pool Cleaning Interval. I'm not too sure but I think it should drop unused connections from the pool. This is clearly not hapening in your case ...just verify that your value is not set very high. default is 10 mins... Could be something to do with ur tools release.

 

[KSI]

Thank you for the input. We did get this working finally.
Key for us was minConnections=0, and adjust the JDBj Connection Pools settings to timeout and clean.

We now get ~900 connections with 150 users.

 

[cdawes]

Glad this worked out.

I'd strongly recommend SQL Server 2005 - 64 bit.

If you're not running 64 bit but instead 32-bit and using user based security with this number of concurrent users you'll start to see pressure in the 4GB space.

Eventually you'll see lost of page swamps, increased disk queue length.........etc.

Running on 64 bit will make all your issues vanish.


Colin

 

[Joel]

Hi KSI,

Just wanted to confirm how you got your JDBJ connection pools to timeout and clean. Did you set the Pool Cleaning Interval in JDBJ ?

 

[KSI]

Joel,
It's a stanza in the JDBJ.ini file on the WEB server.
[JDBj-CONNECTION POOL]
minConnection=0
connectionTimeout=1200000
cleanPoolInterval=300000

We use Server Manager. It's in the JDBJ Database Configuration page of the web server under 'JDBj Connection Pools'.

Good luck.
KSI