Re: Simplifying wal_sync_method

Currently, here are the options available for wal_sync_method:

#wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, fsync_writethrough,
# open_sync, open_datasync

I don't understand why we support so many values. It seems 'fsync'
should be fdatasync(), and if that is not available, fsync(). Same with
open_sync and open_datasync.

In fact, 8.1 uses O_DIRECT if available, and I don't see why we don't
just use the "data" options automatically if available too, rather than
have users guess which options their OS supports. We might need an
option to print the actual features used, but I am not sure.

Is this something for 8.1 or 8.2?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Mon, Aug 08, 2005 at 03:56:39PM -0400, Bruce Momjian wrote:
> Currently, here are the options available for wal_sync_method:
>
> #wal_sync_method = fsync # the default varies across platforms:
> # fsync, fdatasync, fsync_writethrough,
> # open_sync, open_datasync

On same topic:

http://archives.postgresql.org/pgsql-general/2005-07/msg00811.php

Why does win32 PostgreSQL allow data corruption by default?

--
marko

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Currently, here are the options available for wal_sync_method:
> #wal_sync_method = fsync # the default varies across platforms:
> # fsync, fdatasync, fsync_writethrough,
> # open_sync, open_datasync

> I don't understand why we support so many values.

Because there are so many platforms with different subsets of these APIs
and different performance characteristics for the ones they do have.

> It seems 'fsync' should be fdatasync(), and if that is not available,
> fsync().

I have yet to see anyone do any systematic testing of the different
options on different platforms. In the absence of hard data, proposing
that we don't need some of the options is highly premature.

> In fact, 8.1 uses O_DIRECT if available,

That's a decision that hasn't got a shred of evidence to justify
imposing it on every platform.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Currently, here are the options available for wal_sync_method:
> > #wal_sync_method = fsync # the default varies across platforms:
> > # fsync, fdatasync, fsync_writethrough,
> > # open_sync, open_datasync
>
> > I don't understand why we support so many values.
>
> Because there are so many platforms with different subsets of these APIs
> and different performance characteristics for the ones they do have.

Right, and our current behavior makes it harder for people to even know
the supported options.

> > It seems 'fsync' should be fdatasync(), and if that is not available,
> > fsync().
>
> I have yet to see anyone do any systematic testing of the different
> options on different platforms. In the absence of hard data, proposing
> that we don't need some of the options is highly premature.

No one is every going to do it, so we might as well make the best guess
we have. I think any platform where the *data* options are slower than
the non-*data* options is broken, and if that logic holds, we might as
well just use *data* by default if we can, which is my proposal.

> > In fact, 8.1 uses O_DIRECT if available,
>
> That's a decision that hasn't got a shred of evidence to justify
> imposing it on every platform.

Right, and there is no evidence it hurts, so we do our best until
someone comes up with data to suggest we are wrong. The same should be
done with *data*.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Marko Kreen wrote:
> On Mon, Aug 08, 2005 at 03:56:39PM -0400, Bruce Momjian wrote:
> > Currently, here are the options available for wal_sync_method:
> >
> > #wal_sync_method = fsync # the default varies across platforms:
> > # fsync, fdatasync, fsync_writethrough,
> > # open_sync, open_datasync
>
> On same topic:
>
> http://archives.postgresql.org/pgsql-general/2005-07/msg00811.php
>
> Why does win32 PostgreSQL allow data corruption by default?

It behaves the same on Unix as Win32, and if you have battery-backed
cache, you don't need writethrough, so we don't have it as default. I
am going to write a section in the manual for 8.1 about these
reliability issues.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Mon, Aug 08, 2005 at 05:38:59PM -0400, Bruce Momjian wrote:
> Marko Kreen wrote:
> > On Mon, Aug 08, 2005 at 03:56:39PM -0400, Bruce Momjian wrote:
> > > Currently, here are the options available for wal_sync_method:
> > >
> > > #wal_sync_method = fsync # the default varies across platforms:
> > > # fsync, fdatasync, fsync_writethrough,
> > > # open_sync, open_datasync
> >
> > On same topic:
> >
> > http://archives.postgresql.org/pgsql-general/2005-07/msg00811.php
> >
> > Why does win32 PostgreSQL allow data corruption by default?
>
> It behaves the same on Unix as Win32, and if you have battery-backed
> cache, you don't need writethrough, so we don't have it as default. I
> am going to write a section in the manual for 8.1 about these
> reliability issues.

For some reason I don't see "corruped database after crash"
reports on Unixen. Why?

