> 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.

I don't remember big difference in using fsync or O_SYNC in tfsync
tests. Both depend on block size and keeping in mind that fsync
allows us syncing after writing *multiple* blocks I would either
use fsync as default or don't deal with O_SYNC at all.
But if O_DSYNC is defined and O_DSYNC != O_SYNC then we should
use O_DSYNC by default.
(BTW, we didn't compare fdatasync and O_SYNC yet).

Vadim

Thu, 15 Mar 2001 10:53:36 -0800
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
RE: Allowing WAL fsync to be done via O_SYNC
"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> ... I would either
> use fsync as default or don't deal with O_SYNC at all.
> But if O_DSYNC is defined and O_DSYNC != O_SYNC then we should
> use O_DSYNC by default.

Hm.  We could do that reasonably painlessly as a compile-time test in
xlog.c, but I'm not clear on how it would play out as a GUC option.
Peter, what do you think about configuration-dependent defaults for
GUC variables?

			regards, tom lane

Thu, 15 Mar 2001 14:04:24 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
* Tom Lane <tgl@sss.pgh.pa.us> [010315 11:07] wrote:
> "Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> > ... I would either
> > use fsync as default or don't deal with O_SYNC at all.
> > But if O_DSYNC is defined and O_DSYNC != O_SYNC then we should
> > use O_DSYNC by default.
> 
> Hm.  We could do that reasonably painlessly as a compile-time test in
> xlog.c, but I'm not clear on how it would play out as a GUC option.
> Peter, what do you think about configuration-dependent defaults for
> GUC variables?

Sorry, what's a GUC? :)

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


Thu, 15 Mar 2001 11:17:24 -0800
Alfred Perlstein <bright@wintelcom.net>
Re: Allowing WAL fsync to be done via O_SYNC
Alfred Perlstein wrote:
> * Tom Lane <tgl@sss.pgh.pa.us> [010315 11:07] wrote:
> > Peter, what do you think about configuration-dependent defaults for
> > GUC variables?
 
> Sorry, what's a GUC? :)

Grand Unified Configuration, Peter E.'s baby.

See the thread starting at
http://www.postgresql.org/mhonarc/pgsql-hackers/2000-03/msg00107.html
for details.

(And the search is working.... :-)).
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Thu, 15 Mar 2001 14:37:03 -0500
Lamar Owen <lamar.owen@wgcr.org>
Re: Allowing WAL fsync to be done via O_SYNC
* Peter Eisentraut <peter_e@gmx.net> [010315 11:33] wrote:
> Alfred Perlstein writes:
> 
> > Sorry, what's a GUC? :)
> 
> Grand Unified Configuration system
> 
> It's basically a cute name for the achievement that there's now a single
> name space and interface for (almost) all postmaster run time
> configuration variables,

Oh, thanks.

Well considering that, a runtime check for doing_sync_wal_writes
== 1 shouldn't be that expensive.  Sort of the inverse of -F,
meaning that we're using O_SYNC for WAL writes, we don't need to
fsync it.

Btw, if you guys want to get some speed with WAL, I'd implement a
write-behind process if it was possible to do the O_SYNC writes.

...

And since we're sorta on the topic of IO, I noticed that it looks
like (at least in 7.0.3) that vacuum and certain other routines
read files in reverse order.

The problem (at least in FreeBSD) is that we haven't tuned
the system to detect reverse reading and hence don't do
much readahead.  There may be some going on as a function
of the read clustering, but I'm not entirely sure.

I'd suspect that other OSs might have neglected to check
for reverse reading of files as well, but I'm not sure.

Basically, if there was a way to do this another way, or
anticipate the backwards motion and do large reads, it
may add latency, but it should improve performance.

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


Thu, 15 Mar 2001 11:40:14 -0800
Alfred Perlstein <bright@wintelcom.net>
Re: Allowing WAL fsync to be done via O_SYNC
Alfred Perlstein writes:

> Sorry, what's a GUC? :)

Grand Unified Configuration system

It's basically a cute name for the achievement that there's now a single
name space and interface for (almost) all postmaster run time
configuration variables,

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


Thu, 15 Mar 2001 20:43:28 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Allowing WAL fsync to be done via O_SYNC
Alfred Perlstein <bright@wintelcom.net> writes:
> And since we're sorta on the topic of IO, I noticed that it looks
> like (at least in 7.0.3) that vacuum and certain other routines
> read files in reverse order.

Vacuum does that because it's trying to push tuples down from the end
into free space in earlier blocks.  I don't see much way around that
(nor any good reason to think that it's a critical part of vacuum's
performance anyway).  Where else have you seen such behavior?

			regards, tom lane

