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

On Tue, Nov 22, 2011 at 6:57 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Lonni J Friedman <netllama(at)gmail(dot)com> writes:
>> When I strace PID 30188, I see tons of this scrolling past quickly,
>> but I'm not really sure what it means beyond a 'Timeout' not looking
>> good:
>> select(0, NULL, NULL, NULL, {0, 32000}) = 0 (Timeout)
>> lseek(95, 753901568, SEEK_SET)          = 753901568
>> read(95, "\202\1\0\0\260\315\250\245\1\0\0\0\220\0\360\20\360\37\4
>> \0\0\0\0p\237\0\1\360\236\0\1"..., 8192) = 8192
>> lseek(95, 753917952, SEEK_SET)          = 753917952
>> read(95, "\202\1\0\0 N\253\245\1\0\0\0\220\0\360\20\360\37\4
>> \0\0\0\0p\237\0\1\360\236\0\1"..., 8192) = 8192
>> select(0, NULL, NULL, NULL, {0, 32000}) = 0 (Timeout)
>
> I'm betting the selects are implementing vacuum_cost_delay, and that
> the reason this is taking forever is that you have that cranked up
> to an unreasonably high value.  There's no evidence of looping in
> what you showed us, because the seek addresses are changing.

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;'

and it returned 0. Any other suggestions?

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

thanks