Based on the tests we did last week, it seems clear than on many
platforms it's a win to sync the WAL log by writing it with open()
option O_SYNC (or O_DSYNC where available) rather than issuing explicit
fsync() (resp. fdatasync()) calls.  In theory fsync ought to be faster,
but it seems that too many kernels have inefficient implementations of
fsync.

I think we need to make both O_SYNC and fsync() choices available in
7.1.  Two important questions need to be settled:

1. Is a compile-time flag (in config.h.in) good enough, or do we need
to make it configurable via a GUC variable?  (A variable would have to
be postmaster-start-time changeable only, so you'd still need a
postmaster restart to change it.)

2. Which way should be the default?

There's also the lesser question of what to call the config symbol
or variable.

My inclination is to go with a compile-time flag named USE_FSYNC_FOR_WAL
and have the default be off (ie, use O_SYNC by default) but I'm not
strongly set on that.  Opinions anyone?

In any case the code should automatically prefer O_DSYNC over O_SYNC if
available, and should prefer fdatasync() over fsync() if available;
I doubt we need to provide a knob to alter those choices.

BTW, are there any platforms where O_DSYNC exists but has a different
spelling?

			regards, tom lane

Thu, 15 Mar 2001 12:29:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Allowing WAL fsync to be done via O_SYNC
* Tom Lane <tgl@sss.pgh.pa.us> [010315 09:35] wrote:
> 
> BTW, are there any platforms where O_DSYNC exists but has a different
> spelling?

Yes, FreeBSD only has: O_FSYNC
it doesn't have O_SYNC nor O_DSYNC.

-- 
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]


Thu, 15 Mar 2001 09:44:15 -0800
Alfred Perlstein <bright@wintelcom.net>
Re: Allowing WAL fsync to be done via O_SYNC
Alfred Perlstein <bright@wintelcom.net> writes:
> * Tom Lane <tgl@sss.pgh.pa.us> [010315 09:35] wrote:
>> BTW, are there any platforms where O_DSYNC exists but has a different
>> spelling?

> Yes, FreeBSD only has: O_FSYNC
> it doesn't have O_SYNC nor O_DSYNC.

Okay ... we can fall back to O_FSYNC if we don't see either of the
others.  No problem.  Any other weird cases out there?  I think Andreas
might've muttered something about AIX but I'm not sure now.

			regards, tom lane

Thu, 15 Mar 2001 12:48:14 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
Peter Eisentraut <peter_e@gmx.net> writes:
> As a general rule, if something can be a run time option, as opposed to a
> compile time option, then it should be.  At the very least you keep the
> installation simple and allow for easier experimenting.

