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 | offSpecifies whether following transaction commits will guaranteeWAL has been flushed
priorto reporting commit. If no guaranteeis requested (=off), then data loss may result even after thetransaction has
reportedits COMMIT message. USERSET, but listed in postgresql.conf where default = onSet this at role, individual
sessionor transaction level toimprove performance of non-critical user data. Use of thissetting does not interfere with
thetransaction_guaranteethat other transactions may choose. i.e. if somebody elsechooses to take risks with their data
itwill not affectthe transaction guarantees the server offers to you.Can only be set off by a transaction if
commit_fsync_delayhasbeen enabled. Use this parameter with care; if you findyourself wanting to use this parameter all
ofthe time youshould 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.confThis provides a maximum time window of potential data
lossin the event of a server crash for transactions that choosetransaction_guarantee = off. This parameter has no
effectontransactions that choose transaction_guarantee = on.
 

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