On Tue, Feb 1, 2011 at 1:24 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> 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.
One thing to note is that, in my experiments, ext4 handles large files
(such as the 1GiB files postgresql uses for large relations) in a
*vastly* improved manner over ext3. This is due to the use of
extents. I found that, in some cases, heavily fragmented files under
ext3 could not be effectively defragmented - and yes, I tried shake
and some others (including one I wrote which *does* use fallocate /
fallocate_posix). There was improvement, but by far the biggest
improvement was switching to ext4.
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.
--
Jon