Re: 8.2 is 30% better in pgbench than 8.3

On Sat, 2007-07-21 at 13:30 -0400, Tom Lane wrote:

> Note to all: we ***HAVE TO*** settle on some reasonable default
> vacuum_cost_delay settings before we can ship 8.3. With no cost delay
> and two or three workers active, 8.3's autovac does indeed send
> performance into the tank.

Couple of thoughts here:

HOT will reduce the need for VACUUMs quite considerably, so multiple
concurrent VACUUMs becomes a quite rare situation. We should re-evaluate
this thought once we have taken the go/no-go decision for HOT in 8.3

The good thing about having multiple autovacuum daemons active is that
this reduces the possibility of having small tables starved while a
single large VACUUM runs to completion. My experience is that a single
large table can take many hours possibly frequently preventing 10,000
VACUUMs of small tables within that time.

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.

Potential solution: Each autovac worker gets a range of table sizes they
are allowed to VACUUM. This is set with an additional parameter which is
an array of gating values (i.e. one less gating value than number of
autovac workers). That way small VACUUMs are never starved out by large
ones. This is the same as having a Small:Medium:Large style queueing
system. We can work out how to make the queueing system self-tune by
observation of autovacuum frequency.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com