From: Thawley, Peter <pthawley@amazon.com>
To: Bruce Momjian <bruce@momjian.us>
To: Naveen Sankineni <nsankineni@gmail.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: RE: Commit Delay
Date: Fri, 1 May 2020 16:26:59 +0000
Message-ID: <7cee0732a2ac4a479b5da94a6cb2693f@EX13D14UWC004.ant.amazon.com> (raw)
In-Reply-To: <20200430233354.GB31272@momjian.us>
References: <CAOOQyJKp-zPNCPLvYoL+GES22iRv8uXLK3Jh7FsvoH2MDK9rBQ@mail.gmail.com>
<20200430233354.GB31272@momjian.us>
Hi Naveen
I was a little confused by this question so perhaps my interpretation is not accurate but given we are talking about data loss, I felt compelled to respond. Turning off "synchronous commit" means that the application session will get a success back from the API call before the physical i/o is acknowledged. This does risk data loss and should only be done in application use cases where it really makes sense.
If you want to improve performance of write throughput in wal operations but not risk potential data loss, you should probably increase "commit_delay" to encourage more concurrent write sessions to group their commits into fewer writes. The writer sessions will still wait for the synchronous write operation so latency will increase slightly but you may see decreased writes.
Peter Thawley
Amazon Aurora & RDS Database Service team, AWS
-----Original Message-----
From: Bruce Momjian <bruce@momjian.us>
Sent: Thursday, April 30, 2020 4:34 PM
To: Naveen Sankineni <nsankineni@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: RE: [EXTERNAL] Commit Delay
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you can confirm the sender and know the content is safe.
On Thu, Apr 30, 2020 at 07:31:01PM -0400, Naveen Sankineni wrote:
> Hello ,
>
> I would need your advice on which parameter is least impacted in terms
> of Data Loss if we wanted to set .
> One of our Database is waiting on I/O when there is a batch operation
> that runs to Insert/Delete table data is impacting the small updates
> that runs on a continuous basis where it shows that wait is on walwritelock.
>
> commit_delay
> synchronous_commit
The full list is here:
https://www.postgresql.org/docs/12/non-durability.html
I would say synchronous_commit.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pthawley@amazon.com, bruce@momjian.us, nsankineni@gmail.com, pgsql-admin@lists.postgresql.org
Subject: RE: Commit Delay
In-Reply-To: <7cee0732a2ac4a479b5da94a6cb2693f@EX13D14UWC004.ant.amazon.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox