Re: synchronous replication + fsync=off? - Mailing list pgsql-general

On Tue, Nov 22, 2011 at 12:16 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Tomas Vondra wrote:
>> On 17 Listopad 2011, 17:07, Jaime Casanova wrote:
>> > On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg <jschubert@cebacus.de>
>> > wrote:
>> >> Hello,
>> >>
>> >> I have two servers with battery backed power supply (USV). So it is
>> >> unlikely, that both will crash at the same time.
>> >>
>> >> Will synchronous replication work with fsync=off?
>> >> That means we will commit to system cache, but not to disk. Data will
>> >> not
>> >> survive a system crash but the second system should still be consistent.
>> >>
>> >
>> > you should never use fsync=off (in production at least)
>> >
>> > the appropiate parameter to use is synchronous_commit which is the one
>> > that controls synchronous replication:
>> > off = no local nor remote synchronous commit
>> > local = local synchronous commit but no remote
>> > on = both, local and remote, synchronous commit
>> >
>> > synchronous commit = flushed to disk
>>
>> While I don't recommend it, fsync=off definitely is an option, especially
>> with sync replication. The synchronous_commit is not a 1:1 replacement.
>>
>> Imagine for example a master with lot of I/O, and a sync standby. By
>> setting fsync=off on the master and fsync=on on the slave the master does
>> not need to wait for the fsync (so the I/O is not that stressed and can
>> handle more requests from clients), but the slave actually does fsync.
>>
>> So you don't force local fsync, but you're waiting for fsync from the
>> standby. But standby doesn't need to handle all the I/O the primary has.
>>
>> You can't do this with synchronous_commit - that basically forces you to
>> do local fsync on commit, or not to wait for the commit at all.
>>
>> Tomas
>>
>> Disclaimer: I haven't actually tried this, so maybe I missed something.
>
> I think you did.  synchronous_commit really means fsync so that the
> system is alway consistent --- there is no waiting for the fsync to
> happen on the master (unless I am totally missing something).

+1, synchronous_commit has (pretty much) nothing to do with
synchronous replication; it's all about controlling the relationship
between local commits and fsync.

>   With
> fsync off, you can get into cases where the heap/index files are pushed
> to disk before the wal gets written to disk, causing the system to be
> inconsistent in case of a crash replay.
>

I think it's worth saying that this doesn't "guarantee you will lose
your master" as someone claimed upthread; more correctly it just
introduces the possibility that your database will be corrupt upon
server or OS crash (which is something most people should avoid).

> I think the only use of fsync off is for performance testing so see how
> expensive fynsc is.
>

Never speak in absolutes! ;-)

It's not unheard of to run with fsync = off when you have asynchronous
replicated failover. Given you've already decided that you're ok with
data loss, the extra amount that you lose with the fsync off can be
trivial compared to the performance boost you get, especially if
system crashes in your environment are rare (which hopefully they
should be).

Robert Treat
conjecture: xzilla.net
consulting: omniti.com