Re: synchronous replication + fsync=off?

On 22 Listopad 2011, 18:16, Bruce Momjian wrote:
> Tomas Vondra wrote:
>> 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). 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.

Well, but this inconsistency can happen only on the master, right? The
slave receives on the WAL records in order, and applies them just fine.
And it's fsync=on so it is not vulnerable to this kind of data corruption.

When the master crashes, the recovery may fail - that's expected. The
master would have to be rebuilt from scratch in this scenario.

Yes, I know that synchronous_commit actually guarantees data consistency.
The point of the suggested scenario was that

(a) the master does not wait for the I/O (assuming that it's busy and the
latency would be significant)

(b) the master waits for the slave (sync replication)

so that you know that in case of crash of the master the slave actually
contains all the data. That's not what synchronous_commit does - you may
loose data if you use it.

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

There's at least one more quite commonly used scenario - restoring a
database from backup. It often speeds the process significantly and when
something fails, you can simply start again.

Tomas