Geek Answers Handbook

Jboss AS7 connection pool will not reconnect

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 stops responding for a second, JBoss cannot reconnect, and I have to restart the application server.

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

Thing: I can’t understand from this. Correct me if I am wrong, but xa-datasources supposed to be used for synchronous committing in several databases, and this is not so. In fact, I have several databases configured, but I do not need to synchronize transactions between them.

The "default" datasource also seems to have problems setting up the connection pool. Sometimes, it doesn’t matter if the application loads, it opens more than 100 connections (even if the limit is 100) and closes them after a few seconds. This is hard to reproduce because it seems random, so I cannot say for sure that switching to xa-datasource also solves this problem.

Now:

  • why switch to xa-datasource ?
  • What are the consequences of this?
  • Why is the connection pool going crazy?

To clarify, my test is as follows:

  • run postgres and application server;
  • Complete some application requests
  • stop the database;
  • Fulfill some requests to the application - and make sure that they do not work, because they cannot open any connections;
  • start the database again;
  • complete some application requests

At the last stage, xa-datasource can connect to postgres, and everything will work. datasource cannot and will not forever, download does not matter - I need to restart the application server.

+4
source share
2 answers

One thing to keep in mind when setting up jboss is that sometimes the best projects in projects are for individual components. In the case of data source options, I always tell people to check 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>
+3
source

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

PS

PostgreSQLValidConnectionChecker.isValidConnection sends an empty query to postgres stmt.execute(""); I think the postgres driver just ignores it. The XA connection most likely sends some system SQL query to support the XA transaction and receives an SQLException.

+1
source

More articles:


All Articles