Subject: Re: Postgres consuming way too much memory???
From: Mark Lewis <mark.lewis@mir3.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: jody brownell <jody.brownell@q1labs.com>, pgsql-performance@postgresql.org
In-Reply-To: <28321.1150385679@sss.pgh.pa.us>
References: <200606141618.37849.jody.brownell@q1labs.com>
	<200606150901.19201.jody.brownell@q1labs.com>
	<200606150915.10306.jody.brownell@q1labs.com>
	<200606151202.50939.jody.brownell@q1labs.com>
	<28321.1150385679@sss.pgh.pa.us>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: MIR3, Inc.
Date: Thu, 15 Jun 2006 09:57:25 -0700
Message-Id: <1150390645.31200.53.camel@archimedes>
Mime-Version: 1.0

On Thu, 2006-06-15 at 11:34 -0400, Tom Lane wrote:
> "jody brownell" <jody.brownell@q1labs.com> writes:
> > When postgresql starts to go into this bloating state, I can only make it happen from my java app.
> 
> That's interesting.  The JDBC driver uses protocol features that aren't
> used by psql, so it's possible that the leak is triggered by one of
> those features.  I wouldn't worry too much about duplicating the problem
> from psql anyway --- a Java test case will do fine.
> 
> > I am going to try closing the connection after each TX to see if this
> > resolves it for now. If not, I will write a java app, stored procedure
> > (table etc) reproduce it without our application.


Just to mention another possible culprit; this one doesn't seem all that
likely to me, but at least it's easy to investigate.

With DBCP and non-ancient versions of the JDBC driver that use v3
protocol and real prepared statements, it is possible to (mis)configure
the system to create an unbounded number of cached prepared statements
on any particular connection.  Older versions of DBCP were also known to
have bugs which aggravated this issue when prepared statement caching
was enabled, IIRC.

-- Mark Lewis