To: Kevin Kempter <kevin@kevinkempterllc.com>
cc: "Pgsql performance" <pgsql-performance@postgresql.org>
Subject: Re: significant vacuum issues - looking for suggestions 
In-reply-to: <200708241357.23629.kevin@kevinkempterllc.com> 
References: <200708241357.23629.kevin@kevinkempterllc.com>
Comments: In-reply-to Kevin Kempter <kevin@kevinkempterllc.com>
	message dated "Fri, 24 Aug 2007 13:57:23 -0600"
Date: Fri, 24 Aug 2007 17:39:22 -0400
Message-ID: <6073.1187991562@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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