pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Craig Ringer <ringerc@ringerc.id.au>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Daniel Farina <daniel@heroku.com>
Cc: "Harold A. Giménez" <harold.gimenez@gmail.com>
Subject: Re: [PERFORM] DELETE vs TRUNCATE explanation
Date: Mon, 16 Jul 2012 08:22:59 +0800
Message-ID: <50035EE3.8030607@ringerc.id.au> (raw)
In-Reply-To: <19949.1342376967@sss.pgh.pa.us>
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>

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




view thread (46+ messages)  latest in thread

Message-ID: <50035EE3.8030607@ringerc.id.au>
Permalink:  ../50035EE3.8030607@ringerc.id.au/
Also on:    postgresql.org/message-id/50035EE3.8030607@ringerc.id.au

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: ringerc@ringerc.id.au, tgl@sss.pgh.pa.us, jeff.janes@gmail.com, pgsql-hackers@postgresql.org, daniel@heroku.com, harold.gimenez@gmail.com
  Subject: Re: [PERFORM] DELETE vs TRUNCATE explanation
  In-Reply-To: <50035EE3.8030607@ringerc.id.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox