Why does .scroll with stateless session causes OutOfMemoryException?

Question

I want to reduce hibernate session's overhead (to boost performance because session takes too much time to handle its stored objects) and try to use stateless session:

            statelessSession.createQuery(
                    "SELECT me, me.mailSourceFile, me.linf " +
                            "FROM MessageEntry me " +
                            "WHERE me.mailSourceFile.archive.folder = :folder " +
                            "ORDER BY me.messageLength DESC")
                    .setString("folder", folder)
                    .scroll(ScrollMode.FORWARD_ONLY);

But .scroll(...) gives

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    at com.mysql.jdbc.Buffer.<init>(Buffer.java:59)
    at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1477)
    at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2936)
    at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:477)

...

My code doesn't have memleaks, it works correctly with Session and .iterator(). How else should I use stateless session to avoid crash?

Thanx.

score 0 · Accepted Answer · answered Aug 11 '13 at 21:01

Here's an excerpt from the documentation of the MySQL JDBC driver

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                            java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this, any result sets created with the statement will be retrieved row-by-row.

There are some caveats with this approach. You must read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown.

Given that you get an OutOfMemoryError when just reading from a MySQL result set, my guess is that this result set is read completely in memory, and is large enough to consume all the available memory of the JVM.

Well, it is obvious that result set is loaded into memory by Hibernate. But I assumed that this pattern from Hibernate manual is used for huge result sets. Is it possible to iterate over huge resultset with hibernate somehow? — Stepan Yakovenko, Aug 11 '13 at 21:40
It's not loaded in memory by Hibernate, but by the MySQL JDBC driver. Hibernate simply iterates through the result set as you would do if you used JDBC. But MySQL's stupid JDBC driver loads everything into memory. See http://stackoverflow.com/questions/2826319/using-hibernates-scrollableresults-to-slowly-read-90-million-records (and especially the second answer) to do what the above documentation tells using the Hibernate API. — JB Nizet, Aug 11 '13 at 21:50

Why does .scroll with stateless session causes OutOfMemoryException?

1 Answers1