Re: Recommended value for pg_test_fsync
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 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 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 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 wrote: > On Mon, Jun 29, 2020 at 5:27 AM 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). >> > > 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 > >>
Re: Recommended value for pg_test_fsync
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 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 wrote: > >> On Mon, Jun 29, 2020 at 5:27 AM 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). >>> >> >> 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 >> >>>
Re: Recommended value for pg_test_fsync
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 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 wrote: > >> On Mon, Jun 29, 2020 at 5:27 AM 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). >>> >> >> 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 [email protected] www.callix.com.br
Re: Recommended value for pg_test_fsync
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 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 > 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 wrote: >> >>> On Mon, Jun 29, 2020 at 5:27 AM 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). >>> >>> 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 > > [email protected] > > www.callix.com.br >
Re: Recommended value for pg_test_fsync
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 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 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 >> 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 wrote: >>> On Mon, Jun 29, 2020 at 5:27 AM 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). > 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 >> >> [email protected] >> >> www.callix.com.br >> >
Re: Recommended value for pg_test_fsync
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 https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
