Re: Best setting for wal_sync_method

Ravi Krishna wrote:
> On RHEL 6.4 which is the best setting for wal_sync_method for write intensive operations. Is the
> default fdatasync good for all conditions, including reliability.

Yes; you should not change that.
At least not without running performance tests.
There is the pg_test_fsync contrib that can help you test the different methods.
http://www.postgresql.org/docs/current/static/pgtestfsync.html

> Speaking to the sys admins we found that the file system where the WAL was written to did not have
> direct_io and hence all writes were getting buffered. So we enabled direct_io on that FS and now
> writes are order of magnitude slower (5x times).
>
> It seems PG already uses O_DIRECT for WAL. So is the enabling of direct_io in the mount options of the
> FS totally redundant. It definitely has a serious performance impact.

You should not use direct I/O with PostgreSQL.

All that is required is that the file system and the underlying hardware honors
the fsync() (or fdatasync()) system calls, i.e. it is guaranteed that when these
functions return, the written data cannot get lost by a subsequent crash.
You want to make very sure that that is the case, otherwise your database
*will* be corrupted after a crash some day.

Yours,
Laurenz Albe