> > Magnus prepared a trivial patch which added the O_SYNC flag for
> > windows and mapped it to FILE_FLAG_WRITE_THROUGH in win32_open.c.
>
> Attached is this trivial patch. As Merlin says, it needs some
> more reliability testing. But the numbers are at least reasonable - it
> *seems* like it's doing the right thing (as long as you turn
> off write cache). And it's certainly a significant
> performance increase - it brings the speed almost up to the
> same as linux.

I have now run a bunch of pull-the-plug testing on this patch (literally
pulling the plug, yes. to the point of some of my co-workers thinking
I'm crazy)

My results are:
Fisrt, baseline:
* Linux, with fsync (default), write-cache disabled: no data corruption
* Linux, with fsync (default), write-cache enabled: usually no data
corruption, but two runs which had
* Win32, with fsync, write-cache disabled: no data corruption
* Win32, with fsync, write-cache enabled: no data corruption
* Win32, with osync, write cache disabled: no data corruption
* Win32, with osync, write cache enabled: no data corruption. Once I
got:
2005-02-24 12:19:54 LOG: could not open file "C:/Program
Files/PostgreSQL/8.0/data/pg_xlog/000000010000000000000010" (log file 0,
segment 16): No such file or directory

but the data in the database was consistent.

Almost all runs showed a line along the line:
2005-02-24 11:22:41 LOG: record with zero length at 0/A450548


In the final test, the BIOS decided the disk was giving up and
reassigned it as 0Mb.. Required two extra cold boots, then it was back
up to 20Gb. Still no data loss.


My tests was three clients doing lots of inserts and updates, some in
transactions some bare. In some tests, I kicked in a manual vacuum while
at it. Then I yanked the powercord, rebooted, manually started pg, and
verified taht the data in the db came up with the same values the cliens
reported as last committed. I also ran vacuum verbose on all tables
after it was back up to see if there were any warnings.

Test machine is a 1GHz Celeron, 256Mb RAM and a Maxtor IDE disk.

It'd of course be good if others could also test, but I'm getting the
feeling that this patch at least doesn't make things worse than before
:-) ANd it's *a lot* faster.

//Magnus

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

> In the final test, the BIOS decided the disk was giving up and
> reassigned it as 0Mb.. Required two extra cold boots, then it was back
> up to 20Gb. Still no data loss.

I think it would be fun to re-run these tests with MySQL...

Chris

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> My results are:
> Fisrt, baseline:
> * Linux, with fsync (default), write-cache disabled: no data corruption
> * Linux, with fsync (default), write-cache enabled: usually no data
> corruption, but two runs which had
> * Win32, with fsync, write-cache disabled: no data corruption
> * Win32, with fsync, write-cache enabled: no data corruption
> * Win32, with osync, write cache disabled: no data corruption
> * Win32, with osync, write cache enabled: no data corruption. Once I
> got:
> 2005-02-24 12:19:54 LOG: could not open file "C:/Program
> Files/PostgreSQL/8.0/data/pg_xlog/000000010000000000000010" (log file 0,
> segment 16): No such file or directory

In case anyone is wondering, you can turn off write caching on FreeBSD,
for a terrible perfomance loss...

http://freebsd.active-venture.com/ha...k.html#AEN8015

Chris

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

"Magnus Hagander" <mha@sollentuna.net> writes:
> My results are:
> Fisrt, baseline:
> * Linux, with fsync (default), write-cache disabled: no data corruption
> * Linux, with fsync (default), write-cache enabled: usually no data
> corruption, but two runs which had

That makes sense.

> * Win32, with fsync, write-cache disabled: no data corruption
> * Win32, with fsync, write-cache enabled: no data corruption
> * Win32, with osync, write cache disabled: no data corruption
> * Win32, with osync, write cache enabled: no data corruption. Once I
> got:
> 2005-02-24 12:19:54 LOG: could not open file "C:/Program
> Files/PostgreSQL/8.0/data/pg_xlog/000000010000000000000010" (log file 0,
> segment 16): No such file or directory
> but the data in the database was consistent.

