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