Message-ID: <50035EE3.8030607@ringerc.id.au>
Date: Mon, 16 Jul 2012 08:22:59 +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>
CC: Jeff Janes <jeff.janes@gmail.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>,
	Daniel Farina <daniel@heroku.com>, =?ISO-8859-1?Q?=22Harold_A=2E_Gim?=
	=?ISO-8859-1?Q?=E9nez=22?= <harold.gimenez@gmail.com>
Subject: Re: [PERFORM] DELETE vs TRUNCATE explanation
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>
In-Reply-To: <19949.1342376967@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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()

Let the OS do the housekeeping.

It's possible to do something similar on Windows, in that there are 
utilities for the purpose:

   http://technet.microsoft.com/en-us/sysinternals/bb897438.aspx

This probably uses:

   http://msdn.microsoft.com/en-us/library/s9xk9ehd%28VS.71%29.aspx

from COMMODE.OBJ (unfortunate name), which has existed since win98.


> Perhaps we should change fsync
> to be PGC_POSTMASTER (ie frozen at postmaster start), and then we could
> skip forwarding fsync requests when it's off?

Personally, I didn't even know it was runtime switchable.

fsync=off is much less necessary with async commits, group commit via 
commit delay, WAL improvements, etc. To me it's mostly of utility when 
testing, particularly on SSDs. I don't see a DB restart requirement as a 
big issue. It'd be interesting to see what -general has to say, if there 
are people depending on this.

If it's necessary to retain the ability to runtime switch it, making it 
a somewhat rude sync() in exchange for boosted performance the rest of 
the time may well be worthwhile anyway. It'd be interesting to see.

All this talk of synchronisation is making me really frustrated that 
there seems to be very poor support in OSes for syncing a set of files 
in a single pass, potentially saving a lot of time and thrashing. A way 
to relax the ordering guarantee from "Files are synced in the order 
fsync() is called on each" to "files are all synced when this call 
completes" would be great. I've been running into this issue in some 
non-Pg-related work and it's been bugging me.

--
Craig Ringer