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

Re: COMMIT NOWAIT Performance Option

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: COMMIT NOWAIT Performance Option
Date: 2007-02-26 23:06:19
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
> Why do we want this?? Because some apps have *lots* of data and many
> really don't care whether they lose a few records. Honestly, I've met
> people that want this, even after 2 hours of discussion and
> understanding. Plus probably lots of MySQLers also.

Most users will take speed over data loss any day. Whether we want to
admit it or not.

I think these feature is a good middle ground.


Joshua D. Drake

> User Control
> ------------
> New commit mode is available by explicit command, or as a default
> setting that will be applied to all COMMITs, or both.
> The full syntax would be COMMIT [WRITE] NOWAIT [IMMEDIATE], for Oracle
> compatibility (why choose incompatibility?). Note that this is not a
> transaction start setting like Isolation Level; this happens at end of
> transaction. The syntax for END is unchanged, defaulting to normal
> behaviour unless overridden.
> New userset GUC, commit_wait_default = on (default) | off
> We change the meaning of the commit_delay parameter:
> - If commit_delay = 0 then commit_wait_default cannot be set off. 
> - WAL will be flushed every commit_delay milliseconds; if no flush is
> required this will do nothing very quickly, so there is little overhead
> of no COMMIT NOWAIT commits have been made.
> Implementation 
> --------------
> COMMIT NOWAIT in xact.c simply ignores XLogFlush and returns. 
> Who does the XLogFlush? Well, my recommendation is a totally new
> process, WALWriter. But I can see that many of you will say bgwriter
> should be the person to do this work. IMHO doing WAL flushes will take
> time and thats time that bgwriter really needs to do other things, plus
> it can't really guarantee to do flush regularly when its doing
> checkpoints.
> When commit_delay > 0 then the WALwriter will startup, or shutdown if
> commit_delay = 0.
> WALWriter will XLogFlush every commit_delay milliseconds.
> A prototype patch is posted to -patches, which is WORK IN PROGRESS.
> The following TODO items remain
> 1. discuss which process will issue regular XLogFlush(). If agreed,
> implement WALWriter process to perform this task. (Yes, the patch isn't
> fully implemented, yet).
> 2. remove fsync parameter
> 3. Prevent COMMIT NOWAIT when commit_delay = 0
> 4. Discuss whether commit_delay is OK to usurp; twas just an earlier
> suggestion from someone else, can go either way.
> 5. docs
> The remaining items can be completed very quickly if this proposal is
> acceptable. (I wrote this over Christmas, so it turning up now isn't a
> rushed proposal and I'm pretty certain it ain't broke).
> Comments?


      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997

Donate to the PostgreSQL Project:
PostgreSQL Replication:

In response to


pgsql-hackers by date

Next:From: Gavin SherryDate: 2007-02-26 23:07:43
Subject: Re: Bitmap index stuff
Previous:From: Andrew DunstanDate: 2007-02-26 23:02:58
Subject: Re: Seeking Google SoC Mentors

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