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: <1121087266.27427.35.camel@spectre.intellivid.com>
References: <1120839902.20657.197.camel@spectre.intellivid.com>
	<1121081504.3970.53.camel@localhost.localdomain>
	<1121087266.27427.35.camel@spectre.intellivid.com>
Content-Type: text/plain
Organization: 2nd Quadrant
Date: Mon, 11 Jul 2005 15:51:28 +0100
Message-Id: <1121093488.3970.70.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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.

Best Regards, Simon Riggs