On Wed, Jan 6, 2021 at 10:51 AM Joshua Drake <j...@commandprompt.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
