Re: COMMIT NOWAIT Performance Option - Mailing list pgsql-hackers

I am not sure about some of this.  The Oracle option does not change the 
engine fsync behavior I believe.  All that is changed is whether the client 
side waits for the complete of the fsync or not.  If this is true, the data 
store, logs, etc, are all protected.  The user may still experience a data 
loss if a network, or system failure occurred just after the client issued 
the commit.  This would be something like I send the message and exit.  
However prior to the engine receiving the message, a network component fails 
and the message is never delivered.  This will turn into an aborted 
transaction as far as the engine is concerned.  Of course, the exact details 
are in the protocol between the client and the server.

The commit nowait is async with respect to the response to the user, not the 
underlying engine I think.  Therefore performance gains are purely a user 
perspective, not an engine perspective.  Perhaps some network traffic could 
be pruned, not sending the response.

Jordan Henderson

On Tuesday 27 February 2007, Joshua D. Drake wrote:
> Josh Berkus wrote:
> > Simon,
> >
> > One of the things I love about doing informal online user support in the
> > PostgreSQL community, and formal user support for Sun's customers, is the
> > almost-ironclad guarentee that if a user has a corrupt database or data
> > loss, one of three things is true:
> > a) they didn't apply some recommended PG update;
> > b) they have a bad disk controller or disk config;
> > c) they have bad ram.
>
> That is pretty spot on.
>
> > It seriously narrows down the problem space to know that PostgreSQL does
> > *not* allow data loss if it's physically possible to prevent it.
>
> But we do don't we? fsync = off, full_page_writes = off?
>
> > Therefore, if we're going to arm a foot-gun as big as COMMIT NOWAIT for
> > PostgreSQL, I'd like to see the answers to two questions:
>
> I agree with this.
>
> > a) Please give some examples of performance gain on applications using
> > COMMIT NOWAIT.  The performance gain needs to be substantial (like, 50%
> > to 100%) to justify a compromise like this.
>
> WOAH... that seems excessive. There are a couple of things going on here.
>
> 1. We have a potential increase in performance for certain workloads.
> This is good, but must be proven. IS that proof 50%? Bah.. let's talk
> 15-25%.
>
> 2. We have to accept that not everyone wants IRON clad data integrity.
> We have many, many options for dealing with that now, including PITR and
> REPLICATION.
>
> > b) Why this and not global temporary tables or queuing?
>
> /me would love global temp tables.
>
> Much of the PostgreSQL Users out there today, will happily loose a 15
> minutes of data if it means their data is served 25% faster.
>
> Sincerely,
>
> Joshua D. Drake