On Tue, Feb 1, 2011 at 1:24 PM, Greg Smith <greg@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
