Message-ID: <4D485DFC.4090808@2ndquadrant.com>
Date: Tue, 01 Feb 2011 14:24:44 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: mladen.gogala@vmsinfo.com
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Any experience using "shake" defragmenter?
References: <4D45C607.4070101@vmsinfo.com>
In-Reply-To: <4D45C607.4070101@vmsinfo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Mladen Gogala wrote:
> Did anyone try using "shake" while the cluster is active? Any problems 
> with corruption or data loss? I ran the thing on my home directory and 
> nothing was broken. I didn't develop any performance test, so cannot 
> vouch for the effectiveness of the procedure. Did anyone play with 
> that? Any positive or negative things to say about shake?
>

Shake works by allocating a new file the size of the original, in what 
is presumed to be then be unfragmented space.  It copies the original 
over to this new space and then gets rid of the original.  That 
procedure will cause database corruption if the server happens to access 
the file it's moving while it's in the middle of doing so.  If the 
database isn't running, though, it is probably fine.

On ext3 you can measure whether it was useful or not by taking the 
filesystem off-line and running fsck before/after using it.  Look for 
percentages given for "non-contiguous files" and directories.  If those 
were low to begin with, little reason to run the utility.  If they're 
high, running shake should bring them down afterwards if it's doing its 
job right.

On a PostgreSQL database system, you can get the same basic effect while 
leaving the server up--but just with the table locked--using CLUSTER.  
And that will clean up a bunch of other potential messes inside the 
database that shake can't touch.  I just do that instead if I'm worried 
a particular table has become fragmented on disk.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books