JBoss + MySQL low performance

[tsaari]

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>

[tsaari]

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)

[schrouf]

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

Regards
Ulf

[tsaari]

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

[tsaari]

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