Re: win32 performance - fsync question

On Thu, 17 Feb 2005, Magnus Hagander wrote:

>> Hi,
>>
>> looking for the way how to increase performance at Windows XP
>> box, I found the parameters
>>
>> #fsync = true # turns forced
>> synchronization on or off
>> #wal_sync_method = fsync # the default varies across platforms:
>> # fsync, fdatasync,
>> open_sync, or open_datasync
>>
>> I have no idea how it works with win32. May I try fsync =
>> false, or it is dangerous? Which of wal_sync_method may I try
>> at WinXP?
>
> You can try it, but it is dangerous.
> fsync is the correct wal_sync_method.
>
> For some reason the syncing is quite a lot slower on win32. One reason
> might be that it does flush metadata about the file as well, which I
> beleive at least Linux doesn't.
>
> If it wasn't clear already, if you're running antivirus, try
> uninstalling it. Note that you may need to uninstall it to get all
> performance back, just disabling is often *not* enough as the kernel
> driver is still loaded.

No, I have not any resident disk-related staff.

>
> Things worth experimenting with (these are all untested, so please
> report any successes):
> 1) Try reformatting with a cluster size of 8Kb (the pg page size), if
> you can.
> 2) Disable the last access time (like noatime on linux). "fsutil
> behavior set disablelastaccess 1"
> 3) Disable 8.3 filenames "fsutil behavior set disable8dot3 1"
>
> 2 and 3 may require a reboot.
>
> (2 and 3 can be done on earlier windows through registry settings only,
> in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem)

I've repeated the test under 2 and 3 - no noticeable difference. With
disablelastaccess I got about 10% - 15% better results, but it is not
too significant.

Finally I tried

fsync = false

and got 580-620 tps. So, the short summary:

WinXP fsync = true 20-28 tps
WinXP fsync = false 600 tps
Linux 800 tps

The general question is - does PostgreSQL really need fsync? I suppose it
is a question for design, not platform-specific one. It sounds like only
one scenario, when fsync is useful, is to interprocess communication via
open file. But PostgreSQL utilize IPC for this, so does fsync is really
required?

E.R.
_________________________________________________________________________
Evgeny Rodichev Sternberg Astronomical Institute
email: er(at)sai(dot)msu(dot)su Moscow State University
Phone: 007 (095) 939 2383
Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er