Thu, 15 Mar 2001 14:45:46 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC 
* Tom Lane <tgl@sss.pgh.pa.us> [010315 11:45] wrote:
> Alfred Perlstein <bright@wintelcom.net> writes:
> > And since we're sorta on the topic of IO, I noticed that it looks
> > like (at least in 7.0.3) that vacuum and certain other routines
> > read files in reverse order.
> 
> Vacuum does that because it's trying to push tuples down from the end
> into free space in earlier blocks.  I don't see much way around that
> (nor any good reason to think that it's a critical part of vacuum's
> performance anyway).  Where else have you seen such behavior?

Just vacuum, but the source is large, and I'm sort of lacking
on database-foo so I guessed that it may be done elsewhere.

You can optimize this out by implementing the read behind yourselves
sorta like this:

struct sglist *
read(fd, len)
{

	if (fd.lastpos - fd.curpos <= THRESHOLD) {
		fd.curpos = fd.lastpos - THRESHOLD;
		len = THRESHOLD;
	}

	return (do_read(fd, len));
}

of course this is entirely wrong, but illustrates what
would/could help.

I would fix FreeBSD, but it's sort of a mess and beyond what
I've got time to do ATM.

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


Thu, 15 Mar 2001 11:51:21 -0800
Alfred Perlstein <bright@wintelcom.net>
Re: Allowing WAL fsync to be done via O_SYNC
[ Charset ISO-8859-1 unsupported, converting... ]
> > 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.
> 
> I don't remember big difference in using fsync or O_SYNC in tfsync
> tests. Both depend on block size and keeping in mind that fsync
> allows us syncing after writing *multiple* blocks I would either
> use fsync as default or don't deal with O_SYNC at all.

I see what you are saying.  That the OS may be faster at fsync'ing two
blocks in one operation rather than doing to O_SYNC operations.

Seems we should just pick a default and leave the rest for a later
release.  Marc wants RC1 tomorrow, I think.

-- 
  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:30:03 -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:
>> Peter, what do you think about configuration-dependent defaults for
>> GUC variables?

> We have plenty of those already, but we should avoid a variable whose
> specification is:

> "The default is 'on' if your system defines one of the macros O_SYNC,
> O_DSYNC, O_FSYNC, and if O_SYNC and O_DSYNC are distinct, otherwise the
> default is 'off'."

Unfortunately, I think that's just about what the default would need to
be.  What alternative do you have to offer?

> The net result of this would be that the average user would have
> absolutely no clue what the default on his machine is.

Sure he would.  Fire up the software and do "SHOW wal_use_fsync"
(or whatever we call it).  I think the documentation could just say
"the default is platform-dependent".

> Additionally consider that maybe O_SYNC and O_DSYNC have different values
> but the kernel treats them the same anyway.  We really shouldn't try to
> guess that far.

Well, that's exactly *why* we need an overridable default.  Or would you
like to try to do some performance measurements in configure?

			regards, tom lane

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

> "Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:
> > ... I would either
> > use fsync as default or don't deal with O_SYNC at all.
> > But if O_DSYNC is defined and O_DSYNC != O_SYNC then we should
> > use O_DSYNC by default.
>
> Hm.  We could do that reasonably painlessly as a compile-time test in
> xlog.c, but I'm not clear on how it would play out as a GUC option.
> Peter, what do you think about configuration-dependent defaults for
> GUC variables?

We have plenty of those already, but we should avoid a variable whose
specification is:

"The default is 'on' if your system defines one of the macros O_SYNC,
O_DSYNC, O_FSYNC, and if O_SYNC and O_DSYNC are distinct, otherwise the
default is 'off'."

The net result of this would be that the average user would have
absolutely no clue what the default on his machine is.

Additionally consider that maybe O_SYNC and O_DSYNC have different values
but the kernel treats them the same anyway.  We really shouldn't try to
guess that far.

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


Thu, 15 Mar 2001 22:33:44 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Allowing WAL fsync to be done via O_SYNC 
> "The default is 'on' if your system defines one of the macros O_SYNC,
> O_DSYNC, O_FSYNC, and if O_SYNC and O_DSYNC are distinct, otherwise the
> default is 'off'."
> 
> The net result of this would be that the average user would have
> absolutely no clue what the default on his machine is.
> 
> Additionally consider that maybe O_SYNC and O_DSYNC have different values
> but the kernel treats them the same anyway.  We really shouldn't try to
> guess that far.

Good point.  I think Tom already found dfsync points to fsync in his
libc, or something like 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

Thu, 15 Mar 2001 16:39:35 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Allowing WAL fsync to be done via O_SYNC
> Well, that's exactly *why* we need an overridable default.  Or would you
> like to try to do some performance measurements in configure?

At this point I'm more comfortable with a compile-time option
(determined statically or in a configure compilation test, not a
performance test), rather than a GUC variable. But imho 7.1 will be nice
with either choice, and if you think that a variable will make it easier
for developers to do tuning from a distance (as opposed to having it
just confuse new users) then... ;)

                    - Thomas

Thu, 15 Mar 2001 23:50:35 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Allowing WAL fsync to be done via O_SYNC