Database connection pools config when each JDE user has unique database account

[Larry Williams]

We are setup with each JDE user having a unique database account. This is required for auditing purposes. My understanding is that this effectively nullifies the benefiits of JDE database connect pools.
Does anyone have any ideas on what should be set in the jdbj.ini to maximize data access times?
What about minConnection, maxConnection, poolGrowth, initialConnection, connectionTimeout, cleanPoolInterval and maxSize in the jdbj.in.
How should these be coordianted with similar parameters in the enterprise server jde.ini and Websphere?

 

[78aspide78]

How many users are you running?

 

[Larry Williams]

We have around 250 concurrent users. However, we have sized the hardware to support 500 concurrent users.

 

[BOster]

We do the same thing and initially had a lot of problems during our upgrade project from Xe to 9.0. Effectively what happens is each user becomes a connection pool - which of course negates all the benefits of a connection pool. I am in development so our CNC group was obviously WAY more involved in sorting this all out, but if I remember correctly the key change was to reduce the number of connections per pool.

 

[ice_cube210]

Larry,


Recommended values from an Oracle document I came across recently

minConnection=5 (default) (for unique proxy users set to 0)
maxConnection=num of max Concurrent Users (for unique proxy users set to 5)
initialConnection=5 (default) (for unique proxy users set to 1)
poolGrowth=10 (default is 5) (for unique proxy users set to 1)



This topic was discussed in a different context (debugging issues) in this thread here


Post 181705

 

[78aspide78]

Well, if your machine is over sized and your custom interactive applications are good (they have the business logic with IO operation inside BSFN) you can perform well too.

What I found is that DBA wants single db user only to track issues but connections management should be tuned for performance (issues should be resolved before and investigated with specific strategy).

About Oracle suggested settings for jdbc pool.... it's only fantasy. In your test you need to track the connections, monitor the pool and tune it.

 

[jdelisths]

We got this working at one of our clients with these settings:

[JDBj-CONNECTION POOL]
minConnection=0
maxConnection=200
initialConnection=1
poolGrowth=1
connectionTimeout=1800000
cleanPoolInterval=600000
maxSize=50
cachePurgeSize=5

They are on iSeries and it works without excessive connections. Your database server should be scaled for at least 250 * 4 = 1000 concurrent connections. This can take up significant resources on your database servers (mostly memory). Hope this helps.

 

[Larry Williams]

Thanks everyone for your help.
Bruno, I wonder why you have 200 max connections as my understanding is that each user session represents a unique db connect pool that cannot be shared with other sessions and that each session will make on average 5 connections to the various datasources.
We have the following settings:
[JDBj-CONNECTION POOL]
minConnection=0
maxConnection=5
poolGrowth=1
initialConnection=1
connectionTimeout=1800000
cleanPoolInterval=300000
maxSize=50
cachePurgeSize=5

However, with these settings in place, we have seen sever instances of the following connection errors in the e1root logs:
com.jdedwards.database.impl.physical.JDBMaxPoolSizeException: [DATABASE_CONNECT_FAILED] Database Connection failed for DataSource Control Tables - Test
So, perhaps I need to increase the max connections?
We also have the QUSRWRK sbs QZDASOINIT prestart job set at 1700 initial jobs (used for remote db connections from windows web server) and the JDEE910 sbs QSQSRVR prestart job set to 50 initial jobs (used by kernel jobs).

 

[78aspide78]

Never said that.

 

[Larry Williams]

Sorry Bruno, I meant to say Hari who's post I was replying to.

 

[ice_cube210]

Since the connections are pooled at the datasource level then may be you want up it to 7 (System , OL ,DD , Central Objects, Versions , Business Data , Control Tables) plus a few more if you have multiple server maps. , so may be 10 to be safe.