Обсуждение: 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.gogala@vmsinfo.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
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
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: > 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@bfk.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@bfk.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
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| |_____|_____| |===========|
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@2ndQuadrant.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@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
> 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@amadensor.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:
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 :-)
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 :-)