1 Reply Latest reply on Sep 16, 2011 7:24 AM by wdfink

    Connection Pool (DataSource) is slow

    vlc

      Hi Community,

       

      I am using the following MySQL DataSource XML:

       

      <jndi-name>MySQLDB</jndi-name>
      <xa-datasource-property name="URL">jdbc:mysql://127.0.0.1:3306/db</xa-datasource-property>
      <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
      <user-name>username</user-name>
      <password>password</password>
      <!-- <track-connection-by-tx>true</track-connection-by-tx> -->
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
      <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
      <min-pool-size>10</min-pool-size>
      <max-pool-size>100</max-pool-size>
      <idle-timeout-minutes>15</idle-timeout-minutes>
      <blocking-timeout-millis>500</blocking-timeout-millis>
      <track-statements>true</track-statements>

       

      I am doing something like this:

      0. create at application startup DataSource (InitialContext, lookup())

      1. execute at each request java.sql.Connection = ds.getConnection();

      2. do queries (PreparedStatement-s, ResultSet-s)

      3. close PreparedStatement-s, ResultSet-s, Connection object

       

      TEST: I execute 100 or 1000 requests requiring connection to the Database.

      The problem is DataSource.getConnection() takes very often 500 ms ~ 600 ms.

      At every get connection a new WrappedConnection object is returned (but I expect to reuse some already created connections)

      "show processlist" in MySQL show always 10 connections, jmx-console shows the pool is working with information like: created, in-use, destroyed connections.

       

       

      Please give me some answars:

      1. Why getConnection() function is so slow?

      2. Why diferrent WrappedConnection objects are create at each getConnection() call?

      3. The pool is between 10~100. Why does not exceed 10 connections even I execute 100 or 1000 parallel requests.

       

      Thank you for your interest.

      Cristi,

        • 1. Re: Connection Pool (DataSource) is slow
          wdfink

          Are you sure that exact the getConnection is slow?

          What JBoss version you use?

          Do you use the template examples/jca and what did you change?

          Did you have logging in TRACE mode?

           

          Do you check the jmx-console regarding connection count (create, destroy, max-in-use)?

           

          To check the plain JDBC it might be useful to use a simple Java application to check how long this will take.