Re: [Fwd: Deferred Transactions, Transaction Guarantee and COMMITwithout waiting]

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Simon Riggs wrote:

Once more, with feeling.

On Sun, 2007-04-01 at 12:11 +0100, Simon Riggs wrote:

Resending...

-------- Forwarded Message --------
From: Simon Riggs <simon@2ndquadrant.com>
To: pgsql-patches@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Deferred Transactions, Transaction Guarantee and COMMIT without
waiting
Date: Sat, 31 Mar 2007 22:09:23 +0100

Here's the next version (v10) of the patch, ready for review.

I've struggled with what to call all of the new concepts inherent in
this patch, but I think I've got something now. COMMIT NOWAIT doesn't
describe this feature, since there is no command of that name in the
implementation that we've agreed. So what's it called?

This patch implements a feature called Deferred Fsync Transactions, or
Deferred Transactions for short. The idea is we don't fsync at commit,
but we defer that briefly, letting a new WAL Writer process perform the
fsync at regular intervals of 50-250 ms. It's a much safer version of
fsync = off, yet retaining most of the speed *and* it can be used for
some transactions and not others.

Deferred Transactions provide considerable additional performance in a
range of circumstances, but at the cost that a handful of committed
transactions will definitely be lost if the server crashes.

To remind everybody of the risks, this feature is enabled using a
parameter named transaction_guarantee. The default mode is "on"
reminding us that PostgreSQL provides a strong default guarantee that if
a transaction is committed, it stays committed. If you prefer
performance at the risk of data loss, then you can opt to relax the
standard level of protection and request transaction_guarantee = off

The data loss isn't random, nor is it indeterminate, but it is certain.
We will say that a transaction is committed, but it isn't until it has
reached disk. So all transactions that have reached the commit point,
but not yet reached disk will be certainly lost - probably best to use a
guidelines figure of 1000 transactions when assessing the business
impact of such loss. The risk is very similar to normal transactions
waiting to write to disk, but the important difference is we will have
replied to the client that the transaction is safely on disk, when it is
not.

Relaxing the transaction guarantee in this way is completely
controllable by users. Guaranteed and Unguaranteed transactions can
co-exist safely without increased risk for more important data.

v10 fixes a number of lurking bugs present in v9. There are no
outstanding bugs, after a range of tests, though more are needed.

wal_writer_delay = 0 (default) ms enables this feature at server start.
Once enabled, individual sessions or transactions may request
transaction_guarantee = off, or it may be set for the whole server.

It also provides additional instrumentation, with new parameters:
trace_commit = on will show details of each commit (high volume)
trace_bg_flush = on will give more frequent summaries of monitoring data

The patch needs a reviewers guide, which I'll write next week.

patch -p0 < transaction_guarantee.v10.patch
with additional files:
src/backend/postmaster/walwriter.c
src/include/postmaster/walwriter.c

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

[ Attachment, skipping... ]

[ Attachment, skipping... ]

[ Attachment, skipping... ]

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Simon Riggs wrote:

Here's the next version (v10) of the patch, ready for review.

I've struggled with what to call all of the new concepts inherent in
this patch, but I think I've got something now. COMMIT NOWAIT doesn't
describe this feature, since there is no command of that name in the
implementation that we've agreed. So what's it called?

Hmm. Deferred commit? Asynchronous commit?

v10 fixes a number of lurking bugs present in v9. There are no
outstanding bugs, after a range of tests, though more are needed.

We don't need any more bugs, thank you ;-).

wal_writer_delay = 0 (default) ms enables this feature at server start.
Once enabled, individual sessions or transactions may request
transaction_guarantee = off, or it may be set for the whole server.

This error message talks about commit_fsync_delay:
ERROR: cannot set transaction guarantee when server commit_fsync_delay = 0

Should be wal_writer_delay, I suppose.

The HeapTupleSetVisibilityInfo function with it's action-argument looks
funny to me.

You don't need to check if a transaction has been flushed when setting
the HEAP_XMAX/XMIN_INVALID flags for aborted transactions.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, 2007-04-03 at 15:04 +0100, Heikki Linnakangas wrote:

Simon Riggs wrote:

Here's the next version (v10) of the patch, ready for review.

Thanks for reviewing.

I've struggled with what to call all of the new concepts inherent in
this patch, but I think I've got something now. COMMIT NOWAIT doesn't
describe this feature, since there is no command of that name in the
implementation that we've agreed. So what's it called?

Hmm. Deferred commit? Asynchronous commit?

I'll change everything to whatever we agree. Other ideas?

wal_writer_delay = 0 (default) ms enables this feature at server start.
Once enabled, individual sessions or transactions may request
transaction_guarantee = off, or it may be set for the whole server.

This error message talks about commit_fsync_delay:
ERROR: cannot set transaction guarantee when server commit_fsync_delay = 0

Should be wal_writer_delay, I suppose.

Thanks, will change.

The HeapTupleSetVisibilityInfo function with it's action-argument looks
funny to me.

Lets see what others think as well.

You don't need to check if a transaction has been flushed when setting
the HEAP_XMAX/XMIN_INVALID flags for aborted transactions.

I was after as little change as possible in the code, which is why I
left that as it was. But I think you're right and that's a one line
change to avoid that is possible. Will do.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com