[opencms-dev] Performance Tuning Questions

[Christian Steinert]

Brett Sheeran wrote:
> [snip]
> I set testOnBorrow=true and can report the following positive and
> negative changes:
> -Positive change: the number of active connections does *not* steadily increase.
>   
ok - so then we know a bit better what is going on - either there are 
more connections dying than can be cleaned up by your eviction settings 
and/or eviction alone is not enough to really guarantee stable behavior 
for you
> -Negative change: the *maximum* page load times increase by 60%
>   
hmm - sorry to hear that.

Try to use an even simpler test query like "SELECT 1". That should at 
least work with Mysql and might speed things up a little again.

I would expect that you will still noticably lose performance, because 
the main cost in checking your connections each time will probably not 
come from the complexity of the query but rather due to the latency of 
the roundtrip between opencms and the DB.

Alternatively, you might also try to increase the frequency of eviction 
runs and the count of connections that are inspected per eviction run (I 
think to remember that you had 3 connections inspected at once with a 
rather big time span in between).

I don't know whether more frequent eviction (and I mean a LOT more 
frequent) alone will be enough for you - it should be worth trying three 
things
1 - how is performance with testOnBorrow and "SELECT 1" as test query, 
but without eviction?
2 - how is performance and behavior with more frequent eviction but 
without test query? (you will obviously have to experiment with eviction 
times and batch sizes)
3 - how is performance with testOnBorrow AND a semi-frequent eviction, 
while using SELECT 1 as test query?


I would expect that 2 is fastest, if you can get away with it. If that 
does not work reliably, then 1 is probably the next best choice. But 
it's still worth to try 3, because I just assume rather than know that 
latency might be main performance penalty in connection checking.
___

Also, mysql itself has a special query for connection checks. If I 
remember correctly, then the mysql driver in newer releases can sense 
whether it is still connected and can then answer this special query 
directly without really sending to the DB server. This would mean that 
with the right test query, there would be no network communication at 
all to ensure that the query is alive. I only read this in passing and a 
while ago, so take this last bit with a grain of salt.
Sadly, I have forgotten how this test query is called and a bit of 
digging has not yielded any result. It think that it was some special 
kind of comment that had to be inserted into the middle of a SELECT 
statement. Take a look at http://www.mysqlperformanceblog.com/ you might 
find information about this there.
If the behavior that I think to remember does indeed exist, then this 
special query would be a lot cheaper than SELECT 1 and would probably 
make testOnBorrow perform very well.


Good hunting!
Let us know how things turn out.

Christian