Jboss AS7 connection pool won't reconnect

Question

I have the following configuration in my standalone.xml:

<subsystem xmlns="urn:jboss:domain:datasources:1.1">
    <datasources>
        <datasource jta="true" jndi-name="java:/jdbc/myds" pool-name="CADS" enabled="true" use-java-context="true" use-ccm="true">
            <connection-url>jdbc:postgresql://db.host/name</connection-url>
            <driver>postgresql</driver>
            <new-connection-sql>select 1</new-connection-sql>
            <pool>
                <min-pool-size>20</min-pool-size>
                <max-pool-size>100</max-pool-size>
                <flush-strategy>IdleConnections</flush-strategy>
            </pool>
            <security>
                <user-name>user</user-name>
                <password>pwd</password>
            </security>
            <validation>
                <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
                <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
            </validation>
            <timeout>
                <blocking-timeout-millis>30000</blocking-timeout-millis>
                <idle-timeout-minutes>1</idle-timeout-minutes>
            </timeout>
            <statement>
                <track-statements>true</track-statements>
            </statement>
        </datasource>
        <drivers>
            <driver name="postgresql" module="org.postgresql">
                <xa-datasource-class>org.postgresql.Driver</xa-datasource-class>
            </driver>
        </drivers>
    </datasources>
</subsystem>

If, for some reason, the database stop responding for a second, JBoss can't reconnect and I have to restart the app server.

But, If I change the datasource to xa-datasource (keeping the config as it is in the example) using the org.postgresql.xa.PGXADataSource driver, it works.

Thing is: I can't make sense out of this. Correct me if I'm wrong, but xa-datasources are supposed to be used to synchronously commit in more than one database, and that's not the case here. I actually have more than one database configured, but I don't need to sync the transactions between them.

The "default" datasource also seems to have problems with sizing the connection pool. Sometimes, doesn't matter the load of the app, it opens more than 100 connections (even if the limit is 100) and closes them after some seconds. This is hard to reproduce - because it seems random, so, I can't tell for sure that switching to xa-datasource solves this problem too.

Now:

• why switching to xa-datasource works?
• what are the implications of doing this?
• why connection pool is going crazy like this?

---

Just to clarify, my test consists in:

1. start up postgres and the app server;
2. do some requests to the application;
3. stop the database;
4. do some requests to the application - and see that they are not working because it can't open any connections;
5. start up the database again;
6. do some requests to the application

In the last step, xa-datasource can reconnect with postgres and everything works. datasource can't, and fails forever, load doesn't matter - I have to restart the app server.

Thalassography

Answer 1

One thing to remember when configuring jboss is that sometimes the best documentation is in the projects for the individual components. In the case of datasource settings, I always tell people to check out the IronJacamar docs: http://www.ironjacamar.org/doc/userguide/1.0/en-US/html_single/

for what you want to do, these settings should work:

<validation>
    <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
    <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>

    <!-- I don't know what this does but someone on my DevOps 
    team said to set it this way. :) -->
    <validate-on-match>false</validate-on-match>

    <!-- validate the connection using a background 
    thread rather than right before you try to use the connection -->
    <background-validation>true</background-validation>

    <!-- sets the frequency the background thread will check each connection.
    The lower this setting, the quicker it will find a bad connection 
    but it will be more chatty sending the validations to the server -->
    <background-validation-millis>60000</background-validation-millis>

    <!-- fast fail will mark all the connections invalid as soon as 
    it finds a bad one. This will make it clear the pool quicker 
    if all connections are reset at once such as a restart. Fast 
    fail would be trouble though if you had a setup where the database
    sometimes selectively kills a single connection, such as killing long
    running queries. -->
    <use-fast-fail>true</use-fast-fail>

</validation>

Trilobate

The "magic" here seems to be the use-fast-fail=true, I wonder why I need all others... can you explain the rationale of your config a little bit, please? – Thalassography

I added comments about the different parameters. – Trilobate

Thanks! I tested some combinations, and the use of use-fast-fail is not required, it only will take a few more seconds to close the connections, but will work. Thanks! – Thalassography

you should not use use-fast-fail together when background-validation is set to true – Ursuline

@midTIerDeveloper why not? – Trilobate

@teacurran This is from red hat official site "The above considerations are relevant only when using the validate-on-match connection validation method. When using background-validation getConnection() requests do not block to await validation". Or you can refer to this link – Ursuline

I agree with the documentation. sometimes you still want fast fail though. if you have a big server with a lot of connections and one fails you may want to invalidate all connections immediately to take the server out of your HA cluster. the fact that it doesn't block requests just means that you may still get stale connections within your app. – Trilobate

Answer 2

I think you missed: <check-valid-connection-sql>select 1</check-valid-connection-sql> in <validation> section

PS

PostgreSQLValidConnectionChecker.isValidConnection sends empty query to postgres stmt.execute(""); I think postgres driver just ignore it. XA connection most likely sends some system SQL statement for supporting XA transaction and gets SQLException.

Sutton