Re: tuning autovacuum

Alvaro Herrera <alvherre@commandprompt.com> writes:
> In any case, given the "rebalancing" feature of vacuum_cost_delay (which
> increases the delay the more workers there are), the only "solution" to
> the problem of falling behind is reducing the delay parameter.  If you
> just add more workers, they start working more slowly.

Yeah.  Note also that if you're not running a pretty recent minor
release, you're exposed to this bug:

Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master [b58c25055] 2010-11-19 22:28:20 -0500
Branch: REL9_0_STABLE Release: REL9_0_2 [b5efc0940] 2010-11-19 22:28:25 -0500
Branch: REL8_4_STABLE Release: REL8_4_6 [fab2af30d] 2010-11-19 22:28:30 -0500
Branch: REL8_3_STABLE Release: REL8_3_13 [6cb9d5113] 2010-11-19 22:28:35 -0500
   Fix leakage of cost_limit when multiple autovacuum workers are active.      When using default
autovacuum_vac_cost_limit,autovac_balance_cost relied   on VacuumCostLimit to contain the correct global value ... but
afterthe   first time through in a particular worker process, it didn't, because we'd   trashed it in previous
iterations. Depending on the state of other autovac   workers, this could result in a steady reduction of the effective
 cost_limit setting as a particular worker processed more and more tables,   causing it to go slower and slower.
Spottedby Simon Poole (bug #5759).   Fix by saving and restoring the GUC variables in the loop in do_autovacuum.
Inpassing, improve a few comments.      Back-patch to 8.3 ... the cost rebalancing code has been buggy since it was
putin.
 

        regards, tom lane