Message-ID: <50037464.7020503@ringerc.id.au>
Date: Mon, 16 Jul 2012 09:54:44 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
CC: Jeff Janes <jeff.janes@gmail.com>, Daniel Farina <daniel@heroku.com>,
	=?ISO-8859-1?Q?=22Harold_A=2E_Gim=E9nez=22?= <harold.gimenez@gmail.com>
Subject: Proposed change for 9.3(?): Require full restart to change fsync
	parameter, not just pg_ctl reload
References: <4FFCCAC4.4030503@ringerc.id.au>
	<CAAZKuFZ9tYeC1K5ZayZgztm3re_7ytbsEYFa0wwXirB6Hfm+EQ@mail.gmail.com>
	<23853.1342015548@sss.pgh.pa.us>
	<CAAZKuFYsK+ZQuC=zf0xE=M-LNa02tB7RBy3CHEUyq7LWCkK6jQ@mail.gmail.com>
	<CAMkU=1ydaypgAeZxyaMc=KhJJw5riGMBiLpvSqOO42=qArRZmQ@mail.gmail.com>
	<32078D4B40DD40ECB739D0ECD7D95996@gmail.com>
	<CAMkU=1zJK6WiS9-6eO8rNmRpKSSB4=uw2mTsD=k64mNh__=Dcg@mail.gmail.com>
	<CAMkU=1w7vLA63hf-+Uc_j61CvOdKixsOHph9d0H-aLNBYgfX=w@mail.gmail.com>
	<CAMkU=1yLXvODRZZ_=fgrEeJfk2tvZPTTD-8n8BwrAhNz_WBT0A@mail.gmail.com>
	<19949.1342376967@sss.pgh.pa.us> <50035EE3.8030607@ringerc.id.au>
	<27245.1342402661@sss.pgh.pa.us>
In-Reply-To: <27245.1342402661@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi all

Some performance improvements have been proposed - probably for 9.3 - 
that will mean the `fsync' GUC can only be changed with a full cluster 
restart. See quoted, at end of message.

It is currently possible to change `fsync' by altering postgresql.conf 
and issuing a `pg_ctl reload' . It is not clear how safe this really is 
even now, and changes proposed to reduce the amount of expensive 
bookkeeping done when fsync is set to 'off' will make it even less safe. 
Consequently, it is proposed that the ability to change the fsync 
setting while Pg is running be removed.

fsync=off is very unsafe anyway, and these days production setups are 
able to get similar results with async commits and group commit.

Is there anyone here relying on being able to change fsync=off to 
fsync=on at runtime? If so, what for, and what does it gain you over use 
of group/async commit?

For related discussion see the -hackers thread:

  "DELETE vs TRUNCATE explanation"

 
http://archives.postgresql.org/message-id/CAMkU=1yLXvODRZZ_=fgrEeJfk2tvZPTTD-8n8BwrAhNz_WBT0A@mail.gmail.com


and the background threads:

  "PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the 
fastest way to clean each non-empty table and reset unique identifier 
column of empty ones."

 
http://archives.postgresql.org/message-id/CAFXpGYbgmZYij4TgCbOF24-usoiDD0ASQeaVAkYtB7E2TYm8Wg@mail.gmail.com

   "DELETE vs TRUNCATE explanation"

   http://archives.postgresql.org/message-id/4FFCCAC4.4030503@ringerc.id.au


On 07/16/2012 09:37 AM, Tom Lane wrote:
> Craig Ringer <ringerc@ringerc.id.au> writes:
>> On 07/16/2012 02:29 AM, Tom Lane wrote:
>>> Yeah, you have a point there.  It's not real clear that switching fsync
>>> from off to on is an operation that we can make any guarantees about,
>>> short of executing something like the code recently added to initdb
>>> to force-sync the entire PGDATA tree.
>
>> There's one way that doesn't have any housekeeping cost to Pg. It's
>> pretty bad manners if there's anybody other than Pg on the system though:
>>     sync()
>
> Yeah, I thought about that: if we could document that issuing a manual
> sync after turning fsync on leaves you in a guaranteed-good state once
> the sync is complete, it'd probably be fine.  However, I'm not convinced
> that we could promise that with a straight face.  In the first place,
> PG has only very weak guarantees about how quickly all processes in the
> system will absorb a GUC update.  In the second place, I'm not entirely
> sure that there aren't race conditions around checkpoints and the fsync
> request queue (particularly if we do what Jeff is suggesting and
> suppress queuing requests at the upstream end).  It might be all right,
> or it might be all right after expending some work, but the whole thing
> is not an area where I think anyone wants to spend time.  I think it'd
> be much safer to document that the correct procedure is "stop the
> database, do a manual sync, enable fsync in postgresql.conf, restart the
> database".  And if that's what we're documenting, we lose little or
> nothing by marking fsync as PGC_POSTMASTER.
>
> 			regards, tom lane
>