From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Jim C. Nasby <jnasby@pervasive.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Jeff Kirby <Jeff.Kirby@wicourts.gov>
Cc: pgsql-admin@postgresql.org
Subject: Re: Vacuum Full Analyze Stalled
Date: Mon, 03 Oct 2005 16:48:40 -0400
Message-ID: <14993.1128372520@sss.pgh.pa.us> (raw)
In-Reply-To: <20051003201225.GA11527@surnet.cl>
References: <s3413ba7.062@gwmta.wicourts.gov>
<20051003194114.GD40138@pervasive.com>
<20051003201225.GA11527@surnet.cl>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> However, I'm looking at the autovacuum code to see why it's sitting
> holding locks on the small table and not vacuuming it. I see on the
> pg_locks output that process 3158 (autovacuum) has got locks on the
> table and index, but it apparently isn't vacuuming the table. If this
> is correct, it's a bug. However I can't seem to find out why this
> happens.
We can see clearly from the pg_locks output that VACUUM isn't waiting
for an lmgr lock, so the problem must be at a lower level. The
hypothesis I'm thinking about is that VACUUM is trying to do
LockBufferForCleanup() and for some reason it never finishes. There are
a number of possible scenarios that could explain this: leaked buffer
pin, dropped signal, etc.
> Kevin, Jeff, next time this happens please attach gdb to the autovacuum
> process and get a stack trace ("bt" to gdb), if at all possible, and/or
> strace it to see what it's doing.
Please!
Also, we need to keep a little clarity about what we are dealing with.
This thread has mentioned hangups in both plain vacuum (autovacuum) and
VACUUM FULL. It seems very likely to me that there are different
mechanisms involved --- since VACUUM FULL takes an exclusive lock on the
whole table, that eliminates an entire class of possible explanations
for the plain-VACUUM case, while introducing a whole new set of
explanations having to do with the VACUUM being queued up behind
ordinary table locks. Please be perfectly clear about which scenario
each report is about.
Finally, I'm wondering whether this bug is new in 8.1 or is
pre-existing. Has this same application been running successfully
in 8.0?
regards, tom lane
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, alvherre@alvh.no-ip.org, jnasby@pervasive.com, Kevin.Grittner@wicourts.gov, Jeff.Kirby@wicourts.gov
Subject: Re: Vacuum Full Analyze Stalled
In-Reply-To: <14993.1128372520@sss.pgh.pa.us>
References: <s3413ba7.062@gwmta.wicourts.gov> <20051003194114.GD40138@pervasive.com> <20051003201225.GA11527@surnet.cl> <14993.1128372520@sss.pgh.pa.us>
* 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