On Wed, Jan 6, 2021 at 10:51 AM Joshua Drake <jd@xxxxxxxxxxxxxxxxx> 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 forthe 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.
Don.
Don Seiler
www.seiler.us
www.seiler.us
