pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCommit Delay
4+ messages / 4 participants
[nested] [flat]
* Commit Delay
@ 2020-04-30 23:31 Naveen Sankineni <nsankineni@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Naveen Sankineni @ 2020-04-30 23:31 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Commit Delay
@ 2020-04-30 23:33 Bruce Momjian <bruce@momjian.us>
parent: Naveen Sankineni <nsankineni@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 2020-04-30 23:33 UTC (permalink / raw)
To: Naveen Sankineni <nsankineni@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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 +
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Commit Delay
@ 2020-05-01 16:26 Thawley, Peter <pthawley@amazon.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: Thawley, Peter @ 2020-05-01 16:26 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; Naveen Sankineni <nsankineni@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 +
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Commit Delay
@ 2020-05-01 16:46 David G. Johnston <david.g.johnston@gmail.com>
parent: Thawley, Peter <pthawley@amazon.com>
0 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2020-05-01 16:46 UTC (permalink / raw)
To: Thawley, Peter <pthawley@amazon.com>; +Cc: Bruce Momjian <bruce@momjian.us>; Naveen Sankineni <nsankineni@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-05-01 16:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-30 23:31 Commit Delay Naveen Sankineni <nsankineni@gmail.com>
2020-04-30 23:33 ` Bruce Momjian <bruce@momjian.us>
2020-05-01 16:26 ` Thawley, Peter <pthawley@amazon.com>
2020-05-01 16:46 ` David G. Johnston <david.g.johnston@gmail.com>
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