Stuff below relates to IDE drives. On Linux, the fsync() call doesn't actually force that the data reaches the physical disk platters. It just makes sure that the data is sent to the cache on the disk. On Windows, FlushFileBuffers() forces the disk to actually write the data to the physical disk.
This explains why sqlite on Linux is at least 5 times faster than on Windows. It also means that Sqlite is not ACID when using Linux with IDE drives. On MacOSX, There is the F_FULLFSYNC option, which forces the data to the actual disk platters. However from reading discussion boards, it seems like people generally don't feel that the added security of F_FULLFSYNC is worth the performance hit. (see here for instance http://www.mail-archive.com/sqlite-users@sqlite.org/msg07685.html and also this thread http://lists.apple.com/archives/darwin-dev/2005/Feb/msg00087.html). In Sqlite 3.1.4 an option was added to disable F_FULLFSYNC and just using fsync() in MacOSX. For Windows, the only options are: * FlushFileBuffers() i.e. F_FULLFSYNC, which gives bad performance. * no FlushFileBuffers(), ie. pragma SYNCHRONOUS=off;, which doesn't even force it down to the disk's cache. I believe there should be a third option that forces it down to the disk's cache, but doesn't actually wait until it reaches the disk platters. This would give a performance on Windows similar to the performance on Linux. It can be implemented with the FILE_FLAG_WRITE_THROUGH option to CreateFile(), and a buffering system on top of WriteFile would probably be a good idea to minimize the number of calls to WriteFile. /Ludvig
