Re: autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

Lonni J Friedman <netllama(at)gmail(dot)com> writes:
> Thanks for your prompt reply. I was pretty sure that I was using the
> default, but just to confirm, I just ran:
> 'SHOW vacuum_cost_delay;'

What about autovacuum_vacuum_cost_delay? The selects seem to be
delaying for 32msec, which is not the default for anything.

> Is there no way to safely kill off this autovacuum process that
> doesn't involve shutting down & restarting the database?

Sending it a SIGINT ought to be safe enough, though I don't think that
is necessarily advisable, because the next autovacuum will probably take
just as long. Killing this one will mainly mean you've wasted (much of)
the work it did so far.

Before getting hasty I'd suggest identifying what table (or index) it's
working on --- lsof on the process to see what FD 95 is connected to
would be the first step. I'm thinking it's an index since the seek
addresses don't seem to be consecutive. And it might be worth watching
the seek addresses for awhile to see if you can prove that it's looping
--- if it is, that might be an indication of a corrupt index. If it
isn't, but is just steadily working through the index, you'd be best
advised to have patience.

regards, tom lane