From: Robert Haas [mailto:robertmhaas@gmail.com]
> I think open_datasync will be worse on systems where fsync() is expensive
> -- it forces the data out to disk immediately, even if the data doesn't
> need to be flushed immediately. That's bad, because we wait immediately
> when we could have deferred the wait until later and maybe gotten the WAL
> writer to do the work in the background. But it might be better on systems
> where fsync() is basically free, because there you might as well just get
> it out of the way immediately and not leave something left to be done later.
>
> This is just a guess, of course. You didn't mention what the underlying
> storage for your test was?
Uh, your guess was correct. My file system was ext3, where fsync() writes all dirty buffers in page cache.
As you said, open_datasync was 20% faster than fdatasync on RHEL7.2, on a LVM volume with ext4 (mounted with options
noatime,nobarrier) on a PCIe flash memory.
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 50829.597 ops/sec 20 usecs/op
fdatasync 42094.381 ops/sec 24 usecs/op
fsync 42209.972 ops/sec 24 usecs/op
fsync_writethrough n/a
open_sync 48669.605 ops/sec 21 usecs/op
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 26366.373 ops/sec 38 usecs/op
fdatasync 33922.725 ops/sec 29 usecs/op
fsync 32990.209 ops/sec 30 usecs/op
fsync_writethrough n/a
open_sync 24326.249 ops/sec 41 usecs/op
What do you think about changing the default value of wal_sync_method on Linux in PG 11? I can understand the concern
thatusers might hit performance degredation if they are using PostgreSQL on older systems. But it's also mottainai
thatmany users don't notice the benefits of wal_sync_method = open_datasync on new systems.
Regards
Takayuki Tsunakawa