Recommended value for pg_test_fsync
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/sec1139 usecs/op fdatasync 880.911 ops/sec1135 usecs/op fsync 433.456 ops/sec2307 usecs/op fsync_writethrough n/a open_sync 450.094 ops/sec 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/sec2277 usecs/op fdatasync 898.221 ops/sec1113 usecs/op fsync 456.887 ops/sec2189 usecs/op fsync_writethrough n/a open_sync 229.973 ops/sec4348 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/sec2205 usecs/op 2 * 8kB open_sync writes 223.142 ops/sec4481 usecs/op 4 * 4kB open_sync writes 116.360 ops/sec8594 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/sec2245 usecs/op write, close, fsync 448.196 ops/sec2231 usecs/op Non-sync'ed 8kB writes: write132410.061 ops/sec 8 usecs/op Thanks and Regards, Nikhil
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/sec1139 usecs/op > fdatasync 880.911 ops/sec1135 usecs/op > fsync 433.456 ops/sec2307 usecs/op > fsync_writethrough n/a > open_sync 450.094 ops/sec 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/sec2277 usecs/op > fdatasync 898.221 ops/sec1113 usecs/op > fsync 456.887 ops/sec2189 usecs/op > fsync_writethrough n/a > open_sync 229.973 ops/sec4348 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/sec2205 usecs/op > 2 * 8kB open_sync writes 223.142 ops/sec4481 usecs/op > 4 * 4kB open_sync writes 116.360 ops/sec8594 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/sec2245 usecs/op > write, close, fsync 448.196 ops/sec2231 usecs/op > > Non-sync'ed 8kB writes: > write132410.061 ops/sec 8 usecs/op > > > > Thanks and Regards, > Nikhil -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
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 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/sec1139 > usecs/op > > fdatasync 880.911 ops/sec1135 > usecs/op > > fsync 433.456 ops/sec2307 > usecs/op > > fsync_writethrough n/a > > open_sync 450.094 ops/sec > 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/sec2277 > usecs/op > > fdatasync 898.221 ops/sec1113 > usecs/op > > fsync 456.887 ops/sec2189 > usecs/op > > fsync_writethrough n/a > > open_sync 229.973 ops/sec4348 > 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/sec2205 > usecs/op > > 2 * 8kB open_sync writes 223.142 ops/sec4481 > usecs/op > > 4 * 4kB open_sync writes 116.360 ops/sec8594 > 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/sec2245 > usecs/op > > write, close, fsync 448.196 ops/sec2231 > usecs/op > > > > Non-sync'ed 8kB writes: > > write132410.061 ops/sec 8 > usecs/op > > > > > > > > Thanks and Regards, > > Nikhil > > -- > Bruce Momjian https://momjian.us > EnterpriseDB https://enterprisedb.com > > The usefulness of a cup is in its emptiness, Bruce Lee > >
