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
