Simon Riggs wrote:
> The bad thing about having multiple autovacuum daemons active is that
> you can get two large VACUUMs running at the same time. This gives you
> the same small-VACUUM-starvation problem we had before, but now the
> effects of two VACUUMs kill performance even more. I would suggest that
> we look at ways of queueing, so that multiple large VACUUMs cannot
> occur. Setting vacuum_cost_delay will still allow multiple large VACUUMs
> but will make the starvation problem even worse as well. If we allow
> that situation to occur, I think I'd rather stick to autovac_workers=1.
> We will still have this potential problem even with HOT.
We already discussed all this to death before feature freeze. I'm not
sure if it's a good idea to try to come up with new heuristics for the
thing this late. Feel free to work on it for 8.4 though!
I also wonder whether you have noticed the "balancing" code in autovac.
Whenever more than one autovac workers are running, they split the
available I/O allocated to them fairly, so that each one delays more
frequently than if it was running alone. The net effect is supposed to
be that no matter how many workers are running, your vacuum delay
settings are respected.
In any case, I think a better solution to the starvation problem caused
by huge tables is not skipping the vacuuming of them, but making it less
wasteful, for example with the DSM.
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings