Re: COMMIT NOWAIT Performance Option

On Tue, 2007-02-27 at 11:32 -0600, Jim C. Nasby wrote:
> On Tue, Feb 27, 2007 at 10:49:32AM +0000, Simon Riggs wrote:
> > > I dislike introducing new nonstandard syntax ("Oracle compatible" is not
> > > standard). If we did this I'd vote for control via a GUC setting only;
> > > I think that is more useful anyway, as an application can be made to run
> > > with such a setting without invasive source code changes.
> >
> > OK.
> >
> > Having read through all of the above things again, ISTM that we should
> > make this functionality available by a new GUC commit_fsync_delay, which
> > must be set explicitly > 0 before this feature can be used at all. If I
> > confused Tom by using commit_delay, then I'll confuse others also and
> > group commit and deferred fsync are different techniques with different
> > robustness guarantees. When enabled it should have a clear message in
> > the log to show that some commits might be using commit_nowait.
> >
> > I'd even welcome a more descriptive term that summed up the relaxed
> > transaction guarantee implied by the use of the deferred fsync
> > technique. Perhaps even a very explicit USERSET GUC:
> >
> > transaction_guarantee = on (default) | off
>
> So would you set commit_fsync_delay on a per-transaction basis? That
> doesn't make much sense to me... I guess I'm not seeing how you would
> explicitly mark transactions that you didn't want to fsync immediately.

There are 2 GUCs that would control the behaviour here:

transaction_guarantee = on | off
Specifies whether following transaction commits will guarantee
WAL has been flushed prior to reporting commit. If no guarantee
is requested (=off), then data loss may result even after the
transaction has reported its COMMIT message.
USERSET, but listed in postgresql.conf where default = on
Set this at role, individual session or transaction level to
improve performance of non-critical user data. Use of this
setting does not interfere with the transaction_guarantee
that other transactions may choose. i.e. if somebody else
chooses to take risks with their data it will not affect
the transaction guarantees the server offers to you.
Can only be set off by a transaction if commit_fsync_delay
has been enabled. Use this parameter with care; if you find
yourself wanting to use this parameter all of the time you
should consult a psychiatrist or change open source databases.

commit_fsync_delay = 0...10000 microseconds (0 = off, default)
Controls how often the WALWriter issues an XLogFlush()
SIGHUP, so set once for each server, in postgresql.conf
This provides a maximum time window of potential data loss
in the event of a server crash for transactions that choose
transaction_guarantee = off. This parameter has no effect
on transactions that choose transaction_guarantee = on.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com