OutOfMemoryError: Java heap space while indexing for MySql

chewkj · **Joined:** Thu Jun 25, 2009 4:50 am **Posts:** 7

Hi,

I've tried to index a huge mysql table (6.1Million records) with the below hibernate search code:

Code:

   private static int BATCH_SIZE = 100;
   private static int FETCH_SIZE = 100;
... ...
      FullTextSession ftSession = org.hibernate.search.Search
            .getFullTextSession(session);
      ftSession.setFlushMode(FlushMode.MANUAL);
      ftSession.setCacheMode(CacheMode.IGNORE);

      Transaction tx = ftSession.beginTransaction();

      @SuppressWarnings("unchecked")
      Criteria query = ftSession.createCriteria(Mrconso.class)
                          .setFetchSize(FETCH_SIZE);
      ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);

      int index = 0;
      results.beforeFirst();
      while (results.next()) {
         index++;
         ftSession.index(results.get(0));
         if (index % BATCH_SIZE == 0) {
            ftSession.flushToIndexes();
            ftSession.clear();
         }
      }
      tx.commit();
      results.close();
      session.close();

While running on the debug mode, I've encountered the below exception within the execution for "ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY)" :

Code:

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
   at java.util.ArrayList.ensureCapacity(ArrayList.java:169)
   at java.util.ArrayList.add(ArrayList.java:351)
   at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2715)
   at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:475)
   at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2413)
   at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1673)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2038)
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1888)
   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:2293)
   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 Indexer.index(Indexer.java:62)
   at Indexer.main(Indexer.java:94)

The DB and driver I'm using are as follow:

It seem to me the query.scroll method is not functioning but trying to fetch the whole result list.

I've also tried to increase the heap size for the runtime (-Xmx) but doesn't help either. Your suggestion is very much appreciated. Thanks.

Regards,
Joon

sanne.grinovero · **Posted:** Tue Jun 30, 2009 1:12 pm

which versions of hibernate libs?
The code is looking fine.
out of curiosity, do you get the same error if you try scrolling on the entities without indexing them?

chewkj · **Joined:** Thu Jun 25, 2009 4:50 am **Posts:** 7

Hi Sanne,

The hibernate libs I am using are as follow:

As I'm defining these dependencies in Maven, do I need to exclude any transitive dependency pls?

Yeah, the out of memory error pops up while reaching the scroll method, out even touching the index and flushToIndexes at all.

Regards,
Joon

hardy.ferentschik · **Posted:** Wed Jul 01, 2009 5:20 am

Hi,

have you SQL debug trace enabled (hibernate.show_sql = true). If so, can you provide some log file output? Looking at the executed sql might help narrowing down the problem.

--Hardy

sanne.grinovero · **Posted:** Thu Jul 02, 2009 6:10 am

this might be related to http://opensource.atlassian.com/projects/hibernate/browse/HHH-3576

looking forward for someone to post the generated SQL.

chewkj · **Joined:** Thu Jun 25, 2009 4:50 am **Posts:** 7

Hi there,

I've enable the show_sql switch and set the hibernate log to INFO as well, below are the debug logs:

Code:

