From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kurt Overberg <kurt@hotdogrecords.com>
Cc: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Maintenance question / DB size anomaly...
Date: Tue, 19 Jun 2007 17:33:39 -0400
Message-ID: <16441.1182288819@sss.pgh.pa.us> (raw)
In-Reply-To: <1FF54A1D-9085-4C9B-81F3-D860FB132D44@hotdogrecords.com>
References: <A6D35557-74A3-47CF-B426-143C8CE01A13@hotdogrecords.com>
<607iq0dlns.fsf@dba2.int.libertyrms.com>
<1FF54A1D-9085-4C9B-81F3-D860FB132D44@hotdogrecords.com>
Kurt Overberg <kurt@hotdogrecords.com> writes:
> mydb # vacuum verbose _my_cluster.sl_log_1 ;
> INFO: "sl_log_1": found 455001 removable, 309318 nonremovable row
> versions in 13764 pages
> DETAIL: 0 dead row versions cannot be removed yet.
Hmm. So you don't have a long-running-transactions problem (else that
DETAIL number would have been large). What you do have is a failure
to vacuum sl_log_1 on a regular basis (because there are so many
dead/removable rows). I suspect also some sort of Slony problem,
because AFAIK a properly operating Slony system shouldn't have that
many live rows in sl_log_1 either --- don't they all represent
as-yet-unpropagated events? I'm no Slony expert though. You probably
should ask about that on the Slony lists.
> ...I then checked the disk and those pages are still there.
Yes, regular VACUUM doesn't try very hard to shorten the disk file.
> Would a VACUUM FULL take care of this?
It would, but it will take an unpleasantly long time with so many live
rows to reshuffle. I'd advise first working to see if you can get the
table down to a few live rows. Then a VACUUM FULL will be a snap.
Also, you might want to do REINDEX after VACUUM FULL to compress the
indexes --- VACUUM FULL isn't good at that.
regards, tom lane
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: tgl@sss.pgh.pa.us, kurt@hotdogrecords.com, cbbrowne@acm.org
Subject: Re: Maintenance question / DB size anomaly...
In-Reply-To: <16441.1182288819@sss.pgh.pa.us>
* 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