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 <nikhil(dot)dba04(at)gmail(dot)com>
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 <hkerry(at)callix(dot)com(dot)br> 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 <nikhil(dot)dba04(at)gmail(dot)com>
>> 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 <jeff(dot)janes(at)gmail(dot)com> wrote:
>>>
>>>> On Mon, Jun 29, 2020 at 5:27 AM Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com>
>>>> 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
>>
>> hkerry(at)callix(dot)com(dot)br
>>
>> www.callix.com.br
>>
>