Subject: Re: cost-based vacuum
From: Simon Riggs <simon@2ndquadrant.com>
To: Ian Westmacott <ianw@intellivid.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1121093488.3970.70.camel@localhost.localdomain>
References: <1120839902.20657.197.camel@spectre.intellivid.com>
	<1121081504.3970.53.camel@localhost.localdomain>
	<1121087266.27427.35.camel@spectre.intellivid.com>
	<1121093488.3970.70.camel@localhost.localdomain>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Tue, 12 Jul 2005 08:45:38 +0100
Message-Id: <1121154338.3970.106.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Mon, 2005-07-11 at 15:51 +0100, Simon Riggs wrote:
> On Mon, 2005-07-11 at 09:07 -0400, Ian Westmacott wrote:
> > On Mon, 2005-07-11 at 07:31, Simon Riggs wrote:
> > > The ANALYZE commands hold read locks on the tables you wish to write to.
> > > If you slow them down, you merely slow down your write transactions
> > > also, and then the read transactions that wait behind them. Every time
> > > the ANALYZE sleeps it wakes up the other transactions, which then
> > > realise they can't move because of locks and then wake up the ANALYZEs
> > > for another shot. The end result is that you introduce more context-
> > > switching, without any chance of doing more useful work while the
> > > ANALYZEs sleep.
> > 
> > Let me make sure I understand.  ANALYZE acquires a read
> > lock on the table, that it holds until the operation is
> > complete (including any sleeps).  That read lock blocks
> > the extension of that table via COPY.  Is that right?
> > 
> > According to the 8.0 docs, ANALYZE acquires an ACCESS SHARE
> > lock on the table, and that conflicts only with ACCESS
> > EXCLUSIVE.  Thats why I didn't think I had a lock issue,
> > since I think COPY only needs ROW EXCLUSIVE.  Or perhaps
> > the transaction needs something more?
> 
> The docs are correct, but don't show catalog and buffer locks.
> 
> ...but on further reading of the code there are no catalog locks or
> buffer locks held across the sleep points. So, my explanation doesn't
> work as an explanation for the sleep/no sleep difference you have
> observed.

I've been through all the code now and can't find any resource that is
held across a delay point. Nor any reason to believe that the vacuum
cost accounting would slow anything down.

Since vacuum_cost_delay is a userset parameter, you should be able to
SET this solely for the analyze_thread. That way we will know with more
certainty that it is the analyze_thread that is interfering.

What is your default_statistics_target?
Do you have other stats targets set?

How long does ANALYZE take to run, with/without the vacuum_cost_delay?

Thanks,

Best Regards, Simon Riggs