-
1. Re: SQL-Listener limit results for each query
tfennelly Mar 18, 2010 6:08 AM (in response to valeriob)Sorry... is it the issue with overloading of JMS (if that's possible) by the SQL Listener, or too big a fetch size on the DB driver?
By default, it's up to the DB Driver in use to make a "best guess" at the appropriate fetch size and from a quick google... seems as though the default fetch size for most drivers is 10, in which case (assuming this to be the case for your use case), I'm not really sure how you could be hitting this if fetch size is the issue. What DB and DB driver are you using?
-
2. Re: SQL-Listener limit results for each query
valeriob Mar 18, 2010 10:34 AM (in response to tfennelly)JVM runs out of heap space because it seems to get the whole table in memory. Jboss throws the following:
14:45:29,687 WARN [AbstractThreadedManagedLifecycle] Unexpected error from doRun()
java.lang.OutOfMemoryError: Java heap space
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2998)
at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:1519)
at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1409)
at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2883)
at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:476)
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2576)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1757)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2167)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2228)
at org.jboss.soa.esb.helpers.persist.JdbcCleanConn.execQueryWait(JdbcCleanConn.java:174)
at org.jboss.soa.esb.listeners.gateway.SqlTableGatewayListener.pollForCandidates(SqlTableGatewayListener.java:479)
at org.jboss.soa.esb.listeners.gateway.SqlTableGatewayListener.doRun(SqlTableGatewayListener.java:172)
at org.jboss.soa.esb.listeners.lifecycle.AbstractThreadedManagedLifecycle.run(AbstractThreadedManagedLifecycle.java:115)
at java.lang.Thread.run(Unknown Source)
or sometimes this one:
15:22:44,000 WARN [AbstractThreadedManagedLifecycle] Unexpected error from doRun()
java.lang.OutOfMemoryError: Java heap space
at java.lang.StringCoding$StringDecoder.decode(Unknown Source)
at java.lang.StringCoding.decode(Unknown Source)
at java.lang.String.<init>(Unknown Source)
at com.mysql.jdbc.ResultSetRow.getString(ResultSetRow.java:796)
at com.mysql.jdbc.ByteArrayRow.getString(ByteArrayRow.java:69)
at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5632)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5509)
at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4894)
at org.jboss.soa.esb.listeners.gateway.SqlTableGatewayListener.pollForCandidates(SqlTableGatewayListener.java:494)
at org.jboss.soa.esb.listeners.gateway.SqlTableGatewayListener.doRun(SqlTableGatewayListener.java:172)
at org.jboss.soa.esb.listeners.lifecycle.AbstractThreadedManagedLifecycle.run(AbstractThreadedManagedLifecycle.java:115)
at java.lang.Thread.run(Unknown Source)
I tried to work around this issue by adding this line "sb.append(" limit 1000");" to the file org.jboss.soa.esb.listeners.gateway.SqlTableGatewayListener line 591 so each cycle it only fetches 1000 lines, looks like working good for 20k rows but then the thread seems to hang up, i guess this solutions messes up with some state/concurrency management.
Another thing i do not quite understand is how the parameter (in jboss-esb.xml) for the Sql-Listener called "Max Threads" works, i did some testing with different values without any notable change.