0 Replies Latest reply on Sep 13, 2012 10:58 PM by Paul Sideleau

    How to configure connection pool to only execute check-valid-sql in background thread

    Paul Sideleau Newbie

      We have our connection pools configured to do background validation. The validate-on-match is explicitly set to false. Our SQL is simply "SELECT 1".

      We are using JTDS driver 1.2.5 against SQL Server 2012.

       

       

      We use new relic for performance monitoring and it is showing that the "SELECT 1" is executed every time a connection is checked out from the pool. When we had this configured under JBOSS 6, new relic correctly did not show the "SELECT 1" statement occurring.

       

      Here is an example datasource that we place in our domain.xml file:

      <datasource jta="true" jndi-name="java:jboss/MSSQLDS-DKR" pool-name="jboss/DKR41" enabled="true" use-java-context="true" use-ccm="true">

                          <connection-url>jdbc:jtds:sqlserver://OUR_DATABASE;sendStringParametersAsUnicode=true;</connection-url>

                          <driver>jtds-1.2.5.jar</driver>

                          <transaction-isolation>TRANSACTION_READ_UNCOMMITTED</transaction-isolation>

                          <pool>

                              <min-pool-size>45</min-pool-size>

                              <max-pool-size>150</max-pool-size>

                              <prefill>false</prefill>

                              <use-strict-min>false</use-strict-min>

                              <flush-strategy>FailingConnectionOnly</flush-strategy>

                          </pool>

                          <security>

                              <user-name>username</user-name>

                              <password>password</password>

                          </security>

                          <validation>

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

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

                              <background-validation>true</background-validation>

                              <background-validation-millis>60000</background-validation-millis>

                              <use-fast-fail>true</use-fast-fail>

                          </validation>

                          <timeout>

                              <blocking-timeout-millis>5000</blocking-timeout-millis>

                              <idle-timeout-minutes>2</idle-timeout-minutes>

                              <query-timeout>30</query-timeout>

                          </timeout>

                      </datasource>

       

      Is our configuration wrong or could this be a JBOSS bug?

       

      Thank you.