Re: significant vacuum issues - looking for suggestions

On Friday 24 August 2007 15:39:22 Tom Lane wrote:
> Kevin Kempter <kevin(at)kevinkempterllc(dot)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