Recommended value for pg_test_fsync

From: Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Recommended value for pg_test_fsync
Date: 2020-06-29 09:26:42
Message-ID: CAFpL5Vx8KQ4sdH5Bp=nq-uLwsr7N2rHiD+osMaMRzmRADqFVtA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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 ?

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Bruce Momjian 2020-06-29 16:06:38 Re: Recommended value for pg_test_fsync
Previous Message Tom Lane 2020-06-22 19:39:42 Re: PostgreSQL 12.3 slow index scan chosen