Skip site navigation (1) Skip section navigation (2)

Re: COMMIT NOWAIT Performance Option

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,<pgsql-hackers(at)postgresql(dot)org>
Subject: Re: COMMIT NOWAIT Performance Option
Date: 2007-02-27 18:37:30
Message-ID: 1172601450.3760.795.camel@silverbirch.site (view raw or flat)
Thread:
Lists: pgsql-hackers
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



In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-02-27 18:50:46
Subject: Re: Implicit casts with generic arrays
Previous:From: Peter EisentrautDate: 2007-02-27 18:29:00
Subject: Re: Seeking Google SoC Mentors

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group