Обсуждение: OutOfMemory hibernate scroll with 2M records | Postgresql 8.4 DB

Ankit Kumar wrote:
> Hi
>
> I am running a Criteria.scroll() on postgresql on a DB containing 2M
> records. The memory keeps increasing and finally it generates an
> OutOfMemoryException. Please can you advice how to fix this.
>
> *Postgresql DB version:* 8.4
> *Postgresql Driver Used:* postgresql-8.4-701.jdbc4.jar
>
> Some forums do mention that "/scroll/" is slow on Postgresql.
>
> Is this a known issue or we need to do something specific to make
> scroll work on Postgresql?
>
> It appears the driver class is loading all entity objects in memory
> and not releasing them as expected in a scroll operation.
>
>
> Appreciate any guidance/suggestion.
>
> *Exception StackTrace*:
> java.lang.OutOfMemoryError: Java heap space
>
>

This link might be useful for you to look at:

https://forums.hibernate.org/viewtopic.php?p=2398604&sid=f277fe884089594fe39272f69b68e329

It would appear that you need to encourage Hibernate to use a cursor,
instead of buffering the entire resultset in memory - I haven't looked
too much at the hibernate docs (never used it before...) so I have no
idea how to do this myself, but if I have time I'll dig a bit.

Do you have a self contained example to show us how you are setting up
the scrollable resultset?

regards

Mark

On 22/12/2009 12:15 PM, Ankit Kumar wrote:
> Hi
>
> I am running a Criteria.scroll() on postgresql on a DB containing 2M
> records. The memory keeps increasing and finally it generates an
> OutOfMemoryException. Please can you advice how to fix this.

Why ask for help if you're just going to ignore the advice you get?

You were asked to, when you re-posted to the pgsql-jdbc list, include a
self-contained test case and some logging information, which I explained
how to get.

If you have no respect for the value of the time of the people on this
list, why should anybody bother helping you?

--
Craig Ringer

Ankit Kumar wrote on 22.12.2009 05:15:
> It appears the driver class is loading all entity objects in memory and
> not releasing them as expected in a scroll operation.

That's documented:

http://jdbc.postgresql.org/documentation/84/query.html#query-with-cursor

Just enable "query based retrieval" and the driver does not load everything into memory.

Thomas

It's not a place of such discussion, and this is not a bug of PG JDBC,
just Hibernate works in this way

Try to use -Xms4G and buy some RAM;) or try to set
hibernate.jdbc.fetch_size or
https://www.hibernate.org/hib_docs/v3/api/org/hibernate/Criteria.html#setFetchSize(int)

Bear in mind all entities are keep in memory until session end /
transaction end (JPA).
----
Radosław Smogura

On Tue, 22 Dec 2009 09:45:35 +0530, Ankit Kumar <ankitk@xebia.com> wrote:
> Hi
>
> I am running a Criteria.scroll() on postgresql on a DB containing 2M
> records. The memory keeps increasing and finally it generates an
> OutOfMemoryException. Please can you advice how to fix this.
>
> *Postgresql DB version:* 8.4
> *Postgresql Driver Used:* postgresql-8.4-701.jdbc4.jar
>
> Some forums do mention that "/scroll/" is slow on Postgresql.
>
> Is this a known issue or we need to do something specific to make scroll

> work on Postgresql?
>
> It appears the driver class is loading all entity objects in memory and
> not releasing them as expected in a scroll operation.
>
>
> Appreciate any guidance/suggestion.
>
> *Exception StackTrace*:
> java.lang.OutOfMemoryError: Java heap space
>  at java.lang.Class.getDeclaredFields0(Native Method)
>  at java.lang.Class.privateGetDeclaredFields(Class.java:2291)
>  at java.lang.Class.getDeclaredField(Class.java:1880)
>  at
>

java.util.concurrent.atomic.AtomicReferenceFieldUpdater$AtomicReferenceFieldUpdaterImpl.<init>(AtomicReferenceFieldUpdater.java:181)
>  at
>
java.util.concurrent.atomic.AtomicReferenceFieldUpdater.newUpdater(AtomicReferenceFieldUpdater.java:65)
>  at java.sql.SQLException.<clinit>(SQLException.java:353)
>  at
>
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1777)
>  at
>
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
>  at
>
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
>  at
>
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
>  at
>
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
>  at
>
org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
>  at
>
org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
>  at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
>  at org.hibernate.loader.Loader.scroll(Loader.java:2286)
>  at
>
org.hibernate.loader.criteria.CriteriaLoader.scroll(CriteriaLoader.java:89)
>  at org.hibernate.impl.SessionImpl.scroll(SessionImpl.java:1533)
>  at org.hibernate.impl.CriteriaImpl.scroll(CriteriaImpl.java:297)
>  at org.hibernate.impl.CriteriaImpl.scroll(CriteriaImpl.java:291)
>  at
>
com.hi.openname.dao.impl.HibernateKnowledgeItemDao.loadAllCommitted(HibernateKnowledgeItemDao.java:104)
>  at
>
com.hi.openname.dao.impl.HibernateKnowledgeItemDao.loadAllCommitted(HibernateKnowledgeItemDao.java:1)
>
> Regards
> Ankit