From: Kevin Kempter <kevin@kevinkempterllc.com>
To: "Pgsql performance" <pgsql-performance@postgresql.org>
Subject: Re: significant vacuum issues - looking for suggestions
Date: Fri, 24 Aug 2007 23:34:23 -0600
User-Agent: KMail/1.9.7
References: <200708241357.23629.kevin@kevinkempterllc.com>
	<6073.1187991562@sss.pgh.pa.us>
In-Reply-To: <6073.1187991562@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200708242334.24191.kevin@kevinkempterllc.com>

On Friday 24 August 2007 15:39:22 Tom Lane wrote:
> Kevin Kempter <kevin@kevinkempterllc.com> writes:
> > The development folks that have been here awhile tell me that it seems
> > like when they have a query (not limited to vacuum processes) that has
> > been running for a long time (i.e. > 5 or 6 hours) that the query sort of
> > "goes crazy" and the entire system gets pegged until they kill that
> > process. - I've not heard of this
>
> Me either, but I wonder whether their queries are tickling some memory
> leak.  I could imagine that what they are seeing is the backend process
> growing slowly until it starts to swap, and then continuing to grow and
> needing more and more swap activity.  Once you get over the knee of that
> curve, things get real bad real fast.  It might not be a bad idea to run
> the postmaster under a (carefully chosen) ulimit setting to cut such
> things off before the system starts swapping.  Other things to look at:
>
> * what exactly gets "pegged" --- is it CPU or I/O bound?  Watching
> "vmstat 1" is usually a good diagnostic since you can see CPU, swap,
> and regular disk I/O activity at once.
>
> * is there really not any pattern to the queries that cause the problem?
> I don't think 8.1.4 has any widespread leakage problem, but they might
> be tickling something isolated, in which case 8.2 is not necessarily
> gonna fix it.  If you can produce a test case showing this behavior it'd
> be time to call in pgsql-hackers.
>
> Your other points seem pretty well covered by other replies.
>
> 			regards, tom lane

Thanks everyone for the help. I'll first up the memory settings like Bill 
suggested and then see where I'm at. Moving fwd I'll see if I have a test 
case that I can re-create, plus I may try constraining the postmaster via a 
ulimit setting, again based on what I see once the cluster is allowed to use 
the memory it should have been given up front.

/Kevin