From: Ian Westmacott <ianw@intellivid.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: cost-based vacuum
Date: Wed, 13 Jul 2005 17:46:10 -0400
Message-ID: <1121291170.13208.25.camel@spectre.intellivid.com> (raw)
In-Reply-To: <1121287188.3970.296.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>
<1121154338.3970.106.camel@localhost.localdomain>
<1121190651.10346.342.camel@spectre.intellivid.com>
<1121270150.3970.256.camel@localhost.localdomain>
<1121280036.13208.8.camel@spectre.intellivid.com>
<20659.1121281132@sss.pgh.pa.us>
<1121287188.3970.296.camel@localhost.localdomain>
I can at least report that the problem does not seem to
occur with Postgres 8.0.1 running on a dual Opteron.
--Ian
On Wed, 2005-07-13 at 16:39, Simon Riggs wrote:
> On Wed, 2005-07-13 at 14:58 -0400, Tom Lane wrote:
> > Ian Westmacott <ianw@intellivid.com> writes:
> > > On Wed, 2005-07-13 at 11:55, Simon Riggs wrote:
> > >> On Tue, 2005-07-12 at 13:50 -0400, Ian Westmacott wrote:
> > >>> It appears not to matter whether it is one of the tables
> > >>> being written to that is ANALYZEd. I can ANALYZE an old,
> > >>> quiescent table, or a system table and see this effect.
> > >>
> > >> Can you confirm that this effect is still seen even when the ANALYZE
> > >> doesn't touch *any* of the tables being accessed?
> >
> > > Yes.
> >
> > This really isn't making any sense at all.
>
> Agreed. I think all of this indicates that some wierdness (technical
> term) is happening at a different level in the computing stack. I think
> all of this points fairly strongly to it *not* being a PostgreSQL
> algorithm problem, i.e. if the code was executed by an idealised Knuth-
> like CPU then we would not get this problem. Plus, I have faith that if
> it was a problem in that "plane" then you or another would have
> uncovered it by now.
>
> > However, these certainly do not explain Ian's problem, because (a) these
> > only apply to VACUUM, not ANALYZE; (b) they would only lock the table
> > being VACUUMed, not other ones; (c) if these locks were to block the
> > reader or writer thread, it'd manifest as blocking on a semaphore, not
> > as a surge in LWLock thrashing.
>
> I've seen enough circumstantial evidence to connect the time spent
> inside LWLockAcquire/Release as being connected to the Semaphore ops
> within them, not the other aspects of the code.
>
> Months ago we discussed the problem of false sharing on closely packed
> arrays of shared variables because of the large cache line size of the
> Xeon MP. When last we touched on that thought, I focused on the thought
> that the LWLock array was too tightly packed for the predefined locks.
> What we didn't discuss (because I was too focused on the other array)
> was the PGPROC shared array is equally tightly packed, which could give
> problems on the semaphores in LWLock.
>
> Intel says fairly clearly that this would be an issue.
>
> > >> Is that Xeon MP then?
> >
> > > Yes.
> >
> > The LWLock activity is certainly suggestive of prior reports of
> > excessive buffer manager lock contention, but it makes *no* sense that
> > that would be higher with vacuum cost delay than without. I'd have
> > expected the other way around.
> >
> > I'd really like to see a test case for this...
>
> My feeling is that a "micro-architecture" test would be more likely to
> reveal some interesting information.
>
> Best Regards, Simon Riggs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
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: ianw@intellivid.com, simon@2ndquadrant.com, tgl@sss.pgh.pa.us
Subject: Re: cost-based vacuum
In-Reply-To: <1121291170.13208.25.camel@spectre.intellivid.com>
* 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