Re: [Proxool-developer] Proxool hang when there's a connection problem to database
UNMAINTAINED!
Brought to you by:
billhorsman
|
From: Bill H. <bi...@lo...> - 2007-08-06 07:38:56
|
Hi Ivan,
On 08/03/2007 04:56 AM, Ivan Yiu wrote:
> I've start a simple program with a single thread getting connection
> from the pool, execute a query and close the connection within an
> infinite loop. Then I try to disable the connection from the server
> host to the database, the application hang until all connection are
> consumed and the Oracle driver throw the "The Network Adaptor could
> not establish the connection ... " exception. It takes more than 5
> minutes
>
> Seems that the housekeep will not kill the connection and pass the
> control back to the application until the network connection timeout
> occur.I wonder if there's any way for me to solve this problem, why
> the housekeeper not working? It's expected the housekeep will kill
> the connection since maximum-active-time is 20 seconds only. Same
> problem occur when i disconnect the DB before getting the connection
> from pool using the getConnection() method or before executing the
> query.
The house keeper seems to be working as designed, although I can see why
this isn't what you want. Every minute (as you have configured it) the
house keeper will look at each connection and see whether it is still
working. I'm guessing that each connection eventually times out and
throws an exception, at which point it will be discarded. That could
take a while. Actually, it doesn't run every minute - more correctly it
sleeps for one minute between runs. So if each run takes a long time
then it won't run very often.
The maximum-active-time only relates to how long a connection can be
borrowed from the pool for. It doesn't relate to how long the house
keeper will wait for it to respond to the test SQL. True, if a
connection is being used and the housekeeper notices that it has been
used for over 20 seconds it will discard it immediately, but that can
take a while to happen (maybe the house keeper isn't running or it's
waiting for a timeout whilst testing a different connection).
One improvement we could make to the house keeper is to be less patient
when testing a connection. If it doesn't run the test SQL within a
configured time (default to 10 seconds?) then the connection is dumped.
Even when the house keeper has dumped all the connections then every
time you ask for a new one then it will attempt to make one. If that
also causes a time out (rather than an immediate exception) then that
will continue to happen. Proxool doesn't really know the database has
been disconnected and it doesn't know whether exceptions are immediate
or because of a timeout.
If you can write a bit of code that will test a connection but get
impatient if it takes too long then I can see about incorporating that
into the house keeper. FYI, the current code looks like this
(HouseKeeper.java:76-83):
boolean testResult = false;
try {
testResult = testStatement.execute(sql);
} finally {
if (log.isDebugEnabled() &&
definition.isVerbose()) {
log.debug(connectionPool.displayStatistics() + " - Testing connection "
+ proxyConnection.getId() + (testResult ? ": True" : ": False"));
}
}
The only way to be impatient about executing a statement is to kick it
off in a separate thread and the reason we haven't done this so far is
partly because we try not to create too many threads. But I suppose if
it only did it if a property called "house-keeper-patience-time" was set
then it would be OK.
Cheers,
Bill
|