Re: High COMMIT times

[Joshua Drake]

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

JD

Re: High COMMIT times

[Joshua Drake]

>
> 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.
>
>
Based on those metrics, I would start looking at other things. For example,
I once (it was years ago) experienced commit delays because the kernel
cache on Linux was getting over run. Do you have any metrics on the system
as a whole? Perhaps sar running every few minutes will help you identify a
correlation?

JD



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

Re: AWS forcing PG upgrade from v9.6 a disaster

[Joshua Drake]

On Wed, Jun 9, 2021 at 6:50 PM Dean Gibson (DB Administrator) <
postgre...@mailpen.com> wrote:

> Having now successfully migrated from PostgreSQL v9.6 to v13.2 in Amazon
> RDS, I wondered, why I am paying AWS for an RDS-based version, when I was
> forced by their POLICY to go through the effort I did?  I'm not one of the
> crowd who thinks, "It works OK, so I don't update anything".  I'm usually
> one who is VERY quick to apply upgrades, especially when there is a
> fallback ability.  However, the initial failure to successfully upgrade
> from v9.6 to any more recent major version, put me in a time-limited box
> that I really don't like to be in.
>

Right, and had you deployed on EC2 you would not have been forced to
upgrade. This is an argument against RDS for this particular problem.


>
> If I'm going to have to deal with maintenance issues, like I easily did
> when I ran native PostgreSQL, why not go back to that?  So, I've ported my
> database back to native PostgreSQL v13.3 on an AWS EC2 instance.  It looks
> like I will save about 40% of the cost, which is in accord with this
> article:  https://www.iobasis.com/Strategies-to-reduce-Amazon-RDS-Costs/
>

That is correct, it is quite a bit less expensive to host your own EC2
instances. Where it is not cheaper is when you need to easily configure
backups, take a snapshot, or bring up a replica. For those in the know,
putting in some work upfront largely removes the burden that RDS corrects
but a lot of people who deploy RDS are *not* DBAs, or even Systems people.
They are front end developers.

Glad to see you were able to work things out.

JD

-- 

   - Partner, Father, Explorer and Founder.
   - Founder - https://commandprompt.com/ - 24x7x365 Postgres since 1997
   - Founder and Co-Chair - https://postgresconf.org/
   - Founder - https://postgresql.us - United States PostgreSQL
   - Public speaker, published author, postgresql expert, and people
   believer.
   - Host - More than a refresh
   : A podcast about
   data and the people who wrangle it.