Re: finding out vacuum completion %, and vacuum VS vacuum full

From: Steve Atkins <steve(at)blighty(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: finding out vacuum completion %, and vacuum VS vacuum full
Date: 2007-08-07 15:40:47
Message-ID: 5C4F9434-0C2A-4E53-9488-862E8345830A@blighty.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


On Aug 7, 2007, at 1:17 AM, Sergei Shelukhin wrote:

> Ok here's the update after ~30 hours we have killed vacuum full and
> did vacuum on the tables we freed.
> However, VACUUM hasn't freed any space at all 0_o
> We want to launch vacuum full on per-table basis but we can't have any
> more downtime right now so we will launch it at night today.
>
> The original question still stands, is there any way to diagnose
> vacuum full time-to-run?

It could easily take many days. VACUUM FULL is painfully slow.
Dropping indexes and suchlike can make it faster, but it's still
painfully slow.

> Or any way to optimize it besides the obvious (maintenace_work_mem &
> max_fsm_pages increases and no workload)?
> Can someone please help with this one?

VACUUM FULL is about the worst thing you can do in this case.

If you have adequate disk space free (enough to hold another
copy of the new table) and the table has an index on it, then
CLUSTER the table.

If not, dump and restore the table.

Cheers,
Steve

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2007-08-07 15:47:42 Re: truncate transaction log
Previous Message brian 2007-08-07 14:48:16 Re: how to detect the backup database every day