pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Simon Riggs <simon@2ndquadrant.com>
To: Ian Westmacott <ianw@intellivid.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: cost-based vacuum
Date: Tue, 12 Jul 2005 08:45:38 +0100
Message-ID: <1121154338.3970.106.camel@localhost.localdomain> (raw)
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>
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
view thread (13+ messages) latest in thread
Message-ID: <1121154338.3970.106.camel@localhost.localdomain> Permalink: ../1121154338.3970.106.camel@localhost.localdomain/ Also on: postgresql.org/message-id/1121154338.3970.106.camel@localhost.localdomain ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-performance@postgresql.org Cc: simon@2ndquadrant.com, ianw@intellivid.com Subject: Re: cost-based vacuum In-Reply-To: <1121154338.3970.106.camel@localhost.localdomain> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox