431863 - Enable fullfsync for storage on the mac

Status: RESOLVED WONTFIX

(Core :: SQLite and Embedded Database Bindings, defect)

Description

[Shawn Wilsher :sdwilsh]

From the fsync man page on 10.5:
     Note that while fsync() will flush all data from the host to the drive
     (i.e. the "permanent storage device"), the drive itself may not physi-
     cally write the data to the platters for quite some time and it may be
     written in an out-of-order sequence.

     Specifically, if the drive loses power or the OS crashes, the application
     may find that only some or none of their data was written.  The disk
     drive may also re-order the data so that later writes may be present,
     while earlier writes are not.

     This is not a theoretical edge case.  This scenario is easily reproduced
     with real world workloads and drive power failures.

     For applications that require tighter guarantees about the integrity of
     their data, Mac OS X provides the F_FULLFSYNC fcntl.  The F_FULLFSYNC
     fcntl asks the drive to flush all buffered data to permanent storage.
     Applications, such as databases, that require a strict ordering of writes
     should use F_FULLFSYNC to ensure that their data is written in the order
     they expect.  Please see fcntl(2) for more detail.

SQLite can do this trivially too - http://www.sqlite.org/pragma.html#pragma_fullfsync

Comment 1

[Mike Shaver (:shaver emeritus)]

I do not recommend that we block on this, or indeed take it this late.  I don't think we understand what the performance effects are, and we're already hurting from making our I/O synchronous.

What is sqlite's behaviour non-fullfsync behaviour in the power-failure cases above, for our workloads?  Do we get corruption, or just loss of the last few records of data that we thought made it to the journal?

Comment 2

[Mike Shaver (:shaver emeritus)]

Meant to minus, but renom if you really believe that we would delay FF3 long enough to appropriately test the perf and other effects of such a change, if it were the only thing left on the list.

Comment 3

[Shawn Wilsher :sdwilsh]

(In reply to comment #1)
> What is sqlite's behaviour non-fullfsync behaviour in the power-failure cases
> above, for our workloads?  Do we get corruption, or just loss of the last few
> records of data that we thought made it to the journal?
Short answer: database corruption could result.

Long answer:
fsync explicitly states that the data may not make it out to disk, and sqlite makes some assumptions:

SQLite assumes that the operating system will buffer writes and that a write request will return before data has actually been stored in the mass storage device. SQLite further assumes that write operations will be reordered by the operating system. For this reason, SQLite does a "flush" or "fsync" operation at key points. SQLite assumes that the flush or fsync will not return until all pending write operations for the file that is being flushed have completed. We are told that the flush and fsync primitives are broken on some versions of windows and Linux. This is unfortunate. It opens SQLite up to the possibility of database corruption following a power loss in the middle of a commit. However, there is nothing that SQLite can do to test for or remedy the situation. SQLite assumes that the operating system that it is running on works as advertised. If that is not quite the case, well then hopefully you will not lose power too often.

There are a whole lot more details in the sqlite docs with regards to this here - http://www.sqlite.org/atomiccommit.html

Comment 4

[D. Richard Hipp]

We added the fullsync option at Apple's request.  But it turns out that they don't use it themselves.  The fullsync requires the OS to reset the disk controller, so it causes a significant performance impact, not only to the application using SQLite, but all other processes running on the machine that happen to be using the disk drive.

It is true that the fsync() system call does not work as advertised on MacOS (nor Linux) but in practice this does not normally cause too much trouble.  The data is stored in the disk drives track buffers and it normally makes it to oxide without too much delay.  My understanding is that problems caused by fsync() not working are probably no more common that problems caused by head crashes or other hard disk failures.

I would recommend that you not enable fullsync.  Or if you do, at least make it a configuration option so that users can turn it off if they want.

Comment 5

[Mike Beltzner [:beltzner, not reading bugmail]]

Doesn't block, based on comment 2 and comment 4.

Comment 6

[Shawn Wilsher :sdwilsh]

I'm going to go so far as to saw WONTFIX based on information from comment 4.  Thanks!