Re: COMMIT NOWAIT Performance Option

From: "Zeugswetter Andreas ADI SD" <ZeugswetterA(at)spardat(dot)at>
To: "Simon Riggs" <simon(at)2ndquadrant(dot)com>, "Jim C(dot) Nasby" <jim(at)nasby(dot)net>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: COMMIT NOWAIT Performance Option
Date: 2007-02-27 19:16:59
Message-ID: E1539E0ED7043848906A8FF995BDA57901CAFC6B@m0143.s-mxs.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> There are 2 GUCs that would control the behaviour here:
>
> transaction_guarantee = on | off

> has been enabled. Use this parameter with care; if you find
> yourself wanting to use this parameter all of the time you
> should consult a psychiatrist or change open source databases.

If you guarantee your customers, that you wont loose a transaction that
has already been committed you need to at least have synchronous
replication to a remote site. Since not many installations have that, I
do find it funny that people imply so much safety only by syncing the
wal.

Without sync replication a "buffered wal" as proposed only increases the
chances that you loose something. It certainly is no change from safe
heaven to abyssmal hell.

So I think the part after the semicolon can safely be dropped.
Many will be able to use it always, without changing to another db :-)

> commit_fsync_delay = 0...10000 microseconds (0 = off, default)
> Controls how often the WALWriter issues an XLogFlush()
> SIGHUP, so set once for each server, in postgresql.conf
> This provides a maximum time window of potential data loss
> in the event of a server crash for transactions that choose
> transaction_guarantee = off. This parameter has no effect
> on transactions that choose transaction_guarantee = on.

The wal sync method probably needs to be considered ?
If the wal is opened with open_datasync, how does that affect the
performance, or do you ommit the write and leave that to the WALWriter
also ? You probably also want more wal_buffers in such a setup. It may
be better to trigger the WALWriter with wal_buffer fill-level instead of
an extra parameter ?

It is imho great that you are working on this. I always thought it
impossible, because WAL (write ahead) implied to me, that you are not
allowed to do some data/index page changes before wal is on disk.

Andreas

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2007-02-27 19:26:09 Re: conversion efforts (Re: SCMS question)
Previous Message Casey Duncan 2007-02-27 19:05:16 Re: autovacuum next steps, take 2