4 Replies Latest reply on Nov 22, 2005 8:45 AM by Tapani

    JBoss + MySQL low performance

    Tapani Newbie

      Reading rows from the Collection is very slow. 1000 db rows takes 30 seconds with one table and 6 seconds with another. The slower has about 5 times more fields. With another A/S with the same db and JDBC driver no problems. Is this normal JBoss performance?

      JBoss 4.0.3SP1
      Connector/J 3.1.11.
      JDK 1.5

      <?xml version="1.0" encoding="UTF-8"?>
      <datasources>
       <local-tx-datasource>
       <jndi-name>MyDS</jndi-name>
       <connection-url>jdbc:mysql://10.10.10.10/myds?autoReconnect=true&maxReconnects=10</connection-url>
       <driver-class>com.mysql.jdbc.Driver</driver-class>
       <user-name>mymy</user-name>
       <password>mymy</password>
      
       <metadata>
       <type-mapping>mySQL</type-mapping>
       </metadata>
       </local-tx-datasource>
      </datasources>


        • 1. Re: JBoss + MySQL low performance
          Tapani Newbie

          If I enable CMP debug then I get tons of SQL queries just for one findAll finder call:

          19:04:45,473 DEBUG [MyFooEntityBean] Executing SQL: SELECT xx_name, xx_address,... FROM xx_foo WHERE (xx_foo_id=?)
          19:04:45,504 DEBUG [MyFooEntityBean] Executing SQL: SELECT xx_name, xx_address,... FROM xx_foo WHERE (xx_foo_id=?)
          19:04:45,520 DEBUG [MyFooEntityBean] Executing SQL: SELECT xx_name, xx_address,... FROM xx_foo WHERE (xx_foo_id=?)
          19:04:45,551 DEBUG [MyFooEntityBean] Executing SQL: SELECT xx_name, xx_address,... FROM xx_foo WHERE (xx_foo_id=?)
          19:04:45,582 DEBUG [MyFooEntityBean] Executing SQL: SELECT xx_name, xx_address,... FROM xx_foo WHERE (xx_foo_id=?)
          19:04:45,598 DEBUG [MyFooEntityBean] Executing SQL: SELECT xx_name, xx_address,... FROM xx_foo WHERE (xx_foo_id=?)
          ...
          (about 100 more)


          • 2. Re: JBoss + MySQL low performance
            Ulf Schroeter Novice

            Read the cmp2 documentation, especially topics about optimized loading...

            Regards
            Ulf

            • 3. Re: JBoss + MySQL low performance
              Tapani Newbie

              Why do I need to optimize when I have a very default bean in default JBoss installation. I have not optimized the other A/S. Doesn't JBoss work at all without optimization?

              I tried changing the read-ahead settings in my jbosscmp-jdbc.xml, but that didn't have any impact to the performance.

              -Tapani

              • 4. Re: JBoss + MySQL low performance
                Tapani Newbie

                I changed read-ahead strategy to on-find in the of my jbosscmp-jdbc.xml. Now JBoss executes a query, which finds the rows and selects all fields. But it still retrieves the very same properties using separate SQL queries. As you can see from the log dump the row is added to the preloaded data cache, but with the same PK the data is not later found from the cache. Is this a bug or should I change some configurations?

                2005-11-22 15:20:52,062 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.MyEntity#findAll] Executing SQL: SELECT t0_d.name, t0_d.address, ... FROM my_table t0_d
                
                2005-11-22 15:20:52,093 TRACE [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMP2xFieldBridge.MyEntity#Name] result: i=1, type=java.lang.String, value=cache.poll_rate.secs
                2005-11-22 15:20:52,109 TRACE [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMP2xFieldBridge.MyEntity#Address] result: i=2, type=int, value=3
                2005-11-22 15:20:52,109 TRACE [org.jboss.ejb.plugins.cmp.jdbc.ReadAheadCache.MyEntity] Add preload data: entity=MyEntity pk=tapani.MyEntityPrimaryKey@15c75d4 field=Address
                ...
                2005-11-22 15:20:52,265 TRACE [org.jboss.ejb.plugins.cmp.jdbc.ReadAheadCache.MyEntity] load data: entity=MyEntity pk=tapani.EntityPrimaryKey@15c75d4
                2005-11-22 15:20:52,265 TRACE [org.jboss.ejb.plugins.cmp.jdbc.ReadAheadCache.MyEntity] No preload data found: entity=MyEntity pk=tapani.EntityPrimaryKey@15c75d4
                2005-11-22 15:20:52,265 TRACE [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCEntityBridge.MyEntity] Default eager-load for entity: readahead=null
                2005-11-22 15:20:52,265 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.MyEntity] Executing SQL: SELECT address, ... FROM my_table WHERE (name=?)
                


                -Tapani