Thread: out of memory using Postgres with Spring/Hibernate/Java

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 where to look?

Thanks,
Maarten

ps: Java stacktrace: *** [WARN 2006-03-11 20:04:26,288
main]
org.hibernate.util.JDBCExceptionReporter.logExcept
ions(JDBCExceptionReporter.java:71)
SQL Error: 0, SQLState: null
*** [ERROR 2006-03-11 20:04:26,292 main]
org.hibernate.util.JDBCExceptionReporter.logExcept
ions(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.logExcept
ions(JDBCExceptionReporter.java:71)
SQL Error: 0, SQLState: 53200
*** [ERROR 2006-03-11 20:04:26,293 main]
org.hibernate.util.JDBCExceptionReporter.logExcept
ions(JDBCExceptionReporter.java:72)
ERROR: out of memory
*** [ERROR 2006-03-11 20:04:26,318 main]
org.hibernate.event.def.AbstractFlushingEventListe
ner.performExecutions(AbstractFlushingEventListene
r.java:277)
Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could
not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledN
onSpecificException(SQLStateConverter.java:82)
at org.hibernate.exception.SQLStateConverter.convert(
SQLStateConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.conver
t(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(Ab
stractBatcher.java:181)
at org.hibernate.engine.ActionQueue.executeActions(Ac
tionQueue.java:226)
at org.hibernate.engine.ActionQueue.executeActions(Ac
tionQueue.java:137)
at org.hibernate.event.def.AbstractFlushingEventListe
ner.performExecutions(AbstractFlushingEventListene
r.java:274)
at org.hibernate.event.def.DefaultFlushEventListener.
onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.j
ava:730)
at org.hibernate.impl.SessionImpl.managedFlush(Sessio
nImpl.java:324)
at org.hibernate.transaction.JDBCTransaction.commit(J
DBCTransaction.java:86)
at org.springframework.orm.hibernate3.HibernateTransa
ctionManager.doCommit(HibernateTransactionManager.
java:490)
at org.springframework.transaction.support.AbstractPl
atformTransactionManager.processCommit(AbstractPla
tformTransactionManager.java:495)
at org.springframework.transaction.support.AbstractPl
atformTransactionManager.commit(AbstractPlatformTr
ansactionManager.java:468)
at org.springframework.transaction.interceptor.Transa
ctionAspectSupport.doCommitTransactionAfterReturni
ng(TransactionAspectSupport.java:258)
at org.springframework.transaction.interceptor.Transa
ctionInterceptor.invoke(TransactionInterceptor.jav
a:106)
at org.springframework.aop.framework.ReflectiveMethod
Invocation.proceed(ReflectiveMethodInvocation.java
:144)
at org.springframework.aop.framework.JdkDynamicAopPro
xy.invoke(JdkDynamicAopProxy.java:174)
at $Proxy3.invokeWithinNewTx(Unknown Source)
at nl.ictu.spg.service.workflow.ActiviteitCMP.persist
NowWithNewGebeurtenis(ActiviteitCMP.java:546)
at nl.ictu.spg.service.workflow.WorkflowActiviteit.pe
rsistNowWithNewGebeurtenis(WorkflowActiviteit.java
:320)
at nl.ictu.spg.service.workflow.berichten.LO3.LO3Beri
chtDispatcher.executeCycle(LO3BerichtDispatcher.ja
va:224)
at nl.ictu.spg.service.workflow.berichten.LO3.LO3Beri
chtDispatcher.dispatch(LO3BerichtDispatcher.java:1 77)
at nl.ictu.spg.service.workflow.berichten.LO3.LO3Beri
chtDispatcher.dispatch(LO3BerichtDispatcher.java:2 56)
at nl.ictu.spg.service.request.lo3.LO3RequestProcesso
r.runOnce(LO3RequestProcessor.java:194)
at nl.ictu.spg.service.plupdate.PlUpdateBackgroundSer
vice.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$BatchR
esultHandler.handleError(AbstractJdbc2Statement.ja
va:2392)
at org.postgresql.core.v3.QueryExecutorImpl.processRe
sults(QueryExecutorImpl.java:1257)
at org.postgresql.core.v3.QueryExecutorImpl.execute(Q
ueryExecutorImpl.java:334)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execut
eBatch(AbstractJdbc2Statement.java:2451)
at org.apache.commons.dbcp.DelegatingStatement.execut
eBatch(DelegatingStatement.java:294)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(
BatchingBatcher.java:57)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(Ab
stractBatcher.java:174)
... 22 more


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On 3/15/06, maarten roosendaal <mroosendaal@yahoo.com> wrote:
> 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.

What does explain show for the query? Are the fields indexed
appropriately? Have you analyzed the table recently?

Postgres needs to store the ordered results somewhere so of course
that explains the memory increase.

--
Postgresql & php tutorials
http://www.designmagick.com/

maarten roosendaal wrote:
> 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.

So it's postgresql's memory usage that is increasing?
 From what to what?
How many backends are we talking about, and does this happen to all of them?
How much memory does the machine have and what else is using it?
Oh, and what version of PG, JDBC, Spring, Hibernate etc?

 > This has caused an out of memory
> after the system has been processing for a day or 2.

Each new connection will start a new backend, so presumably this
connection is continuously active for days.

> 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.

First step has to be to turn query-logging on for the problem
application. It might be that there is a memory leak in PostgreSQL, but
it might be that something isn't being released properly by the
applicaton libraries.

You can turn statement logging on or off in the postgresql.conf file, or
by issuing "set log_statement=XXX" as a query after connecting. That way
we can see exactly what is happening.

--
   Richard Huxton
   Archonet Ltd

On Tue, 2006-03-14 at 22:06 -0800, maarten roosendaal wrote:

> 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.

Could be a memory leak in PG sort, so please explain further.

> 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 where to look?

The java backtrace mentions bulk update. Where does that fit into this?

You've got a rather large stack of software there and you need to
isolate the problem. If you are running multiple system components on
one system then it is possible that a memory leak in one component can
cause a problem in another. A memory intensive task such as sort would
then be likely to highlight the problem, but that doesn't mean its the
cause of the leak.

If you can reproduce this problem with a simple repetitive test case
using a script executing psql, that will help. If you cannot, then that
points to a failure in another system component.

Right now, you've not told us much about the query, what release level
you are using etc. Are you staying connected and re-issuing the same
command repeatedly or? We'll need to know more about the memory growth:
which processes does it effect.

We'll also need to know about any custom datatypes involved, or any
parts of the query executing user developed code.

Best Regards, Simon Riggs