>>So by all means turn off fsync if you want the performance gain *and*
>>you accept the risk. But if you do, don't come crying later that your
>>data has been lost or corrupted.
>
>>(the results are interesting, though - with fsync off Windows
>and Linux
>>are in the same performance ballpark.)

Yes, this is definitly interesting. It confirms Merlins signs of I/O
being what kills the win32 version. IPC etc is a bit slower, but not
significantly.


>In anything I've done, Windows is very slow when you use fsync or the
>Windows API equivalent.

This is what we have discovered. AFAIK, all other major databases or
other similar apps (like exchange or AD) all open files with
FILE_FLAG_WRITE_THROUGH and do *not* use fsync. It might give noticably
better performance with an O_DIRECT style WAL logging at least. But I'm
unsure if the current code for O_DIRECT works on win32 - I think it
needs some fixing for that. Which might be worth looking at for 8.1.

Not much to do about the bgwriter, the way it is designed it *has* to
fsync during checkpoint. The Other Databases implement their own cache
and write data files directly also, but pg is designed to have the OS
cache helping out. Bypassing it would not be good for performance.


>If you need the performance, you had better have the machine
>hooked up to
>a UPS (probably a good idea in any case) and set up something that is
>triggered by the UPS running down to signal postgreSQL to do
>an immediate
>shutdown.

UPS will not help you. UPS does not help you if the OS crashes (hey,
yuo're on windows, this *does* happen). UPS does not help you if
somebody accidentally pulls the plug between the UPS and the server. UPS
does not help you if your server overheats and shuts down.
Bottom line, there are lots of cases when an UPS does not help. Having
an UPS (preferrably redundant UPSes feeding redundant power supplies -
this is not at all expensive today) is certainly a good thing, but it is
*not* a replacement for fsync. On *any* platform.

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

"Magnus Hagander" <mha@sollentuna.net> writes:
> This is what we have discovered. AFAIK, all other major databases or
> other similar apps (like exchange or AD) all open files with
> FILE_FLAG_WRITE_THROUGH and do *not* use fsync. It might give noticably
> better performance with an O_DIRECT style WAL logging at least. But I'm
> unsure if the current code for O_DIRECT works on win32 - I think it
> needs some fixing for that. Which might be worth looking at for 8.1.

Doesn't Windows support O_SYNC (or even better O_DSYNC) flag to open()?
That should be the Posixy spelling of FILE_FLAG_WRITE_THROUGH, if the
latter means what I suppose it does.

> Not much to do about the bgwriter, the way it is designed it *has* to
> fsync during checkpoint.

Theoretically at least, the fsync during checkpoints should not be a
performance killer. The issue that's at hand here is fsyncing the WAL,
and the reason we need that is (a) to be sure a transaction is committed
when we say it is, and (b) to be sure that WAL writes hit disk before
associated data file updates do (it's write AHEAD log remember). Direct
writes of WAL should be fine.

So: try O_SYNC instead of fsync for WAL, ie, wal_sync_method =
open_sync or open_datasync.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

""Magnus Hagander"" <mha@sollentuna.net>
news:6BCB9D8A16AC4241919521715F4D8BCE4768DD@algol. sollentuna.se...
>
> This is what we have discovered. AFAIK, all other major databases or
> other similar apps (like exchange or AD) all open files with
> FILE_FLAG_WRITE_THROUGH and do *not* use fsync. It might give noticably
> better performance with an O_DIRECT style WAL logging at least. But I'm
> unsure if the current code for O_DIRECT works on win32 - I think it
> needs some fixing for that. Which might be worth looking at for 8.1.
>
>
> UPS will not help you. UPS does not help you if the OS crashes (hey,
> yuo're on windows, this *does* happen). UPS does not help you if
> somebody accidentally pulls the plug between the UPS and the server. UPS
> does not help you if your server overheats and shuts down.
> Bottom line, there are lots of cases when an UPS does not help. Having
> an UPS (preferrably redundant UPSes feeding redundant power supplies -
> this is not at all expensive today) is certainly a good thing, but it is
> *not* a replacement for fsync. On *any* platform.
>
> //Magnus
>

Oracle9 and SQL Server 2000 use this flag. Some comments on the
lost-data-concern about FILE_FLAG_WRITE_THROUGH:

(1) Assume you just use ordinary SCSI disks with write back cache on -
you will lost your data if the server suddently lost power;
you will *not* lost your data when OS crashes, server reset or whatever only
if the server has the power;
This has been verified with Oracle9 and SQL Server 2000.

(2) Turn off write back cache in disks, you will not lost data, but you
will see your performance decreased;

(3) If you use some advanced expensive disks like the battery-equipped
ones, then you can safely enable write back cache;

So UPS is useful for ordinary SCSI disks when write back cache is enabled,
but make sure don't let "somebody accidentally pulls the plug between the
UPS and the server" this unfortunate thing happen.