Horton

Thread

  1. [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Marti Raudsepp <marti@juffo.org> — 2010-11-05T17:23:35Z

    Hi list,

PostgreSQL's default settings change when built with Linux kernel
headers 2.6.33 or newer. As discussed on the pgsql-performance list,
this causes a significant performance regression:
http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php

NB! I am not proposing to change the default -- to the contrary --
this patch restores old behavior. Users might be in for a nasty
performance surprise when re-building their Postgres with newer Linux
headers (as was I), so I propose that this change should be made in
all supported releases.

-- commit message --
Revert default wal_sync_method to fdatasync on Linux 2.6.33+

Linux kernel headers from 2.6.33 (and later) change the behavior of the
O_SYNC flag. Previously O_SYNC was aliased to O_DSYNC, which caused
PostgreSQL to use fdatasync as the default instead.

Starting with kernels 2.6.33 and later, the definitions of O_DSYNC and
O_SYNC differ. When built with headers from these newer kernels,
PostgreSQL will default to using open_datasync. This patch reverts the
Linux default to fdatasync, which has had much more testing over time
and also significantly better performance.
-- end commit message --

Earlier kernel headers defined O_SYNC and O_DSYNC to 0x1000
2.6.33 and later define O_SYNC=0x101000 and O_DSYNC=0x1000 (since old
behavior on most FS-es was always equivalent to POSIX O_DSYNC)

More details at:
http://git.kernel.org/?p=linux/kernel/git/torvalds/linux-2.6.git;a=commit;h=6b2f3d1f769be5779b479c37800229d9a4809fc3

Currently PostgreSQL's include/access/xlogdefs.h defaults to using
open_datasync when O_SYNC != O_DSYNC, otherwise fdatasync is used.

Since other platforms might want to default to fdatasync in the
future, too, I defined a new PLATFORM_DEFAULT_SYNC_METHOD constant in
include/port/linux.h. I don't know if this is the best way to do it.

Regards,
Marti

  2. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-11-05T18:13:47Z

    Marti Raudsepp <marti@juffo.org> writes:
> PostgreSQL's default settings change when built with Linux kernel
> headers 2.6.33 or newer. As discussed on the pgsql-performance list,
> this causes a significant performance regression:
> http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php

> NB! I am not proposing to change the default -- to the contrary --
> this patch restores old behavior.

I'm less than convinced this is the right approach ...

If open_dsync is so bad for performance on Linux, maybe it's bad
everywhere?  Should we be rethinking the default preference order?

			regards, tom lane

  3. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Marti Raudsepp <marti@juffo.org> — 2010-11-05T19:15:26Z

    On Fri, Nov 5, 2010 at 20:13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm less than convinced this is the right approach ...
>
> If open_dsync is so bad for performance on Linux, maybe it's bad
> everywhere?  Should we be rethinking the default preference order?

Sure, maybe for PostgreSQL 9.1

But the immediate problem is older releases (8.1 - 9.0) specifically
on Linux. Something as innocuous as re-building your DB on a newer
kernel will radically affect performance -- even when the DB kernel
didn't change.

So I think we should aim to fix old versions first. Do you disagree?

Regards,
Marti

  4. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-11-05T19:20:08Z

    Marti Raudsepp <marti@juffo.org> writes:
> On Fri, Nov 5, 2010 at 20:13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If open_dsync is so bad for performance on Linux, maybe it's bad
>> everywhere? Should we be rethinking the default preference order?

> So I think we should aim to fix old versions first. Do you disagree?

What's that got to do with it?

			regards, tom lane

  5. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Marti Raudsepp <marti@juffo.org> — 2010-11-05T19:52:45Z

    On Fri, Nov 5, 2010 at 21:20, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Marti Raudsepp <marti@juffo.org> writes:
>> On Fri, Nov 5, 2010 at 20:13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> If open_dsync is so bad for performance on Linux, maybe it's bad
>>> everywhere?  Should we be rethinking the default preference order?
>
>> So I think we should aim to fix old versions first. Do you disagree?
>
> What's that got to do with it?

I'm not sure what you're asking.

Surely changing the default wal_sync_method for all OSes in
maintenance releases is out of the question, no?

Regards,
Marti

  6. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-11-05T20:16:40Z

    Marti Raudsepp <marti@juffo.org> writes:
> On Fri, Nov 5, 2010 at 21:20, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> What's that got to do with it?

> I'm not sure what you're asking.

> Surely changing the default wal_sync_method for all OSes in
> maintenance releases is out of the question, no?

Well, if we could leave well enough alone it would be fine with me,
but I think our hand is being forced by the Linux kernel hackers.
I don't really think that "change the default on Linux" is that
much nicer than "change the default everywhere" when it comes to
what we ought to consider back-patching.  In any case, you're getting
ahead of the game: we need to decide on the desired behavior first and
then think about what to patch.  Do the performance results that were
cited show that open_dsync is generally inferior to fdatasync?  If so,
why would we think that that conclusion is Linux-specific?

			regards, tom lane

  7. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Andres Freund <andres@anarazel.de> — 2010-11-05T20:54:57Z

    On Friday 05 November 2010 19:13:47 Tom Lane wrote:
> Marti Raudsepp <marti@juffo.org> writes:
> > PostgreSQL's default settings change when built with Linux kernel
> > headers 2.6.33 or newer. As discussed on the pgsql-performance list,
> > this causes a significant performance regression:
> > http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php
> > 
> > NB! I am not proposing to change the default -- to the contrary --
> > this patch restores old behavior.
> 
> I'm less than convinced this is the right approach ...
> 
> If open_dsync is so bad for performance on Linux, maybe it's bad
> everywhere?  Should we be rethinking the default preference order?
I fail to see how it could be beneficial on *any* non-buggy platform.
Especially with small wal_buffers and larger commits (but also otherwise) it 
increases the amount of synchronous writes the os has to do tremendously.

* It removes about all benefits of XLogBackgroundFlush() 
* It removes any chances of reordering after writing.
* It makes AdvanceXLInsertBuffer synchronous if it has to write outy 

Whats the theory about placing it so high in the preferences list?

Andres

  8. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Marti Raudsepp <marti@juffo.org> — 2010-11-05T21:09:48Z

    On Fri, Nov 5, 2010 at 22:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I don't really think that "change the default on Linux" is that
> much nicer than "change the default everywhere" when it comes to
> what we ought to consider back-patching.  In any case, you're getting
> ahead of the game: we need to decide on the desired behavior first and
> then think about what to patch.

We should be trying to guarantee the stability of maintenance
releases. "Stability" includes consistent defaults. The fact that
Linux now distinguishes between these two flags has a very surprising
effect on PostgreSQL's defaults; an effect that wasn't intended by any
developer, is not documented anywhere, and certainly won't be
anticipated by users.

Do you reject this premise?

As newer distros are adopting 2.6.33+ kernels, more and more people
will shoot themselves in the foot by this change. I am also worried
that it will have a direct effect on PostgreSQL adoption.

Regards,
Marti

  9. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Greg Smith <greg@2ndquadrant.com> — 2010-11-05T21:53:37Z

    Tom Lane wrote:
> If open_dsync is so bad for performance on Linux, maybe it's bad
> everywhere?  Should we be rethinking the default preference order?
>   

And I've seen the expected sync write performance gain over fdatasync on 
a system with a battery-backed cache running VxFS on Linux, because 
working open_[d]sync means O_DIRECT writes bypassing the OS cache, and 
therefore reducing cache pollution from WAL writes.  This doesn't work 
by default on Solaris because they have a special system call you have 
to execute for direct output, but if you trick the OS into doing that 
via mount options you can observe it there too.  The last serious tests 
of this area I saw on that platform were from Jignesh, and they 
certainly didn't show a significant performance regression running in 
sync mode.  I vaguely recall seeing a set once that showed a minor loss 
compared to fdatasync, but it was too close to make any definitive 
statement about reordering.

I haven't seen any report yet of a serious performance regression in the 
new Linux case that was written by someone who understands fully how 
fsync and drive cache flushing are supposed to interact.  It's been 
obvious for a year now that the reports from Phoronix about this had no 
idea what they were actually testing.  I didn't see anything from 
Marti's report that definitively answers whether this is anything other 
than Linux finally doing the right thing to flush drive caches out when 
sync writes happen.  There may be a performance regression here related 
to WAL data going out in smaller chunks than it used to, but in all the 
reports I've seen it that hasn't been isolated well enough to consider 
making any changes yet--to tell if it's a performance loss or a 
reliability gain we're seeing.

I'd like to see some output from the 9.0 test_fsync on one of these 
RHEL6 systems on a system without a battery backed write cache as a 
first step here.  That should start to shed some light on what's 
happening.  I just bumped up the priority on the pending upgrade of my 
spare laptop to the RHEL6 beta I had been trying to find time for, so I 
can investigate this further myself.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD

  10. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-11-05T22:07:10Z

    Andres Freund <andres@anarazel.de> writes:
> On Friday 05 November 2010 19:13:47 Tom Lane wrote:
>> If open_dsync is so bad for performance on Linux, maybe it's bad
>> everywhere?  Should we be rethinking the default preference order?

> I fail to see how it could be beneficial on *any* non-buggy platform.
> Especially with small wal_buffers and larger commits (but also otherwise) it 
> increases the amount of synchronous writes the os has to do tremendously.

> * It removes about all benefits of XLogBackgroundFlush() 
> * It removes any chances of reordering after writing.
> * It makes AdvanceXLInsertBuffer synchronous if it has to write outy 

> Whats the theory about placing it so high in the preferences list?

I think the original idea was that if you had a dedicated WAL drive then
sync-on-write would be reasonable.  But that was a very long time ago
and I'm not sure that the system's behavior is anything like what it was
then; for that matter I'm not sure we had proof that it was an optimal
choice even back then.  That's why I want to revisit the choice of
default and not just go for "minimum" change.

			regards, tom lane

  11. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Andres Freund <andres@anarazel.de> — 2010-11-05T22:08:00Z

    On Friday 05 November 2010 22:53:37 Greg Smith wrote:
> > If open_dsync is so bad for performance on Linux, maybe it's bad
> > everywhere?  Should we be rethinking the default preference order?
> >
> >   
> 
> And I've seen the expected sync write performance gain over fdatasync on 
> a system with a battery-backed cache running VxFS on Linux, because 
> working open_[d]sync means O_DIRECT writes bypassing the OS cache, and 
> therefore reducing cache pollution from WAL writes.
Which looks like a setup where you definitely need to know what you do. I.e. 
don't need support from wal_sync_method by default being open_fdatasync...

Andres

  12. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Josh Berkus <josh@agliodbs.com> — 2010-11-05T22:14:02Z

    > I think the original idea was that if you had a dedicated WAL drive then
> sync-on-write would be reasonable.  But that was a very long time ago
> and I'm not sure that the system's behavior is anything like what it was
> then; for that matter I'm not sure we had proof that it was an optimal
> choice even back then.  That's why I want to revisit the choice of
> default and not just go for "minimum" change.

What plaforms do we need to test to get a reasonable idea? Solaris,
FreeBSD, Windows?

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

  13. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-11-05T22:31:07Z

    Josh Berkus <josh@agliodbs.com> writes:
> What plaforms do we need to test to get a reasonable idea? Solaris,
> FreeBSD, Windows?

At least.  I'm hoping that Greg Smith will take the lead on testing
this, since he seems to have spent the most time in the area so far.

			regards, tom lane

  14. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Josh Berkus <josh@agliodbs.com> — 2010-11-05T22:33:03Z

    On 11/5/10 3:31 PM, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> What plaforms do we need to test to get a reasonable idea? Solaris,
>> FreeBSD, Windows?
> 
> At least.  I'm hoping that Greg Smith will take the lead on testing
> this, since he seems to have spent the most time in the area so far.

I could test at least 1 version of Solaris, I think.

Greg, any recommendations on pgbench parameters?

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

  15. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Greg Smith <greg@2ndquadrant.com> — 2010-11-07T23:44:48Z

    Tom Lane wrote:
> I'm hoping that Greg Smith will take the lead on testing
> this, since he seems to have spent the most time in the area so far.
>   

It's not coincidence that the chapter of my book I convinced the 
publisher to release as a sample is the one that covers this area; this 
mess has been visibly approaching for some time now.  I'm going to put 
RHEL6 onto a system and start collecting some proper slowdown numbers 
this week, then pass along a suggested test regime for others.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD

  16. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Greg Smith <greg@2ndquadrant.com> — 2010-11-15T01:09:27Z

    Marti Raudsepp wrote:
> PostgreSQL's default settings change when built with Linux kernel
> headers 2.6.33 or newer. As discussed on the pgsql-performance list,
> this causes a significant performance regression:
> http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php
>
> NB! I am not proposing to change the default -- to the contrary --
> this patch restores old behavior.

Following our standard community development model, I've put this patch 
onto our CommitFest list:  
https://commitfest.postgresql.org/action/patch_view?id=432 and assigned 
myself as the reviewer.  I didn't look at this until now because I 
already had some patch development and review work to finish before the 
CommitFest deadline we just crossed.  Now I can go back to reviewing 
other people's work.

P.S. There is no pgsql-patch list anymore; everything goes through the 
hackers list now.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services and Support        www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

  17. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Josh Berkus <josh@agliodbs.com> — 2010-12-03T20:25:02Z

    All,

So, this week I've had my hands on a medium-high-end test system where I
could test various wal_sync_methods.  This is a 24-core Intel Xeon
machine with 72GB of ram, and 8 internal 10K SAS disks attached to a
raid controller with 512MB BBU write cache.  2 of the disks are in a
RAID1, which supports both an Ext4 partition and an XFS partition.  The
remaining disks are in a RAID10 which only supports a single pgdata
partition.

This is running on RHEL6, Linux Kernel: 2.6.32-71.el6.x86_64

I think this kind of a system much better represents our users who are
performance-conscious than testing on people's laptops or on VMs does.

I modified test_fsync in two ways to run this; first, to make it support
O_DIRECT, and second to make it run in the *current* directory.  I think
the second change should be permanent; I imagine that a lot of people
who are running test_fsync are not aware that they're actually testing
the performance of /var/tmp, not whatever FS mount they wanted to test.

Here's the results.  I think you'll agree that, at least on Linux, the
benefits of o_sync and o_dsync as defaults would be highly questionable.
 Particularly, it seems that if O_DIRECT support is absent, fdatasync is
across-the-board faster:

=============

test_fsync with directIO, on 2 drives, XFS tuned:

Loops = 10000

Simple write:
        8k write                      198629.457/second

Compare file sync methods using one write:
        open_datasync 8k write        14798.263/second
        open_sync 8k write            14316.864/second
        8k write, fdatasync           12198.871/second
        8k write, fsync               12371.843/second

Compare file sync methods using two writes:
        2 open_datasync 8k writes      7362.805/second
        2 open_sync 8k writes          7156.685/second
        8k write, 8k write, fdatasync 10613.525/second
        8k write, 8k write, fsync     10597.396/second

Compare open_sync with different sizes:
        open_sync 16k write           13631.816/second
        2 open_sync 8k writes          7645.038/second

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
        8k write, fsync, close        11427.096/second
        8k write, close, fsync        11321.220/second


test_fsync with directIO, on 6 drives RAID10, XFS tuned:

Loops = 10000

Simple write:
        8k write                      196494.537/second

Compare file sync methods using one write:
        open_datasync 8k write        14909.974/second
        open_sync 8k write            14559.326/second
        8k write, fdatasync           11046.025/second
        8k write, fsync               11046.916/second

Compare file sync methods using two writes:
        2 open_datasync 8k writes      7349.223/second
        2 open_sync 8k writes          7667.395/second
        8k write, 8k write, fdatasync  9560.495/second
        8k write, 8k write, fsync      9557.287/second

Compare open_sync with different sizes:
        open_sync 16k write           12060.049/second
        2 open_sync 8k writes          7650.746/second

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
        8k write, fsync, close         9377.107/second
        8k write, close, fsync         9251.233/second



test_fsync without directIO on RAID1, Ext4, data=journal:

Loops = 10000

Simple write:
        8k write                      150514.005/second

Compare file sync methods using one write:
        open_datasync 8k write         4012.070/second
        open_sync 8k write             5476.898/second
        8k write, fdatasync            5512.649/second
        8k write, fsync                5803.814/second

Compare file sync methods using two writes:
        2 open_datasync 8k writes      2910.401/second
        2 open_sync 8k writes          2817.377/second
        8k write, 8k write, fdatasync  5041.608/second
        8k write, 8k write, fsync      5155.248/second

Compare open_sync with different sizes:
        open_sync 16k write            4895.956/second
        2 open_sync 8k writes          2720.875/second

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
        8k write, fsync, close         4724.052/second
        8k write, close, fsync         4694.776/second


test_fsync without directIO on RAID1, XFS, tuned:

Loops = 10000

Simple write:
        8k write                      199796.208/second

Compare file sync methods using one write:
        open_datasync 8k write        12553.525/second
        open_sync 8k write            12535.978/second
        8k write, fdatasync           12268.298/second
        8k write, fsync               12305.875/second

Compare file sync methods using two writes:
        2 open_datasync 8k writes      6323.835/second
        2 open_sync 8k writes          6285.169/second
        8k write, 8k write, fdatasync 10893.756/second
        8k write, 8k write, fsync     10752.607/second

Compare open_sync with different sizes:
        open_sync 16k write           11053.510/second
        2 open_sync 8k writes          6293.270/second

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
        8k write, fsync, close        11087.482/second
        8k write, close, fsync        11157.477/second


test_fsync without directIO on RAID10, 6 drives, XFS Tuned:

Loops = 10000

Simple write:
        8k write                      197262.003/second

Compare file sync methods using one write:
        open_datasync 8k write        12784.699/second
        open_sync 8k write            12684.512/second
        8k write, fdatasync           12404.547/second
        8k write, fsync               12452.757/second

Compare file sync methods using two writes:
        2 open_datasync 8k writes      6376.587/second
        2 open_sync 8k writes          6364.113/second
        8k write, 8k write, fdatasync  9895.699/second
        8k write, 8k write, fsync      9866.886/second

Compare open_sync with different sizes:
        open_sync 16k write           10156.491/second
        2 open_sync 8k writes          6400.889/second

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
        8k write, fsync, close        11142.620/second
        8k write, close, fsync        11076.393/second



-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

  18. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Josh Berkus <josh@agliodbs.com> — 2010-12-05T03:12:57Z

    All,

While I have this machine available I've been trying to run some 
performance tests using pgbench and various wal_sync_methods.  However, 
I seem to be maxing out at the speed of pgbench itself; no matter which 
wal_sync_method I use (including "fsync"), it tops out at around 2750 TPS.

Of course, it's also possible that the wal_sync_method does not in fact 
make a difference in throughput.

-- 
                                   -- Josh Berkus
                                      PostgreSQL Experts Inc.
                                      http://www.pgexperts.com

  19. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Greg Smith <greg@2ndquadrant.com> — 2010-12-05T22:12:18Z

    Josh Berkus wrote:
> I modified test_fsync in two ways to run this; first, to make it support
> O_DIRECT, and second to make it run in the *current* directory.

Patch please?  I agree with the latter change; what test_fsync does is 
surprising.

I suggested a while ago that we refactor test_fsync to use a common set 
of source code as the database itself for detecting things related to 
wal_sync_method, perhaps just extract that whole set of DEFINE macro 
logic to somewhere else.  That happened at a bad time in the development 
cycle (right before a freeze) and nobody ever got back to the idea 
afterwards.  If this code is getting touched, and it's clear it is in 
some direction, I'd like to see things change so it's not possible for 
the two to diverge again afterwards.

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

  20. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Josh Berkus <josh@agliodbs.com> — 2010-12-07T00:41:06Z

    On 12/5/10 2:12 PM, Greg Smith wrote:
> Josh Berkus wrote:
>> I modified test_fsync in two ways to run this; first, to make it support
>> O_DIRECT, and second to make it run in the *current* directory.
> 
> Patch please?  I agree with the latter change; what test_fsync does is
> surprising.

Attached.

Making it support O_DIRECT would be possible but more complex; I don't
see the point unless we think we're going to have open_sync_with_odirect
as a seperate option.

> I suggested a while ago that we refactor test_fsync to use a common set
> of source code as the database itself for detecting things related to
> wal_sync_method, perhaps just extract that whole set of DEFINE macro
> logic to somewhere else.  That happened at a bad time in the development
> cycle (right before a freeze) and nobody ever got back to the idea
> afterwards.  If this code is getting touched, and it's clear it is in
> some direction, I'd like to see things change so it's not possible for
> the two to diverge again afterwards.

I don't quite follow you.  Maybe nobody else did last time, either.

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

  21. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-12-07T01:38:14Z

    Josh Berkus <josh@agliodbs.com> writes:
> Making it support O_DIRECT would be possible but more complex; I don't
> see the point unless we think we're going to have open_sync_with_odirect
> as a seperate option.

Whether it's complex or not isn't really the issue.  The issue is that
what test_fsync is testing had better match what the backend does, or
people will be making choices based on not-comparable test results.
I think we should have test_fsync just automatically fold in O_DIRECT
the same way the backend does.

			regards, tom lane

  22. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Andrew Dunstan <andrew@dunslane.net> — 2010-12-07T01:59:53Z

    
On 12/06/2010 08:38 PM, Tom Lane wrote:
> Josh Berkus<josh@agliodbs.com>  writes:
>> Making it support O_DIRECT would be possible but more complex; I don't
>> see the point unless we think we're going to have open_sync_with_odirect
>> as a seperate option.
> Whether it's complex or not isn't really the issue.  The issue is that
> what test_fsync is testing had better match what the backend does, or
> people will be making choices based on not-comparable test results.
> I think we should have test_fsync just automatically fold in O_DIRECT
> the same way the backend does.
>
> 			

Indeed. We were quite confused for a while when we were dealing with 
this about a week ago, and my handwritten test program failed as 
expected but test_fsync didn't. Anything other than behaving just as the 
backend does violates POLA, in my view.

cheers

andrew

  23. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Josh Berkus <josh@agliodbs.com> — 2010-12-07T02:05:12Z

    > Whether it's complex or not isn't really the issue.  The issue is that
> what test_fsync is testing had better match what the backend does, or
> people will be making choices based on not-comparable test results.
> I think we should have test_fsync just automatically fold in O_DIRECT
> the same way the backend does.

OK, patch coming then.  Right now test_fsync aborts when O_DIRECT fails.
 What should I have it do instead?

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

  24. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-12-07T02:13:29Z

    Josh Berkus <josh@agliodbs.com> writes:
> OK, patch coming then.  Right now test_fsync aborts when O_DIRECT fails.
>  What should I have it do instead?

Report that it fails, and keep testing the other methods.

			regards, tom lane

  25. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Josh Berkus <josh@agliodbs.com> — 2010-12-09T20:14:25Z

    On 12/6/10 6:13 PM, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> OK, patch coming then.  Right now test_fsync aborts when O_DIRECT fails.
>>  What should I have it do instead?
> 
> Report that it fails, and keep testing the other methods.

Patch attached.  Includes a fair amount of comment cleanup, since
existing comments did not meet our current project standards.  Tests all
6 of the methods we support separately.

Some questions, though:

(1) Why are we doing the open_sync different-size write test?  AFAIK,
this doesn't match any behavior which PostgreSQL has.

(2) In this patch, I'm stepping down the number of loops which
fsync_writethrough does by 90%.  The reason for that was that on the
platforms where I tested writethrough (desktop machines), doing 10,000
loops took 15-20 *minutes*, which seems hard on the user.  Would be easy
to revert if you think it's a bad idea.
	Possibly auto-sizing the number of loops based on the first fsync test
might be a good idea, but seems like going a bit too far.

(3) Should the multi-descriptor test be using writethrough on platforms
which support it?

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

  26. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Bruce Momjian <bruce@momjian.us> — 2010-12-23T01:38:01Z

    Josh Berkus wrote:
> On 12/6/10 6:13 PM, Tom Lane wrote:
> > Josh Berkus <josh@agliodbs.com> writes:
> >> OK, patch coming then.  Right now test_fsync aborts when O_DIRECT fails.
> >>  What should I have it do instead?
> > 
> > Report that it fails, and keep testing the other methods.
> 
> Patch attached.  Includes a fair amount of comment cleanup, since
> existing comments did not meet our current project standards.  Tests all
> 6 of the methods we support separately.
> 
> Some questions, though:
> 
> (1) Why are we doing the open_sync different-size write test?  AFAIK,
> this doesn't match any behavior which PostgreSQL has.

I did that so we could see the impact of doing 2 8k writes that were
both fsync'ed vs doing one 16k write and then fsync:

	Compare open_sync with different sizes:
        open_sync 16k write             201.323/second
        2 open_sync 8k writes           332.466/second

We often write multiple 8k WAL pages and then fsync on commit.

> (2) In this patch, I'm stepping down the number of loops which
> fsync_writethrough does by 90%.  The reason for that was that on the
> platforms where I tested writethrough (desktop machines), doing 10,000
> loops took 15-20 *minutes*, which seems hard on the user.  Would be easy
> to revert if you think it's a bad idea.
> 	Possibly auto-sizing the number of loops based on the first fsync test
> might be a good idea, but seems like going a bit too far.

Sure, I recently increased the number, probably too much.

> (3) Should the multi-descriptor test be using writethrough on platforms
> which support it?

Uh, I didn't think that would matter because the test is to test kernel
behavior of writing to one file descriptor and fsyncing using another.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  27. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Bruce Momjian <bruce@momjian.us> — 2010-12-23T01:49:17Z

    Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> > Making it support O_DIRECT would be possible but more complex; I don't
> > see the point unless we think we're going to have open_sync_with_odirect
> > as a seperate option.
> 
> Whether it's complex or not isn't really the issue.  The issue is that
> what test_fsync is testing had better match what the backend does, or
> people will be making choices based on not-comparable test results.
> I think we should have test_fsync just automatically fold in O_DIRECT
> the same way the backend does.

The problem is that O_DIRECT was not implemented in macros but rather
down in the code:

    if (!XLogIsNeeded() && !am_walreceiver)
        o_direct_flag = PG_O_DIRECT;

Which means if we just export the macros, we would still not have caught
this.  I would like to share all the defines --- I am just saying it
isn't trivial.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  28. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Josh Berkus <josh@agliodbs.com> — 2010-12-23T22:45:44Z

    > Which means if we just export the macros, we would still not have caught
> this.  I would like to share all the defines --- I am just saying it
> isn't trivial.

I just called all the define variables manually rather than relying on
the macros.  Seemed to work fine.

-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

  29. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Josh Berkus <josh@agliodbs.com> — 2010-12-23T23:29:24Z

    Greg, All:

Results for Solaris 10u8, on ZFS on a 7-drive attached storage array:

bash-3.00# ./test_fsync -f /dbdata/pgdata/test.out
Loops = 10000

Simple write:
        8k write                      59988.002/second

Compare file sync methods using one write:
        open_datasync 8k write          214.125/second
        (unavailable: o_direct)
        open_sync 8k write              222.155/second
        (unavailable: o_direct)
        8k write, fdatasync             214.086/second
        8k write, fsync                 215.035/second
        (unavailable: fsync_writethrough)

Compare file sync methods using two writes:
        2 open_datasync 8k writes       108.227/second
        (unavailable: o_direct)
        2 open_sync 8k writes           106.935/second
        (unavailable: o_direct)
        8k write, 8k write, fdatasync   205.525/second
        8k write, 8k write, fsync       210.483/second
        (unavailable: fsync_writethrough)

Compare open_sync with different sizes:
        open_sync 16k write             211.481/second
        2 open_sync 8k writes           106.202/second

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
        8k write, fsync, close          207.499/second
        8k write, close, fsync          213.656/second




-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

  30. Re: [PATCH] Revert default wal_sync_method to fdatasync on Linux 2.6.33+

    Bruce Momjian <bruce@momjian.us> — 2011-01-15T16:58:46Z

    Josh Berkus wrote:
> On 12/6/10 6:13 PM, Tom Lane wrote:
> > Josh Berkus <josh@agliodbs.com> writes:
> >> OK, patch coming then.  Right now test_fsync aborts when O_DIRECT fails.
> >>  What should I have it do instead?
> > 
> > Report that it fails, and keep testing the other methods.
> 
> Patch attached.  Includes a fair amount of comment cleanup, since
> existing comments did not meet our current project standards.  Tests all
> 6 of the methods we support separately.
> 
> Some questions, though:
> 
> (1) Why are we doing the open_sync different-size write test?  AFAIK,
> this doesn't match any behavior which PostgreSQL has.

I added program output to explain this.

> (2) In this patch, I'm stepping down the number of loops which
> fsync_writethrough does by 90%.  The reason for that was that on the
> platforms where I tested writethrough (desktop machines), doing 10,000
> loops took 15-20 *minutes*, which seems hard on the user.  Would be easy
> to revert if you think it's a bad idea.
> 	Possibly auto-sizing the number of loops based on the first fsync test
> might be a good idea, but seems like going a bit too far.

I did not know why writethough we always be much slower than other sync
methods so I just reduced the loop count to 2k.

> (3) Should the multi-descriptor test be using writethrough on platforms
> which support it?

Thank you for your patch.  I have applied most of it, attached.  

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +