From: Steinar H. Gunderson <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable?
Date: Thu, 22 Feb 2007 21:29:20 +0100
Message-ID: <20070222202920.GA1102@uio.no> (raw)
In-Reply-To: <8A72E69E1F79004B82F76F228B3F2923067B62B7@corp-mail01.ncircle.com>
References: <8A72E69E1F79004B82F76F228B3F2923067B62B7@corp-mail01.ncircle.com>
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/
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: sgunderson@bigfoot.com
Subject: Re: Vacuum full very slow due to nonremovable dead rows...What makes the dead rows non-removable?
In-Reply-To: <20070222202920.GA1102@uio.no>
* 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