Since the DataSouce connection pool size is set to a minimum of three, it seems that a database connection is not considered dead until all thread pool connections are timed out. The timeouts take a little over two minutes to complete, and if the minimum thread pool size was larger, the failover time would be protracted even more. Is there any way to reduce the duration of the timeouts and/or speed up this process?
What you are talking about is the notion of a 'purge policy' wherein pooled connections are destroyed based upon a certain condition without waiting for them to be 'validated'. I have a working experimental branch with this functionality. My plan is to have this in place for JBoss 4.2.
Note, one of the reasons for the behavior that you are seeing is that prior to 4.0.5, JBoss/JCA validated a connection prior to removing it from the pool for *each* getConnection attempt. As a result, the entire pool of connections had to be exhausted prior to attempting to obtain a new connection. This is the 'slow' failover you are seeing being that every connection has to be checked.
With 4.0.5 background connection validation has been added where connection validation occurs in a background thread. As a result, connection validation for getConnection() can be disabled. Enabling background validation casues the validator to run and periodically removing invalid connections from the pool. While this does not address your problem specifically, choosing background validation can give you the option to disable the validate on match behavior.
The issue in destroying the entire pool when a validation error occurs is that the condition may be temporary (ie network glitch, transient DB failure). Destroying the entire pool when this happens can ultimately become quite expensive.
The purge policy discussed earlier will destroy the entire pool during a connection *failure*, not on validation. Have you considered setting the socketTimeout or experimenting with the other JDBC level properties to see if you can get the wait time down?
With 4.0.5 background connection validation has been added where connection validation occurs in a background thread.
We are currently using 4.0.4, I will look into using 4.0.5.
Have you considered setting the socketTimeout or experimenting with the other JDBC level properties to see if you can get the wait time down?
I have not been able to find a DataSource option that configures TCP/IP timeout, but I did try setting <query-timeout> to 1. I am still looking for some possible Connector/J options to tweak.