Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group