Recovery thread connection acquiring enhancement

[vladmihalcea]

In our current enterprise system we've been struggling to lower the poolingDataSource.acquisitionTimeout from the default 30s to some more appropriate value (1-5s).

For "classic" DataSource.getConnection() scenarios it was possible, thanks to using FlexyPool BTM support.

The only problem is the recovery thread, since we cannot intercept the time-out exceptions and there is no default retrying mechanisms or the possibility for using a separate connection from the regular pooled connections.

So we get:

bitronix.tm.recovery.RecoveryException: cannot start recovery on a PoolingDataSource containing an XAPool of resource dtfDataSource with 7 connection(s) (0 still available) at bitronix.tm.resource.jdbc.PoolingDataSource.startRecovery(PoolingDataSource.java:288) ~[btm-2.1.3.jar:2.1.3] at bitronix.tm.recovery.Recoverer.recover(Recoverer.java:258) [btm-2.1.3.jar:2.1.3] at bitronix.tm.recovery.Recoverer.recoverAllResources(Recoverer.java:226) [btm-2.1.3.jar:2.1.3] at bitronix.tm.recovery.Recoverer.run(Recoverer.java:142) [btm-2.1.3.jar:2.1.3] at java.lang.Thread.run(Thread.java:662) [na:1.6.0_45] Caused by: bitronix.tm.internal.BitronixRuntimeException: XA pool of resource dtfDataSource still empty after 1s wait time at bitronix.tm.resource.common.XAPool.waitForConnectionInPool(XAPool.java:423) ~[btm-2.1.3.jar:2.1.3] at bitronix.tm.resource.common.XAPool.getInPool(XAPool.java:374) ~[btm-2.1.3.jar:2.1.3] at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:123) ~[btm-2.1.3.jar:2.1.3] at bitronix.tm.resource.jdbc.PoolingDataSource.startRecovery(PoolingDataSource.java:284) ~[btm-2.1.3.jar:2.1.3] ... 4 common frames omitted

What do you think of having some fail-over options such as:

1. Add a Bitronix configuration so that the recovery connection acquire will attempt N times before giving up.
2. Add a Bitronix configuration so that the recovery can use a dedicated JDBC connection that's never served trough DataSource.getConnection().

[lorban]

Hi,

I understand your concern, but I think your proposed solutions are just workarounds:

• Add a Bitronix configuration so that the recovery connection acquire will attempt N times before giving up.

That sounds a bit silly to me. By doing that, you're just pushing harder to get hold of a scarce resource, if that ever works (which if it does would be plain luck IMHO) that will simply be detrimental to your other threads using the connection pool. And since the recoverer already runs periodically, there is no need to perform any kind of retry within it: when it fails, it doesn't matter as the next time it runs it will attempt to do the same work over again.

• Add a Bitronix configuration so that the recovery can use a dedicated JDBC connection that's never served trough DataSource.getConnection().

Why on earth would you want to do that? You've used your Flexypool tool to reduce the connections count to a bare minimum to make the most of the available resources, but now you're thinking about reserving one connection that will only be used briefly a couple times per minute. Isn't that counter-productive?

I'm tempted to say that you should simply grow your connection pool's max connections limit and be done with it.

Ludovic

On Fri, May 9, 2014 at 11:04 AM, Vlad Mihalcea notifications@github.comwrote:

In our current enterprise system we've been struggling to lower the poolingDataSource.acquisitionTimeout from the default 30s to some more appropriate value (1-5s).

For "classic" DataSource.getConnection() scenarios it was possible, thanks to using FlexyPool BTM supporthttp://vladmihalcea.com/2014/04/30/professional-connection-pool-sizing/ .

The only problem is the recovery thread, since we cannot intercept the time-out exceptions and there is no default retrying mechanisms or the possibility for using a separate connection from the regular pooled connections.

So we get:

bitronix.tm.recovery.RecoveryException: cannot start recovery on a PoolingDataSource containing an XAPool of resource dtfDataSource with 7 connection(s) (0 still available) at bitronix.tm.resource.jdbc.PoolingDataSource.startRecovery(PoolingDataSource.java:288) ~[btm-2.1.3.jar:2.1.3] at bitronix.tm.recovery.Recoverer.recover(Recoverer.java:258) [btm-2.1.3.jar:2.1.3] at bitronix.tm.recovery.Recoverer.recoverAllResources(Recoverer.java:226) [btm-2.1.3.jar:2.1.3] at bitronix.tm.recovery.Recoverer.run(Recoverer.java:142) [btm-2.1.3.jar:2.1.3] at java.lang.Thread.run(Thread.java:662) [na:1.6.0_45] Caused by: bitronix.tm.internal.BitronixRuntimeException: XA pool of resource dtfDataSource still empty after 1s wait time at bitronix.tm.resource.common.XAPool.waitForConnectionInPool(XAPool.java:423) ~[btm-2.1.3.jar:2.1.3] at bitronix.tm.resource.common.XAPool.getInPool(XAPool.java:374) ~[btm-2.1.3.jar:2.1.3] at bitronix.tm.resource.common.XAPool.getConnectionHandle(XAPool.java:123) ~[btm-2.1.3.jar:2.1.3] at bitronix.tm.resource.jdbc.PoolingDataSource.startRecovery(PoolingDataSource.java:284) ~[btm-2.1.3.jar:2.1.3] ... 4 common frames omitted

What do you think of having some fail-over options such as:

1. Add a Bitronix configuration so that the recovery connection acquire will attempt N times before giving up.
2. Add a Bitronix configuration so that the recovery can use a dedicated JDBC connection that's never served trough DataSource.getConnection().

— Reply to this email directly or view it on GitHubhttps://github.com/bitronix/btm/issues/24 .

[vladmihalcea]

Hi,

It really depends on the connection usage patterns. Thanks for clarifying the recovery behavior. I thought recovery should take precedence over existing transactions.

1. The retry is actually happening in the XAPool.getConnectionHandle while(true) loop that takes keeps on attempting until it hits the timeout threshold. Indeed an extra retry mechanism is useless.
2. I thought the recovery exceptions require more priority, since we'd like old transactions to be run before many new more arrive.

My idea was not to always hold a db connection for recovery. I thought of allowing recovery to open an extra connection when it can't get one from the pool.

Since it's just one connection per DataSource, that won't have a huge impact on the whole connection usage pattern.

When the recovery is done, the connection could be simply closed. But since the recovery is a periodic process, it can simply succeed when the traffic spike is over.

Growing the pool is an easy solution but it's challenging when you have a limited number of db nodes (1 master, 1 slave) and a great deal of db consuming apps (web nodes, schedulers, batch processors), all competing for the limited db connections.

Now if we consider a cloud auto scaling feature (that adds app nodes on demand) you can understand my worries.

Even if we can always add slave(read-only) nodes, for a master-slave environment you might be stuck with a single master(write) node.

That's why you want your apps to strive for a lower connection pool size, so techniques like lowering transaction durations help a lot.

Vlad