11 Replies Latest reply on Feb 9, 2014 2:15 PM by Wolf-Dieter Fink

    Question about connection pools after database stops then restarts

    Nicholas DiPiazza Novice

      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>