2 Replies Latest reply on Mar 18, 2010 10:34 AM by Valerio Borioni

    SQL-Listener limit results for each query

    Valerio Borioni Newbie

      Hello, i'm doing some test in Jboss ESB using SQL-Listener: i prepare a table with different number of rows (10-100-1k-10k-100k) and rows of different sizes (10bytes, 10k bytes).

      I see that sql listeners at first try to read the whole table, and it crashes on the heaviest case (100k rows*10k bytes=1gb table). Is there any way to tell the listener to limit a query to CustomNumber of rows each time (without touching the source code of SqlTableGatewayListener.Java) ?

      Since the sql gateway listener fetches messages from a table and puts those in a jms queue to be retrieved by the jms listener of the action pipeline there is no way to sync their actions too, something like stop the gateway listener if the jms queue is full or something. I mean, it's easy to crash this thing is'nt it?

        • 1. Re: SQL-Listener limit results for each query
          Tom Fennelly Master

          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
            Valerio Borioni Newbie

            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.