Re: Recommended value for pg_test_fsync

Hi Bruce,

Based on pg_test_fsync results, should we choose open_datasync or fdatasync
as wal_sync_method? Can we rely on pg_test_fsync for choosing the best
wal_sync_method or is there any other way?

Thanks and Regards,
Nikhil

On Mon, Jun 29, 2020 at 9:36 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Mon, Jun 29, 2020 at 02:56:42PM +0530, Nikhil Shetty wrote:
> > Hi Team,
> >
> > We have a PostgreSQL 11.5.6 database running on VM.
> > RAM - 48GB
> > CPU - 6 cores
> > Disk - SSD on SAN
> >
> > We wanted to check how the WAL disk is performing using pg_test_fsync.We
> ran a
> > test and got around 870 ops/sec for opendatasync and fdatasync and just
> 430 ops
> > /sec for fsync.We feel it is quite low as compared to what we get for
> local
> > storage(2000 ops/sec for fsync).What is the recommended value for fsync
> ops/sec
> > for PosgreSQL WAL disks on SAN ?
>
> Well, it is the VM and SAN overhead, I guess. open_datasync or
> fdatasync both seem good.
>
> ---------------------------------------------------------------------------
>
>
> >
> > Test Results:
> >
> > pg_test_fsync -f /WAL/pg_wal/test -s 120
> > 120 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 877.891 ops/sec 1139
> usecs/op
> > fdatasync 880.911 ops/sec 1135
> usecs/op
> > fsync 433.456 ops/sec 2307
> usecs/op
> > fsync_writethrough n/a
> > open_sync 450.094 ops/sec 2222
> usecs/op
> >
> > Compare file sync methods using two 8kB writes:
> > (in wal_sync_method preference order, except fdatasync is Linux's
> default)
> > open_datasync 439.119 ops/sec 2277
> usecs/op
> > fdatasync 898.221 ops/sec 1113
> usecs/op
> > fsync 456.887 ops/sec 2189
> usecs/op
> > fsync_writethrough n/a
> > open_sync 229.973 ops/sec 4348
> usecs/op
> >
> > Compare open_sync with different write sizes:
> > (This is designed to compare the cost of writing 16kB in different write
> > open_sync sizes.)
> > 1 * 16kB open_sync write 453.444 ops/sec 2205
> usecs/op
> > 2 * 8kB open_sync writes 223.142 ops/sec 4481
> usecs/op
> > 4 * 4kB open_sync writes 116.360 ops/sec 8594
> usecs/op
> > 8 * 2kB open_sync writes 55.718 ops/sec 17948
> usecs/op
> > 16 * 1kB open_sync writes 27.766 ops/sec 36015
> usecs/op
> >
> > Test if fsync on non-write file descriptor is honored:
> > (If the times are similar, fsync() can sync data written on a different
> > descriptor.)
> > write, fsync, close 445.493 ops/sec 2245
> usecs/op
> > write, close, fsync 448.196 ops/sec 2231
> usecs/op
> >
> > Non-sync'ed 8kB writes:
> > write 132410.061 ops/sec 8
> usecs/op
> >
> >
> >
> > Thanks and Regards,
> > Nikhil
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
> EnterpriseDB https://enterprisedb.com
>
> The usefulness of a cup is in its emptiness, Bruce Lee
>
>