[ INFO] 21:13:54 Version - Hibernate Annotations 3.4.0.GA
[ INFO] 21:13:54 Environment - Hibernate 3.2.5
[ INFO] 21:13:54 Environment - hibernate.properties not found
[ INFO] 21:13:54 Environment - Bytecode provider name : cglib
[ INFO] 21:13:54 Environment - using JDK 1.4 java.sql.Timestamp handling
[ INFO] 21:13:54 Version - Hibernate Commons Annotations 3.1.0.GA
[ INFO] 21:13:59 AnnotationBinder - Binding entity from annotated class: domain.Mrconso
[ INFO] 21:13:59 EntityBinder - Bind entity domain.Mrconso on table MRCONSO
[ INFO] 21:13:59 AnnotationConfiguration - Hibernate Validator not found: ignoring
[ INFO] 21:13:59 ConnectionProviderFactory - Initializing connection provider: org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider
[ INFO] 21:14:10 SettingsFactory - RDBMS: MySQL, version: 5.1.33
[ INFO] 21:14:10 SettingsFactory - JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-5.1.3-rc ( Revision: ${svn.Revision} )
[ INFO] 21:14:10 Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
[ INFO] 21:14:10 TransactionFactoryFactory - Using default transaction strategy (direct JDBC transactions)
[ INFO] 21:14:10 TransactionManagerLookupFactory - No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
[ INFO] 21:14:10 SettingsFactory - Automatic flush during beforeCompletion(): disabled
[ INFO] 21:14:10 SettingsFactory - Automatic session close at end of transaction: disabled
[ INFO] 21:14:10 SettingsFactory - JDBC batch size: 15
[ INFO] 21:14:10 SettingsFactory - JDBC batch updates for versioned data: disabled
[ INFO] 21:14:10 SettingsFactory - Scrollable result sets: enabled
[ INFO] 21:14:10 SettingsFactory - JDBC3 getGeneratedKeys(): enabled
[ INFO] 21:14:10 SettingsFactory - Connection release mode: on_close
[ INFO] 21:14:10 SettingsFactory - Maximum outer join fetch depth: 2
[ INFO] 21:14:10 SettingsFactory - Default batch fetch size: 1
[ INFO] 21:14:10 SettingsFactory - Generate SQL with comments: disabled
[ INFO] 21:14:10 SettingsFactory - Order SQL updates by primary key: disabled
[ INFO] 21:14:10 SettingsFactory - Order SQL inserts for batching: disabled
[ INFO] 21:14:10 SettingsFactory - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
[ INFO] 21:14:10 ASTQueryTranslatorFactory - Using ASTQueryTranslatorFactory
[ INFO] 21:14:10 SettingsFactory - Query language substitutions: {}
[ INFO] 21:14:10 SettingsFactory - JPA-QL strict compliance: disabled
[ INFO] 21:14:10 SettingsFactory - Second-level cache: enabled
[ INFO] 21:14:10 SettingsFactory - Query cache: disabled
[ INFO] 21:14:10 SettingsFactory - Cache provider: org.hibernate.cache.NoCacheProvider
[ INFO] 21:14:10 SettingsFactory - Optimize cache for minimal puts: disabled
[ INFO] 21:14:10 SettingsFactory - Structured second-level cache entries: disabled
[ INFO] 21:14:10 SettingsFactory - Echoing all SQL to stdout
[ INFO] 21:14:10 SettingsFactory - Statistics: disabled
[ INFO] 21:14:10 SettingsFactory - Deleted entity synthetic identifier rollback: disabled
[ INFO] 21:14:10 SettingsFactory - Default entity-mode: pojo
[ INFO] 21:14:10 SettingsFactory - Named query checking : enabled
[ INFO] 21:14:10 Version - Hibernate Search 3.1.1.GA
[ INFO] 21:14:11 SessionFactoryImpl - building session factory
[ INFO] 21:14:11 SessionFactoryObjectFactory - Not binding factory to JNDI, no JNDI name configured
Hibernate: select this_.aui as aui0_0_, this_.cui as cui0_0_, this_.str as str0_0_ from MRCONSO this_

I have noticed the two lines from the log:

Quote:

[ INFO] 21:14:10 SettingsFactory - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
[ INFO] 21:14:10 ASTQueryTranslatorFactory - Using ASTQueryTranslatorFactory

Not sure if this has effected the query being generated. Thanks.

Regards,
Joon

sanne.grinovero · **Posted:** Thu Jul 02, 2009 9:58 am

Hi Joon,
thanks for posting the query, it is looking wrong as it's not limiting the amount of data retrieved, I think it is a bug in hibernate core.

Quote:

[ INFO] 21:13:54 Environment - Hibernate 3.2.5

the other versions of the hibernate libs are targeting hibernate 3.3, I'd suggest you to upgrade to hibernate core 3.3.2, it would be very useful to know if this bug is still present in 3.3.2.

chewkj · **Joined:** Thu Jun 25, 2009 4:50 am **Posts:** 7

Hi Sanne,

Below are the changes to the hibernate lib version:

Code:

