Re: [HACKERS] [pgsql-hackers-win32] fsync with sync, and Win32 unlink
I have talked to Tom today and he is willing to implement the discussed method of doing fsync on every file modified between checkpoints, and add unlink handling for open files for Win32. Great news. I'm sure this will benefig Unix platforms as well, when taking into account the discussions previously. snip 3) On checkpoint request, either by the user or postmaster, the background writer will create a subprocess, do a sync(), wait, then do fsync of all files that were marked as dirty. The sync() should flush out most of the dirty files in an optimal manner. Please make a config variable to make this sync() call optional. This goes for Unix as well, and not just win32 (which doesn't support it). Consider either a box with many different postgresql instances, or one that run both postgresql and other software. Issuing sync() in that sitaution will cause sync of a lot of data that probably doesn't need syncing. But it'd probably be a very good thing on a dedicated server, giving the kernel the chance to optimise. //Magnus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-hackers-win32] fsync with sync, and Win32 unlink
Consider either a box with many different postgresql instances, or one that run both postgresql and other software. Issuing sync() in that sitaution will cause sync of a lot of data that probably doesn't need syncing. But it'd probably be a very good thing on a dedicated server, giving the kernel the chance to optimise. It is not like the sync is done every few seconds ! It is currently done every 5 minutes (I actually think this is too frequent now that we have bgwriter, 10 - 20 min would be sufficient). So imho even on a heavily otherwise used system the sync will be better. Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [pgsql-hackers-win32] fsync with sync, and Win32 unlink
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: Consider either a box with many different postgresql instances, or one that run both postgresql and other software. Issuing sync() in that sitaution will cause sync of a lot of data that probably doesn't need syncing. But it'd probably be a very good thing on a dedicated server, giving the kernel the chance to optimise. It is not like the sync is done every few seconds ! It is currently done every 5 minutes (I actually think this is too frequent now that we have bgwriter, 10 - 20 min would be sufficient). So imho even on a heavily otherwise used system the sync will be better. Well, the further apart it is the more dangerous it is to be calling sync... I've seen some pretty severe damage caused by calling sync(2) on a loaded system. The system in question was in the process of copying data to an NFS mounted archival site. When the sync hit basically everything stopped until the buffered network writes could be synced. The live database was basically frozen for a few seconds and the web site nearly crashed. The sysadmin had to send out a notice asking everybody to refrain from using sync until the archival process had completed. Now that's not a common situation, but I think it shows how doing things that cause system-wide effects is unwise. -- 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] [pgsql-hackers-win32] fsync with sync, and Win32 unlink
Greg Stark [EMAIL PROTECTED] writes: I've seen some pretty severe damage caused by calling sync(2) on a loaded system. The system in question was in the process of copying data to an NFS mounted archival site. When the sync hit basically everything stopped until the buffered network writes could be synced. The live database was basically frozen for a few seconds and the web site nearly crashed. The sysadmin had to send out a notice asking everybody to refrain from using sync until the archival process had completed. This seems, um, hard to believe. Did he shut down the standard syncer daemon? I have never seen a Unix system that would allow more than thirty seconds' worth of unwritten buffers to accumulate, and would not care to use one if it existed. regards, tom lane ---(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] [pgsql-hackers-win32] fsync with sync, and Win32 unlink
Tom Lane [EMAIL PROTECTED] writes: This seems, um, hard to believe. Did he shut down the standard syncer daemon? I have never seen a Unix system that would allow more than thirty seconds' worth of unwritten buffers to accumulate, and would not care to use one if it existed. Well it was Solaris so it didn't have the BSD 30s sync style strategy. But this was a large NFS file transfer to another host on a 100Mb/s network. In 30s there could be a lot of writes buffered up. I'm not saying the behaviour was ideal, and I don't know exactly why it interfered with anything else. But I'm not entirely surprised either. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html