Some notes about fsync
On my laptop, fsync has effectively no cost. That is probably because of some configuration setting (it is a battery based system, no need to pay the fsync call). On my desktop machine (significantly more powerful than my laptop), I have an fsync times that are an order of magnitude or more higher.
In practice, if you work with fsync, you can expect to get a maximum of about 200 – 300 fsync calls per second on SSD, and significantly less on HDD. If you are seeing higher numbers than that, you are probably not really doing fsync, and are exposed to data integrity issues if you have a hard crash.
In particular, it appears that for high performance code, you really want to forget all about fsync for ensuring your transactional needs.
And that is before we started talking about the cost of fsync (FlushFileBuffers, to be more accurate) as the file size grows. It appears that there is at least some correlation between the size of the file and the cost of calling fsyn/FlushFileBuffers on it.
Considering that we are talking about potentially very large files, we really want to be careful about it. All in all, I think that we need to say goodbye to relying of fsync for ensuring ACID.
But how can we ensure that we’ll be properly ACID? Well, the answer is in the previous post, look at how other people are doing it, but I’ll expand on that in my next post.
Comments
It appears that Windows is perfectly optimized for the way SQL Server does it (a custom buffer cache and WRITE_THROUGH IOs). Everything else is just there, but not as production-ready.
Tobi, It wouldn't surprise me at all to hear that SQL Server asked and got dedicated APIs for its use. WriteFileGather, for example, seems like a great example.
To be fair, on the Linux side of things, the situation isn't really much different.
write_through mode was invented to make database developers' life easier, but I'm afraid it's older than Windows or SQL server.
If it doesn't already exist it could be time to create a dedicated database platform and OS. The Raven Machine.
Rafal, Write_Through is required for more than just dbs, but they are a major and important user, yes. Note, however, that there are many thins in Windows specifically for SQL Server. For example, Fibers were created for them.
Mark, There are multiple distros from db providers that provide a custom Linux platform to run their db.
Is the laptop/desktop difference based on whether the "Turn off Windows write-cache buffer flushing on the device" checkbox is checked in the drive device properties?
I'm assuming Windows doesn't automatically make that determination. If that's all that's driving it, putting the desktop on an UPS and turning off write-cache buffer flushing may give a big jump in perf.
I've always wondered how much of an effect that has, but never had hard numbers to back it up.
What is the use of fsync, if it does not give you any guarantees?
Fsync does give you guarantees, as long as things like the OS, driver or disk do not lie. And they can and do lie to your face.
Chris, I don't think that this is relevant, I have turned off write cache buffer, and it didn't really change the perf. I think this is the disk driver or even the disk itself lying.
Hmm. Just trying to think what could cause that much perf difference between the two. It doesn't seem right that its a driver/disk issue assuming they are similar technologies, but some sort of setting instead. Maybe Windows knows it has a battery to rely on and does different things? Even that doesn't make sense -- those are usually still manual settings one must set.
Comment preview