After some good research, I switched to the development beta version 3.1.0b of Hibernate Search after Emmanuel Bernard suggested that the old way (hibernate batch size parameter) was deprecated and now to use the latest ScrollableResults/flushToIndexes solution but this is still suffering with OutOfMemoryError (for me).

ScrollingResultSet should batch by 50 products at a time ... has anyone got this working for MySQL? Is there some parameter to be set on the Hibernate SessionFactory? Please suggest why scrolling is not working ... ?

Kind regards,

Mark Dathorne

Hibernate version: 3.2
Hibernate Search version: 3.1.0beta1

Full stack trace of any exception that occurs:
Mon Jul 21 02:58:15 BST 2008 indexing Products for Hibernate Search ...
Mon Jul 21 02:58:15 BST 2008 new indexProducts ... Scrollable ...Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at com.mysql.jdbc.Buffer.<init>(Buffer.java:58)
at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1441)
at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2816)
at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:467)
at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2510)
at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1746)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2135)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:184)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1785)
at org.hibernate.loader.Loader.scroll(Loader.java:2278)
at org.hibernate.loader.criteria.CriteriaLoader.scroll(CriteriaLoader.java:91)
at org.hibernate.impl.SessionImpl.scroll(SessionImpl.java:1537)
at org.hibernate.impl.CriteriaImpl.scroll(CriteriaImpl.java:297)
at com.dathorne.houseparts.helper.ProductManager.indexProducts(ProductManager.java:357)
at com.dathorne.houseparts.helper.ProductManager$$FastClassByCGLIB$$3e4eb5c6.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:628)
at com.dathorne.houseparts.helper.ProductManager$$EnhancerByCGLIB$$91f98b72.indexProducts(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy17.indexProducts(Unknown Source)

Name and version of the database you are using: MySQL 5.0.22


method source:

public void indexProducts()
{
int BATCH_SIZE = 50;

Session session = this.getHibernateTemplate().getSessionFactory().getCurrentSession();
FullTextSession fullTextSession = Search.getFullTextSession(session);
fullTextSession.setFlushMode(FlushMode.MANUAL);
fullTextSession.setCacheMode(CacheMode.IGNORE);
org.hibernate.Transaction transaction = fullTextSession.beginTransaction();

//Scrollable results will avoid loading too many objects in memory
System.out.print(new Date() + " new indexProducts ... Scrollable ...");
ScrollableResults results = fullTextSession.createCriteria( com.dathorne.houseparts.models.Product.class )
.setFetchSize(BATCH_SIZE)
.scroll( ScrollMode.FORWARD_ONLY );

int index = 0;
while( results.next() ) {
index++;
fullTextSession.index( results.get(0) ); //index each element
if (index % BATCH_SIZE == 0) {
fullTextSession.flushToIndexes(); //apply changes to indexes
fullTextSession.clear(); //clear since the queue is processed
System.out.print(new Date() + " ... batch flush ...");
}
}

System.out.print(new Date() + " ... indexProducts ... Scrollable ... DONE!!!");
transaction.commit();
System.out.print(new Date() + " ... commit ... DONE!!!");
}

it might be because MySql does not properly support ScrollableResults.
There is an old post about such a limitation but it might as well not be relevant anymore.

http://forums.sun.com/thread.jspa?threadID=451346&messageID=2050767

Are you sure you use this exact routine and are you sure you're using the latest MySQL DB and Drivers?

Hi Emmanuel, I was hoping that it was you who would reply.

I will re-check again but the code I copied down is the routine I am running, I upgraded to latest Hibernate Search (3.1.0b), latest MySQL driver (5.1.6).

Does Spring conflict with the session/transaction handling?
I will check the version of Hibernate itself but I wanted to make sure that there are no config parameters that were missed.

Another sleepless night beckons.

Kind regards,

Mark

Hello,
I used to test batch indexing with older versions of Search and Hibernate using MS SQL server 2000 and had some problems like this; I had to close the Session periodically, but this is not longer required.

Currently using latest Hibernate jars and MySQL 5.0.45, jdbc driver 5.0.8 I don't have any more problems, even with more than 6 million elements.
Would it be possible to try using the 5.0 DB and relative JDBC driver?

regards,

Correction: MySQL 5.0.22 (Debian/Ubuntu)

Streaming/Scrolling seems to apply to MySQL version 5.0.3+

"When using versions of the JDBC driver earlier than 3.2.1, and connected to server versions earlier than 5.0.3, the setFetchSize() method has no effect, other than to toggle result set streaming as described above."

So should work ok for 5.0.22
Can anyone else confirm this to be correct, please?

Can't think what the problem could be .... something with the Spring config, maybe?
Clutching at straws now.

Mark Dathorne

Resolved by implementing my own batch processing control (since ScrollableResult was not working).
Simply by retrieving the last id (of the auto increment key) and then paging by a given BATCH_SIZE (1000)
... Used Flushing/clearing the indices as described at top of this thread.