Re: autovac hung/blocked

On Tuesday November 14 2006 1:02 pm, Ed L. wrote:
> On Tuesday November 14 2006 12:56 pm, Jim C. Nasby wrote:
> > You don't have the vacuum cost delay settings set
> > unreasonably high, do you?
>
> On Tuesday November 14 2006 12:56 pm, you wrote:
> > You don't have the vacuum cost delay settings set
> > unreasonably high, do you?
>
> I'm not sure. Here's what we're running:

Well, I think we clearly have an HPUX CPU bottleneck (long pri
queue, high cpu utilization, high user cpu %, lots of processes
"blocked on pri").

It seems to get worst and slow all queries down across the board
when autovac tries to vacuum a 15GB table. I'm guessing this is
flushing the OS/DB caches, exacerbating the CPU bottleneck.

I'm also not sure what to do about it beyond the customer buying
some politically/financially expensive CPUs. The table in
question appears to be the pathological case for vacuum: very
large with lots of frequent UPDATEs. It's essentially a log
table. So the other options seem to be figure out how to
partition the table so as to minimize size of the data needing
vacuuming, or to redesign if possible to replace UPDATEs with
INSERTs. Other ideas?

Ed