pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Brad Nicholson <bnichols@ca.afilias.info>
To: Eamonn Kent <ekent@xsigo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Vacuum not identifying rows for removal..
Date: Mon, 21 Aug 2006 15:25:04 -0400
Message-ID: <1156188304.8772.6.camel@dba5.int.libertyrms.com> (raw)
In-Reply-To: <9146E3EBBFBCC94D95F95A1C4065348A837AB1@exch01.xsigo.com>
References: <9146E3EBBFBCC94D95F95A1C4065348A837AB1@exch01.xsigo.com>

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 ´in transaction¡ 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.    

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
 
-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.




view thread (6+ messages)  latest in thread

Message-ID: <1156188304.8772.6.camel@dba5.int.libertyrms.com>
Permalink:  ../1156188304.8772.6.camel@dba5.int.libertyrms.com/
Also on:    postgresql.org/message-id/1156188304.8772.6.camel@dba5.int.libertyrms.com

 · 

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: bnichols@ca.afilias.info, ekent@xsigo.com
  Subject: Re: Vacuum not identifying rows for removal..
  In-Reply-To: <1156188304.8772.6.camel@dba5.int.libertyrms.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