Thread: Re: Possible Commit Syntax Change for Improved TPS

In the last exciting episode, seunosewa@inaira.com (Seun Osewa) wrote:
> So I want to ask, "what is databases have a 'COMMIT NOSYNC;' option?" 
> Then we can really improve "transaction-per-second" performance for a
> database that has lots of non-critical transactions while not
> jeopardising the durability of critical transactions in the
> (relatively unlikely) case of system failure.  Primarily through
> combining the log updates for several non-critical transactions.

Another possibility in this would be to have not one, but TWO
backends.  

One database, on one port, is running in FSYNC mode, so that the
"really vital" stuff is sure to get committed quickly.  The other, on
another port, has FSYNC turned off in its postgresql.conf file, and
the set of "untrusted" files go there.

That has the added merit that you can do other tuning that
distinguishes between the "important" and "unimportant" data.  For
instance, if the "unimportant" stuff is a set of logs that don't get
directly referred to, you might set cacheing real low on that backend
so that cache isn't being wasted on unimportant data.

So if you really want this, you can have it right now without anyone
doing any implementation work.
-- 
let name="aa454" and tld="freenet.carleton.ca" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/internet.html
God is real unless declared integer.

Christopher Browne <cbbrowne@acm.org> writes:
> In the last exciting episode, seunosewa@inaira.com (Seun Osewa) wrote:
>> So I want to ask, "what is databases have a 'COMMIT NOSYNC;' option?" 

> Another possibility in this would be to have not one, but TWO
> backends.  
> One database, on one port, is running in FSYNC mode, so that the
> "really vital" stuff is sure to get committed quickly.  The other, on
> another port, has FSYNC turned off in its postgresql.conf file, and
> the set of "untrusted" files go there.

They would have in fact to be two separate installations (not two
databases under one postmaster).  There is no way to make some
transactions less safe than others in a single installation, because
they're all hitting the same WAL log, and potentially modifying the
same disk buffers to boot.  Anyone's WAL sync therefore syncs everyone's
changes-so-far.
        regards, tom lane

tgl@sss.pgh.pa.us (Tom Lane) wrote in message news:<19310.1064932466@sss.pgh.pa.us>...
> Christopher Browne <cbbrowne@acm.org> writes:
> > In the last exciting episode, seunosewa@inaira.com (Seun Osewa) wrote:
> > So I want to ask, "what if databases have a 'COMMIT NOSYNC;' option?" 
> > Another possibility in this would be to have not one, but TWO
> > backends.  
> > One database, on one port, is running in FSYNC mode, so that the
> > "really vital" stuff is sure to get committed quickly.  The other, on
> > another port, has FSYNC turned off in its postgresql.conf file, and
> > the set of "untrusted" files go there.
> They would have in fact to be two separate installations (not two
> databases under one postmaster).  There is no way to make some
> transactions less safe than others in a single installation, because
> they're all hitting the same WAL log, and potentially modifying the
> same disk buffers to boot.  Anyone's WAL sync therefore syncs everyone's
> changes-so-far.
The beauty of the scheme is that the WAL syncs which "sync everyone's 
changes so far" would cost about the same as the WAL syncs for just 
one transaction being committed.  But when there are so many trans-
actions we would not have to sync the WAL so often.

Seun Osewa

On Thu, Oct 02, 2003 at 05:31:52AM -0700, Seun Osewa wrote:
>
> The beauty of the scheme is that the WAL syncs which "sync everyone's 
> changes so far" would cost about the same as the WAL syncs for just 
> one transaction being committed.  But when there are so many trans-
> actions we would not have to sync the WAL so often.

In that case, why not go to a "lazy" policy in high-load situations,
where subsequent commits are bundled up into a single physical write?
Just hold up a commit until either there's a full buffer's worth of 
commits waiting to be written, or some timer says it's time to flush
so the client doesn't wait too long.

It would increase per-client latency when viewed in isolation, but if
it really improves throughput that much you might end up getting a
faster response after all.

(BTW I haven't looked at the code involved so this may be completely
wrong, impossible, and/or how it works already)


Jeroen