Re: High COMMIT times

On Wed, Jan 6, 2021 at 10:51 AM Joshua Drake <jd(at)commandprompt(dot)com> wrote:

> I have the stats_temp_directory in a tmpfs mount. I *do* have pg_wal on
>> the same premium SSD storage volume as the data directory. Normally I would
>> know to separate these but I was told with the cloud storage that it's all
>> virtualized anyway, plus storage IOPS are determined by disk size so having
>> a smaller volume just for pg_wal would hurt me in this case. The kind folks
>> in the PG community Slack suggested just having one large premium cloud
>> storage mount for
>>
> the data directory and leave pg_wal inside because this virtualization
>> removes any guarantee of true separation.
>>
>
> It is true that the IO is virtualized but that does not mean that separate
> volumes won't help. In cloud storage you are granted specific IOPS/MB/s per
> volume. Separating pg_wal to a new volume mount will take pressure off of
> page writes and allow the wal to write within its own prioritization.
>

Looking at the Azure portal metric, we are nowhere close to the advertised
maximum IOPS or MB/s throughput (under half of the maximum IOPS and under a
quarter of the MB/s maximum). So there must be some other bottleneck in
play. The IOPS limit on this VM size is even higher so that shouldn't be it.

If I were to size a separate volume for just WAL, I would think 64GB but
obviously the Azure storage IOPS are much less. On this particular DB host,
we're currently on a 2.0T P40 disk that is supposed to give 7500 IOPS and
250MB/s [1] (but again, Azure's own usage graphs show us nowhere near those
limits). A smaller volume like 64GB would be provisioned at 240 IOPS in
this example. Doesn't seem like a lot. Really until you get a terabyte it
seems like a real drop-off as far as Azure storage goes.

I'd be interested to hear what others might have configured on their
write-heavy cloud databases.

[1] https://azure.microsoft.com/en-us/pricing/details/managed-disks/

Don.

--
Don Seiler
www.seiler.us