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

Hi,

On 23 November 2011 13:20, Lonni J Friedman <netllama(at)gmail(dot)com> wrote:
>  I investigated, and found that for the past ~18 hours,
> there's one autovacuum process that has been running, and not making
> any obvious progress:

snip...

> I'm using the defaults for all the *vacuum* options in
> postgresql.conf, except for:
> log_autovacuum_min_duration = 2500

Defaults are:
autovacuum_vacuum_cost_delay = 20 msec
autovacuum_vacuum_cost_limit = -1 (ie vacuum_cost_limit is used)
vacuum_cost_limit = 200

If table is busy -- many updates and deletes then auto vacuum exhausts
cost limit almost immediately. You can try to set
autovacuum_vacuum_cost_delay to -1 (which disables cost based auto
vacuum) but you don't want to saturate your disks. Other option is to
increase vacuum_cost_limit/autovacuum_vacuum_cost_limit

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)