From: Grant Johnson <grant@amadensor.com>
To: pgsql-performance@postgresql.org
Cc: Jon Nelson <jnelson+pgsql@jamponi.net>
Subject: Re: Any experience using "shake" defragmenter?
Date: Tue, 01 Feb 2011 13:31:22 -0700
Message-ID: <4D486D9A.9040404@amadensor.com> (raw)
In-Reply-To: <AANLkTimE_qe1YD+t==xton3SdfXcOYL1Pk6kb0su2_Dh@mail.gmail.com>
References: <4D45C607.4070101@vmsinfo.com>
<4D485DFC.4090808@2ndquadrant.com>
<AANLkTimE_qe1YD+t==xton3SdfXcOYL1Pk6kb0su2_Dh@mail.gmail.com>
> Instead of something like 'shake' (which more or less works, even
> though it doesn't use fallocate and friends) I frequently use either
> CLUSTER (which is what Greg Smith is suggesting) or a series of ALTER
> TABLE ... ALTER COLUMN... which rewrites the table. With PG 9 perhaps
> VACUUM FULL is more appropriate. Of course, the advice regarding
> using 'shake' (or any other defragmenter) on a "live" postgresql data
> directory is excellent - the potential for causing damage if the
> database is active during that time is very high.
>
>
I agree that unless it makes sure there are no open file handles before
moving the file, there is a high chance of corrupting data, and if it
does check, there is little chance it will do anything useful on a live
DB, since it will skip every open file.
Does vacuum full rewrite the whole table, or only the blocks with free
space? If it only rewrites the blocks with free space, the only
solution may be exclusive table lock, alter table to new name, create
old table name as select * from new table name. I also like the cluster
idea, but I am not sure if it rewrites everything, or just the blocks
that have out of order rows, in which case, it would not work well the
second time.
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: grant@amadensor.com, jnelson+pgsql@jamponi.net
Subject: Re: Any experience using "shake" defragmenter?
In-Reply-To: <4D486D9A.9040404@amadensor.com>
* 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