Question about connection pools after database stops then restarts

[ndipiazza]

I am experiencing different behaviors with Data sources of different databases and I am wondering if JBoss is at fault, or if the JDBC database driver is at fault.

When we start Jboss, it creates the <min-pool-size> # of connections which remain idle.

As users connect they grab an idle connections from the pool.

When they disconnect the connections remain and return to idle status.

When I stop and start the database the pool in DB2, the connections would re-appear in less than 30 seconds (all <min-pool-size> # of  idle connections).

When I stop and start the database in Postgres the pool does not get re-created.

Each user that connects starts a connection.

When that use logs out the connection goes to idle status and remains.

How can we get Postgres to do this for us like DB2 did? Is this a JBoss issue or a Data source issue?

Here is the DB2 ds configuration:

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>DB2DS</jndi-name>
<connection-url>jdbc:db2:REMOVED</connection-url>
<driver-class>com.ibm.db2.jcc.DB2Driver</driver-class>
<user-name>removed</user-name>
<password>removed</password>
<min-pool-size>80</min-pool-size>
<max-pool-size>100</max-pool-size>
<idle-timeout-minutes>15</idle-timeout-minutes>
<application-managed-security/>
<new-connection-sql>select count(*) from MY_ACCOUNT</new-connection-sql><metadata>
<type-mapping>DB2</type-mapping>
</metadata>

</local-tx-datasource>
</datasources>

Here is the Postgres ds configuration:

<?xml version="1.0" encoding="UTF-8"?>

<datasources>
<local-tx-datasource>
<jndi-name>PostgresDS</jndi-name>
<connection-url>jdbc:postgresql://REMOVED:5432/REMOVED/connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>removed</user-name>
<password>removed</password>
<min-pool-size>20</min-pool-size>
<max-pool-size>50</max-pool-size>
<idle-timeout-minutes>15</idle-timeout-minutes>
<application-managed-security/>

<!-- sql to call when connection is created -->
<new-connection-sql>select 1</new-connection-sql>

<!-- sql to call on an existing pooled connection when it is obtained from pool -->
<check-valid-connection-sql>select 1</check-valid-connection-sql>

<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
<metadata>
<type-mapping>PostgreSQL</type-mapping>
</metadata>
</local-tx-datasource>

</datasources>

[jbertram]

What version of JBoss AS are you using?

[ndipiazza]

JBoss 6.1.0 Final.... however I would like to know how this affects JBoss 7.1.0.Final as well.

[jesper.pedersen]

<prefill>true</prefill>

[ndipiazza]

I get this error when trying to use this:

12:01:01,103 WARN  [JBossManagedConnectionPool] Prefill pool option was selected for pool with JNDI name null that does not support this feature.

12:01:01,103 WARN  [JBossManagedConnectionPool] Please verify your *-ds.xml file that corresponds with this resource and either remove the <prefill>true|false</prefill element or explicitly set this value to false.

[jesper.pedersen]

You are in the wrong forum - upgrade to AS7; it supports prefill for OnePool and PoolBySubject.

[jbertram]

In both JBoss AS 6.x and 7.x if you want the connection pool to recover from a database outage then you need to configure connection validation.  If you don't have connection validation then when the database goes down all the connections in the pool will be invalid (even after the database comes back up).  When an application tries to use the invalid connection it will receive an error.  Check out this wiki and read up on <validate-on-match>, <background-validation>, <background-validation-millis>, <valid-connection-checker-class-name>, and <check-valid-connection-sql>.  Note, these attributes only apply to AS 6.x.  I don't have the IronJacamar stuff (i.e. JCA implementation for AS 7.x) on hand, but I'll try to find it.

As far as why this was working for DB2 without connection validation, I can't say.

[jbertram]

Here's the IronJacamar docs which relate to AS 7.1.0.Final.  See the stuff related to validation.

[ndipiazza]

Justin,

The question is: When I stop and start the database, the JBoss Postgres connection pool does not regenerate <min-pool-size> # of connections. It does get regenerated, but the connection pool slowly fills instead of automatically creating <min-pool-size> idle connections like it did when it started up.

How can I get postgres to re-generate <min-pool-size> connections after a database restart?

We have already specified:

<new-connection-sql>select 1</new-connection-sql>

and

<check-valid-connection-sql>select 1</check-valid-connection-sql>

This makes it so JBoss does not need to be restarted when the database has been restarted. But it does not auto-populate the pool with <min-pool-size> # of connections.

[jesper.pedersen]

Nicholas, AS7 features a new JCA container - IronJacamar - so I don't really care about AS 6.1 anymore, so that is open_to_community if you find a problem. Otherwise get a subscription on JBoss Enterprise Application Platform.

If you find an issue in the latest snapshot of 7.1, that is another story...

[nitinksks]

Hi All ,

I am using Jboss EAP 6.0 and using Oracle database . I have below  data source configuration in the domain.xml

<datasource jta="false" jndi-name="java:/MobileApplicationDBResources" pool-name="Mobile_Application" enabled="true" use-ccm="false">

                        <connection-url>jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST=testdb-scan.com)(PORT=1875)) (CONNECT_DATA=(SERVICE_NAME= ltest.com)))</connection-url>

                        <driver-class>oracle.jdbc.OracleDriver</driver-class>

                        <driver>ojdbc6.jar</driver>

                        <security>

                            <user-name>Test_application</user-name>

                            <password>Test123</password>

                        </security>

                        <validation>

                            <validate-on-match>false</validate-on-match>

                            <background-validation>false</background-validation>

                        </validation>

                        <statement>

                            <share-prepared-statements>false</share-prepared-statements>

                        </statement>

                    </datasource>

I am phasing problem when my database restarted , jboss is not able to connect database... at that situation I need to restart jboss instances otherwise my application not able to connect with database. we were getting below error in the server.log.

ERROR--- [getLoginInformation]-------> Exception: java.sql.SQLException: Closed Connection

Please advise me which configuration need to change in the datasource configuration to overcome from this issue means  jboss automatically connect database whenever database restarted ..we don't need to restart jboss instances.

Thanks..

Nitin

[wdfink]

You should open a new thread (i.e. for EAP forum)

You might set a check-valid-connection-sql or restart the pool manually.