Hibernate Community • View topic - OutOfMemoryError after using scroll for a while

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

OutOfMemoryError after using scroll for a while

Page 1 of 1

[ 13 posts ]

Previous topic | Next topic

Author

Message

anthon.r

Post subject: OutOfMemoryError after using scroll for a while

Posted: Tue Apr 03, 2007 5:35 am

Beginner

Joined: Fri Jan 27, 2006 4:20 am
Posts: 22

I'm trying to migrate all the data from one database to another. The amount of data is huge, so using scroll is the way to go. But after a while, I get outofmemory exception. Look at the code:

Code:

      System.out.println("Initializing Sybase Datasource");

      // JPA
      EntityManagerFactory emfSybase = Persistence
            .createEntityManagerFactory("sybase");
      EntityManager emSybase = emfSybase.createEntityManager();

      // Hibernate
      HibernateEntityManagerFactory hibEmfSybase = (HibernateEntityManagerFactory) emfSybase;
      HibernateEntityManager hibEmSybase = (HibernateEntityManager) emSybase;

      System.out.println("Done.");

      /*
       * Reusable Vars
       */

      long totalRows = 0;
      long nullCounter = 0;
      long loopCounter = 0;

      /*
       * audittrail
       */

      System.out.println("Migrating Audittrail");

      totalRows = (Long) hibEmSybase.createQuery("select count(*) from Audittrail2 a").getSingleResult();
      
      ScrollableResults sr = hibEmSybase.getSession()
         .createQuery("from Audittrail2")
         .setCacheMode( CacheMode.IGNORE )
         .scroll( ScrollMode.SCROLL_INSENSITIVE );
         

      Audittrail audittrail2 = null;
      net.brokerhub.otcex.persistence.mysql.Audittrail audittrail = null;

      loopCounter = 0;

      while (sr.next())
      {
         audittrail2 = (Audittrail)sr.get()[0];
         loopCounter++;
         System.out.println(loopCounter);
      }

Top

piet.t

Post subject:

Posted: Tue Apr 03, 2007 5:51 am

Regular

Joined: Fri May 12, 2006 4:05 am
Posts: 106

Hi,

CacheMode.IGNORE will only disable the second-level cache. Nevertheless hibernate will still sotre all retrieved objcets in the entity-manager's persistence-context, which results in the outOfMemory-Exception.
To avoid this you'll have to clear() your persistence-context in regular intervals.
Since you are using specific hibernate-APIs you could als try using a StatelessSession, but I don't know if this will work when using a ScrollableResult.

Top

david

Post subject:

Posted: Tue Apr 03, 2007 6:56 pm

Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia

I suggest you verify that the JDBC driver does not impliments the scroll cursors in memory (thus potentially out of memory errors) rather than correctly employing native DB cursors.

Top

anthon.r

Post subject:

Posted: Tue Apr 03, 2007 9:33 pm

Beginner

Joined: Fri Jan 27, 2006 4:20 am
Posts: 22

Thanks for responses.

This is what an article I found at hibernate site says:

Quote:

Sybase on Jtds

When jTDS connects to Sybase it uses client-side cursors, so the whole ResultSet is cached on the client. So no good. Thanks to Alin Sinpalean of the jtds team for help.

Quoted from http://www.hibernate.org/314.html

What does this mean? Does it mean I will not be able to use cursors?
I'm using jConnect 6.05 JDBC from Sybase.

I'll try using flush and clear method and be back ASA problem still persist.[/url]

Top

anthon.r

Post subject:

Posted: Tue Apr 03, 2007 9:39 pm

Beginner

Joined: Fri Jan 27, 2006 4:20 am
Posts: 22

Here's what the JDBC jConnect 6.05 doc says:

Quote:

jConnect uses the Tabular Data Stream (TDS)--Sybase's proprietary protocol--to communicate with Sybase database servers. As of jConnect 5.5, TDS does not support scrollable cursors. To support scrollable cursors, jConnect caches the row data on demand, on the client, on each call to ResultSet.next( ). However, when the end of the result set is reached, the entire result set is stored in the client's memory. Because this may cause a performance strain, Sybase recommends that you use TYPE_SCROLL_INSENSITIVE result sets only when the result set is reasonably small.

Top

anthon.r

Post subject:

Posted: Tue Apr 03, 2007 9:57 pm

Beginner

Joined: Fri Jan 27, 2006 4:20 am
Posts: 22

Hi Piet.t

I revised the code to the following lines:

Code:

      EntityTransaction etSybase =
         emSybase.getTransaction();
      
      etSybase.begin();
      
      ScrollableResults sr = hibEmSybase.getSession()
         .createQuery("from Audittrail2")
         .setReadOnly( true )
         .scroll( ScrollMode.SCROLL_INSENSITIVE );

      Audittrail audittrail2 = null;
      net.brokerhub.otcex.persistence.mysql.Audittrail audittrail = null;

      loopCounter = 0;

      while (sr.next())
      {
         audittrail2 = (Audittrail)sr.get()[0];
         loopCounter++;
         
         if( loopCounter % 100 == 0)
         {
            //emSybase.flush();
            emSybase.clear();         
         }
         System.out.println(loopCounter);
      }
      
      etSybase.commit();

and I receive the following last 4 lines:

Code:

6368
6369
6370
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space

It throws the exception after executing line number 99 from the following lines:

Code:

97   public boolean next() throws HibernateException {
98      try {
99         boolean result = getResultSet().next();
100         prepareCurrentRow(result);
101         return result;
102      }
103      catch (SQLException sqle) {
104         throw JDBCExceptionHelper.convert(
105               getSession().getFactory().getSQLExceptionConverter(),
106               sqle,
107               "could not advance using next()"
108            );
109      }

Top

anthon.r

Post subject:

Posted: Tue Apr 03, 2007 10:20 pm

Beginner

Joined: Fri Jan 27, 2006 4:20 am
Posts: 22

I'm closer to solving the problem. Thanks again for responses.

I'm sorry to bring this up, but could anyone look at this post ->
http://forum.hibernate.org/viewtopic.php?t=972367

Please... Thanks in advance

Top

anthon.r

Post subject:

Posted: Wed Apr 04, 2007 4:43 am

Beginner

Joined: Fri Jan 27, 2006 4:20 am
Posts: 22

Hey guys, I've got hypothesis.

No matter how early you call flush and clear method, you're going to receive OutOfMemoryError exception if you are using ScrollMode.SCROLL_INSENSITIVE. But if you are using FORWARD_ONLY, there's a chance that you'll lose the connection and you've got no ways to move forward to resume where you are before you lost the connection.

I used ScrollMode.SCROLL_INSENSITIVE so that when I lose the connection, I have a chance to scroll forward to where I was before by invoking .scroll( myRecentPosition ). This is not possible with FORWARD_ONLY.

Now what am I going to do? What are the other ways?? Please show me the right path, enlightened one..

This is frakking making me insane..

Top

anthon.r

Post subject:

Posted: Wed Apr 04, 2007 6:25 am

Beginner

Joined: Fri Jan 27, 2006 4:20 am
Posts: 22

Crap... look at this..

Quote:

Support for SCROLL_INSENSITIVE Result Sets in jConnect jConnect version 5.0 supports only TYPE_SCROLL_INSENSITIVE result sets.jConnect uses the Tabular Data Stream (TDS)--Sybase's proprietary protocol--to communicate with Sybase database servers. As of jConnect 5.0, TDS does not support scrollable cursors. To support scrollable cursors, jConnect caches the row data on demand, on the client, on each call to ResultSet.next( ). However, when the end of the result set is reached, the entire result set is stored in the client's memory. Because this may cause a performance strain, we recommend that you use TYPE_SCROLL_INSENSITIVE result sets only when the result set is reasonably small.

It says

Quote:

However, when the end of the result set is reached, the entire result set is stored in the client's memory.

.

Tsktsktsk... is there any other way aside from using scrollable results?

Or is there a way to resume a lost connection when using FORWARD_ONLY?

Or is there any other downloadable 3rd party JDBC driver exists? (aside from jTDS)

This is definitely a showstopper..

Top

david

Post subject:

Posted: Thu Apr 05, 2007 9:20 pm

Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia

You could try paging the data, eg, read a page of data - process the data then loop back - read next page then process. Most databases do this efficently enough and you have greater control of memory use. It will be a little slower but it will work.

Top

anthon.r

Post subject:

Posted: Sat Apr 07, 2007 3:00 am

Beginner

Joined: Fri Jan 27, 2006 4:20 am
Posts: 22

Thanks david, I really appreciate it when someone from hibernate responded given the flooded situation here..

You mean, using setFirstResult and setMaxResults method? It's really helpful but I've got billions of data to process.. which means, I need to pass a long type parameter to the setFirstResult argument.

If there's only a method that takes a parameter as a value "relative" to current rows or previous rows retrieved, just like the method scroll(int relativePos), then I can make a solution just by using integer type..

mmm.. if I use FORWARD_ONLY, then I can't use scroll(int relativePos), I can only use scroll with no argument..

if I use CASE_INSENSITIVE, i'm going to have OutOfMemory exception.. (flush, clear don't seem to clear the internal resultset.. maybe evict() does?)

wait, what if I recreate the session after every 3 thousand scrolls, then I will be forced to commit a transaction... let me see how can I solve the problem..

I'll post here as soon as I solve the problem..

Thanks again

Top

david

Post subject:

Posted: Sat Apr 07, 2007 3:25 am

Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia

You have billions of data rows to process so forget the paging idea. This is an interesting use case, use FORWARD_ONLY and track your position for restart purposes if the connection drops out. What about the rollback buffer size? Sizing of transaction segments (assuming you can do a partial process which affects the connection dropout issue). All very interesting. Anyway, in general, this requirement tells me that using Hibernate (hence JDBC obviously because of the driver) for this bulk process might just not be the right tool for the job. Outside of finding a well implemented JDBC driver (as your primary issue is with the JDBC driver), do not use JDBC and use a native library (C or C++) for native DB scroll support for this task.

Top

anthon.r

Post subject:

Posted: Sat Apr 07, 2007 10:10 am

Beginner

Joined: Fri Jan 27, 2006 4:20 am
Posts: 22

Thanks

Yeah.. maybe it's just me. Too much for insisting and making hibernate an all-purpose tool for persistence lol

Top

Page 1 of 1

[ 13 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum