2 ms·

I'm very surprised that SQLite trusts fdatasync() when appending to a file, rather than fsync(), given SQLite is written to be so portable and cautious and has

I'm very surprised that SQLite trusts fdatasync() when appending to a file, rather than fsync(), given SQLite is written to be so portable and cautious and has been around a long time. I've always been suspicious that fdatasync() does not promise to durably commit the size metadata (and perhaps indirection blocks) on some OSes/versions, just the data blocks itself. Such that it's only safe to use fdatasync() on already allocated regions of the file. That was my understanding of fdatasync() when I first read about it, and I vaguely recall some DB handled this by rarely appending to files, instead doing it occasionally by pausing writes (to that file), appending a large block of zeros to the file then fsync() to commit, then resuming writes. It's not just me thinking this. From a quick Google: > https://www.percona.com/blog/2018/02/08/fsync-performance-storage-devices/ https://www.percona.com/blog/2018/02/08/fsync-performance-st... > "The fsync system is not the only system call that persists data to disk. There is also the fdatasync call. fdatasync persists the data to disk but does not update the metadata information like the file size and last update time. Said otherwise, it performs one write operation instead of two."

yencabulator 6y ago
For what it's worth, `man fdatasync` on linux says (emphasis mine) fdatasync() is similar to fsync(), but does not flush modified metadata *unless that meta‐ data is needed* in order to allow a subsequent data retrieval to be correctly handled. For example, changes to st_atime or st_mtime (respectively, time of last access and time of last modification; see inode(7)) do not require flushing because they are not neces‐ sary for a subsequent data read to be handled correctly. On the other hand, a change to the file size (*st_size, as made by say ftruncate(2)*), *would require a metadata flush*.
- jlokier 6y ago
  It does say that for Linux now, but it didn't used to, and it doesn't on other OSes. FreeBSD says the opposite at https://www.freebsd.org/cgi/man.cgi?query=fdatasync&sektion=2 https://www.freebsd.org/cgi/man.cgi?query=fdatasync&sektion=...: The fdatasync() system call causes all modified data of fd to be moved to a permanent storage device. Unlike fsync(), the system call does not guarantee that file attributes or metadata necessary to access the file are committed to the permanent storage. I.e. "does not guarantee that ... metadata necessary to access the file are committed". Thus "given SQLite is written to be so portable and cautious and has been around a long time" I'm surprised. I'm not sure if we should believe the FreeBSD documentation, as current OpenBSD, NetBSD and Solaris have statements that work out similar to current Linux. However, if the documentation is to be believed, SQLite's durability using fdatasync() is broken on FreeBSD after a file append. My point really is that if I recall correctly, what FreeBSD's man page says isn't an accident, it's indicative of what used to be the understood guarantee provided by most if not all implementations. Actual guarantees were not exactly specified, and I think there may have been a vague transition in the meaning of fdatasync() from "commits the data blocks" to "commits data blocks and the metadata necessary to access them" over time because the latter is obviously much more useful. But that transition over time is why it seems a risk to assume it in highly portable software. That said, back in the 2.6 days Linux even fsync() wasn't durable on consumer HDDs, it was on SCSI HDDs though. Then for a while O_DSYNC (which is like fdatasync() after every write) was unreliable for data retrieval (see the Persona link in GP post) but O_SYNC worked (which is like fsync() after every write).
  - rhencke 6y ago
    From SQLite's unix.c: /* ** We do not trust systems to provide a working fdatasync(). Some do. ** Others do no. To be safe, we will stick with the (slightly slower) ** fsync(). If you know that your system does support fdatasync() correctly, ** then simply compile with -Dfdatasync=fdatasync or -DHAVE_FDATASYNC */ #if !defined(fdatasync) && !HAVE_FDATASYNC # define fdatasync fsync #endif
    - jlokier 6y ago
      From SQLite's autoconf/configure.ac: AC_CHECK_FUNCS([fdatasync usleep fullfsync localtime_r gmtime_r]) In other words, it automatically uses fdatasync if that's available on any target OS. SQLite's os_unix.c actually confirms my suspicions about fdatasync: /* fdatasync() on HFS+ doesn't yet flush the file size if it changed correctly Given its portability, and the folklore around fdatasync not flushing the file size on other OSes at some time (and the current FreeBSD man page), I'm still surprised the code as written assumes the size issue only exists on OSX.
ayende 6y ago
Note that they talk about opening and calling fdatasync on the parent folder? That is to handle that change in size
- jlokier 6y ago
  No it isn't. That's to commit the directory change, which updates directory entries pointing to the file inode. File creation, renaming and deletion are directory changes, and are unconnected with the file size. As the article says: "open and fsync() the containing directory in or to ensure that the file will still be there following reboot from a power loss". Without the fsync() on the directory, after reboot a newly created file name may not exist at all in the target directory. That's separate from whether the inode size is updated. If you just create the file at its target location, fsync() and then abruptly reboot, it may not be found, yet fsck may find it and put it into /lost+found with the data and size intact, because data and size were committed by the file fsync() while the directory entry was not. Another way to show this is by creating the file elsewhere and using link() or rename(). Then you can see the committed file data at the old name, while the target of link() or rename() may be missing after abrupt reboot. This is why some atomic+durable file write patterns: Open a file under a temporary alternate name in the same directory, write(), fsync() on the file to commit data and metadata to the inode, then rename() to atomically replace the target filename, then fsync() on the directory after rename() to commit the directory change. (Some particularly careful programs go further than that because rename() is not atomic on some network filesystems.) (In the article, there are what appear to be typos where it says fdatasync() on the containing directory in some places and fsync() in others. It should always be fsync() on a directory.)

