Hibernate: Walk millions of rows and don't leak memory

Question

The below code functions, but Hibernate never lets go of its grip of any object. Calling session.clear() causes exceptions regarding fetching a joined class, and calling session.evict(currentObject) before retrieving the next object also fails to free the memory. Eventually I exhaust my heap space.

Checking my heap dumps, StatefulPersistenceContext is the garbage collector's root for all references pointing to my objects.

public class CriteriaReportSource implements JRDataSource {

    private ScrollableResults sr;
    private Object currentObject;
    private Criteria c;
    private static final int scrollSize = 10;
    private int offset = 1;

    public CriteriaReportSource(Criteria c) {
        this.c = c;
        advanceScroll();
    }

    private void advanceScroll() {
//        ((Session) Main.em.getDelegate()).clear();
        this.sr = c.setFirstResult(offset)
                   .setMaxResults(scrollSize)
                   .scroll(ScrollMode.FORWARD_ONLY);
        offset += scrollSize;
    }

    public boolean next() {
        if (sr.next()) {
            currentObject = sr.get(0);
            if (sr.isLast()) {
                advanceScroll();
            }
            return true;
        }

        return false;
    }

    public Object getFieldValue(JRField jrf) throws JRException {
        Object retVal = null;
        if(currentObject == null) { return null; }
        try {
            retVal = PropertyUtils.getProperty(currentObject, jrf.getName());
        } catch (Exception ex) {
            Logger.getLogger(CriteriaReportSource.class.getName()).log(Level.SEVERE, null, ex);
        }
        return retVal;
    }
}

I have seen examples of this in the [Hibernate reference](http://docs.jboss.org/hibernate/core/3.3/reference/en/html/batch.html#batch-update), however, there `session.flush()` was called prior to `session.clear()`. Could you try whether it makes a difference? — Péter Török, Jun 18 '10 at 16:25
What database is it? Not all support true cursor scrolling. Also, I don't see you closing the ScrollableResults. — Brian Deterling, Jun 18 '10 at 18:03
Regarding the flush before clear question, it had no effect. Since I'm not updating the database, it serves no purpose. If I were updating, it'd keep me from dumping everything when I did clear. (I did test this to answer your question.) — Jeff Ferland, Jun 18 '10 at 20:11
Check out the section on resultsets - it may be that the MySql JDBC driver is loading it all into memory: http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html. Also look at useCursorFetch. Here's more information (see the comments also): http://benjchristensen.com/2008/05/27/mysql-jdbc-memory-usage-on-large-resultset/ — Brian Deterling, Jun 18 '10 at 21:47

score 4 · Answer 1 · edited May 23 '17 at 10:27

4

Don't use the stateful session here, it's just NOT the right tool to walk millions of rows and build a report. Use The StatelessSession interface instead.

If using MySQL Connector/J even that is not enough, you need to also defeat the internal buffering done by the JDBC driver, with this:

Query query = session.createQuery(query);
query.setReadOnly(true);
// MIN_VALUE gives hint to JDBC driver to stream results
query.setFetchSize(Integer.MIN_VALUE);
ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);
// iterate over results
while (results.next()) {
    Object row = results.get();
    // process row then release reference
    // you may need to evict() as well
}
results.close();

edited May 23 '17 at 10:27

Community

1
1

answered Jun 18 '10 at 16:42

Pascal Thivent

562,542
136
1,062
1,124

1

At the moment, using StatelessSession solves the memory problem, but raises all hell when associated OneToMany joins are accessed (lazy loading). Kind of an interesting win-lose. Any suggestions while I go about testing if eager loading solves this? – Jeff Ferland Jun 18 '10 at 19:38
Turns out StatelessSession can't handle collections. I did find a way to use Stateful and control my memory. See my answer to my own question. – Jeff Ferland Jun 18 '10 at 20:11

Greg Case · Answer 2 · 2010-06-18T16:39:13.213

0

Couple of things I would suggest:

Try calling setCacheMode(CacheMode.IGNORE) on the Criteria before opening it.

In the advanceScroll() method, add if (sr != null) sr.close(); so that the previous ScrollableResults gets closed before you do the re-assignment to the new one.

One question: What is the reason for calling setMaxSize(), and then keeping track of the offset and then re-opening the scrollable results, why not just do this?

public CriteriaReportSource(Criteria c) {
    this.c = c;
    this.sr = c.setCacheMode(CacheMode.IGNORE)
               .scroll(ScrollMode.FORWARD_ONLY);
}


public boolean next() {
    if (sr.next()) {
        currentObject = sr.get(0);
        return true;
    }
    return false;
}

edited Jun 18 '10 at 16:39

answered Jun 18 '10 at 16:33

Greg Case

3,200
1
19
17

None of the suggestions in this answer worked, unfortunately. setMaxSize() and such were attempts at solving the problem by controlling the window of what was queried. With or without them, the memory continues to grow. The sr.close() attempt was also fruitless. – Jeff Ferland Jun 18 '10 at 19:36
Sorry to hear that. Give the StatelessSession that Pascal mentioned, that may be your best bet. – Greg Case Jun 18 '10 at 19:58

score 0 · Accepted Answer · answered Jun 18 '10 at 20:10

I think one of my problems was that

if (sr.isLast()) {
    advanceScroll();
    //...

combined with

((Session) Main.em.getDelegate()).clear();
//Also, "Main.em.clear()" should do...

resulted in flushing the database out one run too early. That was the cause of exceptions regarding collections. Collections cannot be handled in a StatelessSession, so that's off the table. I don't know why session.evict(currentObject) fails to work when Session.clear() does work, but that's the way I'll have to handle it for now. I'll toss the answer points to whoever can figure that one out.

So, for now, there we have an answer. A manual scrolling window is required, closing the ScrollableResults doesn't help, and I need to properly run a Session.clear().

Why is that? This successfully solves my problem. While it does pose a question about Session.evict, which I could move over, it is the answer. — Jeff Ferland, Jun 19 '10 at 04:35

Hibernate: Walk millions of rows and don't leak memory

3 Answers3