It disturbs me that you couldn't produce data corruption in the cases
where it theoretically should occur. Seems like this is an indication
that your test was insufficiently severe, or that there is something
going on we don't understand.

regards, tom lane

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

http://www.postgresql.org/docs/faq

> "Magnus Hagander" <mha@sollentuna.net> writes:
>> My results are:
>> Fisrt, baseline:
>> * Linux, with fsync (default), write-cache disabled: no data corruption
>> * Linux, with fsync (default), write-cache enabled: usually no data
>> corruption, but two runs which had
>
> That makes sense.
>
>> * Win32, with fsync, write-cache disabled: no data corruption
>> * Win32, with fsync, write-cache enabled: no data corruption
>> * Win32, with osync, write cache disabled: no data corruption
>> * Win32, with osync, write cache enabled: no data corruption. Once I
>> got:
>> 2005-02-24 12:19:54 LOG: could not open file "C:/Program
>> Files/PostgreSQL/8.0/data/pg_xlog/000000010000000000000010" (log file 0,
>> segment 16): No such file or directory
>> but the data in the database was consistent.
>
> It disturbs me that you couldn't produce data corruption in the cases
> where it theoretically should occur. Seems like this is an indication
> that your test was insufficiently severe, or that there is something
> going on we don't understand.
>
I was thinking about that. A few years back, Microsoft had some serious
issues with write caching drives. They were taken to task for losing data
if Windows shut down too fast, especially on drives with a large cache.

MS is big enough and bad enough to get all the info they need from the
various drive makers to know how to handle write cache flushing. Even the
stuff that isn't documented.

If anyone has a very good debugger and/or emulator or even a logic
analyzer, it would be interesting to see if MS sends commands to the
drives after a disk write or a set of disk writes.

Also, I would like to see this test performed on NTFS and FAT32, and see
if you are more likely to lose data on FAT32.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

"Magnus Hagander" <mha@sollentuna.net> writes:

> * Linux, with fsync (default), write-cache enabled: usually no data
> corruption, but two runs which had

Are you verifying that all the data that was committed was actually stored? Or
just verifying that the database works properly after rebooting?

I'm a bit surprised that the write-cache lead to a corrupt database, and not
merely lost transactions. I had the impression that drives still handled the
writes in the order received.

You may find that if you check this case again that the "usually no data
corruption" is actually "usually lost transactions but no corruption".

--
greg


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Greg Stark <gsstark@mit.edu> writes:
> I'm a bit surprised that the write-cache lead to a corrupt database, and not
> merely lost transactions. I had the impression that drives still handled the
> writes in the order received.

There'd be little point in having a cache if they did, I should think.
I thought the point of the cache was to allow the disk to schedule I/O
in an order that minimizes seek time (ie, such a disk has got its own
elevator queue or similar).

> You may find that if you check this case again that the "usually no data
> corruption" is actually "usually lost transactions but no corruption".

That's a good point, but it seems difficult to be sure of the last
reportedly-committed transaction in a powerfail situation. Maybe if
you drive the test from a client on another machine?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Greg Stark <gsstark@mit.edu> writes:
> > I'm a bit surprised that the write-cache lead to a corrupt database, and not
> > merely lost transactions. I had the impression that drives still handled the
> > writes in the order received.
>
> There'd be little point in having a cache if they did, I should think.
> I thought the point of the cache was to allow the disk to schedule I/O
> in an order that minimizes seek time (ie, such a disk has got its own
> elevator queue or similar).

If that were the case then SCSI drives that ship with write caching disabled
and using tagged command queuing instead would perform poorly.

I think the main motivation for write caching on IDE drives is that the IDE
protocol forces commands to be issued synchronously. So you can't send a
second command until the first command has completed. Without write caching
that limits the write bandwidth tremendously. Write caching is being used here
as a poor man's tcq.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org