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 <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