Re: COMMIT NOWAIT Performance Option

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