Proposal: Implement a new option for COMMIT, for enhancing performance,
providing a MySQL-like trade-off between performance and robustness for
*only* those that want it.

COMMIT NOWAIT

This form of COMMIT will *not* perform XLogFlush(), but will rely on a
special background process to perform regular WAL fsyncs (see later).

COMMIT NOWAIT can co-exist with the normal form of COMMIT and does not
threaten the consistency or robustness of other COMMIT modes. Read that
again and think about it, before we go further, please. Normal COMMIT
still guarantees to flush all of WAL up to the point of the commit,
whether or not the previous commits have requested that.

Mixing COMMIT NOWAIT with other modes does not effect the performance of
other backends - those that specify that mode are faster, those that do
not simply go at the same speed they did before. This is important,
because it allows us to have a fully robust server, yet with certain
critical applications going along much faster. No need for an
all-or-nothing approach at db cluster level.

Unlike fsync = off, WAL is always consistent and the server can be
recovered easily, though with some potential for data loss for
transactions that chose the COMMIT NOWAIT option. Sounds like a hole
there: normal COMMITs that rely on data written by COMMIT NOWAIT
transactions are still safe, because the normal COMMIT is still bound by
the guarantee to go to disk. The buffer manager/WAL interlock is not
effected by this change and remains in place, as it should.

This implements the TODO item:
--Allow buffered WAL writes and fsync
"Instead of guaranteeing recovery of all committed transactions, this
would provide improved performance by delaying WAL writes and fsync so
an abrupt operating system restart might lose a few seconds of committed
transactions but still be consistent. We could perhaps remove the
'fsync' parameter (which results in an an inconsistent database) in
favor of this capability."

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.

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?

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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

> 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/


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Simon Riggs wrote:
> Proposal: Implement a new option for COMMIT, for enhancing performance,
> providing a MySQL-like trade-off between performance and robustness for
> *only* those that want it.
>
> COMMIT NOWAIT
>
> This form of COMMIT will *not* perform XLogFlush(), but will rely on a
> special background process to perform regular WAL fsyncs (see later).
>
> COMMIT NOWAIT can co-exist with the normal form of COMMIT and does not
> threaten the consistency or robustness of other COMMIT modes. Read that
> again and think about it, before we go further, please. Normal COMMIT
> still guarantees to flush all of WAL up to the point of the commit,
> whether or not the previous commits have requested that.
>
> Mixing COMMIT NOWAIT with other modes does not effect the performance of
> other backends - those that specify that mode are faster, those that do
> not simply go at the same speed they did before. This is important,
> because it allows us to have a fully robust server, yet with certain
> critical applications going along much faster. No need for an
> all-or-nothing approach at db cluster level.
>
> Unlike fsync = off, WAL is always consistent and the server can be
> recovered easily, though with some potential for data loss for
> transactions that chose the COMMIT NOWAIT option. Sounds like a hole
> there: normal COMMITs that rely on data written by COMMIT NOWAIT
> transactions are still safe, because the normal COMMIT is still bound by
> the guarantee to go to disk. The buffer manager/WAL interlock is not
> effected by this change and remains in place, as it should.

OK, so do I have this right?

A is issuing COMMIT NOWAIT once a second
B is issuing COMMIT every other second
Checkpoints happen every 10 seconds

Every 10 seconds we have a checkpoint and all WAL+data are on-disk.
Every 2 seconds a standard COMMIT occurs and A+B are synced to WAL
In-between COMMIT NOWAIT occurs and no data is written to disk - neither
WAL nor data-blocks.

So, if I have a load of connections issuing standard COMMITs regularly
then I'll not see much performance-gain. However, I can guarantee any
data visible to those transactions is committed to disk.

If I have only one connection to the database and that uses only COMMIT
NOWAIT, it should approach the speed of fsync=off.

So this is for:
1. data-loading where I don't want to use fsync=off (for whatever reason)
2. Data-logging type apps

Have I got the gist of that?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Mon, 2007-02-26 at 23:25 +0000, Richard Huxton wrote:
> Simon Riggs wrote:
> > Proposal: Implement a new option for COMMIT, for enhancing performance,
> > providing a MySQL-like trade-off between performance and robustness for
> > *only* those that want it.
> >
> > COMMIT NOWAIT
> >
> > This form of COMMIT will *not* perform XLogFlush(), but will rely on a
> > special background process to perform regular WAL fsyncs (see later).
> >
> > COMMIT NOWAIT can co-exist with the normal form of COMMIT and does not
> > threaten the consistency or robustness of other COMMIT modes. Read that
> > again and think about it, before we go further, please. Normal COMMIT
> > still guarantees to flush all of WAL up to the point of the commit,
> > whether or not the previous commits have requested that.
> >
> > Mixing COMMIT NOWAIT with other modes does not effect the performance of
> > other backends - those that specify that mode are faster, those that do
> > not simply go at the same speed they did before. This is important,
> > because it allows us to have a fully robust server, yet with certain
> > critical applications going along much faster. No need for an
> > all-or-nothing approach at db cluster level.
> >
> > Unlike fsync = off, WAL is always consistent and the server can be
> > recovered easily, though with some potential for data loss for
> > transactions that chose the COMMIT NOWAIT option. Sounds like a hole
> > there: normal COMMITs that rely on data written by COMMIT NOWAIT
> > transactions are still safe, because the normal COMMIT is still bound by
> > the guarantee to go to disk. The buffer manager/WAL interlock is not
> > effected by this change and remains in place, as it should.
>
> OK, so do I have this right?
>
> A is issuing COMMIT NOWAIT once a second
> B is issuing COMMIT every other second
> Checkpoints happen every 10 seconds
>
> Every 10 seconds we have a checkpoint and all WAL+data are on-disk.
> Every 2 seconds a standard COMMIT occurs and A+B are synced to WAL
> In-between COMMIT NOWAIT occurs and no data is written to disk - neither
> WAL nor data-blocks.

