Recommended value for pg_test_fsync

Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Recommended value for pg_test_fsync

Nikhil Shetty
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
Reply | Threaded
Open this post in threaded view
|

Re: Recommended value for pg_test_fsync

Bruce Momjian
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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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



Reply | Threaded
Open this post in threaded view
|

Re: Recommended value for pg_test_fsync

Nikhil Shetty
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 <[hidden email]> 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  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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

Reply | Threaded
Open this post in threaded view
|

Re: Recommended value for pg_test_fsync

Bruce Momjian
On Tue, Jun 30, 2020 at 10:32:13AM +0530, Nikhil Shetty wrote:
> 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

I would just pick the fastest method, but if the method is _too_ fast,
it might mean that it isn't actually writing to durable storage.

> wal_sync_method or is there any other way?

pg_test_fsync is the only way I know of, which is why I wrote it.

--
  Bruce Momjian  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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



Reply | Threaded
Open this post in threaded view
|

Re: Recommended value for pg_test_fsync

Jeff Janes
In reply to this post by Nikhil Shetty
On Mon, Jun 29, 2020 at 5:27 AM Nikhil Shetty <[hidden email]> 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).

It is not surprising to me that SAN would have higher latency than internal storage.  What kind of connection do you have between your server and your SAN?
 
What is the recommended value for fsync ops/sec for PosgreSQL WAL disks on SAN ?

You have the hardware you have.  You can't change it the same way you can change a config file entry, so I don't think that "recommended value" really applies.  Is the latency of sync requests a major bottleneck for your workload? pg_test_fsync can tell you what the latency is, but can't tell you how much you care.
 
Cheers,

Jeff
Reply | Threaded
Open this post in threaded view
|

Re: Recommended value for pg_test_fsync

Jeff Janes
In reply to this post by Nikhil Shetty
On Tue, Jun 30, 2020 at 1:02 AM Nikhil Shetty <[hidden email]> wrote:
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?

Probably the default of fdatasync.  The place where pg_test_fsync would tell me not to use fdatasync is if it were so fast that it was not credible that it was honestly syncing the data.  I don't think pg_test_fsync does a good job of exercising the realistic differences between fdatasync and open_datasync.  So unless it shows that one of them is lying about the durability, it doesn't offer much help.
 
Cheers,

Jeff
Reply | Threaded
Open this post in threaded view
|

Re: Recommended value for pg_test_fsync

Nikhil Shetty
In reply to this post by Bruce Momjian
Hi Bruce,

Thank you. We may stick with fdatasync for now.

Thanks and regards,
Nikhil

On Tue, Jun 30, 2020 at 8:54 PM Bruce Momjian <[hidden email]> wrote:
On Tue, Jun 30, 2020 at 10:32:13AM +0530, Nikhil Shetty wrote:
> 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

I would just pick the fastest method, but if the method is _too_ fast,
it might mean that it isn't actually writing to durable storage.

> wal_sync_method or is there any other way?

pg_test_fsync is the only way I know of, which is why I wrote it.

--
  Bruce Momjian  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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

Reply | Threaded
Open this post in threaded view
|

Re: Recommended value for pg_test_fsync

Nikhil Shetty
In reply to this post by Jeff Janes
Hi Jeff,

Thank you for your inputs. We may stick with fdatasync for now. We will get more details on connection details between SAN and server from the storage team and update this thread.

Storage is Hitachi G900 with 41Gbps bandwidth.

Thanks and regards,
Nikhil



On Tue, Jun 30, 2020 at 9:51 PM Jeff Janes <[hidden email]> wrote:
On Mon, Jun 29, 2020 at 5:27 AM Nikhil Shetty <[hidden email]> 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).

It is not surprising to me that SAN would have higher latency than internal storage.  What kind of connection do you have between your server and your SAN?
 
What is the recommended value for fsync ops/sec for PosgreSQL WAL disks on SAN ?

You have the hardware you have.  You can't change it the same way you can change a config file entry, so I don't think that "recommended value" really applies.  Is the latency of sync requests a major bottleneck for your workload? pg_test_fsync can tell you what the latency is, but can't tell you how much you care.
 
Cheers,

Jeff
Reply | Threaded
Open this post in threaded view
|

Re: Recommended value for pg_test_fsync

Nikhil Shetty
Hi Jeff,

To avoid confusion, Hitachi Storage G900 has 41Gbps Performance bandwidth (Throughput) and 10Gbps N/W bandwidth.