[ INFO] 22:52:55 Version - Hibernate Annotations 3.4.0.GA
[ INFO] 22:52:55 Environment - Hibernate 3.3.2.GA
[ INFO] 22:52:55 Environment - hibernate.properties not found
[ INFO] 22:52:55 Environment - Bytecode provider name : javassist
[ INFO] 22:52:55 Environment - using JDK 1.4 java.sql.Timestamp handling
[ INFO] 22:52:56 Version - Hibernate Commons Annotations 3.1.0.GA
[ INFO] 22:52:59 AnnotationBinder - Binding entity from annotated class: domain.Mrconso
[ INFO] 22:52:59 EntityBinder - Bind entity domain.Mrconso on table MRCONSO
[ INFO] 22:52:59 AnnotationConfiguration - Hibernate Validator not found: ignoring
[ INFO] 22:52:59 ConnectionProviderFactory - Initializing connection provider: org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider
[ INFO] 22:53:01 SettingsFactory - RDBMS: MySQL, version: 5.1.33
[ INFO] 22:53:01 SettingsFactory - JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-5.1.6 ( Revision: ${svn.Revision} )
[ INFO] 22:53:01 Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
[ INFO] 22:53:01 TransactionFactoryFactory - Using default transaction strategy (direct JDBC transactions)
[ INFO] 22:53:01 TransactionManagerLookupFactory - No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
[ INFO] 22:53:01 SettingsFactory - Automatic flush during beforeCompletion(): disabled
[ INFO] 22:53:01 SettingsFactory - Automatic session close at end of transaction: disabled
[ INFO] 22:53:01 SettingsFactory - JDBC batch size: 15
[ INFO] 22:53:01 SettingsFactory - JDBC batch updates for versioned data: disabled
[ INFO] 22:53:01 SettingsFactory - Scrollable result sets: enabled
[ INFO] 22:53:01 SettingsFactory - JDBC3 getGeneratedKeys(): enabled
[ INFO] 22:53:01 SettingsFactory - Connection release mode: on_close
[ INFO] 22:53:01 SettingsFactory - Maximum outer join fetch depth: 2
[ INFO] 22:53:01 SettingsFactory - Default batch fetch size: 1
[ INFO] 22:53:01 SettingsFactory - Generate SQL with comments: disabled
[ INFO] 22:53:01 SettingsFactory - Order SQL updates by primary key: disabled
[ INFO] 22:53:01 SettingsFactory - Order SQL inserts for batching: disabled
[ INFO] 22:53:01 SettingsFactory - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
[ INFO] 22:53:01 ASTQueryTranslatorFactory - Using ASTQueryTranslatorFactory
[ INFO] 22:53:01 SettingsFactory - Query language substitutions: {}
[ INFO] 22:53:01 SettingsFactory - JPA-QL strict compliance: disabled
[ INFO] 22:53:01 SettingsFactory - Second-level cache: enabled
[ INFO] 22:53:01 SettingsFactory - Query cache: disabled
[ INFO] 22:53:01 SettingsFactory - Cache region factory : org.hibernate.cache.impl.NoCachingRegionFactory
[ INFO] 22:53:01 SettingsFactory - Optimize cache for minimal puts: disabled
[ INFO] 22:53:01 SettingsFactory - Structured second-level cache entries: disabled
[ INFO] 22:53:01 SettingsFactory - Echoing all SQL to stdout
[ INFO] 22:53:01 SettingsFactory - Statistics: disabled
[ INFO] 22:53:01 SettingsFactory - Deleted entity synthetic identifier rollback: disabled
[ INFO] 22:53:01 SettingsFactory - Default entity-mode: pojo
[ INFO] 22:53:01 SettingsFactory - Named query checking : enabled
[ INFO] 22:53:01 Version - Hibernate Search 3.1.1.GA
[ INFO] 22:53:02 SessionFactoryImpl - building session factory
[ INFO] 22:53:02 SessionFactoryObjectFactory - Not binding factory to JNDI, no JNDI name configured
Hibernate: select this_.aui as aui0_0_, this_.cui as cui0_0_, this_.str as str0_0_ from MRCONSO this_

Notably the changes are

Quote:

[ INFO] 22:52:55 Environment - Hibernate 3.3.2.GA
[ INFO] 22:52:55 Environment - Bytecode provider name : javassist

However, it's still lead to the OutOfMemoryError ... Any alternative I can adopt pls? Thanks.

Regards,
Joon

sanne.grinovero · **Posted:** Thu Jul 02, 2009 5:32 pm

Hi,
some options:

wait for the bug to be fixed; I will try to give a look into it but I can't tell you how long it will take
provide a patch yourself, that would be very cool
work around the problem in your code, you could use the list() API instead of the scrollable, limiting your result size in a loop to load all elements in sub-lists
try the new batch indexing API which I committed last weekend, it's in trunk for hibernate-search 3.2.0-SNAPSHOT and requires hibernate core 3.5.0-SNAPSHOT... it would be great to receive feedback on that work but it's not production ready.

chewkj · **Joined:** Thu Jun 25, 2009 4:50 am **Posts:** 7

Hi Sanne,

Thanks for the advice, I'll evaluate on the options provided.

Regards,
Joon

sanne.grinovero · **Posted:** Sat Jul 04, 2009 4:43 am

I've had a brief chat with Emmanuel about this, he suggested to look into the database's support for scrollable results;
I've found this:
http://lists.mysql.com/java/8523
It appears MySQL doesn't support them, the driver implements it loading it all in memory.
the post is quite old, maybe it has been fixed, but for sure not in the versions you are using;
It's also possible that latest DB versions do support it but the driver has not been updated.
I'm going to try it myself moving all actors (DB server, JDBC driver, hibernate) to "nightly build" or latest I can find as I am curious and I might hit the same problem soon, but I'll definitely suggest you to switch away from scrollable for now;
use pagination with the list() API and setFirstResult and setMaxResults.

Or try the latest indexing API of Hibernate Search, it will give you more benefits like using several threads to speed up indexing: you will need that having millions of records; I've said it's not production ready as I can't say that for a snapshot version, but I do use it in production myself. Just be aware that the method names are not final.

marco-p · **Posted:** Fri Sep 04, 2009 10:09 am

I've tested this code right now with Oracle 9i v. 9.2.0.8 and it works, no OutOfMemory problem.
I had to comment out the line results.beforeFirst(); since it's unsupported with a forward only cursor.

sanne.grinovero · **Posted:** Fri Sep 04, 2009 11:06 am

Cool! thank you for your tests.
About "results.beforeFirst();", you're right that was very bad of me. fixed!
Today or tomorrow I'm committing an update, the public API methods get slightly renamed to better thought names and documentation gets in.
So look out for the method name change, but basically it's the same stuff.

Do you have some performance numbers? As a tip: if your CPU is not running 100% during indexing, you can go faster tweaking some of the parameters like batch size and numbers of threads.