Обсуждение: Commit Delay
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
Thanks,
Naveen.
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 +
Hi Naveen
I was a little confused by this question so perhaps my interpretation is not accurate but given we are talking about
dataloss, I felt compelled to respond. Turning off "synchronous commit" means that the application session will get a
successback from the API call before the physical i/o is acknowledged. This does risk data loss and should only be
donein 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
probablyincrease "commit_delay" to encourage more concurrent write sessions to group their commits into fewer writes.
Thewriter sessions will still wait for the synchronous write operation so latency will increase slightly but you may
seedecreased 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
confirmthe 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 +
On Fri, May 1, 2020 at 9:27 AM Thawley, Peter <pthawley@amazon.com> wrote:
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.
Yeah, the problem does seem under-specified. Knowing the form of these bulk operations helps greatly when trying to tune based upon their presence (or, in some cases, rewrite them to a better form).
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.
For some definitions of data loss. Specifically, a server that crashes before the data is full written to disk would end up in the exact same state (relative to the transaction) whether synchronous commit is on or off - the difference being whether the session is still waiting when the crash happens, or has moved on. When the server comes back up it, and possibly other components, may now think differently about the present state than what the database thinks. Dealing with the potential discrepancy is the the trade-off for not having to wait.
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.
Agreed, this is probably a better first avenue to approach.
David J.