From: Andres Freund <andres@anarazel.de>
To: Greg Smith <greg@2ndquadrant.com>
Subject: Re: Defaulting wal_sync_method to fdatasync on Linux for 9.1?
Date: Fri, 5 Nov 2010 22:24:54 +0100
User-Agent: KMail/1.13.5 (Linux/2.6.37-rc1-andres-00005-gd88c092; KDE/4.5.1;
	x86_64; ; )
Cc: pgsql-performance@postgresql.org,
 Marti Raudsepp <marti@juffo.org>
References: <AANLkTi=n3zxiLNFhbpiN=JPQPwB=BhXf9iepw1KOEs8U@mail.gmail.com>
	<201011010010.28724.andres@anarazel.de>
	<4CD472CC.80006@2ndquadrant.com>
In-Reply-To: <4CD472CC.80006@2ndquadrant.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Message-Id: <201011052224.54352.andres@anarazel.de>

On Friday 05 November 2010 22:10:36 Greg Smith wrote:
> Andres Freund wrote:
> > On Sunday 31 October 2010 20:59:31 Greg Smith wrote:
> >> Writes only are sync'd out when you do a commit, or the database does a
> >> checkpoint.
> > 
> > Hm?  WAL is written out to disk after an the space provided by
> > wal_buffers(def 8) * XLOG_BLCKSZ (def 8192) is used. The default is 64kb
> > which you reach pretty quickly - especially after a checkpoint.
> Fair enough; I'm so used to bumping wal_buffers up to 16MB nowadays that
> I forget sometimes that people actually run with the default where this
> becomes an important consideration.
If you have relatively frequent checkpoints (quite a sensible in some 
environments given the burstiness/response time problems you can get) even a 
16MB wal_buffers can cause significantly more synchronous writes with O_DSYNC 
because of the amounts of wal traffic due to full_page_writes. For one the 
background wal writer wont keep up and for another all its writes will be 
synchronous...

Its simply a pointless setting.

> > Not having a real O_DSYNC on linux until recently makes it even more
> > dubious to have it as a default...
> If Linux is now defining O_DSYNC, and it's buggy, that's going to break
> more software than just PostgreSQL.  It wasn't defined before because it
> didn't work.  If the kernel developers have made changes to claim it's
> working now, but it doesn't really, I would think they'd consider any
> reports of actual bugs here as important to fix.  There's only so much
> the database can do in the face of incorrect information reported by the
> operating system.
I don't see it being buggy so far. Its just doing what it should. Which is 
simply a terrible thing for our implementation. Generally. Independent from 
linux.

> Anyway, I haven't actually seen reports that proves there's any problem
> here, I was just pointing out that we haven't seen any positive reports
> about database stress testing on these kernel versions yet either.  The
> changes here are theoretically the right ones, and defaulting to safe
> writes that flush out write caches is a long-term good thing.
I have seen several database which run under 2.6.33 with moderate to high load 
for some time now. And two 2.6.35.
Loads of problems, but none kernel related so far ;-)

Andres