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

Re: Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable?

From: "Steinar H(dot) Gunderson" <sgunderson(at)bigfoot(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable?
Date: 2007-02-22 20:29:20
Message-ID: 20070222202920.GA1102@uio.no (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Feb 22, 2007 at 12:19:50PM -0800, Barbara Cosentino wrote:
> I have a table that gets many rows inserted, updated and then deleted,
> consistently throughout the day.  At any point in time this table should
> have no more than 50 actual rows and many times a direct select against
> this table produces no rows.  This table also has a VACUUM FULL ANALYZE
> performed against it about very 30 minutes.

You should not usually need VACUUM FULL; doing so all the time will probably
_decrease_ your performance.

> I noticed the vacuum was taking a considerable amount of time for a table
> with a small number of actual rows.  The output of the first vacuum full
> analyze verbose I performed showed that this table had 3,699,704 dead row
> versions that could not be removed.  This number of dead rows that could
> not be released increased with each vacuum full that was performed. The
> output of the last vacuum full is shown below.  

Do you have any long-running transactions going? Those are likely to make
rows nonremovable. Look for idle workers in a transaction.

/* Steinar */
-- 
Homepage: http://www.sesse.net/

In response to

pgsql-performance by date

Next:From: Mark StosbergDate: 2007-02-22 21:53:52
Subject: Using the 8.2 autovacuum values with 8.1
Previous:From: Barbara CosentinoDate: 2007-02-22 20:19:50
Subject: Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable?

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