X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7D290D9ED3 for ; Mon, 3 Oct 2005 17:50:19 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 29606-10 for ; Mon, 3 Oct 2005 20:50:14 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 15AC8D9EAB for ; Mon, 3 Oct 2005 17:50:14 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j93KmeBk014994; Mon, 3 Oct 2005 16:48:40 -0400 (EDT) To: Alvaro Herrera Cc: "Jim C. Nasby" , Kevin Grittner , Jeff Kirby , pgsql-admin@postgresql.org Subject: Re: Vacuum Full Analyze Stalled In-reply-to: <20051003201225.GA11527@surnet.cl> References: <20051003194114.GD40138@pervasive.com> <20051003201225.GA11527@surnet.cl> Comments: In-reply-to Alvaro Herrera message dated "Mon, 03 Oct 2005 16:12:25 -0400" Date: Mon, 03 Oct 2005 16:48:40 -0400 Message-ID: <14993.1128372520@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200510/40 X-Sequence-Number: 19112 Alvaro Herrera 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