Re: COMMIT NOWAIT Performance Option

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