Simplifying wal_sync_method

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@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In summary, we added all those wal_sync_method values in hopes of
getting some data on which is best on which platform, but having gone
several years with few reports, I am thinking we should just choose the
best ones we can and move on, rather than expose a confusing API to the
users.

Does anyone show a platform where the *data* options are slower than the
non-*data* ones?

---------------------------------------------------------------------------

pgman wrote:

Tom Lane wrote:

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

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

I think we should offer the reliable option by default, and mention the
fast option for those who have battery-backed cache in the manual.

But only on Win32?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.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 06:02:37PM -0400, Bruce Momjian wrote:

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

I think we should offer the reliable option by default, and mention the
fast option for those who have battery-backed cache in the manual.

But only on Win32?

Yes, because that's the only place where that option works, right?

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"I dream about dreams about dreams", sang the nightingale
under the pale moon (Sandman)

I think we should offer the reliable option by default, and mention the
fast option for those who have battery-backed cache in the manual.

But only on Win32?

Yes, because that's the only place where that option works, right?

fsync_writethrough only works on Win32 the postgresql.conf should
reflect that.

On Mon, Aug 08, 2005 at 06:02:37PM -0400, Bruce Momjian wrote:

Alvaro Herrera wrote:

I think we should offer the reliable option by default, and mention the
fast option for those who have battery-backed cache in the manual.

But only on Win32?

We should do what's possible with what's given to us.

On Win32:

1. We can write through cache.
2. We have unreliable OS with unreliable filesystem.
3. The probability of mediocre hardware is higher.

Regular POSIX:
1. We can't write through cache.
2. We have good OS with good filesystem (probably even
journaled).
3. The probably of mediocre hardware is lower.

Why shouldn't we offer reliable option to win32?

Options:

- Win32 guy complains that PG is bit slow.
We tell him to RTFM.
- Win32 guy complains he lost database.
We tell him he didn't RTFM.

Which way you make more friends?

--
marko

PS. Yeah, I was the guy who helped him to restore what's left.
I'd say he wasn't exactly happy.

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

On Mon, 2005-08-08 at 17:44 -0400, Bruce Momjian wrote:

In summary, we added all those wal_sync_method values in hopes of
getting some data on which is best on which platform, but having gone
several years with few reports, I am thinking we should just choose the
best ones we can and move on, rather than expose a confusing API to the
users.

I agree this should be attempted over the 8.1 beta period.

This is a good case for having a Port Coordinator assigned for each
port, so we could ask them to hunt out the solution for their platform.
Maybe this is something that we can broadcast to the BuildFarm team, so
each person can reflect on the appropriate settings?

Best Regards, Simon Riggs

Bruce Momjian <pgman@candle.pha.pa.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

Simon Riggs wrote:

On Mon, 2005-08-08 at 17:44 -0400, Bruce Momjian wrote:

In summary, we added all those wal_sync_method values in hopes of
getting some data on which is best on which platform, but having gone
several years with few reports, I am thinking we should just choose the
best ones we can and move on, rather than expose a confusing API to the
users.

I agree this should be attempted over the 8.1 beta period.

This is a good case for having a Port Coordinator assigned for each
port, so we could ask them to hunt out the solution for their platform.
Maybe this is something that we can broadcast to the BuildFarm team, so
each person can reflect on the appropriate settings?

It might be possible to build a new set of tests that we could perform.
That would have to be built into the buildfarm script, as the PL tests
were, but they were picked up pretty quickly by the community.
Unfortunately it doesn't sound like these would fit into the pg_regress
setup, so we'll have to devise a different test harness - probably not a
bad idea for automated performance testing anyway.

So the short answer is possibly "You build the tests and we'll run 'em."

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

So the short answer is possibly "You build the tests and we'll run 'em."

The availability of the buildfarm certainly makes it a lot more feasible
to do performance tests on a variety of platforms. So, who wants to
knock something together?

I suppose we would usually be interested in one-time tests, rather than
something repeated every time CVS is touched. How might that sort of
requirement fit into the buildfarm software design?

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.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