Skip site navigation (1) Skip section navigation (2)

Re: 8.2 is 30% better in pgbench than 8.3

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>,"PostgreSQL-development" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: 8.2 is 30% better in pgbench than 8.3
Date: 2007-07-23 09:26:32
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Dave PageDate: 2007-07-23 10:06:59
Subject: Re: SSPI vs MingW
Previous:From: Magnus HaganderDate: 2007-07-23 09:09:11
Subject: SSPI vs MingW

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group