Re: Recommended value for pg_test_fsync

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