Also, why can't win32 be safe without battery-backed cache?
I can't see such requirement on other platforms.

--
marko

Marko Kreen wrote:
> On Mon, Aug 08, 2005 at 05:38:59PM -0400, Bruce Momjian wrote:
> > Marko Kreen wrote:
> > > On Mon, Aug 08, 2005 at 03:56:39PM -0400, Bruce Momjian wrote:
> > > > Currently, here are the options available for wal_sync_method:
> > > >
> > > > #wal_sync_method = fsync # the default varies across platforms:
> > > > # fsync, fdatasync, fsync_writethrough,
> > > > # open_sync, open_datasync
> > >
> > > On same topic:
> > >
> > > http://archives.postgresql.org/pgsql-general/2005-07/msg00811.php
> > >
> > > Why does win32 PostgreSQL allow data corruption by default?
> >
> > It behaves the same on Unix as Win32, and if you have battery-backed
> > cache, you don't need writethrough, so we don't have it as default. I
> > am going to write a section in the manual for 8.1 about these
> > reliability issues.
>
> For some reason I don't see "corruped database after crash"
> reports on Unixen. Why?

They use SCSI or battery-backed RAID cards more often?

> Also, why can't win32 be safe without battery-backed cache?
> I can't see such requirement on other platforms.

If it uses SCSI, it is secure, just like Unix.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Marko,

> Also, why can't win32 be safe without battery-backed cache?
> I can't see such requirement on other platforms.

Read the referenced message again. It's only an issue if you want to use
open_datasync. fsync_writethrough should be safe.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

On Mon, Aug 08, 2005 at 03:10:54PM -0700, Josh Berkus wrote:
> Marko,
> > Also, why can't win32 be safe without battery-backed cache?
> > I can't see such requirement on other platforms.
>
> Read the referenced message again. It's only an issue if you want to use
> open_datasync. fsync_writethrough should be safe.

But thats the point. Why isn't fsync_writethrough default?

--
marko

Bruce,

> No one is every going to do it, so we might as well make the best guess
> we have.  I think any platform where the *data* options are slower than
> the non-*data* options is broken, and if that logic holds, we might as
> well just use *data* by default if we can, which is my proposal.

Changing the defaults is fine with me. I just don't think that we can
afford to prune options without more testing. And we will be getting
more testing (from companies) in the future, so I don't think this is
completely out of the question.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> No one is every going to do it, so we might as well make the best guess
> we have. I think any platform where the *data* options are slower than
> the non-*data* options is broken, and if that logic holds, we might as
> well just use *data* by default if we can, which is my proposal.

Adjusting the default settings I don't have a problem with. Removing
options I have a problem with --- and that appeared to be what you
were proposing.

regards, tom lane

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Marko Kreen wrote:
>> On same topic:
>> http://archives.postgresql.org/pgsql-general/2005-07/msg00811.php
>> Why does win32 PostgreSQL allow data corruption by default?

> It behaves the same on Unix as Win32, and if you have battery-backed
> cache, you don't need writethrough, so we don't have it as default. I
> am going to write a section in the manual for 8.1 about these
> reliability issues.

I thought we had changed the default for Windows to be fsync_writethrough
in 8.1? We didn't have that code in 8.0, but now that we do, it surely
seems like the sanest default.

regards, tom lane

On Mon, 2005-08-08 at 17:03 -0400, Tom Lane wrote:
>
> That's a decision that hasn't got a shred of evidence to justify
> imposing it on every platform.

This option has its uses on Linux, however. In my testing it's good for
a large speedup (20%) on a 10-client pgbench, and a minor improvement
with 100 clients. See my mail of July 14th "O_DIRECT for WAL writes".

-jwb

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > No one is every going to do it, so we might as well make the best guess
> > we have. I think any platform where the *data* options are slower than
> > the non-*data* options is broken, and if that logic holds, we might as
> > well just use *data* by default if we can, which is my proposal.
>
> Adjusting the default settings I don't have a problem with. Removing
> options I have a problem with --- and that appeared to be what you
> were proposing.

Well, right now we support:

* open_datasync (write WAL files with open() option O_DSYNC)
* fdatasync (call fdatasync() at each commit),
* fsync (call fsync() at each commit)
* fsync_writethrough (force write-through of any disk write cache)
* open_sync (write WAL files with open() option O_SYNC)

and we pick the first supported item as the default. I have updated our
documentation to clarify this.

My proposal is to remove fdatasync and open_datasync, and have have
fsync _prefer_ fdatasync, and open_sync prefer open_datastync, but fall
back to fsync and open_sync if the *data* version are not supported.

