Date: Mon, 29 Jun 2020 12:06:38 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Nikhil Shetty <nikhil.dba04@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Recommended value for pg_test_fsync
Message-ID: <20200629160638.GB17530@momjian.us>
References: 
 <CAFpL5Vx8KQ4sdH5Bp=nq-uLwsr7N2rHiD+osMaMRzmRADqFVtA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAFpL5Vx8KQ4sdH5Bp=nq-uLwsr7N2rHiD+osMaMRzmRADqFVtA@mail.gmail.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

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@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee