Hi,

We are currently having a problem that our Postgres DB is throwing an SQL error which states that it's 'out of memory'.

What we have is a DB with 1 table that has 3.9 million records. We need to find certain records that are to be processed by a Java App so we do a "select id from table where type=a and condition in (1, 2) order by id limit 2000". When this query gets executed we see the memory on the DB Server increasing and after it has finishes it drops a bit but we see it growing a few MB per few minutes. This has caused an out of memory after the system has been processing for a day or 2. The query is heavy because of the order by but that does not explain why the memory is increasing.

We use a DAO which extends HibernateDaoSupport and the method (findIds) has been marked as propagation_required. So we assume Spring manages the transaction and thus closing of the resultset. Here's part of the DAO method: Query q = getSession(false).createQuery(query);
q.setMaxResults(RESULT_SIZE);
List list = q.list();

No rocketscience but somehow this causes a problem on the DB Server.
Does anyone have an idea?

Thanks,


Hibernate version: 3.0.5

*** [WARN 2006-03-11 20:04:26,288 main] org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:71)
SQL Error: 0, SQLState: null
*** [ERROR 2006-03-11 20:04:26,292 main] org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:72)
Batch entry 0 update activiteit set activiteit_type=102, activiteit_subtype=1305, start_dt=2006-03-11 20:03:40.940000+0100, moeder_id=NULL, toestand=8022, laatste_actie_dt=2006-03-11 20:03:41.012000+0100, uiterlijke_actie_dt=2006-03-11 20:03:40.940000+0100, pl_id=NULL, communicatie_partner=1905, nr_1=NULL, nr_2=NULL, nr_3=NULL, nr_4=NULL, nr_5=NULL, tekst_1=NULL, tekst_2=NULL, tekst_3=NULL, tekst_4=NULL, tekst_5=NULL where activiteit_id=11079994 was aborted. Call getNextException to see the cause.
*** [WARN 2006-03-11 20:04:26,293 main] org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:71)
SQL Error: 0, SQLState: 53200
*** [ERROR 2006-03-11 20:04:26,293 main] org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:72)
ERROR: out of memory
*** [ERROR 2006-03-11 20:04:26,318 main] org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:277)
Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:181)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:226)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:137)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:730)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:324)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:490)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:495)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:468)
at org.springframework.transaction.interceptor.TransactionAspectSupport.doCommitTransactionAfterReturning(TransactionAspectSupport.java:258)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:174)
at $Proxy3.invokeWithinNewTx(Unknown Source)
at nl.ictu.spg.service.workflow.ActiviteitCMP.persistNowWithNewGebeurtenis(ActiviteitCMP.java:546)
at nl.ictu.spg.service.workflow.WorkflowActiviteit.persistNowWithNewGebeurtenis(WorkflowActiviteit.java:320)
at nl.ictu.spg.service.workflow.berichten.LO3.LO3BerichtDispatcher.executeCycle(LO3BerichtDispatcher.java:224)
at nl.ictu.spg.service.workflow.berichten.LO3.LO3BerichtDispatcher.dispatch(LO3BerichtDispatcher.java:177)
at nl.ictu.spg.service.workflow.berichten.LO3.LO3BerichtDispatcher.dispatch(LO3BerichtDispatcher.java:256)
at nl.ictu.spg.service.request.lo3.LO3RequestProcessor.runOnce(LO3RequestProcessor.java:194)
at nl.ictu.spg.service.plupdate.PlUpdateBackgroundService.main(PlUpdateBackgroundService.java:69)
Caused by: java.sql.BatchUpdateException: Batch entry 0 update activiteit set activiteit_type=102, activiteit_subtype=1305, start_dt=2006-03-11 20:03:40.940000+0100, moeder_id=NULL, toestand=8022, laatste_actie_dt=2006-03-11 20:03:41.012000+0100, uiterlijke_actie_dt=2006-03-11 20:03:40.940000+0100, pl_id=NULL, communicatie_partner=1905, nr_1=NULL, nr_2=NULL, nr_3=NULL, nr_4=NULL, nr_5=NULL, tekst_1=NULL, tekst_2=NULL, tekst_3=NULL, tekst_4=NULL, tekst_5=NULL where activiteit_id=11079994 was aborted. Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2392)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1257)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:334)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2451)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:57)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:174)
... 22 more

Name and version of the database you are using: Postgres 8.0.3

may be it helps when you work with BATCH PROCESSING
look at hibernate docu at page 137

Hi,

Thanks for the reply. What we do is limit the result by 2000, this query is managed by Spring with PROPAGATION_REQUIRED so it runs within it's own TX. After that each element in the result gets processed within a new TX so batch processing isn't necessary but i'll have a look at the documentation.

But that doesn't explain why the memory keeps growing on the Postgres server. Somehow something is not properly closed but we'll try some other configurations as well.

Cheers,
Maarten