We have flexibility by having more options, but we also have complexity
of having options that have never proven to be useful in the years we
have had them, namely using fsync if fdatasync is supported.

If we remove the *data* spellings, we can probably support both
open_sync and fsync on all platforms because the *data* varieties are
the ones that are not always supported.

One problem is that by removing the *data* versions, you would never
know if you were calling fsync or fdatasync internally.

We also need to re-test these defaults because we now have O_DIRECT and
groups writes of WAL.

If we test using the build farm, if we test two options and alternate
the tests, and one is always faster than the other, I think we can
conclude that that one is faster, even if there are other loads on the
system.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Marko Kreen wrote:
> >> On same topic:
> >> http://archives.postgresql.org/pgsql-general/2005-07/msg00811.php
> >> Why does win32 PostgreSQL allow data corruption by default?
>
> > It behaves the same on Unix as Win32, and if you have battery-backed
> > cache, you don't need writethrough, so we don't have it as default. I
> > am going to write a section in the manual for 8.1 about these
> > reliability issues.
>
> I thought we had changed the default for Windows to be fsync_writethrough
> in 8.1? We didn't have that code in 8.0, but now that we do, it surely
> seems like the sanest default.

Well, 8.0 shipped with commit() for fsync(), which in fact is
writethrough, but we decided that that wasn't a good default because:

o it didn't match Unix
o Oracle doesn't use that method for fsync
o we would be slower than Oracle on Win32
o it is a loss for battery backed RAID

so we moved commit() to fsync_writethrough, and found a way to do real
fdatasync as the default on Win32 in 8.0.2. This is clearly mentioned
in the release notes:

* Enable the wal_sync_method setting of "open_datasync" on Windows, and
make it the default for that platform (Magnus, Bruce) Because the
default is no longer "fsync_writethrough", data loss is possible during
a power failure if the disk drive has write caching enabled. To turn off
the write cache on Windows, from the Device Manager, choose the drive
properties, then Policies.

This was discussed on the lists extensively.

One problem with writethrough is that drives that don't do writethrough
by default are often the ones with the worst performance for this,
namely IDE drives.

Also, in FreeBSD, if you add "hw.ata.wc=0" to /boot/loader.conf, you get
write-through, but for all ATA drives. Should we recommend that?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> My proposal is to remove fdatasync and open_datasync, and have have
> fsync _prefer_ fdatasync, and open_sync prefer open_datastync, but fall
> back to fsync and open_sync if the *data* version are not supported.

And this will buy us what, other than lack of flexibility?

The "data" options already are the default when available, I think
(if not, I have no objection to making them so). That does not
equate to saying we should remove access to the other options.
Your argument that they are useless only holds up in a perfect
world where there are no hardware bugs and no kernel bugs ...
and last I checked, we do not live in such a world.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > My proposal is to remove fdatasync and open_datasync, and have have
> > fsync _prefer_ fdatasync, and open_sync prefer open_datastync, but fall
> > back to fsync and open_sync if the *data* version are not supported.
>
> And this will buy us what, other than lack of flexibility?

Clarity in testing options.

> The "data" options already are the default when available, I think
> (if not, I have no objection to making them so). That does not

They are.

> equate to saying we should remove access to the other options.
> Your argument that they are useless only holds up in a perfect
> world where there are no hardware bugs and no kernel bugs ...
> and last I checked, we do not live in such a world.

Is it useful to have the option of using non-*data* options when *data*
options are available? I have never heard of anyone wanting to do that,
nor do I imagine anyone doing that. Is there a real use case?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Mon, Aug 08, 2005 at 08:04:44PM -0400, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Marko Kreen wrote:
> >> On same topic:
> >> http://archives.postgresql.org/pgsql-general/2005-07/msg00811.php
> >> Why does win32 PostgreSQL allow data corruption by default?
>
> > It behaves the same on Unix as Win32, and if you have battery-backed
> > cache, you don't need writethrough, so we don't have it as default. I
> > am going to write a section in the manual for 8.1 about these
> > reliability issues.
>
> I thought we had changed the default for Windows to be fsync_writethrough
> in 8.1? We didn't have that code in 8.0, but now that we do, it surely
> seems like the sanest default.

Seems it _was_ default in 8.0 and 8.0.1 (called fsync) but
renamed to fsync_writethrough in 8.0.2 and moved away from being
default.

Now, 8.0.2 was released on 2005-04-07 and first destruction
happened in 2005-07-20. If this says anything about future,
I don't think PostgreSQL will stay known as 'reliable' database.

--
marko