yencabulator 6y ago

For what it's worth, `man fdatasync` on linux says (emphasis mine) fdatasync() is similar to fsync(), but does not flush modified metadata *unless that meta‐ data is needed* in order to allow a subsequent data retrieval to be correctly handled. For example, changes to st_atime or st_mtime (respectively, time of last access and time of last modification; see inode(7)) do not require flushing because they are not neces‐ sary for a subsequent data read to be handled correctly. On the other hand, a change to the file size (*st_size, as made by say ftruncate(2)*), *would require a metadata flush*.

jlokier 6y ago
It does say that for Linux now, but it didn't used to, and it doesn't on other OSes. FreeBSD says the opposite at https://www.freebsd.org/cgi/man.cgi?query=fdatasync&sektion=2 https://www.freebsd.org/cgi/man.cgi?query=fdatasync&sektion=...: The fdatasync() system call causes all modified data of fd to be moved to a permanent storage device. Unlike fsync(), the system call does not guarantee that file attributes or metadata necessary to access the file are committed to the permanent storage. I.e. "does not guarantee that ... metadata necessary to access the file are committed". Thus "given SQLite is written to be so portable and cautious and has been around a long time" I'm surprised. I'm not sure if we should believe the FreeBSD documentation, as current OpenBSD, NetBSD and Solaris have statements that work out similar to current Linux. However, if the documentation is to be believed, SQLite's durability using fdatasync() is broken on FreeBSD after a file append. My point really is that if I recall correctly, what FreeBSD's man page says isn't an accident, it's indicative of what used to be the understood guarantee provided by most if not all implementations. Actual guarantees were not exactly specified, and I think there may have been a vague transition in the meaning of fdatasync() from "commits the data blocks" to "commits data blocks and the metadata necessary to access them" over time because the latter is obviously much more useful. But that transition over time is why it seems a risk to assume it in highly portable software. That said, back in the 2.6 days Linux even fsync() wasn't durable on consumer HDDs, it was on SCSI HDDs though. Then for a while O_DSYNC (which is like fdatasync() after every write) was unreliable for data retrieval (see the Persona link in GP post) but O_SYNC worked (which is like fsync() after every write).
- rhencke 6y ago
  From SQLite's unix.c: /* ** We do not trust systems to provide a working fdatasync(). Some do. ** Others do no. To be safe, we will stick with the (slightly slower) ** fsync(). If you know that your system does support fdatasync() correctly, ** then simply compile with -Dfdatasync=fdatasync or -DHAVE_FDATASYNC */ #if !defined(fdatasync) && !HAVE_FDATASYNC # define fdatasync fsync #endif
  - jlokier 6y ago
    From SQLite's autoconf/configure.ac: AC_CHECK_FUNCS([fdatasync usleep fullfsync localtime_r gmtime_r]) In other words, it automatically uses fdatasync if that's available on any target OS. SQLite's os_unix.c actually confirms my suspicions about fdatasync: /* fdatasync() on HFS+ doesn't yet flush the file size if it changed correctly Given its portability, and the folklore around fdatasync not flushing the file size on other OSes at some time (and the current FreeBSD man page), I'm still surprised the code as written assumes the size issue only exists on OSX.

ayende 6y ago

Note that they talk about opening and calling fdatasync on the parent folder? That is to handle that change in size

jlokier 6y ago
No it isn't. That's to commit the directory change, which updates directory entries pointing to the file inode. File creation, renaming and deletion are directory changes, and are unconnected with the file size. As the article says: "open and fsync() the containing directory in or to ensure that the file will still be there following reboot from a power loss". Without the fsync() on the directory, after reboot a newly created file name may not exist at all in the target directory. That's separate from whether the inode size is updated. If you just create the file at its target location, fsync() and then abruptly reboot, it may not be found, yet fsck may find it and put it into /lost+found with the data and size intact, because data and size were committed by the file fsync() while the directory entry was not. Another way to show this is by creating the file elsewhere and using link() or rename(). Then you can see the committed file data at the old name, while the target of link() or rename() may be missing after abrupt reboot. This is why some atomic+durable file write patterns: Open a file under a temporary alternate name in the same directory, write(), fsync() on the file to commit data and metadata to the inode, then rename() to atomically replace the target filename, then fsync() on the directory after rename() to commit the directory change. (Some particularly careful programs go further than that because rename() is not atomic on some network filesystems.) (In the article, there are what appear to be typos where it says fdatasync() on the containing directory in some places and fsync() in others. It should always be fsync() on a directory.)