X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 3A0FF9FB3F7 for ; Mon, 21 Aug 2006 16:25:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 37855-07 for ; Mon, 21 Aug 2006 19:25:24 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.libertyrms.com (vgateway.libertyrms.info [207.219.45.62]) by postgresql.org (Postfix) with ESMTP id 651689FB3C4 for ; Mon, 21 Aug 2006 16:25:06 -0300 (ADT) Received: from dba5.int.libertyrms.com ([10.1.3.44]) by mail.libertyrms.com with esmtp (Exim 4.22) id 1GFFOT-0001n0-3s; Mon, 21 Aug 2006 15:25:05 -0400 Subject: Re: Vacuum not identifying rows for removal.. From: Brad Nicholson To: Eamonn Kent Cc: pgsql-performance@postgresql.org In-Reply-To: <9146E3EBBFBCC94D95F95A1C4065348A837AB1@exch01.xsigo.com> References: <9146E3EBBFBCC94D95F95A1C4065348A837AB1@exch01.xsigo.com> Content-Type: text/plain; charset=iso-8859-13 Date: Mon, 21 Aug 2006 15:25:04 -0400 Message-Id: <1156188304.8772.6.camel@dba5.int.libertyrms.com> Mime-Version: 1.0 X-Mailer: Evolution 2.6.2 (2.6.2-1.fc5.5) Content-Transfer-Encoding: quoted-printable X-SA-Exim-Mail-From: bnichols@ca.afilias.info X-SA-Exim-Scanned: No; SAEximRunCond expanded to false X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200608/317 X-Sequence-Number: 20502 On Mon, 2006-08-21 at 11:50 -0700, Eamonn Kent wrote: > So, my best guess is that something in our application is preventing > vacuum from removing dead rows. What could cause this? Would it be > caused by a long-living transaction? What is the best way to track > the problem down...right now, I am looking through pg_stat_activity > and pg_locks to find processes that are =B4in transaction=A1 and what > locks they are holding. If you have any long running transactions - idle or active, that's your problem. Vacuum can only clear out dead tuples older than that oldest transaction. Deal with those. Make sure every single transaction your app initiates commits or rolls back every single time. =20 You'll generally find them in pg_stat_activity, but not always. ps may show you idle transactions not showing as idle in pg_stat_activity =20 --=20 Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.