Skip site navigation (1) Skip section navigation (2)

Re: Ineffective autovacuum

From: Royce Ausburn <royce(dot)ml(at)inomial(dot)com>
To: Marti Raudsepp <marti(at)juffo(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Ineffective autovacuum
Date: 2011-09-27 14:27:39
Message-ID: A0DE3560-2C7F-4220-BED5-CC8D6266499B@inomial.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 27/09/2011, at 8:29 PM, Marti Raudsepp wrote:

> 1. First things first: vacuum cannot delete tuples that are still
> visible to any old running transactions. You might have some very long
> queries or transactions that prevent it from cleaning properly:
> 
> select * from pg_stat_activity where xact_start < now()-interval '10 minutes';

Thanks -- that query is very handy.  I suspect this might be the cause of our woes as this query results in a handful of long lived connections, however they're connections to databases other than the one that I'm having trouble with.  

I've checked up on the FSM as you suggested, I don't think that's the problem as there're no warnings in the verbose output nor the logs.  But another clue:

DETAIL:  93 dead row versions cannot be removed yet.

After clearing those stuffed transactions vacuum verbose manages to clear away all the dead rows… That's confirmation enough for me - Now to find the application bugs - Thanks Tom, Marti & Scott for your help!

--Royce

In response to

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2011-09-27 14:32:14
Subject: Re: : Tracking Full Table Scans
Previous:From: Scott MarloweDate: 2011-09-27 14:00:08
Subject: Re: Ineffective autovacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group