Re: COMMIT NOWAIT Performance Option - Mailing list 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.

Sincerely,

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            http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/