Re: Any experience using "shake" defragmenter?

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?

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

On Sun, 30 Jan 2011 14:11:51 -0600, Mladen Gogala
<mladen(dot)gogala(at)vmsinfo(dot)com> 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?
>

Why do you feel the need to defrag your *nix box?

Regards,

Mark

W dniu 2011-01-30 22:31, Mark Felder pisze:
> Why do you feel the need to defrag your *nix box?

I'm guessing, maybe he used filefrag and saw >30000 extents? :)
Next question will be "which fs do you use?" and then flame will start:(
Regards

Marcin Mirosław wrote:
> W dniu 2011-01-30 22:31, Mark Felder pisze:
>
>> Why do you feel the need to defrag your *nix box?
>>
>
> I'm guessing, maybe he used filefrag and saw >30000 extents? :)
> Next question will be "which fs do you use?" and then flame will start:(
> Regards
>
>
With all due respect, I don't want to start a fruitless flame war. I am
asking those who have used it about their experiences with the product.
Let's leave discussion of my motivation for some other time. I guess
it's all about my unhappy childhood. If you have used the defragmenter,
I'd be grateful for your experience.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

Mark Felder wrote:
> Why do you feel the need to defrag your *nix box?
>
>
>
Let's stick to the original question and leave my motivation for some
other time. Have you used the product? If you have, I'd be happy to hear
about your experience with it.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

On 01/30/2011 11:38 PM, Mladen Gogala wrote:
> Mark Felder wrote:
>> Why do you feel the need to defrag your *nix box?
>>
>>
> Let's stick to the original question and leave my motivation for some other
> time. Have you used the product? If you have, I'd be happy to hear about your
> experience with it.

That seems a little harsh. You post to a discussion group but want to
suppress discussion?

Maybe that works with paid tech-support staff, but here ...

--
Lew
Ceci n'est pas une fenêtre.
.___________.
|###] | [###|
|##/ | *\##|
|#/ * | \#|
|#----|----#|
|| | * ||
|o * | o|
|_____|_____|
|===========|

* Mark Felder:

> Why do you feel the need to defrag your *nix box?

Some file systems (such as XFS) read the whole extent list into RAM
when a file is opened. When the extend list is long due to
fragmentation, this can take a *long* time (in the order of minutes
with multi-gigabyte Oracle Berkeley DB files). This phenomenon is
less pronounced with PostgreSQL because it splits large relations into
one-gigabyte chunks, and it writes the files sequentally. But a small
effect is probably still there.

--
Florian Weimer <fweimer(at)bfk(dot)de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

* Mladen Gogala:

> Did anyone try using "shake" while the cluster is active?

As far as I can tell, it's totally unsafe.

--
Florian Weimer <fweimer(at)bfk(dot)de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

Please reply to the list with list business.

On 01/31/2011 03:22 PM, Mladen Gogala wrote:
> On 1/31/2011 7:28 AM, Lew wrote:
>> That seems a little harsh.
> Oh? How so?
>> You post to a discussion group but want to
>> suppress discussion?
>
> No, I just want to stick to the subject. My motivation for doing so, my
> unhappy childhood or somebody's need for attention are not too important. If
> you have had any experience with the product, I'd be extremely keen to learn
> about it and grateful to you for sharing it. If not, then....well, I'll
> explain my reasoning some other time. I have better things to do right now.
>
>> Maybe that works with paid tech-support staff, but here ...
>>
> Things have lived up to my expectation. Basically, the only people who replied
> are those who have no experience with the product but apparently do have an
> irresistible urge to discuss something that I am not particularly interested
> in discussing.
>

I'm so very, very sorry that we insist on having a discussion instead of
adhering to your ukase.

Perhaps your dictatorial attitude discourages people from responding? I mean,
Mark Felder asked a perfectly reasonable question and now you're all snarky.
Well, a big "Harrumph!" to that!

I wish you the best of luck. You'll need it with that attitude.

--
Lew
Ceci n'est pas une fenêtre.
.___________.
|###] | [###|
|##/ | *\##|
|#/ * | \#|
|#----|----#|
|| | * ||
|o * | o|
|_____|_____|
|===========|

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(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

On Tue, Feb 1, 2011 at 1:24 PM, Greg Smith <greg(at)2ndquadrant(dot)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

> 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.

On Tue, Feb 1, 2011 at 3:31 PM, Grant Johnson <grant(at)amadensor(dot)com> wrote:
> Does vacuum full rewrite the whole table, or only the blocks with free
> space?

The whole table.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 31/01/11 17:38, Mladen Gogala wrote:
> Mark Felder wrote:
>> Why do you feel the need to defrag your *nix box?
>>
>>
> Let's stick to the original question and leave my motivation for some
> other time. Have you used the product? If you have, I'd be happy to
> hear about your experience with it.
>
>

Mladen sometimes asking about the motivation behind a question brings to
light new information that makes the original question no longer
relevant. In this case it might bring to light a better solution than
"Shake", or else methods for determining if fragmentation is harmful or
not.

I don't believe people are asking in order to either flame you or insult
your intelligence, but there is genuine interest in why you are wanting
to defrag. There is a lot of expertise on this list - indulging a little
curiosity will only help you get better value for your questions.

Cheers

Mark

P.s: I'm curious too :-)