Refreshing Connection Pools

[nclement]

This is a sticky area. There are no standards for what sql exceptions mean "this connection is broken, throw it out". The jca wrappers are designed to make it very easy to write a driver specific class or extension with this knowledge in it. Also, if your app can always discard a connection whenever ANY sql exception occurs (pk violation, unique index violation, etc) you can uncomment a line in BaseManagedConnection.

Otherwise, use a very recent jboss version and invoke the flush operation by hand from the jmx console on the pool whenever the db dies.

[rkite01]

I noticed this too, but I did a little more digging on it. It seems that the idle reaper thread is the culprit. Let's say you have a pool with a minimum of five conns and an idle timeout of 1 minute (not likely I know, but bear with me cuz this is how I tested). If none of those five conns are touched for one minute, the next run of the idle reaper will drop them, but then the pool manager will immediately re-create them since we no longer have our requisite minimum of five connections.

Is this a bug? I honestly don't know. It does appear from my testing (and what I've seen in the source code) that the connections in the pool are round-robined to some degree, so this issue probably has no bearing on a live system where connections get used on a frequent basis and the idle timeout will be sufficiently high to prevent it.

Interesting behaviour though...

-chris

[davidjencks]

This is a sticky area. There are no standards for what sql exceptions mean "this connection is broken, throw it out". The jca wrappers are designed to make it very easy to write a driver specific class or extension with this knowledge in it. Also, if your app can always discard a connection whenever ANY sql exception occurs (pk violation, unique index violation, etc) you can uncomment a line in BaseManagedConnection.

Otherwise, use a very recent jboss version and invoke the flush operation by hand from the jmx console on the pool whenever the db dies.

[davidjencks]

There's also a CheckValidConnectionSQL property you can use to make jboss check connections before handing them out. Slow but effective.

[jvalldaura]

where can I configure this parameter in my mysql-ds.xml???

[bigendian]

I noticed this too, but I did a little more digging on it. It seems that the idle reaper thread is the culprit. Let's say you have a pool with a minimum of five conns and an idle timeout of 1 minute (not likely I know, but bear with me cuz this is how I tested). If none of those five conns are touched for one minute, the next run of the idle reaper will drop them, but then the pool manager will immediately re-create them since we no longer have our requisite minimum of five connections.

Is this a bug? I honestly don't know. It does appear from my testing (and what I've seen in the source code) that the connections in the pool are round-robined to some degree, so this issue probably has no bearing on a live system where connections get used on a frequent basis and the idle timeout will be sufficiently high to prevent it.

Interesting behaviour though...

-chris

[davidjencks]

even without any documentation, what do you think a parameter called IdleTimeoutMinutes might do? Now, if its set to say 15, and you see connections getting closed after being idle about 15 minutes, do you really think it's a bug?

[juhalindfors]

LOL!

(I'm sorry, I'm just here trolling the datasource forums ;-)

[bigendian]

Boy, you really have a way of fostering relationships with your potential customers.

Did you read my entire post? My comment was regarding the way the pool behaves if none of the connections have been touched during the idle timout interval and the current pool size is at or below the minimum. If I have 50 connections as my *minimum*, and I don't touch any of the 50 for the timeout interval (say, 15 minutes), they'll all get dropped and then immediately re-created again to meet the minimum. It makes more sense to me that only existing connections that are in excess of the minimum should be dropped if they meet the idle criteria. If that was not your intention when writing the code, fine, but at least have the courtesy of responding to well-intentioned questions and comments in a like manner.

[davidjencks]

OK, maybe you have a point.

I usually think of the idle timeout as a way of getting rid of connections that the database has unilaterally shut down without notice because they haven't been used in a while. From this point of view, obviously all connections idle for x minuntes should be discarded. From the point of view of getting the pool back to the minimum size, this would be unnecessary. However, if your connections aren't getting used for minutes at a time, the strain on the db server of recreating a few connections is unlikely to be severe.

[k2c]

Hi to all,

sorry about using this thread but i have a question regarding this matter but for jboss 2.4.9.

How would i solve the same problem but for jboss 2.4.9?

I think the easy solution will be the upgrade solution to the version 3.x, but i need to use the version 2.4.9.

1. Should i change the jboss source code in order to solve this problem?
2. Where ?
3. Is there another solution that i had not thought about? (i am a beguinner on this matter)

Thanks to all in advance

[vidyesh]

We are using JBossManagedConnectionPool as MBean Service with following entries where I would also like to specify "exception-sorter-class-name" as "org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter".

Question 1. How do I specify that ? There is no Setter/Getter method for ExceptionSorterClassName.
Question 2. Where do I get the above exception sorter class ?


<depends optional-attribute-name="ManagedConnectionFactoryName">

MYDS


<config-property name="ConnectionURL" type="java.lang.String">jdbc:oracle:thin:@::</config-property>
<config-property name="DriverClass" type="java.lang.String">oracle.jdbc.driver.OracleDriver</config-property>


<!--Below here are advanced properties -->
<!--hack-->
<depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper


<depends optional-attribute-name="ManagedConnectionPool">
<!--embedded mbean-->

1
10
5000
15
ByContainer


<depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager
<depends optional-attribute-name="JaasSecurityManagerService">jboss.security:service=JaasSecurityManager
<!-- java:/TransactionManager -->
<!--make the rar deploy! hack till better deployment-->
jboss.jca:service=RARDeployer