Re: Loaded footgun open_datasync on Windows

On Fri, Jun 1, 2018 at 3:26 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> On Fri, Jun 1, 2018 at 3:13 PM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
> wrote:
>
>> I recently read our documentation about reliability on Windows:
>>
>> > On Windows, if wal_sync_method is open_datasync (the default), write
>> caching can
>> > be disabled by unchecking
>> > My Computer\Open\disk drive\Properties\Hardware\Properties\Policies\Enable
>> write caching
>> > on the disk. Alternatively, set wal_sync_method to fsync or
>> fsync_writethrough,
>> > which prevent write caching.
>>
>> It seems dangerous to me to initialize "wal_sync_method" to a method that
>> is unsafe
>> by default. Admittedly I am not a Windows man, but the fact that this
>> has eluded me
>> up to now leads me to believe that other people running PostgreSQL on
>> Windows might
>> also have missed that important piece of advice and are consequently
>> running with
>> an unsafe setup.
>>
>> Wouldn't it be smarter to set a different default value on Windows, like
>> we do on
>> Linux (for other reasons)?
>>
>>
> One thing to note is that it seems that in code we use
> FILE_FLAG_WRITE_THROUGH for open_datasync which according to MSDN [1] will
> bypass any intermediate cache . See pgwin32_open. Have you experimented
> to set any other option as we have a comment in code which say Win32 only
> has O_DSYNC?
>
>
These settings go back to the original Windows port, and it would probably
be a good idea to read back on the discusison there (sorry, I don't have a
direct reference to it here).

Basically, this method *is* safe as long as you have proper storage. For
example, if yo have a RAID controller with cache, it is perfectly safe. If
you have a consumer level device with unsafe caching, then it's not safe.
This behaves basically the same as it does on e.g. Linux, which is also
unsafe if you have an unsafe conusmer device.

If you use fsync_writethrough, we actually write through the cache on the
raidcontroller *even if it has bettery/flash cache*, which gives absolutely
terrible performance on these platforms. It is useful if you have a
consumer drive that by default does insafe caching but does respect
writethrough requests.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>