Thread: Fsync on/off For Various Filesystems/Platforms
The fsync Question ------------------ During a conversation with a fellow Postgresql colleague (Jeramey Crawford <jac@oogyboogy.org>) about the penalty of fsync=true, I made a comment to the effect that this is considered to be much less in current versions. Jeramey was curious about what the penalty actually is, so we did measurements using some of the tables from my data warehouse benchmark setup (The 3000000 row ~ 350Mb fact0 table in particular). Jeramey has Postgresql running on Mac OS X and he supplied the data for this platform. I supplied the Intel based results. Platforms Tested ---------------- Postgres version:7.2 Platforms : G4 733Mhz Mac OS X,P2 333 Linux 2.4.14,P2 333 Freebsd 4.4 IO Subsystems : ATA-66 (G4) , ATA-33 (P2) Filesystems : ufs (Freebsd + OS X), ext2 ext3 and Reiserfs (Linux) and hf+ (OS X) Results ------- Operating Sys...Filesystem..Fsync off/on...Diff ----------------------------------------------- Freebsd 4.4.....ufs+soft....5:38..5:25 ..-:13 Linux 2.4.14....ext2........5:23..5:35 ...:12 Linux 2.4.14....ext3........5:35..6:03 ...:28 Linux 2.4.14....Reiserfs....5:50..5:53 ...:03 Mac OS X........ufs.........3:01..2:56 ...:05 Mac OS X........hfs+........9:54..9:47 ..-:07 Conslusion ---------- The results turned out to be highly filesystem dependent. It is interesting that Freebsd ufs and Mac hfs+ record times _faster_ for fsync=true. However I suspect that the natural variation inherient in our results could be of a similar magnitude to the differences measured, so I didnt get too excited about it. Linux + ext3 appears to be the real anomoly here, there is a 10% penalty for fsync=true. We thought these results were interesting enough to share with the list. regards Mark
>(Quoting Tom Lane) >Still not much help. Was it a single COPY command, or a bunch of them? > Err..clearly should not send messages in the evening :-( One single process was doing the load - so there is 1 big copy , and 1 transaction. >try something with one commit per inserted >row if you want to see a bigger penalty ... you are indeed right. We were (attempting) to determine the penalty for loads via copy - rather than determining bounds for the fsync=on penalty in a general case (but we didnt actually say that). In hindsight the posting was poorly named - something like : "The effect of fsync on/off for a single threaded copy" would have been a much better name. >Also, if there was only one transaction >commit in your 5-minute benchmark run, then I can see why fsync would >be pretty irrelevant ... So I was expecting to see pretty much no effect for fsync on vs off. Which is pretty much what we saw - except for ext3, where that single 5-min transaction got an extra 30s added to it for fsync=on This is what I thought was interesting - I guess maybe the ext3 development lists may have been a better place do the post. (Quoting Bruce Momjian) >Also, someone reported ext3 >as 50% slower than ext2, so again, your numbers are a surprise. I found myself thinking "what mount options and journal sizes option did they use?", which prompted me to include the ones I used (data=ordered & 100M journal ) - another small oversight :-(. If I use data=journal + a small (say 20M) journal then I am sure I can approach your 50%... Regards and apologies for the several confusions Mark
Re: Fsync on/off For Various Filesystems/Platforms
From
nconway@klamath.dyndns.org (Neil Conway)
Date:
On Sun, Mar 10, 2002 at 03:24:10PM +1300, Mark kirkwood wrote: > >Also, someone reported ext3 > >as 50% slower than ext2, so again, your numbers are a surprise. > > I found myself thinking "what mount options and journal sizes option did > they use?", which prompted me to include the ones I used (data=ordered & > 100M journal ) - another small oversight :-(. I've mentioned this before on -hackers, but using data=writeback with ext3 gets you significantly better performance, and you don't lose any safety (since Postgres has its own WAL, there isn't any benefit to ordering writes, AFAIK). That said, ext3 performance is still surprisingly bad. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC