[opencms-dev] Performance Tuning Questions
christian_steinert at web.de
Thu Jul 23 07:27:17 CEST 2009
Brett Sheeran wrote:
> 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
> -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
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.
Let us know how things turn out.
More information about the opencms-dev