Re: 8.2 is 30% better in pgbench than 8.3

On Mon, 2007-07-23 at 10:04 +0100, Heikki Linnakangas wrote:
> 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?

Neither of those things prevent the problem, they just make it less
likely. I don't think thats a good answer for production systems that
have response time service level agreements to meet. 

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

I agree, but only if we can prevent the starvation problem while we do
it, otherwise it just gets worse.

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