Thanks and Regards,
Nikhil

On Wed, Jul 1, 2020 at 10:36 PM Nikhil Shetty <[hidden email]> wrote:
Hi Jeff,

Thank you for your inputs. We may stick with fdatasync for now. We will get more details on connection details between SAN and server from the storage team and update this thread.

Storage is Hitachi G900 with 41Gbps bandwidth.

Thanks and regards,
Nikhil



On Tue, Jun 30, 2020 at 9:51 PM Jeff Janes <[hidden email]> wrote:
On Mon, Jun 29, 2020 at 5:27 AM Nikhil Shetty <[hidden email]> 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).

It is not surprising to me that SAN would have higher latency than internal storage.  What kind of connection do you have between your server and your SAN?
 
What is the recommended value for fsync ops/sec for PosgreSQL WAL disks on SAN ?

You have the hardware you have.  You can't change it the same way you can change a config file entry, so I don't think that "recommended value" really applies.  Is the latency of sync requests a major bottleneck for your workload? pg_test_fsync can tell you what the latency is, but can't tell you how much you care.
 
Cheers,

Jeff
Reply | Threaded
Open this post in threaded view
|

Re: Recommended value for pg_test_fsync

Haroldo Kerry
In reply to this post by Nikhil Shetty
Hello Nikhil,
We had performance issues with our Dell SC2020 storage in the past. We had a 6 SSD RAID10 setup and due all the latencies expected 20K IOPS but were getting 2K...
After *a lot* of work the issue was not with the storage itself but with the I/O scheduler of the filesystem (EXT4/Debian 9).
The default scheduler is CFQ, changing to deadline provided us the 10x difference that we were expecting.
In the end this was buried on the storage documentation that somehow slipped us...
Hope this helps.
Regards,
Haroldo Kerry

On Wed, Jul 1, 2020 at 2:06 PM Nikhil Shetty <[hidden email]> wrote:
Hi Jeff,

Thank you for your inputs. We may stick with fdatasync for now. We will get more details on connection details between SAN and server from the storage team and update this thread.

Storage is Hitachi G900 with 41Gbps bandwidth.

Thanks and regards,
Nikhil



On Tue, Jun 30, 2020 at 9:51 PM Jeff Janes <[hidden email]> wrote:
On Mon, Jun 29, 2020 at 5:27 AM Nikhil Shetty <[hidden email]> 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).

It is not surprising to me that SAN would have higher latency than internal storage.  What kind of connection do you have between your server and your SAN?
 
What is the recommended value for fsync ops/sec for PosgreSQL WAL disks on SAN ?

You have the hardware you have.  You can't change it the same way you can change a config file entry, so I don't think that "recommended value" really applies.  Is the latency of sync requests a major bottleneck for your workload? pg_test_fsync can tell you what the latency is, but can't tell you how much you care.
 
Cheers,

Jeff


--

Haroldo Kerry

CTO/COO

Rua do Rócio, 220, 7° andar, conjunto 72

São Paulo – SP / CEP 04552-000

[hidden email]

www.callix.com.br

Reply | Threaded
Open this post in threaded view
|

Re: Recommended value for pg_test_fsync

Nikhil Shetty
Hi Haroldo,

Thank you for the details.

We are using xfs on IBM Power Linux Rhel7 but I will check this in our environment and get back to you with the results.

Thanks and Regards,
Nikhil


On Wed, Jul 1, 2020, 22:46 Haroldo Kerry <[hidden email]> wrote:
Hello Nikhil,
We had performance issues with our Dell SC2020 storage in the past. We had a 6 SSD RAID10 setup and due all the latencies expected 20K IOPS but were getting 2K...
After *a lot* of work the issue was not with the storage itself but with the I/O scheduler of the filesystem (EXT4/Debian 9).
The default scheduler is CFQ, changing to deadline provided us the 10x difference that we were expecting.
In the end this was buried on the storage documentation that somehow slipped us...
Hope this helps.
Regards,
Haroldo Kerry

On Wed, Jul 1, 2020 at 2:06 PM Nikhil Shetty <[hidden email]> wrote:
Hi Jeff,

Thank you for your inputs. We may stick with fdatasync for now. We will get more details on connection details between SAN and server from the storage team and update this thread.

Storage is Hitachi G900 with 41Gbps bandwidth.

Thanks and regards,
Nikhil