I've been mentally working through the code, and see only one reason why
it might be necessary to go with a compile-time choice: suppose we see
that none of O_DSYNC, O_SYNC, O_FSYNC, [others] are defined?  With the
compile-time choice it's easy: #define USE_FSYNC_FOR_WAL, and sail on.
If it's a GUC variable then we need a way to prevent the GUC option from
becoming unset (which would disable the fsync() calls, leaving nothing
to replace 'em).  Doable, perhaps, but seems kind of ugly ... any
thoughts about that?

			regards, tom lane

Thu, 15 Mar 2001 13:15:18 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
Tom Lane writes:

> I think we need to make both O_SYNC and fsync() choices available in
> 7.1.  Two important questions need to be settled:
>
> 1. Is a compile-time flag (in config.h.in) good enough, or do we need
> to make it configurable via a GUC variable?  (A variable would have to
> be postmaster-start-time changeable only, so you'd still need a
> postmaster restart to change it.)

As a general rule, if something can be a run time option, as opposed to a
compile time option, then it should be.  At the very least you keep the
installation simple and allow for easier experimenting.

> There's also the lesser question of what to call the config symbol
> or variable.

I suggest "wal_use_fsync" as a GUC variable, assuming the default would be
off.  Otherwise "wal_use_open_sync".  (Use a general-to-specific naming
scheme to allow for easier grouping.  Having defaults be "off"
consistently is more intuitive.)

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Thu, 15 Mar 2001 19:17:20 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Allowing WAL fsync to be done via O_SYNC
> Based on the tests we did last week, it seems clear than on many
> platforms it's a win to sync the WAL log by writing it with open()
> option O_SYNC (or O_DSYNC where available) rather than issuing explicit
> fsync() (resp. fdatasync()) calls.  In theory fsync ought to be faster,
> but it seems that too many kernels have inefficient implementations of
> fsync.

Can someone explain why configure/platform-specific flags are allowed to
be added at this stage in the release, but my pgmonitor patch was
rejected?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 15 Mar 2001 15:20:09 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC
> Peter Eisentraut <peter_e@gmx.net> writes:
> > As a general rule, if something can be a run time option, as opposed to a
> > compile time option, then it should be.  At the very least you keep the
> > installation simple and allow for easier experimenting.
> 
> I've been mentally working through the code, and see only one reason why
> it might be necessary to go with a compile-time choice: suppose we see
> that none of O_DSYNC, O_SYNC, O_FSYNC, [others] are defined?  With the
> compile-time choice it's easy: #define USE_FSYNC_FOR_WAL, and sail on.
> If it's a GUC variable then we need a way to prevent the GUC option from
> becoming unset (which would disable the fsync() calls, leaving nothing
> to replace 'em).  Doable, perhaps, but seems kind of ugly ... any
> thoughts about that?

I don't think having something a run-time option is always a good idea. 
Giving people too many choices is often confusing.  

I think we should just check at compile time, and choose O_* if we have
it, and if not, use fsync().  No one will ever do the proper timing
tests to know which is better except us.  Also, it seems O_* should be
faster because you are fsync'ing the buffer you just wrote, so there is
no looking around for dirty buffers like fsync().

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 15 Mar 2001 15:26:59 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can someone explain why configure/platform-specific flags are allowed to
> be added at this stage in the release, but my pgmonitor patch was
> rejected?

Possibly just because Marc hasn't stomped on me quite yet ;-)

However, I can actually make a case for this: we are flushing out
performance bugs in a new feature, ie WAL.

			regards, tom lane

Thu, 15 Mar 2001 15:29:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Can someone explain why configure/platform-specific flags are allowed to
> > be added at this stage in the release, but my pgmonitor patch was
> > rejected?
> 
> Possibly just because Marc hasn't stomped on me quite yet ;-)
> 
> However, I can actually make a case for this: we are flushing out
> performance bugs in a new feature, ie WAL.


You did a masterful job of making my pgmonitor patch sound like a debug
aid instead of a feature too.  :-)

Have you considered a career in law.  :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 15 Mar 2001 15:32:08 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC
> > I've been mentally working through the code, and see only one reason why
> > it might be necessary to go with a compile-time choice: suppose we see
> > that none of O_DSYNC, O_SYNC, O_FSYNC, [others] are defined?  With the
> > compile-time choice it's easy: #define USE_FSYNC_FOR_WAL, and sail on.
> > If it's a GUC variable then we need a way to prevent the GUC option from
> > becoming unset (which would disable the fsync() calls, leaving nothing
> > to replace 'em).  Doable, perhaps, but seems kind of ugly ... any
> > thoughts about that?
> 
> I don't think having something a run-time option is always a good idea. 
> Giving people too many choices is often confusing.  
> 
> I think we should just check at compile time, and choose O_* if we have
> it, and if not, use fsync().  No one will ever do the proper timing
> tests to know which is better except us.  Also, it seems O_* should be
> faster because you are fsync'ing the buffer you just wrote, so there is
> no looking around for dirty buffers like fsync().

I later read Vadim's comment that fsync() of two blocks may be faster
than two O_* writes, so I am now confused about the proper solution. 
However, I think we need to pick one and make it invisible to the user. 
Perhaps a compiler/config.h flag for testing would be a good solution.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 15 Mar 2001 15:36:36 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I later read Vadim's comment that fsync() of two blocks may be faster
> than two O_* writes, so I am now confused about the proper solution. 
> However, I think we need to pick one and make it invisible to the user. 
> Perhaps a compiler/config.h flag for testing would be a good solution.

I believe that we don't know enough yet to nail down a hard-wired
decision.  Vadim's idea of preferring O_DSYNC if it appears to be
different from O_SYNC is a good first cut, but I think we'd better make
it possible to override that, at least for testing purposes.

So I think it should be configurable at *some* level.  I don't much care
whether it's a config.h entry or a GUC variable.

But consider this: we'll be more likely to get some feedback from the
field (allowing us to refine the policy in future releases) if it is a
GUC variable.  Not many people will build two versions of the software,
but people might take the trouble to play with a run-time configuration
setting.

			regards, tom lane

Thu, 15 Mar 2001 15:44:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I later read Vadim's comment that fsync() of two blocks may be faster
> > than two O_* writes, so I am now confused about the proper solution. 
> > However, I think we need to pick one and make it invisible to the user. 
> > Perhaps a compiler/config.h flag for testing would be a good solution.
> 
> I believe that we don't know enough yet to nail down a hard-wired
> decision.  Vadim's idea of preferring O_DSYNC if it appears to be
> different from O_SYNC is a good first cut, but I think we'd better make
> it possible to override that, at least for testing purposes.
> 
> So I think it should be configurable at *some* level.  I don't much care
> whether it's a config.h entry or a GUC variable.
> 
> But consider this: we'll be more likely to get some feedback from the
> field (allowing us to refine the policy in future releases) if it is a
> GUC variable.  Not many people will build two versions of the software,
> but people might take the trouble to play with a run-time configuration
> setting.

Yes, I can imagine.  Can we remove it once we know the answer?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 15 Mar 2001 15:46:20 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC
I'd actually vote for it to remain for a release or two or more, as 
we get more experience with stuff, the defaults may be different for 
different workloads. 

LER
-- 
Larry Rosenman                                                            
         http://www.lerctr.org/~ler/
Phone: +1 972 414 9812                                                    
         E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 US

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 3/15/01, 2:46:20 PM, Bruce Momjian <pgman@candle.pha.pa.us> wrote 
regarding Re: [HACKERS] Allowing WAL fsync to be done via O_SYNC:


> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > I later read Vadim's comment that fsync() of two blocks may be faster
> > > than two O_* writes, so I am now confused about the proper solution.
> > > However, I think we need to pick one and make it invisible to the user.
> > > Perhaps a compiler/config.h flag for testing would be a good solution.
> >
> > I believe that we don't know enough yet to nail down a hard-wired
> > decision.  Vadim's idea of preferring O_DSYNC if it appears to be
> > different from O_SYNC is a good first cut, but I think we'd better make
> > it possible to override that, at least for testing purposes.
> >
> > So I think it should be configurable at *some* level.  I don't much care
> > whether it's a config.h entry or a GUC variable.
> >
> > But consider this: we'll be more likely to get some feedback from the
> > field (allowing us to refine the policy in future releases) if it is a
> > GUC variable.  Not many people will build two versions of the software,
> > but people might take the trouble to play with a run-time configuration
> > setting.

> Yes, I can imagine.  Can we remove it once we know the answer?

> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?

> http://www.postgresql.org/users-lounge/docs/faq.html

Thu, 15 Mar 2001 21:12:36 GMT
Larry Rosenman <ler@lerctr.org>
Re: Allowing WAL fsync to be done via O_SYNC
Peter Eisentraut <peter_e@gmx.net> writes:
> I haven't followed the jungle of numbers too closely.
> Is it not the case that WAL + fsync is still faster than 7.0 + fsync and
> WAL/no fsync is still faster than 7.0/no fsync?

I believe the first is true in most cases.  I wouldn't swear to the
second though, since WAL requires more I/O and doesn't save any fsyncs
if you've got 'em all turned off anyway ...

			regards, tom lane

Thu, 15 Mar 2001 16:32:09 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
Tom Lane writes:

> I've been mentally working through the code, and see only one reason why
> it might be necessary to go with a compile-time choice: suppose we see
> that none of O_DSYNC, O_SYNC, O_FSYNC, [others] are defined?

We postulate that one of those has to exist.  Alternatively, you make the
option read

wal_sync_method = fsync | open_sync

In the "parse_hook" for the parameter you if #ifdef out 'open_sync' as a
valid option if none of those exist, so a user will get "'open_sync' is
not a valid option value".

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Thu, 15 Mar 2001 22:37:57 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Allowing WAL fsync to be done via O_SYNC 
Tom Lane writes:

> However, I can actually make a case for this: we are flushing out
> performance bugs in a new feature, ie WAL.

I haven't followed the jungle of numbers too closely.

Is it not the case that WAL + fsync is still faster than 7.0 + fsync and
WAL/no fsync is still faster than 7.0/no fsync?

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Thu, 15 Mar 2001 22:40:10 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Allowing WAL fsync to be done via O_SYNC 
Peter Eisentraut <peter_e@gmx.net> writes:
> We postulate that one of those has to exist.  Alternatively, you make the
> option read
> wal_sync_method = fsync | open_sync
> In the "parse_hook" for the parameter you if #ifdef out 'open_sync' as a
> valid option if none of those exist, so a user will get "'open_sync' is
> not a valid option value".

I like this a lot.  In fact, I am mightily tempted to make it

wal_sync_method = fsync | fdatasync | open_sync | open_datasync

where fdatasync would only be valid if configure found fdatasync() and
open_datasync would only be valid if we found O_DSYNC exists and isn't
O_SYNC.  This would let people try all the available methods under
realistic test conditions, for hardly any extra work.

Furthermore, the documentation could say something like "The default is
the first available method in the order open_datasync, fdatasync, fsync,
open_sync" (assuming that Vadim's preferences are right).

A small problem is that I don't want to be doing multiple strcasecmp's
to figure out what to do in xlog.c.  Do you object if I add an
"assign_hook" to guc.c that's called when an actual assignment is made?
That would provide a place to set up the flag variables that xlog.c
would actually look at.  Furthermore, having an assign_hook would let us
support changing this value at SIGHUP, not only at postmaster start.
(The assign hook would just need to fsync whatever WAL file is currently
open and possibly close/reopen the file, to ensure that no blocks miss
getting synced when we change conventions.)

Creeping featurism strikes again ;-) ... but this feels right ...

			regards, tom lane

Thu, 15 Mar 2001 17:11:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
Peter Eisentraut <peter_e@gmx.net> writes:
> switch(lower(string[0]) + lower(string[5]))
> {
> 	case 'f':	/* fsync */
> 	case 'f' + 's':	/* fdatasync */
> 	case 'o' + 's':	/* open_sync */
> 	case 'o' + 'd':	/* open_datasync */
> }

> Although ugly, it should serve as a readable solution for now.

Ugly is the word ...

>> Do you object if I add an "assign_hook" to guc.c that's called when an
>> actual assignment is made?

> Something like this is on my wish list, but I'm not sure if it's wise to
> start this now.

I'm not particularly concerned about changing the interface later if
that proves necessary.  We're not likely to have so many of the things
that an API change is burdensome, and they will all be strictly backend
internal.

What I have in mind for now is just

	void (*assign_hook) (const char *newval);

(obviously this is for string variables only, for now) called just
before actually changing the variable value.  This lets the hook see
the old value if it needs to.

			regards, tom lane

Thu, 15 Mar 2001 18:11:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
Tom Lane writes:

> wal_sync_method = fsync | fdatasync | open_sync | open_datasync

> A small problem is that I don't want to be doing multiple strcasecmp's
> to figure out what to do in xlog.c.

This should be efficient:

switch(lower(string[0]) + lower(string[5]))
{
	case 'f':	/* fsync */
	case 'f' + 's':	/* fdatasync */
	case 'o' + 's':	/* open_sync */
	case 'o' + 'd':	/* open_datasync */
}

Although ugly, it should serve as a readable solution for now.

> Do you object if I add an "assign_hook" to guc.c that's called when an
> actual assignment is made?

Something like this is on my wish list, but I'm not sure if it's wise to
start this now.  There are a few issues that need some thought, like how
to make the interface for non-string options, and how to keep it in sync
with the parse hook of string options, ...

> That would provide a place to set up the flag variables that xlog.c
> would actually look at.  Furthermore, having an assign_hook would let
> us support changing this value at SIGHUP, not only at postmaster
> start. (The assign hook would just need to fsync whatever WAL file is
> currently open and possibly close/reopen the file, to ensure that no
> blocks miss getting synced when we change conventions.)

... and possibly here you need to pass the context to the assign hook as
well.  This application strikes me as a bit too esoteric for a first try.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Fri, 16 Mar 2001 00:13:00 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Allowing WAL fsync to be done via O_SYNC 
Bruce Momjian wrote:
> 
<snip>
> No one will ever do the proper timing tests to know which is better except us.

Hi Bruce,

I believe in the future that anyone doing serious benchmark tests before
large-scale implementation will indeed be testing things like this. 
There will also be people/companies out there who will specialise in
"tuning" PostgreSQL systems and they will definitely test stuff like
this... different variations, different database structures, different
OS's, etc.

Regards and best wishes,

Justin Clift

Fri, 16 Mar 2001 11:50:25 +1100
Justin Clift <aa2@bigpond.net.au>
Re: Allowing WAL fsync to be done via O_SYNC
> Bruce Momjian wrote:
> > 
> <snip>
> > No one will ever do the proper timing tests to know which is better except us.
> 
> Hi Bruce,
> 
> I believe in the future that anyone doing serious benchmark tests before
> large-scale implementation will indeed be testing things like this. 
> There will also be people/companies out there who will specialize in
> "tuning" PostgreSQL systems and they will definitely test stuff like
> this... different variations, different database structures, different
> OS's, etc.

But I don't want to go the Informix/Oracle way where we have so many
tuning options that no one understands them all.  I would like us to
find the best options and only give users choices when there is a real
tradeoff.

For example, Tom had a nice fsync test program.  Why can't we run that
on various platforms and collect the results, then make a decision on
the best default.

Trying to test the affects of fsync() with a database wrapped around it
really makes for difficult measurement anyway.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 15 Mar 2001 19:56:34 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC
Is someone able to put together a testing-type script or sequence so
people can run this on the various platforms and then report the
results?

For example, I can setup benchmarking, (or automated testing) on various
Solaris platforms to run overnight and report the results in the
morning.  I suspect that quite a few people can do similar.

Would this be a good thing for someone to spend some time and effort on,
in generating testing-type scripts/structures?  It might be a useful
tool to use in the future when making performance/related decisions like
this.

Regards and best wishes,

Justin Clift

Tom Lane wrote:
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I later read Vadim's comment that fsync() of two blocks may be faster
> > than two O_* writes, so I am now confused about the proper solution.
> > However, I think we need to pick one and make it invisible to the user.
> > Perhaps a compiler/config.h flag for testing would be a good solution.
> 
> I believe that we don't know enough yet to nail down a hard-wired
> decision.  Vadim's idea of preferring O_DSYNC if it appears to be
> different from O_SYNC is a good first cut, but I think we'd better make
> it possible to override that, at least for testing purposes.
> 
> So I think it should be configurable at *some* level.  I don't much care
> whether it's a config.h entry or a GUC variable.
> 
> But consider this: we'll be more likely to get some feedback from the
> field (allowing us to refine the policy in future releases) if it is a
> GUC variable.  Not many people will build two versions of the software,
> but people might take the trouble to play with a run-time configuration
> setting.
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Fri, 16 Mar 2001 12:02:39 +1100
Justin Clift <aa2@bigpond.net.au>
Testing structure (was) Re: Allowing WAL fsync to be done via O_SYNC
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> For example, Tom had a nice fsync test program.  Why can't we run that
> on various platforms and collect the results, then make a decision on
> the best default.

Mainly because (a) there's not enough time before release, and (b) that
test program was far too stupid to give trustworthy results anyway.
(It was assuming exactly one commit per XLOG block, for example.)

> Trying to test the affects of fsync() with a database wrapped around it
> really makes for difficult measurement anyway.

Exactly.  What I'm doing now is providing some infrastructure with which
we can hope to see some realistic tests.  For example, I'm gonna be
leaning on Great Bridge's lab guys to rerun their TPC tests with a bunch
of combinations, just as soon as the dust settles.  But I'm not planning
to put my faith in only that one benchmark.

I'm all for improving the intelligence of the defaults once we know
enough to pick better defaults.  But we don't yet, and there's no way
that we *will* know enough until after we've shipped a release that has
these tuning knobs and gotten some real-world results from the field.

			regards, tom lane

Thu, 15 Mar 2001 20:04:11 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
I was wondering if the multiple writes performed to the XLOG could be
grouped into one write().  Seems everyone agrees:
	
	fdatasync/O_DSYNC is better then plain fsync/O_SYNC

and the O_* flags are better than fsync() if we are doing only one write
before every fsync.  It seems the only open question is now often we do
multiple writes before fsync, and if that is ever faster than putting
the O_* on the file for all writes.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 15 Mar 2001 21:57:17 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I was wondering if the multiple writes performed to the XLOG could be
> grouped into one write().

That would require fairly major restructuring of xlog.c, which I don't
want to undertake at this point in the cycle (we're trying to push out
a release candidate, remember?).  I'm not convinced it would be a huge
win anyway.  It would be a win if your average transaction writes
multiple blocks' worth of XLOG ... but if your average transaction
writes less than a block then it won't help.

I think it probably is a good idea to restructure xlog.c so that it can
write more than one page at a time --- but it's not such a great idea
that I want to hold up the release any more for it.

			regards, tom lane

Thu, 15 Mar 2001 22:41:12 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I was wondering if the multiple writes performed to the XLOG could be
> > grouped into one write().
> 
> That would require fairly major restructuring of xlog.c, which I don't
> want to undertake at this point in the cycle (we're trying to push out
> a release candidate, remember?).  I'm not convinced it would be a huge
> win anyway.  It would be a win if your average transaction writes
> multiple blocks' worth of XLOG ... but if your average transaction
> writes less than a block then it won't help.
> 
> I think it probably is a good idea to restructure xlog.c so that it can
> write more than one page at a time --- but it's not such a great idea
> that I want to hold up the release any more for it.

OK, but the point of adding all those configuration options was to allow
us to figure out which was faster.  If you can do the code so we no
longer need to know the answer of which is best, why bother adding the
config options.  Just ship our best guess and fix it when we can.  Does
that make sense?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 15 Mar 2001 22:57:57 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, but the point of adding all those configuration options was to allow
> us to figure out which was faster.  If you can do the code so we no
> longer need to know the answer of which is best, why bother adding the
> config options.

How in the world did you arrive at that idea?  I don't see anyone around
here but you claiming that we don't need any experimentation ...

			regards, tom lane

Thu, 15 Mar 2001 23:01:24 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > OK, but the point of adding all those configuration options was to allow
> > us to figure out which was faster.  If you can do the code so we no
> > longer need to know the answer of which is best, why bother adding the
> > config options.
> 
> How in the world did you arrive at that idea?  I don't see anyone around
> here but you claiming that we don't need any experimentation ...

I am trying to understand what testing we need to do.   I know we need
configure tests to check to see what exists in the OS.

My question was what are we needing to test?  If we can do only single writes
to the log, don't we prefer O_* to fsync, and the O_D* options over
plain O_*?  Am I confused?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 15 Mar 2001 23:06:16 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> My question was what are we needing to test?  If we can do only single writes
> to the log, don't we prefer O_* to fsync, and the O_D* options over
> plain O_*?  Am I confused?

I don't think we have enough data to conclude that with any certainty.

			regards, tom lane

Fri, 16 Mar 2001 00:23:31 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > My question was what are we needing to test?  If we can do only single writes
> > to the log, don't we prefer O_* to fsync, and the O_D* options over
> > plain O_*?  Am I confused?
> 
> I don't think we have enough data to conclude that with any certainty.

I just figured we knew the answers to above issues, that that the only
issue was multiple writes vs. fsync().

It is hard for me to imagine O_* being slower than fsync(), or fdatasync
being slower than fsync.  Are we not able to assume that?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 16 Mar 2001 00:26:36 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> It is hard for me to imagine O_* being slower than fsync(),

Not hard at all --- if we're writing multiple xlog blocks per
transaction, then O_* constrains the sequence of operations more
than we really want.  Changing xlog.c to combine writes as much
as possible would reduce this problem, but not eliminate it.

Besides, the entire object of this exercise is to work around
an unexpected inefficiency in some kernels' implementations of
fsync/fdatasync (viz, scanning over lots of not-dirty buffers).
Who's to say that there might not be inefficiencies in other
platforms' implementations of the O_* options?

			regards, tom lane

Fri, 16 Mar 2001 00:54:54 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
Added to TODO:

	* Determine optimal fdatasync/fsync, O_SYNC/O_DSYNC options
	        * Allow multiple blocks to be written to WAL with one write()  


> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > It is hard for me to imagine O_* being slower than fsync(),
> 
> Not hard at all --- if we're writing multiple xlog blocks per
> transaction, then O_* constrains the sequence of operations more
> than we really want.  Changing xlog.c to combine writes as much
> as possible would reduce this problem, but not eliminate it.
> 
> Besides, the entire object of this exercise is to work around
> an unexpected inefficiency in some kernels' implementations of
> fsync/fdatasync (viz, scanning over lots of not-dirty buffers).
> Who's to say that there might not be inefficiencies in other
> platforms' implementations of the O_* options?
> 
> 			regards, tom lane
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 20 Mar 2001 15:32:44 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC