Ayende @ Rahien

Refunds available at head office

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.

Tags:

Posted By: Ayende Rahien

Published at

Originally posted at

Comments

tobi
11/18/2013 11:16 AM by
tobi

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.

Ayende Rahien
11/18/2013 11:20 AM by
Ayende Rahien

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.

Rafal
11/18/2013 11:49 AM by
Rafal

write_through mode was invented to make database developers' life easier, but I'm afraid it's older than Windows or SQL server.

Mark Bryant
11/18/2013 11:50 AM by
Mark Bryant

If it doesn't already exist it could be time to create a dedicated database platform and OS. The Raven Machine.

Ayende Rahien
11/18/2013 12:03 PM by
Ayende Rahien

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.

Ayende Rahien
11/18/2013 12:04 PM by
Ayende Rahien

Mark, There are multiple distros from db providers that provide a custom Linux platform to run their db.

Chris Hynes
11/18/2013 11:23 PM by
Chris Hynes

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.

Thomas Krause
11/19/2013 05:34 PM by
Thomas Krause

What is the use of fsync, if it does not give you any guarantees?

Ayende Rahien
11/19/2013 06:00 PM by
Ayende Rahien

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.

Ayende Rahien
11/19/2013 10:57 PM by
Ayende Rahien

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.

Chris Hynes
11/20/2013 08:55 PM by
Chris Hynes

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.

Comments have been closed on this topic.