Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id AD875632747 for ; Fri, 18 Jun 2010 00:57:54 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 69339-08 for ; Fri, 18 Jun 2010 03:57:46 +0000 (UTC) X-Greylist: delayed 00:21:30.065531 by SQLgrey-1.7.6 Received: from mail-01.primus.ca (smtp-noauth7.primus.ca [216.254.180.38]) by mail.postgresql.org (Postfix) with ESMTP id 11EEF632712 for ; Fri, 18 Jun 2010 00:57:46 -0300 (ADT) Received: from [206.191.44.11] (helo=bambi.pccyber.com) by mail-01.primus.ca with esmtp (Exim 4.69) (envelope-from ) id 1OPSNG-0003Yq-0i; Thu, 17 Jun 2010 23:36:10 -0400 Received: from localhost (localhost.localdomain [127.0.0.1]) by bambi.pccyber.com (Postfix) with ESMTP id 4E5271B49BC; Thu, 17 Jun 2010 23:36:08 -0400 (EDT) X-Virus-Scanned: amavisd-new at pccyber.com Received: from bambi.pccyber.com ([127.0.0.1]) by localhost (bambi.pccyber.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 3HY1vQjMJ-29; Thu, 17 Jun 2010 23:36:04 -0400 (EDT) Received: from [173.35.103.0] (CPE0050fca9b0e1-CM0018c0d3a5ba.cpe.net.cable.rogers.com [173.35.103.0]) (Authenticated sender: mark@pccyber.com) by bambi.pccyber.com (Postfix) with ESMTPA id 17BFB1B49AC; Thu, 17 Jun 2010 23:36:04 -0400 (EDT) Message-ID: <4C1AE9A3.4080307@mark.mielke.cc> Date: Thu, 17 Jun 2010 23:36:03 -0400 From: Mark Mielke User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.9) Gecko/20100430 Fedora/3.0.4-3.fc13 Thunderbird/3.0.4 MIME-Version: 1.0 To: Greg Smith CC: Mark Kirkwood , pgsql-performance@postgresql.org Subject: Re: wal_synch_method = open_sync safe on RHEL 5.5? References: <4C1ACE5F.1080802@catalyst.net.nz> <4C1AE820.6020506@2ndquadrant.com> In-Reply-To: <4C1AE820.6020506@2ndquadrant.com> Content-Type: multipart/alternative; boundary="------------020705000605070702090703" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=-10 required=5 tests=BAYES_20=-0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201006/326 X-Sequence-Number: 39182 This is a multi-part message in MIME format. --------------020705000605070702090703 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit The conclusion I read was that Linux O_SYNC behaves like O_DSYNC on other systems. For WAL, this seems satisfactory? Personally, I use fdatasync(). I wasn't able to measure a reliable difference for my far more smaller databases, and fdatasync() seems reliable and fast enough, that fighting with O_SYNC doesn't seem to be worth it. Also, technically speaking, fdatasync() appeals more to me, as it allows the system to buffer while it can, and the application to instruct it across what boundaries it should not buffer. O_SYNC / O_DSYNC seem to imply a requirement that it does a synch on every block. My gut tells me that fdatasync() gives the operating system more opportunities to optimize (whether it does or not is a different issue :-) ). Cheers, mark On 06/17/2010 11:29 PM, Greg Smith wrote: > Mark Kirkwood wrote: >> Now I recall some discussion about this enabling direct io and the >> general flakiness of this on Linux, so is the option regarded as safe? > > No one has ever refuted the claims in > http://archives.postgresql.org/pgsql-hackers/2007-10/msg01310.php that > it can be unsafe under a heavy enough level of mixed load on RHEL5. > Given the performance benefits are marginal on ext3, I haven't ever > considered it worth the risk. (I've seen much larger gains on > Linux+Veritas VxFS). From what I've seen, recent Linux kernel work > has reinforced that the old O_SYNC implementation was full of bugs now > that more work is being done to improve that area. My suspicion > (based on no particular data, just what I've seen it tested with) is > that it only really worked before in the very specific way that Oracle > does O_SYNC writes, which is different from what PostgreSQL does. > > P.S. Be wary of expecting pgbench to give you useful numbers on a > single run. For the default write-heavy test, I recommend three runs > of 10 minutes each (-T 600 on recent PostgreSQL versions) before I > trust any results it gives. You can get useful data from the > select-only test in only a few seconds, but not the one that writes a > bunch. > > -- > Greg Smith 2ndQuadrant US Baltimore, MD > PostgreSQL Training, Services and Support > greg@2ndQuadrant.com www.2ndQuadrant.us > -- Mark Mielke --------------020705000605070702090703 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit The conclusion I read was that Linux O_SYNC behaves like O_DSYNC on other systems. For WAL, this seems satisfactory?

Personally, I use fdatasync(). I wasn't able to measure a reliable difference for my far more smaller databases, and fdatasync() seems reliable and fast enough, that fighting with O_SYNC doesn't seem to be worth it. Also, technically speaking, fdatasync() appeals more to me, as it allows the system to buffer while it can, and the application to instruct it across what boundaries it should not buffer. O_SYNC / O_DSYNC seem to imply a requirement that it does a synch on every block. My gut tells me that fdatasync() gives the operating system more opportunities to optimize (whether it does or not is a different issue :-) ).

Cheers,
mark


On 06/17/2010 11:29 PM, Greg Smith wrote:
Mark Kirkwood wrote:
Now I recall some discussion about this enabling direct io and the general flakiness of this on Linux, so is the option regarded as safe?

No one has ever refuted the claims in http://archives.postgresql.org/pgsql-hackers/2007-10/msg01310.php that it can be unsafe under a heavy enough level of mixed load on RHEL5.  Given the performance benefits are marginal on ext3, I haven't ever considered it worth the risk.  (I've seen much larger gains on Linux+Veritas VxFS).  From what I've seen, recent Linux kernel work has reinforced that the old O_SYNC implementation was full of bugs now that more work is being done to improve that area.  My suspicion (based on no particular data, just what I've seen it tested with) is that it only really worked before in the very specific way that Oracle does O_SYNC writes, which is different from what PostgreSQL does.

P.S. Be wary of expecting pgbench to give you useful numbers on a single run.  For the default write-heavy test, I recommend three runs of 10 minutes each (-T 600 on recent PostgreSQL versions) before I trust any results it gives.  You can get useful data from the select-only test in only a few seconds, but not the one that writes a bunch.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us
  


-- 
Mark Mielke <mark@mielke.cc>
--------------020705000605070702090703--