Right, but there'd also be a WAL flush every 100ms or so, according to
how you set the parameter. So the window of data loss is controllable
for both efficiency and user selected robustness for those transactions
that want it.

(Plus the point that a COMMIT doesn't send data to disk anyway, only
WAL. This proposal does nothing to change that, I should add).

> So, if I have a load of connections issuing standard COMMITs regularly
> then I'll not see much performance-gain. However, I can guarantee any
> data visible to those transactions is committed to disk.
>
> If I have only one connection to the database and that uses only COMMIT
> NOWAIT, it should approach the speed of fsync=off.
>
> So this is for:
> 1. data-loading where I don't want to use fsync=off (for whatever reason)
> 2. Data-logging type apps
>
> Have I got the gist of that?

Pretty much.

Only the people using COMMIT NOWAIT see a performance gain, and of
course only if they are doing write transactions of relatively short
duration.

The interesting point is you can have a huge data grinding app, yet with
other tables alongside that hold more important data. In that scenario,
90% of the data would be COMMIT NOWAIT, whilst the small important data
is safe.

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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Simon Riggs wrote:

> The interesting point is you can have a huge data grinding app, yet with
> other tables alongside that hold more important data. In that scenario,
> 90% of the data would be COMMIT NOWAIT, whilst the small important data
> is safe.

Does this means that the regular COMMIT is slower because it has to
force more data to disk? I imagine that this isn't the case, because
it's not the write itself that's slow; rather, it's the wait until the
fsync on WAL is reported complete. However, did you measure this?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Feb 26, 2007, at 18:58 , Simon Riggs wrote:

> On Mon, 2007-02-26 at 23:25 +0000, Richard Huxton wrote:
>> Simon Riggs wrote:
>>> Proposal: Implement a new option for COMMIT, for enhancing
>>> performance,
>>> providing a MySQL-like trade-off between performance and
>>> robustness for
>>> *only* those that want it.
>>>
>>> COMMIT NOWAIT

Isn't the domain of transient data relegated to certain tables
instead of specific transactions? Wouldn't it be easier to create un-
wal-logged global temp tables?

-M

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Mon, 2007-02-26 at 22:56 +0000, Simon Riggs wrote:
> Proposal: Implement a new option for COMMIT, for enhancing performance,
> providing a MySQL-like trade-off between performance and robustness for
> *only* those that want it.
>
> COMMIT NOWAIT
>
> This form of COMMIT will *not* perform XLogFlush(), but will rely on a
> special background process to perform regular WAL fsyncs (see later).
>

I would think that the type of people who would want this feature would
want it to be a property of the connection, not per-statement. For one
thing, many applications use solitary SQL statements without surrounding
them in BEGIN/COMMIT.

Regards,
Jeff Davis


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jeff Davis wrote:
> On Mon, 2007-02-26 at 22:56 +0000, Simon Riggs wrote:
> > Proposal: Implement a new option for COMMIT, for enhancing performance,
> > providing a MySQL-like trade-off between performance and robustness for
> > *only* those that want it.
> >
> > COMMIT NOWAIT
> >
> > This form of COMMIT will *not* perform XLogFlush(), but will rely on a
> > special background process to perform regular WAL fsyncs (see later).
> >
>
> I would think that the type of people who would want this feature would
> want it to be a property of the connection, not per-statement. For one
> thing, many applications use solitary SQL statements without surrounding
> them in BEGIN/COMMIT.

You can set the GUC locally in your connection, of course. (You can
even do it per role or per database by using ALTER/SET commands)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"Simon Riggs" <simon@2ndquadrant.com> writes:
> COMMIT NOWAIT can co-exist with the normal form of COMMIT and does not
> threaten the consistency or robustness of other COMMIT modes. Read that
> again and think about it, before we go further, please.

I read that, and thought about it, and don't think I believe it. The
problem is that there are more interconnections between different
transactions than you're allowing for. In particular you need to
justify that the behavior is safe for non-transactional operations like
btree page splits and pg_clog buffer page writes. The idea that's
particularly bothering me at the moment is that after a system crash,
we might come back up in a state where a NOWAIT transaction appears
committed when its updates didn't all get to disk. "Database corrupt"
is a situation that threatens all your transactions...

> New commit mode is available by explicit command, or as a default
> setting that will be applied to all COMMITs, or both.

I dislike introducing new nonstandard syntax ("Oracle compatible" is not
standard). If we did this I'd vote for control via a GUC setting only;
I think that is more useful anyway, as an application can be made to run
with such a setting without invasive source code changes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Tue, February 27, 2007 06:06, Joshua D. Drake wrote:
>
>> 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.

In that case, wouldn't it make just as much sense to have an equivalent
for this special transaction mode on individual statements, without
transaction context? I'm guessing that who don't really know or want
transactions would never start one, running lots of loose statements
instead that otherwise get committed individually.


Jeroen



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

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