Re: 8.2 is 30% better in pgbench than 8.3

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.
>
> 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.

default autovac_workers is 3, so wouldn't you need three, not two, large
VACUUMs to starvate a smaller table?

Instead of queuing, how about increasing autovac_workers if starvation
is a concern?

I'd like to set a default autovacuum_vacuum_cost_delay anyway. Without
it, autovacuum is a performance hit when it kicks in, even if there's
only one of them running, and even if it only lasts for a short time.
It's an unpleasant surprise for someone who's new to PostgreSQL and
doesn't yet understand how vacuum and autovacuum works.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com