Re: Re: Memory Leak / Prepared Statement - Mailing list pgsql-jdbc

Barry,

The PreparedStatement references were being held in a Hashtable cache inside
Enhydra.  I was never actually using PreparedStatements directly, which was part
of what made finding the problem difficult.

For those who use Enhydra, there is a parameter called "maxPreparedStatements" in
the DatabaseManager.  When using very large select statements, setting this
parameter too high can cause OutOfMemoryErrors.  After adjusting this, the problem
was solved.

Thanks again for the help.

John

Barry Lind wrote:

> John,
>
> Objects in java don't get garbage collected when other objects hold
> references to them.  So the challenge now is to understand what objects
> are holding references to these PreparedStatment objects.  In OptimizeIt
> you can look at the dependency graph to determine what objects are
> holding references to the PreparedStatement objects.  From there you
> should be able to figure out how to fix the problem.
>
> You say that you are using Enhydra.  It is certainly possible that how
> you are using Enhydra could be causing it to hold the references.  For
> example are you always ensuring that you are closing your
> PreparedStatement objects? (ideally in a try/finally block to ensure
> that you don't forget to close them when exceptions are thrown).  I
> could see how not closing PreparedStatements could easily lead to
> Enhydra keeping references to the PreparedStatements.
>
> thanks,
> --Barry
>
> John Cook wrote:
>
> > Barry,
> >
> > I got OptimizeIt configured, and it looks like it is jdbc2/PreparedStatement
> > which is not being garbage collected.  All of my PreparedStatement s stay
> > visible in Optimize it and the number of instances never decreases.  What
> > other information can I provide to help determine if this is a memory leak or
> > another problem?  I am using Postgresql 7.1.2 and the driver that accompanied
> > it.
> >
> > Thanks.
> >
> > John
> >
> > Barry Lind wrote:
> >
> >
> >>My guess is that this is unlikely to be the result of the ThreadLocal
> >>issues and also I doubt 1.4 will have any effect.  This sounds like a
> >>memory leak which could be in the driver or in your application code.  I
> >>also doubt that the use of LIKE is the problem as the JDBC code doesn't
> >>parse the SQL so it doesn't know if you are using LIKE or not.
> >>
> >>To fix this we would either need a reproducable test case that you could
> >>submit such that we could reproduce the problem and fix it, or you could
> >>use a java memory profiler to track down what objects are being
> >>allocated and not released.  I personally like OptimizeIt as it has
> >>helped me solve quite a few memory leak problems with java code.
> >>
> >>thanks,
> >>--Barry
> >>
> >>John Cook wrote:
> >>
> >>
> >>>Hi,
> >>>
> >>>I noticed several postings a month ago regarding a memory leak brought
> >>>about by the use of Thread.Local in jdbc2/PreparedStatement.java.  I
> >>>have what seems like a similar problem, but I am not sure.  I have
> >>>applied the patch, but I still have my problem (mine is not really
> >>>associated with DateFormat usage.)
> >>>
> >>>I am running Enhydra Application Server 3.1 on top of Postgres and I
> >>>have been running an application that tries to match text strings to
> >>>items in my database.  It has been running fine for months until
> >>>I recently made a modification that uses a lot of "LIKE" statements
> >>>(i.e. anywhere from 2-200 at a time.)  Performance is fine, but memory
> >>>starts getting eaten up and I eventually hits an OutOfMemoryError.  The
> >>>bigger the prepared statement (i.e. the more LIKEs) the more memory is
> >>>eaten and the faster I run out of memory.  Oddly enough, before I added
> >>>the LIKE statements, I was running fine and, although I used a lot of
> >>>prepared statements (the app server did), memory was not being eaten
> >>>like this.
> >>>
> >>>Has anyone had a similar experience and is it related to the
> >>>Thread.Local problem?  Does anyone know if this is addressed in the
> >>>1.4.0 beta JDK or is it scheduled to be addressed in an upcoming
> >>>Postgresql release?
> >>>
> >>>Any help would be greatly appreciated.
> >>>
> >>>Thanks in advance.
> >>>
> >>>John Cook
> >>>
> >>>
> >>>
> >>>
> >>>---------------------------(end of broadcast)---------------------------
> >>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >>>
> >>>
> >>>
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org