7 Replies Latest reply on Mar 28, 2017 6:11 AM by Piyush Goel

    jboss 7 connection pool issue

    Marc Rosenthal Newbie

      We recently upgraded from jboss4 and we are now on jboss7 (7.1.1.Final).  During high load times we started noticing this error:

       

      javax.resource.ResourceException: IJ000453: Unable to get managed connection for [datasource]

       

      Even during these times, our database, MySQL was not busy and a 'show processlist' showed plenty of sleeping connections for the pool.

       

      We tried an experiment with a pool of size 1. (min = 1 and max = 1)

       

      Under Jboss4, with this config:

      <datasources>

        <local-tx-datasource>

          <jndi-name>DbDS</jndi-name>

          <connection-url>jdbc:mysql://database:3306/db</connection-url>

          <driver-class>org.gjt.mm.mysql.Driver</driver-class>

          <min-pool-size>1</min-pool-size>

          <max-pool-size>1</max-pool-size>

          <idle-timeout-minutes>10</idle-timeout-minutes>

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

          <password>password</password>

        </local-tx-datasource>

      </datasources>

       

      I was able to login to the application and it could handle multiple concurrently running queries.

       

      In Jboss7, we used this config:

                      <datasource jta="true" jndi-name="java:jboss/datasources/dbDS" pool-name="mysqlpool" enabled="true" use-ccm="true">

                          <connection-url>jdbc:mysql://database:3306/db</connection-url>

                          <driver-class>com.mysql.jdbc.Driver</driver-class>

                          <driver>mysql-connector-java-5.1.16.jar</driver>

                          <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                          <pool>

                              <min-pool-size>1</min-pool-size>

                              <max-pool-size>1</max-pool-size>

                              <prefill>true</prefill>

                              <flush-strategy>IdleConnections</flush-strategy>

                          </pool>

                          <security>

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

                              <password>password</password>

                          </security>

                          <timeout>

                              <blocking-timeout-millis>30000</blocking-timeout-millis>

                              <allocation-retry>3</allocation-retry>

                          </timeout>

                          <statement>

                              <prepared-statement-cache-size>50</prepared-statement-cache-size>

                          </statement>

                      </datasource>

       

      but even with one connection, it waits the 30 seconds (from the blocking-timeout-millis), throws the managed connection exception, and kicks the user out.  If you look at the MySQL processlist during this period, it shows the one connection sleeping.

       

       

      We have tried switching some of the config options (with and witouth flush-strategy, with and without allow-multiple-users, with and without the timeout options), but always the same result.

       

      Do you know if there is a configuration option that we are missing that will allow jboss to use the empty/sleeping connection from the pool or wait for it if it is currently being used?  It seems that this isn't happening.

       

       

      More Info: It does finish some of the queries, but at some point it gets locked up and is unable to run any further queries. 

       

      More Info: we figured out it takes four connections to handle this login.  These queries are quick returning queries, so it seems that the pool is not releasing the connections so they can be reused.

       

       

      Thanks.

       

      Environment: jboss-7.1.1 on centos5.8

      mysql 5.0.77 on centos5.6