On Tue, Jun 30, 2020 at 9:51 PM Jeff Janes <[hidden email]> wrote:
On Mon, Jun 29, 2020 at 5:27 AM Nikhil Shetty <[hidden email]> 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).

It is not surprising to me that SAN would have higher latency than internal storage.  What kind of connection do you have between your server and your SAN?
 
What is the recommended value for fsync ops/sec for PosgreSQL WAL disks on SAN ?

You have the hardware you have.  You can't change it the same way you can change a config file entry, so I don't think that "recommended value" really applies.  Is the latency of sync requests a major bottleneck for your workload? pg_test_fsync can tell you what the latency is, but can't tell you how much you care.
 
Cheers,

Jeff


--

Haroldo Kerry

CTO/COO

Rua do Rócio, 220, 7° andar, conjunto 72

São Paulo – SP / CEP 04552-000

[hidden email]

www.callix.com.br

Reply | Threaded
Open this post in threaded view
|

Re: Recommended value for pg_test_fsync

Nikhil Shetty
Hi,

The client has done benchmark tests on available storage using a storage benchmark tool and got IOPS of around 14k on iSCSI  and around 150k on HBA channel, which seems a good number but pg_test_fysnc gives numbers which are not reflecting good op/sec. Though pg_test_fysnc result should not be compared to benchmark throughput but both are indicative of overall database performance.
WAL sync should not become a bottleneck during actual production workload.

Thanks and Regards,
Nikhil

On Wed, Jul 1, 2020 at 11:13 PM Nikhil Shetty <[hidden email]> wrote:
Hi Haroldo,

Thank you for the details.

We are using xfs on IBM Power Linux Rhel7 but I will check this in our environment and get back to you with the results.

Thanks and Regards,
Nikhil


On Wed, Jul 1, 2020, 22:46 Haroldo Kerry <[hidden email]> wrote:
Hello Nikhil,
We had performance issues with our Dell SC2020 storage in the past. We had a 6 SSD RAID10 setup and due all the latencies expected 20K IOPS but were getting 2K...
After *a lot* of work the issue was not with the storage itself but with the I/O scheduler of the filesystem (EXT4/Debian 9).
The default scheduler is CFQ, changing to deadline provided us the 10x difference that we were expecting.
In the end this was buried on the storage documentation that somehow slipped us...
Hope this helps.
Regards,
Haroldo Kerry

On Wed, Jul 1, 2020 at 2:06 PM Nikhil Shetty <[hidden email]> wrote:
Hi Jeff,

Thank you for your inputs. We may stick with fdatasync for now. We will get more details on connection details between SAN and server from the storage team and update this thread.

Storage is Hitachi G900 with 41Gbps bandwidth.

Thanks and regards,
Nikhil



On Tue, Jun 30, 2020 at 9:51 PM Jeff Janes <[hidden email]> wrote:
On Mon, Jun 29, 2020 at 5:27 AM Nikhil Shetty <[hidden email]> 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).

It is not surprising to me that SAN would have higher latency than internal storage.  What kind of connection do you have between your server and your SAN?
 
What is the recommended value for fsync ops/sec for PosgreSQL WAL disks on SAN ?

You have the hardware you have.  You can't change it the same way you can change a config file entry, so I don't think that "recommended value" really applies.  Is the latency of sync requests a major bottleneck for your workload? pg_test_fsync can tell you what the latency is, but can't tell you how much you care.
 
Cheers,

Jeff


--

Haroldo Kerry

CTO/COO

Rua do Rócio, 220, 7° andar, conjunto 72

São Paulo – SP / CEP 04552-000

[hidden email]

www.callix.com.br

Reply | Threaded
Open this post in threaded view
|

Re: Recommended value for pg_test_fsync

Bruce Momjian
On Wed, Jul  1, 2020 at 11:41:23PM +0530, Nikhil Shetty wrote:
> Hi,
>
> The client has done benchmark tests on available storage using a storage
> benchmark tool and got IOPS of around 14k on iSCSI  and around 150k on HBA
> channel, which seems a good number but pg_test_fysnc gives numbers which are
> not reflecting good op/sec. Though pg_test_fysnc result should not be compared
> to benchmark throughput but both are indicative of overall database
> performance.

Well, by definition, pg_test_fsync asks for fsync after every set of
writes.  Only the last report, "Non-sync'ed 8kB writes:" gives non-fsync
performance.

--
  Bruce Momjian  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

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