[opencms-dev] Performance Tuning Questions

[Christian Steinert]

Dear Brett
> Also, a *huge* thankyou to Christian Steinert for taking the time
> to write such a deeply detailed and very informative explantion on
> OpenCMS caching. 
>   
If you like what I wrote, then please write it up a bit more nicely and 
elaborately and put it on opencms-wiki.org.

In this way, others would be able to find this information more easily.
Thanks.
> Unfortunately, after extended periods under load, I am experiencing
> SQL errors and what appears to be thousands of leaking/lost database
> connections . Can anyone tell me what I can do about these issues
> please? Details as follows.
>
> I simulated 1000 test users each loading a page once every 15 seconds.
> After several hours, page errors started to occur and OpenCMS
> generated SQL errors similar to this (abridged) message:
>   
[...]
> The number of active connections was a *staggering* 3691. 
I see - I cannot imagine that this is right for 1000 simulated users 
under normal conditions.
> Usually,
> there are only 0, 1 or 2 active connections. Days after halting the
> performance tests, the number of active connections has not come down.
> Since there is currently no load on my system (and presumably no
> *real* active connections), I presume these connections are in fact a
> massive leak.
>   
You suffer from dying connections in the DB pool.
You need to tell opencms that it should test your connections when 
taking them out of the pool and using them again. This is done through a 
few settings in opencms.properties. To quote from a mail from Achim 
Westermann:

You should turn on eviction in opencms.properties. This has been added 
to opencms.properties. This has been added round about 07/07:

# connections will be validated by evictor thread
db.pool.default.testWhileIdle=true

# number of milliseconds to sleep between runs of the evictor thread
# -1 means no idle connection evictor thread will be run
db.pool.default.timeBetweenEvictionRuns=3600000

# number of connections tested in a run of the evictor thread
db.pool.default.numTestsPerEvictionRun=3

# minimum amount of time in milliseconds a connection may be idle in the 
pool before it is eligable for eviction
db.pool.default.minEvictableIdleTime=1800000

# the query to validate connections
db.pool.default.testQuery=SELECT STRUCTURE_ID FROM CMS_OFFLINE_STRUCTURE 
WHERE RESOURCE_PATH = '/'


> Incidentally, I am not querying the database directly.
>   
What do you mean by that? Do you mean that you queried opencms rather 
than the DB for how many connections are open? - In that case, I would 
expect that the DB gives much lower figures for the amount of live DB 
connections and that some connections to which opencms is still holding 
on have actually been closed by the DB.

Hope that helps
Christian