What is true definition of "acquisition timeout" property?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

What is true definition of "acquisition timeout" property?

chrisjr
Hi,

I've been trying to tune my BTM pool for resilience, and one of my more important test cases is "What happens if one of the database nodes becomes unavailable?". I've been simulating this by firewalling one node off while the application is under load, and seeing what happens.

I have set acquisitionTimeout=20; the Javadoc and my reading of XaPool.getConnectionHandle() suggests that this means that my application will try for 20 seconds to obtain a valid connection from the pool before throwing an exception. However, I have discovered that my application fails the firewall test unless I also set this connection property:

oracle.jdbc.ReadTimeout = 5000

This connection property means that JDBC4's Connection.isValid() function will timeout after 5 seconds, which in turn means that BTM has time to test several connections in the pool before the acquisition timeout expires. However, I'm not particularly happy with this because oracle.jdbc.ReadTimeout affects *all* network reads, and so could be problematic with some slower queries.

I then examined how BTM uses Connection.isValid(), and discovered this line in JdbcPooledConnection.testConnection():

isValid = (Boolean) isValidMethod.invoke(connection, new Object[]{new Integer(poolingDataSource.getAcquisitionTimeout())});

This reuse of the acquisition timeout surprised me - you are effectively allowing BTM enough time to test only *one* connection here! Wouldn't you prefer to use a separate timeout parameter, e.g.

jdbc4ConnectionTestTimeout = 5

You obviously need some explicit timeout here, and the acquisition timeout seems like a reasonable fall-back choice. But my application would definitely like to use a lower value.

Have I misunderstood anything here, please?

Thanks for any assistance,
Cheers,
Chris
 
Reply | Threaded
Open this post in threaded view
|

Re: What is true definition of "acquisition timeout" property?

Brett Wooldridge-2
I'm not sure what you're really simulating here (i.e. how realistic it is) using a firewall.  If a network connection is severed, why would four tries of 5 seconds be any more likely to succeed than one try of 20?  If we're going to make a change, I would be more inclined to eject idle connections from the pool if isValid() fails with a timeout exception.  Pool recovery would be faster.


Reply | Threaded
Open this post in threaded view
|

Re: What is true definition of "acquisition timeout" property?

chrisjr
> If a network connection is severed, why would four tries of 5 seconds be any more likely to succeed
> than one try of 20?

Because our JDBC URLs contain multiple hosts, e.g.

jdbc:oracle:thin:@(DESCRIPTION = (enable=broken)(ADDRESS = (PROTOCOL = TCP)(HOST = oracle001.node.net)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = oracle002.node.net)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = oracle003.node.net)(PORT = 1521)) (LOAD_BALANCE = on) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = thingy)))

The firewall test simulates the case when only one of these nodes stops responding, in which case we would expect to "fail over" as quickly as possible to one of the others. (Or more correctly, iterate through the pool until we find an available connection to one of those other nodes).

> I would be more inclined to eject idle connections from the pool if isValid() fails with a timeout exception.

Hmm, I haven't actually observed isValid() failing with a timeout exception. It still returns false even when oracle.jdbc.ReadTimeout expires.

Cheers,
Chris
Reply | Threaded
Open this post in threaded view
|

Re: What is true definition of "acquisition timeout" property?

chrisjr
In reply to this post by Brett Wooldridge-2
> If we're going to make a change, I would be more inclined to eject idle connections
> from the pool if isValid() fails with a timeout exception.

But isn't this (effectively) current behaviour? If isValid() returns false then testConnection() throws an SQLException. XAPool.getConnectionHandle() then catches this exception and calls xaStatefulHolder.close(), which in turn makes the JdbcPooledConnection unregister itself from the PoolingDataSource before closing the SQL and XA connections.

Mind you, it would be "nice" if closing both of those connections could happen asynchronously. With Oracle at least, both of those operations tend to block when there are network difficulties. I *definitely* need to set oracle.jdbc.ReadTimeout to something > 0, but I'm worried that 5000 is too low. Especially since Connection.isValid() already supports a timeout parameter of its very own and so doesn't need to depend upon oracle.jdbc.ReadTimeout.