Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
* 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/00010010 (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. The Windows driver knows abotu the write cache, and at least fsync() pushes through the write cache even if it's there. This seems to indicate taht O_SYNC at least partiallyi does this as well. This is why there is no performance difference at all on fsync() with write cache on or off. I don't know if this is true for all IDE disks. COuld be that my disk is particularly well-behaved. This indicated to me that open_sync did not require any additional changes than our current fsync. fsync and open_sync both write through the write cache in the operating system. Only fsync=off turns this off. fsync also writes through the hardware write cache. o_sync does not. This is what causes the large slowdown with write cache enabled, *including* most battery backed write cache systems (pretty much making the write-cache a waste of money). This may be a good thing on IDE systems (for admins that don't know how to remove the little check in the box for enable write caching on the disk that MS provides, which *explicitly* warns that you may lose data if you enabled it), but it's a very bad thing for anything higher end. fsync also syncs the directory metadata. o_sync only cares about the files contents. (This is what causes the large slowdown with write cache *disabled*, becuase it requires multiple writes on multiple disk locations for each fsync). Basically, fsync hurts people who configure their box correctly, or who use things like SCSI disks. o_sync hurts people who configure their machine in an unsafe way. //Magnus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Magnus Hagander wrote: This indicated to me that open_sync did not require any additional changes than our current fsync. fsync and open_sync both write through the write cache in the operating system. Only fsync=off turns this off. fsync also writes through the hardware write cache. o_sync does not. This is what causes the large slowdown with write cache enabled, *including* most battery backed write cache systems (pretty much making the write-cache a waste of money). This may be a good thing on IDE systems (for admins that don't know how to remove the little check in the box for enable write caching on the disk that MS provides, which *explicitly* warns that you may lose data if you enabled it), but it's a very bad thing for anything higher end. I found the checkbox on XP looking at Properties for the drive, then choosing Hardware, the drive, Properties, and Policies. fsync also syncs the directory metadata. o_sync only cares about the files contents. (This is what causes the large slowdown with write cache *disabled*, because it requires multiple writes on multiple disk locations for each fsync). Basically, fsync hurts people who configure their box correctly, or who use things like SCSI disks. o_sync hurts people who configure their machine in an unsafe way. So, it seems that Win32 open_sync is exactly the same as our wal_sync_method = open_datasync on Unix (it needs to be renamed), and wal_sync_method = fsync on Win32 is something we don't have that writes through the disk write cache even if it is enabled. I have developed the following patch which renames our wal_sync_method Win32 support from open_sync to open_datasync: ftp://candle.pha.pa.us/pub/postgresql/mypatches One issue with this patch is that if applied it would make open_datasync the default sync method on Win32 because we prefer open_datasync over all other sync methods. If we don't want to do that, I think we should still do the rename for accuracy and add a !WIN32 test to prevent open_datasync from being the default. However, I do prefer this patch and let Win32 have the same write cache issues as Unix, for consistency. -- 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Bruce Momjian pgman@candle.pha.pa.us writes: However, I do prefer this patch and let Win32 have the same write cache issues as Unix, for consistency. I agree that the open flag is more nearly O_DSYNC than O_SYNC. ISTM Windows' idea of fsync is quite different from Unix's and therefore we should name the wal_sync_method that invokes it something different than fsync. write_through or some such? We already have precedent that not all wal_sync_method values are available on all platforms. I'm not taking a position on which the default should be ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: However, I do prefer this patch and let Win32 have the same write cache issues as Unix, for consistency. I agree that the open flag is more nearly O_DSYNC than O_SYNC. ISTM Windows' idea of fsync is quite different from Unix's and therefore we should name the wal_sync_method that invokes it something different than fsync. write_through or some such? We already have precedent that not all wal_sync_method values are available on all platforms. I'm not taking a position on which the default should be ... Yes, I am thinking that too. I hesistated because it adds yet another sync method, and we have to document it works only on Win32, but I see no better solution. I am going to let the Win32 users mostly vote on what the default should be. -- 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: we should name the wal_sync_method that invokes it something different than fsync. write_through or some such? We already have precedent that not all wal_sync_method values are available on all platforms. Yes, I am thinking that too. I hesistated because it adds yet another sync method, and we have to document it works only on Win32, but I see no better solution. It occurs to me that it'd probably be a good idea if the error message for an unsupported wal_sync_method value explicitly listed the allowed values for the platform. If there's no objection I'll try to make that happen. (I'm not sure if it's trivial or not: I think the GUC framework is a bit restrictive about custom error messages from GUC assign hooks...) regards, tom lane ---(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
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
The default should clearly be the safest method. Personally, I would disable anything but the safest method for all database files that are not read-only. IMO-YMMV. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: Thursday, March 17, 2005 10:53 AM To: Tom Lane Cc: Magnus Hagander; Michael Paesold; pgsql-hackers@postgresql.org; [EMAIL PROTECTED]; Merlin Moncure Subject: Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: However, I do prefer this patch and let Win32 have the same write cache issues as Unix, for consistency. I agree that the open flag is more nearly O_DSYNC than O_SYNC. ISTM Windows' idea of fsync is quite different from Unix's and therefore we should name the wal_sync_method that invokes it something different than fsync. write_through or some such? We already have precedent that not all wal_sync_method values are available on all platforms. I'm not taking a position on which the default should be ... Yes, I am thinking that too. I hesistated because it adds yet another sync method, and we have to document it works only on Win32, but I see no better solution. I am going to let the Win32 users mostly vote on what the default should be. -- 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: ISTM Windows' idea of fsync is quite different from Unix's and therefore we should name the wal_sync_method that invokes it something different than fsync. write_through or some such? Ah, I remember now. On Win32 our fsync is: #define fsync(a)_commit(a) I am wondering if we should call the new mode open_commit or open_writethrough. Our typical rule is to tie it to the API call, which should suggest open_commit. fsync_writethrough, perhaps. I don't see any open about it. Sorry, yea, go confused. -- 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: ISTM Windows' idea of fsync is quite different from Unix's and therefore we should name the wal_sync_method that invokes it something different than fsync. write_through or some such? Ah, I remember now. On Win32 our fsync is: #define fsync(a)_commit(a) I am wondering if we should call the new mode open_commit or open_writethrough. Our typical rule is to tie it to the API call, which should suggest open_commit. fsync_writethrough, perhaps. I don't see any open about it. regards, tom lane ---(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
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Michael Paesold wrote: Magnus Hagander wrote: Magnus Hagander wrote: 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. [snip] Michael Paesold wrote: The original patch did not have any documentation. Have you added some? Since this has to be configured in GUC (wal_sync_method), the implications should be documented somewhere, no? The patch just implements behaviour that was already documented (for unix) on a new platform (win32). The documentation in general appears to have very little information on what to pick there, though ;-) Reading your mails about the pull-the-plug tests, I see that at least with write caching enabled, fsync is more secure on win32 than open_sync. I.e. one should disable write caching for use with open_sync. Also open_sync seems to perform much better. All that information would be nice to have in the docs. Michael, I am not sure why you come to the conclusion that open_sync requires turning off the disk write cache. I saw nothing to indicate that in the thread: http://archives.postgresql.org/pgsql-hackers-win32/2005-02/msg00035.php I read the following: * 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/00010010 (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. The Windows driver knows abotu the write cache, and at least fsync() pushes through the write cache even if it's there. This seems to indicate taht O_SYNC at least partiallyi does this as well. This is why there is no performance difference at all on fsync() with write cache on or off. I don't know if this is true for all IDE disks. COuld be that my disk is particularly well-behaved. This indicated to me that open_sync did not require any additional changes than our current fsync. -- 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Bruce Momjian wrote: Michael Paesold wrote: Magnus Hagander wrote: [snip] Michael, I am not sure why you come to the conclusion that open_sync requires turning off the disk write cache. I saw nothing to indicate that in the thread: I was just seeing his error message below... http://archives.postgresql.org/pgsql-hackers-win32/2005-02/msg00035.php I read the following: * 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/00010010 (log file 0, segment 16): No such file or directory but the data in the database was consistent. A missing xlog file does not strike me as very save. Perhaps someone can explain what happened, but I would not feel good about this. Again this note (from Tom Lane) in combination with the above error would tell me, we don't fully understand the risk here. 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. The Windows driver knows abotu the write cache, and at least fsync() pushes through the write cache even if it's there. This seems to indicate taht O_SYNC at least partiallyi does this as well. This is why there is no performance difference at all on fsync() with write cache on or off. I don't know if this is true for all IDE disks. COuld be that my disk is particularly well-behaved. This indicated to me that open_sync did not require any additional changes than our current fsync. We both based our understanding on the same evidence. It seems we just have a different level of paranoia. ;-) Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
-Original Message- From: [EMAIL PROTECTED] on behalf of Bruce Momjian Sent: Sun 2/27/2005 12:54 AM To: Magnus Hagander Cc: Tom Lane; pgsql-hackers@postgresql.org; [EMAIL PROTECTED]; Merlin Moncure Subject: Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question Patch applied. Thanks. I assume this is not approprate for 8.0.X. I think it would be good to backpatch it given proper testing - the changes are relatively minor, and they do give a significant performance boost. Regards, Dave ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] win32 performance - fsync question
Patch applied. Thanks. I assume this is not approprate for 8.0.X. --- Magnus Hagander wrote: 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. The original patch did not have any documentation. Have you added some? Since this has to be configured in GUC (wal_sync_method), the implications should be documented somewhere, no? The patch just implements behaviour that was already documented (for unix) on a new platform (win32). The documentation in general appears to have very little information on what to pick there, though ;-) //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] win32 performance - fsync question
Bruce Momjian wrote: Patch applied. Thanks. I assume this is not approprate for 8.0.X. --- Magnus Hagander wrote: 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. The original patch did not have any documentation. Have you added some? Since this has to be configured in GUC (wal_sync_method), the implications should be documented somewhere, no? Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] win32 performance - fsync question
Magnus Hagander wrote: Magnus Hagander wrote: 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. [snip] Michael Paesold wrote: The original patch did not have any documentation. Have you added some? Since this has to be configured in GUC (wal_sync_method), the implications should be documented somewhere, no? The patch just implements behaviour that was already documented (for unix) on a new platform (win32). The documentation in general appears to have very little information on what to pick there, though ;-) Reading your mails about the pull-the-plug tests, I see that at least with write caching enabled, fsync is more secure on win32 than open_sync. I.e. one should disable write caching for use with open_sync. Also open_sync seems to perform much better. All that information would be nice to have in the docs. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
I'd like to see this one also considered for 8.0.x, though I'd certainly like to see some more testing as well. Perhaps it's suitable for the 8.0.x with extended testing that is planned for the ARC replacement code? It does make a huge difference on win32. While we definitly don't want to risk data, a 60% speedup in write intensive apps is a *lot*. //Magnus -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: den 27 februari 2005 01:54 To: Magnus Hagander Cc: Tom Lane; pgsql-hackers@postgresql.org; [EMAIL PROTECTED]; Merlin Moncure Subject: Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question Patch applied. Thanks. I assume this is not approprate for 8.0.X. --- Magnus Hagander wrote: 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. //Magnus Content-Description: o_sync.patch [ Attachment, skipping... ] ---(end of broadcast)--- TIP 8: explain analyze is your friend -- 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] win32 performance - fsync question
Patch applied. Thanks. I assume this is not approprate for 8.0.X. --- Magnus Hagander wrote: 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. //Magnus Content-Description: o_sync.patch [ Attachment, skipping... ] ---(end of broadcast)--- TIP 8: explain analyze is your friend -- 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Are you verifying that all the data that was committed was actually stored? Or just verifying that the database works properly after rebooting? I verified the data. Does pg startup increase the xid by some amount (say 1000 xids) after crash ? Else I think you would also need to rollback a range of xids after the crash, to see if you don't loose data by reusing and rolling back xids. The risk is datapages reaching the disk before WAL, because the disk rearranges. I think you would not notice such corruption (with pg_dump) unless you do the range rollback. Andreas ---(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
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
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/00010010 (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
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
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/00010010 (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/handbook/configtuning-disk.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
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Magnus Hagander [EMAIL PROTECTED] 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/00010010 (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
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
* 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/00010010 (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. The Windows driver knows abotu the write cache, and at least fsync() pushes through the write cache even if it's there. This seems to indicate taht O_SYNC at least partiallyi does this as well. This is why there is no performance difference at all on fsync() with write cache on or off. I don't know if this is true for all IDE disks. COuld be that my disk is particularly well-behaved. //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Magnus Hagander [EMAIL PROTECTED] 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
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Greg Stark [EMAIL PROTECTED] 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
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
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? FYI, that's what I did. Test client ran across the network to the server, so it could output on the console which transaction was last reported commityted. In a couple of cases, the server came up with a transaction the client had *not* reported as committed. But I think that can be explained by the commit message not reaching the client over the network before power went out. //Magnus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
* 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 verified the data. 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. In this case, it was lost transactions, not data corruption. Should be more careful. I had copy/pasted the no data corruption, should specify what was lost. A couple of the latest transactions were gone, but the database came up in a consistent state, if a bit old. Since Linux wasn't the stuff I actually was testing, I didn't run very many tests on it though. //Magnus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
Magnus Hagander [EMAIL PROTECTED] 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. In this case, it was lost transactions, not data corruption. ... A couple of the latest transactions were gone, but the database came up in a consistent state, if a bit old. That's interesting. It would be very interesting to know how reliably this is true. It could potentially vary depending on the drive firmware. I can't see any painless way to package up this kind of test for people to run though. Powercycling machines repeatedly really isn't fun and takes a long time. And testing this on vmware doesn't buy us anything. -- greg ---(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
Re: [HACKERS] win32 performance - fsync question
One point that I no longer recall the reasoning behind is that xlog.c doesn't think O_SYNC is a preferable default over fsync. For larger (8k) transactions O_SYNC|O_DIRECT is only good with the recent pending patch to group WAL writes together. The fsync method gives the OS a chance to do the grouping. (Of course it does not matter if you have small tx 8k WAL) This would be true for fdatasync() but not for fsync(), I think. No, it is only worse with fsync, since that adds a mandatory seek. On win32 (which started this discussion, fsync will sync the directory entry as well, which will lead to *at least* two seeks on the disk. Writing two blocks after each other to an O_SYNC opened file should give exactly two seeks. I think you are making the following not maintainable assumptions. 1. there is no other outstanding IO on that drive that the OS happily inserts between your two 8k writes 2. the rotational delay is neglectible 3. the per call overhead is neglectible You will at least wait until the heads reach the write position again, since you will not be able to supply the next 8k in time for the drive to continue writing (with the single backend large tx I was referring to). If you doubt what I am saying do dd blocksize tests on a raw device. The results are, that up to ~256kb blocksize you can increase the drive performance on a drive that does not have a powerfailsafe cache, and does not lie about write success. Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] win32 performance - fsync question
On win32 (which started this discussion, fsync will sync the directory entry as well, which will lead to *at least* two seeks on the disk. Writing two blocks after each other to an O_SYNC opened file should give exactly two seeks. I think you are making the following not maintainable assumptions. 1. there is no other outstanding IO on that drive that the OS happily inserts between your two 8k writes 2. the rotational delay is neglectible 3. the per call overhead is neglectible You will at least wait until the heads reach the write position again, since you will not be able to supply the next 8k in time for the drive to continue writing (with the single backend large tx I was referring to). If you doubt what I am saying do dd blocksize tests on a raw device. The results are, that up to ~256kb blocksize you can increase the drive performance on a drive that does not have a powerfailsafe cache, and does not lie about write success. On win32 with standard hardware, WAL O_SYNC gives about 2-3x performance according to pg_bench. This is in part because fsync() on win32 is the 'nuclear option', syncing meta data which slows down things considerably. Not sure about unix, but the win32 O_DIRECT equivalent disables the read cache and also gives slightly faster write performance (presumably from removing the overhead of the cache manager). The other issue is high performance RAID controllers. With dedicated memory and processor, a good raid controller w/bbu might perform significantly better with everything sent right to the controller, all the time. On win32, fsync() bypasses the raid write cache killing the performance gain from moving to a caching RAID controller. Merlin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [pgsql-hackers-win32] [HACKERS] win32 performance - fsync question
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. For testing, I have built and uploaded binaries from the 8.0 stable branch with this patch applied. They are available from http://www.hagander.net/pgsql/. Install the 8.0.1 version first (from MSI or manually, your choice), then replace postmaster.exe and postgres.exe with the ones in the ZIP file. If you're running as a service, make sure to stop the service first. To make sure it uses the new code, change wal_sync_method to open_sync in postgresql.conf and restart the service. The kind of testing we need help is pulling the plug reliability testing. For this, make sure you have write caching turned off (it's no the disks properties page in the Device Manager), run a bunch of transactions on the db and then pull the plug of the machine in the middle. It should come up with all acknowledged transactions still applied, and all others not. //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] win32 performance - fsync question
Portability, or rather the complete lack of it. Stuff that isn't in the Single Unix Spec is a hard sell. O_DIRECT is reasonably common among modern Unixen (it is supported by Linux, FreeBSD, and probably a couple of the commercial variants like AIX or IRIX); it should also be reasonably easy to check for support at configure time. It's on my TODO list to take a gander at adding support for O_DIRECT for WAL, I just haven't gotten around to it yet. Let me know when you do, and if you need some pointers on the win32 parts of it :-) I'll happily leave the main changes alone. //Magnus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] win32 performance - fsync question
One point that I no longer recall the reasoning behind is that xlog.c doesn't think O_SYNC is a preferable default over fsync. For larger (8k) transactions O_SYNC|O_DIRECT is only good with the recent pending patch to group WAL writes together. The fsync method gives the OS a chance to do the grouping. (Of course it does not matter if you have small tx 8k WAL) This would be true for fdatasync() but not for fsync(), I think. On win32 (which started this discussion, fsync will sync the directory entry as well, which will lead to *at least* two seeks on the disk. Writing two blocks after each other to an O_SYNC opened file should give exactly two seeks. Of course, this only moves the breakpoint up to n blocks, where n 2 (3 or 4 depending on how many seeks the filesystem will require). //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] win32 performance - fsync question
Tom Lane wrote: Portability, or rather the complete lack of it. Stuff that isn't in the Single Unix Spec is a hard sell. O_DIRECT is reasonably common among modern Unixen (it is supported by Linux, FreeBSD, and probably a couple of the commercial variants like AIX or IRIX); it should also be reasonably easy to check for support at configure time. It's on my TODO list to take a gander at adding support for O_DIRECT for WAL, I just haven't gotten around to it yet. -Neil ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] win32 performance - fsync question
One point that I no longer recall the reasoning behind is that xlog.c doesn't think O_SYNC is a preferable default over fsync. For larger (8k) transactions O_SYNC|O_DIRECT is only good with the recent pending patch to group WAL writes together. The fsync method gives the OS a chance to do the grouping. (Of course it does not matter if you have small tx 8k WAL) Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] win32 performance - fsync question
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. We pg_benched it and here are the results of our test on my WinXP workstation on a 10k raptor: Settings were pgbench -t 100 -c 10. fsync = off: ~ 280 tps fsync on, WAL=fsync: ~ 35 tps fsync on, WAL=open_sync write cache policy on: ~ 240 tps fsync on, WAL=open_sync write cache policy off: ~ 80 tps 80 tps, btw, is about the results I'd expect from linux on this hardware. Also, the open_sync method plays much nicer with RAID devices, but it would need some more rigorous testing before I'd personally certify it as safe. As an aside, it doesn't look like the open_sync can be trusted with write caching policy on the disk (the default), and that's worth noting. Merlin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] win32 performance - fsync question
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. //Magnus o_sync.patch Description: o_sync.patch ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] win32 performance - fsync question
Hi, looking for the way how to increase performance at Windows XP box, I found the parameters #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync I have no idea how it works with win32. May I try fsync = false, or it is dangerous? Which of wal_sync_method may I try at WinXP? You can try it, but it is dangerous. fsync is the correct wal_sync_method. For some reason the syncing is quite a lot slower on win32. One reason might be that it does flush metadata about the file as well, which I beleive at least Linux doesn't. If it wasn't clear already, if you're running antivirus, try uninstalling it. Note that you may need to uninstall it to get all performance back, just disabling is often *not* enough as the kernel driver is still loaded. Things worth experimenting with (these are all untested, so please report any successes): 1) Try reformatting with a cluster size of 8Kb (the pg page size), if you can. 2) Disable the last access time (like noatime on linux). fsutil behavior set disablelastaccess 1 3) Disable 8.3 filenames fsutil behavior set disable8dot3 1 2 and 3 may require a reboot. (2 and 3 can be done on earlier windows through registry settings only, in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem) //Magnus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] win32 performance - fsync question
Things worth experimenting with (these are all untested, so please report any successes): 1) Try reformatting with a cluster size of 8Kb (the pg page size), if you can. What about recompiling pg with a 4k block size. Win32 file cluster sizes and memory allocation units are both on 4k boundries. Merlin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] win32 performance - fsync question
The general question is - does PostgreSQL really need fsync? I suppose it is a question for design, not platform-specific one. It sounds like only one scenario, when fsync is useful, is to interprocess communication via open file. But PostgreSQL utilize IPC for this, so does fsync is really required? NO! Fsync is so that when your computer loses power without warning, you will have no data loss. If you turn it off, you run the risk of losing data if you lose power. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] win32 performance - fsync question
On Thu, 17 Feb 2005, Magnus Hagander wrote: Hi, looking for the way how to increase performance at Windows XP box, I found the parameters #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync I have no idea how it works with win32. May I try fsync = false, or it is dangerous? Which of wal_sync_method may I try at WinXP? You can try it, but it is dangerous. fsync is the correct wal_sync_method. For some reason the syncing is quite a lot slower on win32. One reason might be that it does flush metadata about the file as well, which I beleive at least Linux doesn't. If it wasn't clear already, if you're running antivirus, try uninstalling it. Note that you may need to uninstall it to get all performance back, just disabling is often *not* enough as the kernel driver is still loaded. No, I have not any resident disk-related staff. Things worth experimenting with (these are all untested, so please report any successes): 1) Try reformatting with a cluster size of 8Kb (the pg page size), if you can. 2) Disable the last access time (like noatime on linux). fsutil behavior set disablelastaccess 1 3) Disable 8.3 filenames fsutil behavior set disable8dot3 1 2 and 3 may require a reboot. (2 and 3 can be done on earlier windows through registry settings only, in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem) I've repeated the test under 2 and 3 - no noticeable difference. With disablelastaccess I got about 10% - 15% better results, but it is not too significant. Finally I tried fsync = false and got 580-620 tps. So, the short summary: WinXP fsync = true 20-28 tps WinXP fsync = false 600 tps Linux 800 tps The general question is - does PostgreSQL really need fsync? I suppose it is a question for design, not platform-specific one. It sounds like only one scenario, when fsync is useful, is to interprocess communication via open file. But PostgreSQL utilize IPC for this, so does fsync is really required? E.R. _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] win32 performance - fsync question
On Thu, 17 Feb 2005, Christopher Kings-Lynne wrote: The general question is - does PostgreSQL really need fsync? I suppose it is a question for design, not platform-specific one. It sounds like only one scenario, when fsync is useful, is to interprocess communication via open file. But PostgreSQL utilize IPC for this, so does fsync is really required? NO! Fsync is so that when your computer loses power without warning, you will have no data loss. If you turn it off, you run the risk of losing data if you lose power. Chris This problem is addressed by file system (fsck, journalling etc.). Is it reasonable to handle it directly within application? Regards, E.R. _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(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
Re: [HACKERS] win32 performance - fsync question
On Thu, 17 Feb 2005 17:54:38 +0300 (MSK) E.Rodichev [EMAIL PROTECTED] wrote: On Thu, 17 Feb 2005, Christopher Kings-Lynne wrote: The general question is - does PostgreSQL really need fsync? I suppose it is a question for design, not platform-specific one. It sounds like only one scenario, when fsync is useful, is to interprocess communication via open file. But PostgreSQL utilize IPC for this, so does fsync is really required? NO! Fsync is so that when your computer loses power without warning, you will have no data loss. If you turn it off, you run the risk of losing data if you lose power. Chris This problem is addressed by file system (fsck, journalling etc.). Is it reasonable to handle it directly within application? NO again! Fsck only fixes up file system pointers after a crash. If the data did not make it to the disk, no amount of fscking will put it there. I'm not positive but I think that journalled file systems also need fsync to guarantee that the information gets journalled but in any case, journalling only helps if you have a journalled file system. Not everyone does. This is not to say that fsync is always required, just that it solves a different problem than all those other tools. -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] win32 performance - fsync question
E.Rodichev [EMAIL PROTECTED] writes: On Thu, 17 Feb 2005, Christopher Kings-Lynne wrote: Fsync is so that when your computer loses power without warning, you will have no data loss. If you turn it off, you run the risk of losing data if you lose power. Chris This problem is addressed by file system (fsck, journalling etc.). Is it reasonable to handle it directly within application? No, it's not addressed by the file system. fsync() tells the OS to make sure the data is on disk. Without that, the OS is free to just keep the WAL data in memory cache, and a power failure could cause data from committed transactions to be lost (we don't report commit success until fsync() tells us the file data is on disk). -Doug ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] win32 performance - fsync question
E.Rodichev wrote: This problem is addressed by file system (fsck, journalling etc.). Is it reasonable to handle it directly within application? In the words of the Duke of Wellington, If you believe that you'll believe anything. Please review past discussions on the mailing lists on this point. BTW, most journalling file systems do not guarantee file integrity, only file metadata integrity. In particular, I believe this is tru of NTFS (and whether it even does that has been debated). So by all means turn off fsync if you want the performance gain *and* you accept the risk. But if you do, don't come crying later that your data has been lost or corrupted. (the results are interesting, though - with fsync off Windows and Linux are in the same performance ballpark.) cheers andrew ---(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
Re: [HACKERS] win32 performance - fsync question
In [EMAIL PROTECTED], on 02/17/05 at 10:21 AM, Andrew Dunstan [EMAIL PROTECTED] said: E.Rodichev wrote: This problem is addressed by file system (fsck, journalling etc.). Is it reasonable to handle it directly within application? In the words of the Duke of Wellington, If you believe that you'll believe anything. Please review past discussions on the mailing lists on this point. BTW, most journalling file systems do not guarantee file integrity, only file metadata integrity. In particular, I believe this is tru of NTFS (and whether it even does that has been debated). So by all means turn off fsync if you want the performance gain *and* you accept the risk. But if you do, don't come crying later that your data has been lost or corrupted. (the results are interesting, though - with fsync off Windows and Linux are in the same performance ballpark.) cheers andrew In anything I've done, Windows is very slow when you use fsync or the Windows API equivalent. If you need the performance, you had better have the machine hooked up to a UPS (probably a good idea in any case) and set up something that is triggered by the UPS running down to signal postgreSQL to do an immediate shutdown. -- --- [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] win32 performance - fsync question
So by all means turn off fsync if you want the performance gain *and* you accept the risk. But if you do, don't come crying later that your data has been lost or corrupted. (the results are interesting, though - with fsync off Windows and Linux are in the same performance ballpark.) Yes, this is definitly interesting. It confirms Merlins signs of I/O being what kills the win32 version. IPC etc is a bit slower, but not significantly. In anything I've done, Windows is very slow when you use fsync or the Windows API equivalent. This is what we have discovered. AFAIK, all other major databases or other similar apps (like exchange or AD) all open files with FILE_FLAG_WRITE_THROUGH and do *not* use fsync. It might give noticably better performance with an O_DIRECT style WAL logging at least. But I'm unsure if the current code for O_DIRECT works on win32 - I think it needs some fixing for that. Which might be worth looking at for 8.1. Not much to do about the bgwriter, the way it is designed it *has* to fsync during checkpoint. The Other Databases implement their own cache and write data files directly also, but pg is designed to have the OS cache helping out. Bypassing it would not be good for performance. If you need the performance, you had better have the machine hooked up to a UPS (probably a good idea in any case) and set up something that is triggered by the UPS running down to signal postgreSQL to do an immediate shutdown. UPS will not help you. UPS does not help you if the OS crashes (hey, yuo're on windows, this *does* happen). UPS does not help you if somebody accidentally pulls the plug between the UPS and the server. UPS does not help you if your server overheats and shuts down. Bottom line, there are lots of cases when an UPS does not help. Having an UPS (preferrably redundant UPSes feeding redundant power supplies - this is not at all expensive today) is certainly a good thing, but it is *not* a replacement for fsync. On *any* platform. //Magnus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] win32 performance - fsync question
This is what we have discovered. AFAIK, all other major databases or other similar apps (like exchange or AD) all open files with FILE_FLAG_WRITE_THROUGH and do *not* use fsync. It might give noticably better performance with an O_DIRECT style WAL logging at least. But I'm unsure if the current code for O_DIRECT works on win32 - I think it needs some fixing for that. Which might be worth looking at for 8.1. Doesn't Windows support O_SYNC (or even better O_DSYNC) flag to open()? That should be the Posixy spelling of FILE_FLAG_WRITE_THROUGH, if the latter means what I suppose it does. They should, but someone said it didn't work. I haven't followed up on it, though, so it is quite possible it works. If so, it is definitly worth trying. Not much to do about the bgwriter, the way it is designed it *has* to fsync during checkpoint. Theoretically at least, the fsync during checkpoints should not be a performance killer. If you run a tight benchmark past a checkpoint, it will make an effect if the fsync takes twice as long as it does on unix. If the checkpoint happens when other I/O is fairly low then it shuold not have an effect. Merlin, was that by any chance you? We've been talking about these things quite a lot :-) So: try O_SYNC instead of fsync for WAL, ie, wal_sync_method = open_sync or open_datasync. Definitly worth cehcking out. //Magnus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] win32 performance - fsync question
Magnus Hagander [EMAIL PROTECTED] writes: This is what we have discovered. AFAIK, all other major databases or other similar apps (like exchange or AD) all open files with FILE_FLAG_WRITE_THROUGH and do *not* use fsync. It might give noticably better performance with an O_DIRECT style WAL logging at least. But I'm unsure if the current code for O_DIRECT works on win32 - I think it needs some fixing for that. Which might be worth looking at for 8.1. Doesn't Windows support O_SYNC (or even better O_DSYNC) flag to open()? That should be the Posixy spelling of FILE_FLAG_WRITE_THROUGH, if the latter means what I suppose it does. Not much to do about the bgwriter, the way it is designed it *has* to fsync during checkpoint. Theoretically at least, the fsync during checkpoints should not be a performance killer. The issue that's at hand here is fsyncing the WAL, and the reason we need that is (a) to be sure a transaction is committed when we say it is, and (b) to be sure that WAL writes hit disk before associated data file updates do (it's write AHEAD log remember). Direct writes of WAL should be fine. So: try O_SYNC instead of fsync for WAL, ie, wal_sync_method = open_sync or open_datasync. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] win32 performance - fsync question
Things worth experimenting with (these are all untested, so please report any successes): 1) Try reformatting with a cluster size of 8Kb (the pg page size), if you can. 2) Disable the last access time (like noatime on linux). fsutil behavior set disablelastaccess 1 3) Disable 8.3 filenames fsutil behavior set disable8dot3 1 2 and 3 may require a reboot. (2 and 3 can be done on earlier windows through registry settings only, in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem) I've repeated the test under 2 and 3 - no noticeable difference. With disablelastaccess I got about 10% - 15% better results, but it is not too significant. Actually, that's enough to care about in a real world deployment. Finally I tried fsync = false and got 580-620 tps. So, the short summary: WinXP fsync = true 20-28 tps WinXP fsync = false 600 tps Linux 800 tps This Linux figure is really compared to the WinXP fsync=false, since you have write cacheing on. The interesting one to compare with is the other one you did: Linux w/o write cache80-90 tps Which is still faster than windows, but not as much faster. The general question is - does PostgreSQL really need fsync? I suppose it is a question for design, not platform-specific one. It sounds like only one scenario, when fsync is useful, is to interprocess communication via open file. But PostgreSQL utilize IPC for this, so does fsync is really required? No, fsync is used to make sure your data is committed to disk once you commit a transaction. IPC is handled through shared memory and named pipes. //Magnus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] win32 performance - fsync question
On Thu, 17 Feb 2005, Andrew Dunstan wrote: (the results are interesting, though - with fsync off Windows and Linux are in the same performance ballpark.) Some addition: WinXP fsync = true 20-28 tps WinXP fsync = false 600 tps Linux fsync = true 800 tps Linux fsync = false 980 tps Regards, E.R. _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] win32 performance - fsync question
Doesn't Windows support O_SYNC (or even better O_DSYNC) flag to open()? That should be the Posixy spelling of FILE_FLAG_WRITE_THROUGH, if the latter means what I suppose it does. They should, but someone said it didn't work. I haven't followed up on it, though, so it is quite possible it works. If so, it is definitly worth trying. Update on that. There is no O_SYNC nor O_DSYNC. They just aren't there. However, we already have win32_open (in port/open.c) which is used to open these files. We could probably add code there to check for O_SYNC and map it to the correct win32 flags for CreateFile (because the support certainly is there). To make this happen, is it enough to define O_DSYNC in the win32 port include file, and then implement it in the open call? Or do I need to hack xlog.c? The comment claims it's hackery ;-), so I figured I should verify that before actually testing things. Oh, and finally. The win32 commands have the following options: FILE_FLAG_NO_BUFFERING. This disables the cache completely. It also has lots of limits, like every read and write has to be on a sector boundary etc. It gives great performance with async I/O, because it bypasses the memory manager. It appears to be like O_DIRECT on linux? FILE_FLAG_WRITE_THROUGH: Instructs the system to write through any intermediate cache and go directly to disk. If FILE_FLAG_NO_BUFFERING is not also specified, so that system caching is in effect, then the data is written to the system cache, but is flushed to disk without delay. If FILE_FLAG_NO_BUFFERING is also specified, so that system caching is not in effect, then the data is immediately flushed to disk without going through the system cache. The operating system also requests a write-through the hard disk cache to persistent media. However, not all hardware supports this write-through capability. It seems to me FILE_FLAG_NO_BUFFERING is the same as O_DSYNC. (A different place in the docs says Also, the file metadata may still be cached. To flush the metadata to disk, use the FlushFileBuffers function., so it seems it's more DSYNC than SYNC) //Magnus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] win32 performance - fsync question
Doesn't Windows support O_SYNC (or even better O_DSYNC) flag to open()? That should be the Posixy spelling of FILE_FLAG_WRITE_THROUGH, if the latter means what I suppose it does. They should, but someone said it didn't work. I haven't followed up on it, though, so it is quite possible it works. If so, it is definitly worth trying. Yes, and the other issue is that FlushFileBuffers() does not play nice with raid controllers, it actually overrides their write caching so that you can not get around the fsync performance issue using raid + bbu on most configurations. Not much to do about the bgwriter, the way it is designed it *has* to fsync during checkpoint. Theoretically at least, the fsync during checkpoints should not be a performance killer. I agree: it's the WAL sync that is the problem. I don't mind a slower sync during checkpoint because that is controllable. However, there is also the raid issue. If you run a tight benchmark past a checkpoint, it will make an effect if the fsync takes twice as long as it does on unix. If the checkpoint happens when other I/O is fairly low then it shuold not have an effect. Merlin, was that by any chance you? We've been talking about these things quite a lot :-) So: try O_SYNC instead of fsync for WAL, ie, wal_sync_method = open_sync or open_datasync. Definitly worth cehcking out. Yeah. Merlin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] win32 performance - fsync question
Magnus Hagander [EMAIL PROTECTED] writes: Oh, and finally. The win32 commands have the following options: FILE_FLAG_NO_BUFFERING. This disables the cache completely. It also has lots of limits, like every read and write has to be on a sector boundary etc. It gives great performance with async I/O, because it bypasses the memory manager. It appears to be like O_DIRECT on linux? FILE_FLAG_WRITE_THROUGH: Instructs the system to write through any intermediate cache and go directly to disk. If FILE_FLAG_NO_BUFFERING is not also specified, so that system caching is in effect, then the data is written to the system cache, but is flushed to disk without delay. If FILE_FLAG_NO_BUFFERING is also specified, so that system caching is not in effect, then the data is immediately flushed to disk without going through the system cache. The operating system also requests a write-through the hard disk cache to persistent media. However, not all hardware supports this write-through capability. AFAICS it would make sense for us to specify both of those flags for WAL writes. We could either hack win32_open() to translate O_SYNC to those flags, or make xlog.c aware of the Windows spellings of the flags. Probably the former is less painful given that open.c already does wholesale translations of open() flags. One point that I no longer recall the reasoning behind is that xlog.c doesn't think O_SYNC is a preferable default over fsync. We'd certainly want to hack xlog.c to change its mind about that, at least on Windows; assuming that the FILE_FLAG way is indeed faster. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] win32 performance - fsync question
Some addition: WinXP fsync = true 20-28 tps WinXP fsync = false 600 tps Linux fsync = true 800 tps Linux fsync = false 980 tps Wow, that's terrible on Windows. If there's a solution, it'd be nice to backport it... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] win32 performance - fsync question
There are two different concerns here. 1. transactions loss because of unexpected power loss and/or system failure 2. inconsistent database state For many application (1) is fairly acceptable, and (2) is not. So I'd like to formulate my questions by another way. - if PostgeSQL is running without fsync, and power loss occur, which kind of damage is possible? 1, 2, or both? - it looks like with proper fwrite/fflush policy it is possible to guarantee that only transactions loss may occur, but database keeps some consistent state as before (several) last transactions. Is it true for PostgeSQL? Regards, E.R. e Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] win32 performance - fsync question
WinXP fsync = true 20-28 tps WinXP fsync = false 600 tps Linux fsync = true 800 tps Linux fsync = false 980 tps Wow, that's terrible on Windows. If there's a solution, it'd be nice to backport it... there is. I just rigged up a test benchmark comparing sync methods. I ran on 2 boxes, my xp workstation on 10k raptor and a win2k server on 3ware raid 5 (also on 10k raptors). Workstation: did 1000 FILE_FLAG_WRITE_THROUGH | FILE_FLAG_NO_BUFFERING writes in 5.729633 seconds did 1000 FILE_FLAG_WRITE_THROUGH writes in 0.593322 seconds did 1000 flushfilebuffers writes in 15.898989 seconds server: did 1000 FILE_FLAG_WRITE_THROUGH | FILE_FLAG_NO_BUFFERING writes in 16.501076 seconds did 1000 FILE_FLAG_WRITE_THROUGH writes in 16.104133 seconds did 1000 flushfilebuffers writes in 18.962439 seconds server after running super altra secret dskcache '+p' mode: did 1000 FILE_FLAG_WRITE_THROUGH | FILE_FLAG_NO_BUFFERING writes in 0.256574 seconds did 1000 FILE_FLAG_WRITE_THROUGH writes in 2.627602 seconds did 1000 flushfilebuffers writes in 15.290967 seconds dskcache.exe is required to enable power protect mode (unbypassing raid conttoller write cache settings) on win2k. enjoy. Merlin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] win32 performance - fsync question
Christopher Kings-Lynne [EMAIL PROTECTED] writes: WinXP fsync = true 20-28 tps WinXP fsync = false 600 tps Linux fsync = true 800 tps Linux fsync = false 980 tps Wow, that's terrible on Windows. If there's a solution, it'd be nice to backport it... Actually, the number that's way out of line there is the Linux w/fsync one. I infer that he's got disk write cache enabled and therefore the transactions aren't really being synced to disk at all. Any claimed TPS rate exceeding your disk drive's rotation rate is a red flag. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] win32 performance - fsync question
Evgeny Rodichev wrote: There are two different concerns here. 1. transactions loss because of unexpected power loss and/or system failure 2. inconsistent database state For many application (1) is fairly acceptable, and (2) is not. So I'd like to formulate my questions by another way. - if PostgeSQL is running without fsync, and power loss occur, which kind of damage is possible? 1, 2, or both? Both. If 1 can happen then 2 can happen. - it looks like with proper fwrite/fflush policy it is possible to guarantee that only transactions loss may occur, but database keeps some consistent state as before (several) last transactions. Is it true for PostgeSQL? No - if fsync is on and the transaction is reported as committed then it should still be there when the power returns. Provided you don't suffer hardware failure you should be able to rely on a committed transaction actually being written to disk. That's what fsync does for you. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] win32 performance - fsync question
WinXP fsync = true 20-28 tps WinXP fsync = false 600 tps Linux fsync = true 800 tps Linux fsync = false 980 tps Wow, that's terrible on Windows. If there's a solution, it'd be nice to backport it... there is. I just rigged up a test benchmark comparing sync methods. I ran on 2 boxes, my xp workstation on 10k raptor and a win2k server on 3ware raid 5 (also on 10k raptors). Workstation: did 1000 FILE_FLAG_WRITE_THROUGH | FILE_FLAG_NO_BUFFERING writes in 5.729633 seconds did 1000 FILE_FLAG_WRITE_THROUGH writes in 0.593322 seconds did 1000 flushfilebuffers writes in 15.898989 seconds server: did 1000 FILE_FLAG_WRITE_THROUGH | FILE_FLAG_NO_BUFFERING writes in 16.501076 seconds did 1000 FILE_FLAG_WRITE_THROUGH writes in 16.104133 seconds did 1000 flushfilebuffers writes in 18.962439 seconds server after running super altra secret dskcache '+p' mode: did 1000 FILE_FLAG_WRITE_THROUGH | FILE_FLAG_NO_BUFFERING writes in 0.256574 seconds did 1000 FILE_FLAG_WRITE_THROUGH writes in 2.627602 seconds did 1000 flushfilebuffers writes in 15.290967 seconds dskcache.exe is required to enable power protect mode (unbypassing raid conttoller write cache settings) on win2k. I draw the following conclusions: 1) Using just FILE_FLAG_WRITE_THROUGH is not enough. It sends it out of the cache, but it returns to the application before the data has hit disk. AFAIK, that's not good enough for us. 2) Using both, we can get a *significant* speed boost. Tom, if you look at all the requirements of FILE_FLAG_NO_BUFFERING on http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/ base/createfile.asp, can you say offhand if the WAL code fulfills them? If it does, we can probably just hack it in win32_open (at least for testing and a possible backpatch). Ifn ot, then we'll need to stuff code in xlog.c. (Specifically, I'm most worried about the memory alignment requirement) //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] win32 performance - fsync question
One point that I no longer recall the reasoning behind is that xlog.c doesn't think O_SYNC is a preferable default over fsync. We'd certainly want to hack xlog.c to change its mind about that, at least on Windows; assuming that the FILE_FLAG way is indeed faster. I also confirmed that the totally un-cached mode in windows (FILE_FLAG_WRITE_THROUGH | FILE_FLAG_NO_BUFFERING) will only work if the amount of data written is some multiple of 512 bytes. Can WAL work under this restriction? Merlin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] win32 performance - fsync question
Magnus Hagander [EMAIL PROTECTED] writes: Tom, if you look at all the requirements of FILE_FLAG_NO_BUFFERING on http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/ base/createfile.asp, can you say offhand if the WAL code fulfills them? If I'm reading it right, you are referring to: File access must begin at byte offsets within the file that are integer multiples of the volume's sector size. File access must be for numbers of bytes that are integer multiples of the volume's sector size. For example, if the sector size is 512 bytes, an application can request reads and writes of 512, 1024, or 2048 bytes, but not of 335, 981, or 7171 bytes. Buffer addresses for read and write operations should be sector aligned (aligned on addresses in memory that are integer multiples of the volume's sector size). Depending on the disk, this requirement may not be enforced. 1 and 2 should be no problem since we only read or write integral pages (8K). 3 is a bit bogus IMHO, or even a lot bogus. You can set ALIGNOF_BUFFER in src/include/pg_config_manual.h to whatever you think the alignment requirement really needs to be (I'd try 512). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] win32 performance - fsync question
On Thu, 17 Feb 2005, Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: WinXP fsync = true 20-28 tps WinXP fsync = false 600 tps Linux fsync = true 800 tps Linux fsync = false 980 tps Wow, that's terrible on Windows. If there's a solution, it'd be nice to backport it... Actually, the number that's way out of line there is the Linux w/fsync one. I infer that he's got disk write cache enabled and therefore the transactions aren't really being synced to disk at all. Any claimed TPS rate exceeding your disk drive's rotation rate is a red flag. Write cache is enabled under Linux by default all the time I make deal with it (since 1993). It doesn't interfere with fsync(), as linux kernel uses cache flush for fsync. I have 2.6.10 kernel running *without* any additional patches, and without any specific hdparm settings. fsync() really works fine as I switch off my notebook everyday 2-3 times, and never had any data loss :) Related staff from dmesg is hda: cache flushes supported Regards, E.R. _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] win32 performance - fsync question
Magnus Hagander [EMAIL PROTECTED] writes: Tom, if you look at all the requirements of FILE_FLAG_NO_BUFFERING on http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/ base/createfile.asp, can you say offhand if the WAL code fulfills them? If I'm reading it right, you are referring to: File access must begin at byte offsets within the file that are integer multiples of the volume's sector size. File access must be for numbers of bytes that are integer multiples of the volume's sector size. For example, if the sector size is 512 bytes, an application can request reads and writes of 512, 1024, or 2048 bytes, but not of 335, 981, or 7171 bytes. Buffer addresses for read and write operations should be sector aligned (aligned on addresses in memory that are integer multiples of the volume's sector size). Depending on the disk, this requirement may not be enforced. 1 and 2 should be no problem since we only read or write integral pages (8K). 3 is a bit bogus IMHO, or even a lot bogus. You can set ALIGNOF_BUFFER in src/include/pg_config_manual.h to whatever you think the alignment requirement really needs to be (I'd try 512). After multiple runs on different blocksizes( a few anomalous results aside), I didn't see a whole lot of difference between FILE_FLAG_NO_BUFFERING being on or off for writing performance. However, with NO_BUFFERING set, the file is not *read* cached at all. While the performance is on not terrible for reads, some careful consideration would have to be given for using it outside of WAL. For WAL, though, it seems perfect. If my results are to be believed, we can expect up to a 30 yes, that's three + zero times faster sync performance by ditching FlushFileBuffers (although probably far less in practice). Applying FILE_FLAG_WRITE_THROUGH to non WAL data files will give similar speedups to checkpoints, but right now I'm making no assumptions about the safety issue. I'd like to point out here that using the FlushFileBuffers() sync approach it was impossible to get my 3ware raid controller to cache the writes at all. This means that unless we change the sync method for data files, win32 will always have horrible checkpoint performance (and I do mean horrible). My suggestion would be to FILE_FLAG_NO_BUFFERING | FILE_FLAG_WRITE_THROUGH for WAL, and FILE_FLAG_WRITE_THROUGH for everything else. Then it's time to power-fail test etc. and make sure things work the way they are supposed to. By the way, by some quirk of fate, 8k seems to be a fairly good choice of block size. 4k block sizes give slightly lower latency but not nearly as much throughput. Merlin ---(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
Re: [HACKERS] win32 performance - fsync question
Evgeny Rodichev [EMAIL PROTECTED] writes: Any claimed TPS rate exceeding your disk drive's rotation rate is a red flag. Write cache is enabled under Linux by default all the time I make deal with it (since 1993). You're playing with fire. fsync() really works fine as I switch off my notebook everyday 2-3 times, and never had any data loss :) Given that it's a notebook, it's possible that the hardware is smart enough not to power down the disk until the disk is done writing everything it's cached. Do you care to try some experiments with pulling out the battery while Postgres is busy making updates? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] win32 performance - fsync question
After multiple runs on different blocksizes( a few anomalous results aside), I didn't see a whole lot of difference between FILE_FLAG_NO_BUFFERING being on or off for writing performance. However, with NO_BUFFERING set, the file is not *read* cached at all. While the performance is on not terrible for reads, some careful consideration would have to be given for using it outside of WAL. For WAL, though, it seems perfect. If my results are to be believed, we can expect up to a 30 yes, that's three + zero times faster sync performance by ditching FlushFileBuffers (although probably far less in practice). Yes, for WAL it won't blow away read-cache stuff, since we normally don't expect to read the data that's in WAL. Is there actually a reason why we don't use O_DIRECT on Unix? From what I can tell, O_SYNC does the write through but also puts it in the cache, whereas O_DIRECT doesn't waste cache on it? I was thinking of using O_DIRECT as the compatibility flag for the combination of FILE_FLAG_WRITE_THROUGH and NO_BUFFERING, and using O_SYNC for just the WRITE_THROUGH. Reasonable? //Magnus ---(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
Re: [HACKERS] win32 performance - fsync question
Magnus Hagander [EMAIL PROTECTED] writes: Is there actually a reason why we don't use O_DIRECT on Unix? Portability, or rather the complete lack of it. Stuff that isn't in the Single Unix Spec is a hard sell. regards, tom lane ---(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
Re: [HACKERS] win32 performance - fsync question
Evgeny Rodichev wrote: Write cache is enabled under Linux by default all the time I make deal with it (since 1993). It doesn't interfere with fsync(), as linux kernel uses cache flush for fsync. The problem is that most IDE drives lie (or perhaps you could say the specification is ambiguous) about completion of the cache-flush command -- they say Yeah, I've flushed when they have not actually written the data to the media and have no provision for making sure it will get there in the event of power failure. So Linux is indeed doing a cache flush on fsync, but the hardware is not behaving as expected. By turning off the write-cache on the disk via hdparm, you manage to get the hardware to behave better. The kernel is caching anyway, so the loss of the drive's write cache doesn't make a big difference. There was some work done for better IDE write-barrier support (related to TCQ/SATA support?) in the kernel, but I'm not sure how far that has progressed. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] win32 performance - fsync question
Magnus Hagander [EMAIL PROTECTED] writes: Is there actually a reason why we don't use O_DIRECT on Unix? Portability, or rather the complete lack of it. Stuff that isn't in the Single Unix Spec is a hard sell. Well, how about this (ok, maybe I'm way out in left field): Change fsync option from on/off to on/off/O_SYNC. On win32 we treat O_SYNC as opened with FILE_FLAG_WRITE_THROUGH. When we are in O_SYNC mode, all files, WAL or otherwise, are assumed to be synced when written and are therefore not synced during pg_fsync(). WAL syncing may of course be overridden using alternate sync methods in postgresql.conf. I suspect that this will drastically alter windows performance, especially on raid systems. What is TBD is the safety aspect. What I like about this that now are not dealing with a win32-only hack, any unix system now has another performance setting top play with. We also don't touch the O_DIRECT flag (on win32: FILE_FLAG_WRITE_THROUGH | FILE_FLAG_NO_BUFFERING) leaving that can of worms for another day. Under normal situations, we would expect O_SYNCing everything all the time to slow stuff down, especially during checkpoints, but it might actually help on a caching raid controller. On win32, it will help because the performance of fsync() sucks so horribly, even or raid. Merlin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] win32 performance - fsync question
Oliver Jowett [EMAIL PROTECTED] writes: So Linux is indeed doing a cache flush on fsync Actually I think the root of the problem was precisely that Linux does not issue any sort of cache flush commands to drives on fsync. There was some talk on linux-kernel of what how they could take advantage of new ATA features planned on new SATA drives coming out now to solve this. But they didn't seem to think it was urgent or worth the performance hit of doing a complete cache flush. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] win32 performance - fsync question
Greg Stark wrote: Oliver Jowett [EMAIL PROTECTED] writes: So Linux is indeed doing a cache flush on fsync Actually I think the root of the problem was precisely that Linux does not issue any sort of cache flush commands to drives on fsync. There was some talk on linux-kernel of what how they could take advantage of new ATA features planned on new SATA drives coming out now to solve this. But they didn't seem to think it was urgent or worth the performance hit of doing a complete cache flush. Oh, ok. I haven't really kept up to date with it; I just run with write-cache disabled on my IDE drives as a matter of course. I did see this: http://www.ussg.iu.edu/hypermail/linux/kernel/0304.1/0471.html which implies you're never going to get an implementation that is safe across all IDE hardware :( -O ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] win32 performance - fsync question
On Thu, 17 Feb 2005, Tom Lane wrote: Evgeny Rodichev [EMAIL PROTECTED] writes: Any claimed TPS rate exceeding your disk drive's rotation rate is a red flag. Write cache is enabled under Linux by default all the time I make deal with it (since 1993). You're playing with fire. Yes. I'm lucky in this play :) More seriously, we (with Oleg Bartunov) investigated many platforms/OS for commercial, scientific and other applications during past 10-12 years. I suppose, virtually all excluding modern mainframes. For reliability Linux + PostreSQL was found the best one (including the environment with very frequent unexpected power-off, as at some astronomical observatories at high mountains). Hence, I'm lucky :) fsync() really works fine as I switch off my notebook everyday 2-3 times, and never had any data loss :) Given that it's a notebook, it's possible that the hardware is smart enough not to power down the disk until the disk is done writing everything it's cached. Do you care to try some experiments with pulling out the battery while Postgres is busy making updates? Yes, you are exactly right. All modern HDDs (not entry level ones) has a huge cache (at device, not at controller), and provide the safe hardware flush of cache *after* power off (thanks capacitors). My HDD has 16MB cache, and it is the reason for excellent performance. Regards, E.R. _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] win32 performance - fsync question
On Fri, 18 Feb 2005, Oliver Jowett wrote: Evgeny Rodichev wrote: Write cache is enabled under Linux by default all the time I make deal with it (since 1993). It doesn't interfere with fsync(), as linux kernel uses cache flush for fsync. The problem is that most IDE drives lie (or perhaps you could say the specification is ambiguous) about completion of the cache-flush command -- they say Yeah, I've flushed when they have not actually written the data to the media and have no provision for making sure it will get there in the event of power failure. Yes, I agree. But in my real SA practice I've met 50-100 times the situation when HDD were unexpectedly physically corrupted (the heads touch a surface), without possibility to restore. And I never met any corruption because of possible hardware lie. So Linux is indeed doing a cache flush on fsync, but the hardware is not behaving as expected. By turning off the write-cache on the disk via hdparm, you manage to get the hardware to behave better. The kernel is caching anyway, so the loss of the drive's write cache doesn't make a big difference. Again, in practice, it is different. FreeBSD had a true flush (at least 2-3 yeas ago, not sure about the modern versions), and for write-intensive applications it was a bit slower (comparing with linux), but it never was more reliable (since 1996, at least). Another practical example is Google :) Isn't reliable? There was some work done for better IDE write-barrier support (related to TCQ/SATA support?) in the kernel, but I'm not sure how far that has progressed. Yes, but IMHO it is not stable enough at the moment. Regards, E.R. _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] win32 performance - fsync question
On Fri, 17 Feb 2005, Greg Stark wrote:
Oliver Jowett [EMAIL PROTECTED] writes:
So Linux is indeed doing a cache flush on fsync
Actually I think the root of the problem was precisely that Linux does not
issue any sort of cache flush commands to drives on fsync.
No, it does. Let's try the simplest test:
for (i = 0; i LEN; i++) {
write (fd, buf, 512);
if (sync) fsync (fd);
}
with sync = 0 and 1, and you'll see the difference.
There was some talk
on linux-kernel of what how they could take advantage of new ATA features
planned on new SATA drives coming out now to solve this. But they didn't seem
to think it was urgent or worth the performance hit of doing a complete cache
flush.
It was a bit different topic.
Regards,
E.R.
_
Evgeny Rodichev Sternberg Astronomical Institute
email: [EMAIL PROTECTED] Moscow State University
Phone: 007 (095) 939 2383
Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] win32 performance - fsync question
Magnus Hagander [EMAIL PROTECTED] news:[EMAIL PROTECTED] This is what we have discovered. AFAIK, all other major databases or other similar apps (like exchange or AD) all open files with FILE_FLAG_WRITE_THROUGH and do *not* use fsync. It might give noticably better performance with an O_DIRECT style WAL logging at least. But I'm unsure if the current code for O_DIRECT works on win32 - I think it needs some fixing for that. Which might be worth looking at for 8.1. UPS will not help you. UPS does not help you if the OS crashes (hey, yuo're on windows, this *does* happen). UPS does not help you if somebody accidentally pulls the plug between the UPS and the server. UPS does not help you if your server overheats and shuts down. Bottom line, there are lots of cases when an UPS does not help. Having an UPS (preferrably redundant UPSes feeding redundant power supplies - this is not at all expensive today) is certainly a good thing, but it is *not* a replacement for fsync. On *any* platform. //Magnus Oracle9 and SQL Server 2000 use this flag. Some comments on the lost-data-concern about FILE_FLAG_WRITE_THROUGH: (1) Assume you just use ordinary SCSI disks with write back cache on - you will lost your data if the server suddently lost power; you will *not* lost your data when OS crashes, server reset or whatever only if the server has the power; This has been verified with Oracle9 and SQL Server 2000. (2) Turn off write back cache in disks, you will not lost data, but you will see your performance decreased; (3) If you use some advanced expensive disks like the battery-equipped ones, then you can safely enable write back cache; So UPS is useful for ordinary SCSI disks when write back cache is enabled, but make sure don't let somebody accidentally pulls the plug between the UPS and the server this unfortunate thing happen. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] win32 performance - fsync question
Evgeny Rodichev [EMAIL PROTECTED] writes:
No, it does. Let's try the simplest test:
for (i = 0; i LEN; i++) {
write (fd, buf, 512);
if (sync) fsync (fd);
}
with sync = 0 and 1, and you'll see the difference.
Uh, I'm sure you'll see a difference, one will be limited by the i/o
throughput the IDE interface is capable of, the other will be limited purely
by the memory bandwidth and kernel syscall latency.
Try it with sync=1 and write caching disabled on your IDE drive and you should
see an even larger difference.
However, no filesystem and ide driver combination in linux 2.4 and afaik none
in 2.6 either issue any special ATA commands to force the drive to
There was some talk on linux-kernel of what how they could take advantage
of new ATA features planned on new SATA drives coming out now to solve
this. But they didn't seem to think it was urgent or worth the performance
hit of doing a complete cache flush.
It was a bit different topic.
Well no way to tell if we're talking about the same threads. But in the
discussion I saw it was clear they were talking about adding an interface to
drivers so for filesystems to issue cache flushes when necessary to guarantee
filesystem integrity. They still didn't seem to get that users cared about
their data too, not just filesystem integrity.
--
greg
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[HACKERS] win32 performance - fsync question
Hi, looking for the way how to increase performance at Windows XP box, I found the parameters #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync I have no idea how it works with win32. May I try fsync = false, or it is dangerous? Which of wal_sync_method may I try at WinXP? Regards, E.R. _ Evgeny Rodichev Sternberg Astronomical Institute email: [EMAIL PROTECTED] Moscow State University Phone: 007 (095) 939 2383 Fax: 007 (095) 932 8841 http://www.sai.msu.su/~er ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] win32 performance - fsync question
looking for the way how to increase performance at Windows XP box, I found the parameters #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync I have no idea how it works with win32. May I try fsync = false, or it is dangerous? Which of wal_sync_method may I try at WinXP? wal_sync_method does nothing on XP. The fsync option will tremendously increase performance on writes at the cost of possible data corruption in the event of a expected server power down. The main performance difference between win32 and various unix systems is that fsync() takes much longer on win32 than linux. Merlin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
