26 ms·

PostgreSQL used fsync incorrectly for 20 years

676 points by lelf 8y ago

htfy96 8y ago
> In short, PostgreSQL assumes that a successful call to fsync() indicates that all data written since the last successful call made it safely to persistent storage. But that is not what the kernel actually does. When a buffered I/O write fails due to a hardware-level error, filesystems will respond differently, but that behavior usually includes discarding the data in the affected pages and marking them as being clean. So a read of the blocks that were just written will likely return something other than the data that was written. > Google has its own mechanism for handling I/O errors. The kernel has been instrumented to report I/O errors via a netlink socket; a dedicated process gets those notifications and responds accordingly. This mechanism has never made it upstream, though. Freund indicated that this kind of mechanism would be "perfect" for PostgreSQL, so it may make a public appearance in the near future. https://lwn.net/Articles/752063/ https://lwn.net/Articles/752063/ A real-life example can be found at https://stackoverflow.com/questions/42434872/writing-programs-to-cope-with-i-o-errors-causing-lost-writes-on-linux https://stackoverflow.com/questions/42434872/writing-program...
- metildaa 8y ago
  Sounds like Google is continuing to act poorly when it comes to upstreaming their code.
  - 420codebro 8y ago
    I like to beat on them just like everyone else but what is the expectation? Everything they write if it runs at an OS level?
    - metildaa 8y ago
      Google should be working with the kernel team to get their code mainlined, rather than keep their patches out of tree for years on end.
      - mixmastamyk 8y ago
        Perhaps they tried. Do you have evidence otherwise?
- solidsnack9000 8y ago
  The linked LWN article (from April 2018) is a great summary of the problem and potential solutions and its cause: Ted Ts'o, instead, explained why the affected pages are marked clean after an I/O error occurs; in short, the most common cause of I/O errors, by far, is a user pulling out a USB drive at the wrong time. If some process was copying a lot of data to that drive, the result will be an accumulation of dirty pages in memory, perhaps to the point that the system as a whole runs out of memory for anything else. So those pages cannot be kept if the user wants the system to remain usable after such an event.
  - anarazel 8y ago
    That justification is bogus however. There's already separate logic for the case the entire underlying device vanishes.
    - Dylan16807 8y ago
      Also USB bus resets are not unheard of. Or moving devices from one port to another. If the device comes back within a minute or two you probably shouldn't throw out those writes.
      - 0x0 8y ago
        If someone quickly pulls an usb drive, plugs it in another system, and then plugs it back in to the original system, then flushing writes could cause massive data corruption if those writes are relative to an outdated idea of what's on the block device. Sounds like a misfeature to me
        zozbot123 8y ago
        > If someone quickly pulls an usb drive, plugs it in another system, and then plugs it back in to the original system, then flushing writes could cause massive data corruption That's user error, though. The kernel should react to removable media being pulled by sending a wall message to the appropriate session/console, stating something similar to "Please IMMEDIATELY place media [USB_LABEL] back into drive!!", with [Retry] and [Cancel] options. That way, the user knows what to expect -- OS's used to do this as a matter of course when removable media was in common use. In fact, you could even generalize this, by asking the user to introduce some specific media (identified by label) when some mountpoint is accessed, even if no operations were actually in progress.
- puzzle 8y ago
  Bigtable, and presumably Spanner, goes beyond that. Data chunks have checksums and they're immediately re-read and verified after compactions, because it turns out that errors do happen and corruption also happens, even when you use ECC — roughly once every 5PB compacted, per Jeff Dean figures.
deleted 8y ago
[deleted]
chubot 8y ago
Doesn't this affect all databases? Or is it a different issue? https://www.sqlite.org/atomiccommit.html https://www.sqlite.org/atomiccommit.html 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. Also this seems related: https://danluu.com/file-consistency/ https://danluu.com/file-consistency/ That results in consistent behavior and guarantees that our operation actually modifies the file after it's completed, as long as we assume that fsync actually flushes to disk. OS X and some versions of ext3 have an fsync that doesn't really flush to disk. OS X requires fcntl(F_FULLFSYNC) to flush to disk, and some versions of ext3 only flush to disk if the the inode changed (which would only happen at most once a second on writes to the same file, since the inode mtime has one second granularity), as an optimization. The linked OSDI '14 paper looks good: We find that applications use complex update protocols to persist state, and that the correctness of these protocols is highly dependent on subtle behaviors of the underlying file system, which we term persistence properties. We develop a tool named BOB that empirically tests persistence properties, and use it to demonstrate that these properties vary widely among six popular Linux file systems.
- anarazel 8y ago
  > Doesn't this affect all databases? Yes, most. And several did similar changes (crash-restart -> recovery) to handle it too. It's possible to avoid the issue by using direct IO too, but often that's not the default mode of $database.
- networkimprov 8y ago
  Notably, Go had failed to use F_FULLFSYNC on MacOS until I reported it. Fix landing in 1.12, but won't be back-ported. https://github.com/golang/go/issues/26650 https://github.com/golang/go/issues/26650
wuxb 8y ago
It's a perfect scenario to hear some rants from Linus. While, the "code" has changed and he really has to calm down for this :)
EGreg 8y ago
Have there been any real-world consequences from this, and how can they be prevented? Does MySQL have the same flaw?
- macdice 8y ago
  The original report from Craig Ringer was based on a real system that experienced a failure like this on thin provisioning. MySQL in buffered IO mode surely has the same problem, and has implemented the same solution: PANIC rather than retrying. Same for WiredTiger (MongoDB). Other systems using direct (non buffered) IO are not affected.
- j16sdiz 8y ago
  This can happen when io device have intermittent failure. Bad device or faulty cable.
  - pgaddict 8y ago
    Interestingly enough, such issues are becoming more common. It's not just about devices being less reliable, but e.g. thin provisioning being used more widely etc.
bbunix 8y ago
Historic note (like from the 80's) - any time a machine was rebooted we'd type sync; sync; sync; reboot - the explanation was that the only guarantee was that the second sync wouldn't start until the first sync successfully completed, plus one for good luck...
- yyx 8y ago
  https://utcc.utoronto.ca/~cks/space/blog/unix/TheLegendOfSync https://utcc.utoronto.ca/~cks/space/blog/unix/TheLegendOfSyn... people were told 'do several sync commands, typing each by hand' But this mutated to just 'sync three times', so of course people started writing 'sync; sync; sync'
  - sgt 8y ago
    I still type sync (just once) before I reboot. Just a habit.
    - beefhash 8y ago
      I always get some kind of error message from dmraid about having been unable to stop the RAID array on shutdown/reboot. I thus manually do a sync(1) in hopes that the data survives. Hasn't failed me thus far, at least.
    - slavik81 8y ago
      The only machine I ever developed on that needed a sync before reboot was an ancient SPARC workstation. I forgot to sync almost every time, but it would always boot after a fsck.
    - noir_lord 8y ago
      The last thing I do before I leave my work desktop for the day is ./eod.sh && sync && sync 20+ year old habits die hard.
- Jerry2 8y ago
  I have a flash drive that I sometimes put a video on to watch it on a small TV in the basement and I've noticed that Linux doesn't copy the file right away. The 'cp' does finish quickly but the data is not on the flash drive yet. You either have to eject and wait or sync and wait for it to actually transfer. Needless to say, this tripped me up few times and videos weren't fully transferred.
anarazel 8y ago
I think it's worthwhile to note that this, even before both kernel and postgres changes, really only is an issue if there are very serious storage issues, commonly causing more corruption than just forgetting the data due to be fsynced. The kernel has its own timeout / retry logic and if those retries succeed, there's no issue. Additionally, most filesystems remount read-only if there's any accompanying journaling errors, and in a lot of cases PG IO will also have a metadata effect.
- waz0wski 8y ago
  i/o errors are an ongoing issue for AWS M5 and C5 instance types with their cloudy nvme devices, they have a tendency to randomly disappear or have extended timeouts https://bugs.launchpad.net/ubuntu/+source/linux/+bug/1788035 https://bugs.launchpad.net/ubuntu/+source/linux/+bug/1788035 unfortunately the upstream patches being backported don't provide any real write guarantee
xorgar831 8y ago
The meta point here is that just in OSS folks assume that the code has been reviewed since it's open, but the reality is that unless someone actually does you really don't know. The popularity of a project doesn't mean there aren't fatal flaws.
- fxfan 8y ago
  This is a contrarian view and I will sound phobic but for this very reason- false sense of security + possibility of malicious check ins- I now place less emphasis on open/closed source and more on repute. I am happier to download exe's from good companies these days than a package from arch-aur
- ascar 8y ago
  Personally I assume if a project is at least somewhat popular it works most of the time and frequent/serious bugs are reported and researchable. For that to work I also report or second bugs I encounter. That doesn't mean there aren't any bugs in even the most popular OSS, especially in edge cases and rare scenarios. With closed source though I often don't know the popularty and how many/what kind of bugs have been reported, but just the reputation of the vendor. I prefer a popular software from a highly reputable vendor over a somewhat popular OSS. But I also prefer the most popular and battletested OSS, like postgresql and linux, over any closed system, e.g. SQL Server and Windows.
- loeg 8y ago
  No need to bring out that dead horse for a beating. No one in this thread has yet made the claim that OSS code is flawless; you're picking a fight with a straw man.
  - zapzupnz 8y ago
    It's a genuine issue, and one that is made time and again by people who think "open == security" whenever there's a discussion about something like Google or iMessage, when the armchair security experts come out of the woodwork to promote their favourite whatever-it-is. Sure, it mightn't be made in this thread yet, but that doesn't make it an irrelevant, invalid, or uninteresting observation. I think that the spirit of discussion, so integral to what separates HN from other websites, means we should not poo-poo this line of inquiry just because you're bored of it.
georgebarnett 8y ago
Years ago (2010 iirc), I reported to the Postgres list, with a patch, that (one of) the reason Postgres “didn’t work on NFS” was because it couldn’t deal with short writes. I got told the usual “you’re holding it wrong” instead of an acknowledgement of PG not sticking to the spec. I patched my own systems (wrap the calls in a loop as per standard practice) and then proceeded to run literally hundreds of thousands of PostgreSQL instances on NFS for many more years with no problems. The patch was eventually integrated it seems but I never found out why because I lost interest in trying to work with the community after that experience.
- anarazel 8y ago
  The relevant thread: https://www.postgresql.org/message-id/46ED7555-9383-45D2-A5C0-6373BE2FDCB6%40atlassian.com https://www.postgresql.org/message-id/46ED7555-9383-45D2-A5C... There's literally one person saying that NFS is crapshot anyway, and several +1s for retries. And the former was accompanied with several questions about the concrete implementation.
  - georgebarnett 8y ago
    One the messages told me to go lobby NFS vendors. Many others were as you note bikeshedding the commit. Only one, if I recall, questioned why it was a controversial patch at all. If you check the patch that was committed eventually, IIRC it’s identical to what I proposed. As I said, I don’t look back particularly favourably on my interactions with the community.
    - pgaddict 8y ago
      Which of the messages on the thread do you consider as bikeshedding? I personally don't see any - that's not to say I agree with everything said on the thread, but overall it seems like a reasonable discussion. IMHO it's a bit strange to assume everyone will agree with your patch from the very beginning. People may lack proper understanding of the issue, or they may see it from a different angle, and so on. Convincing others that your solution is the right one is an important part of getting patch done. I don't see anything wrong with that.
pkaye 8y ago
There is an old paper http://pages.cs.wisc.edu/~remzi/Classes/736/Papers/iron.pdf http://pages.cs.wisc.edu/~remzi/Classes/736/Papers/iron.pdf that analyzes how various filesystems do error handling and not too long ago it was fairly bad. My own experience was some of the older Windows would not even check if a write command failed. I used to laugh when developers would state how robust their databases were when the underlying filesystem does not even check for many errors. Hopefully things are better now.
beefhash 8y ago
OpenBSD has brought forth a patch earlier this month to try and make fsync(2) less of a mess to use on OpenBSD[1], though it hasn't been committed yet. [1] https://marc.info/?l=openbsd-tech&m=154897756917794&w=2 https://marc.info/?l=openbsd-tech&m=154897756917794&w=2
- anarazel 8y ago
  Note that that patch doesn't really fix the issue. You can write();close();open();fsync(); and you'll miss the issue if the OS failed during writeback before the open(). That's worse than on new-ish linux, where at least it'll only forget the error if there's a lot of memory pressure.
pr3dr49 8y ago
I can see a mention of SQLite and MySQL. Also worth mentioning since this can affect any system: https://www.firebirdsql.org/pdfmanual/html/gfix-sync.html https://www.firebirdsql.org/pdfmanual/html/gfix-sync.html https://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/qsg3-safety.html#qsg3-safety-howtocorrupt https://www.firebirdsql.org/file/documentation/reference_man... I wonder how Oracle handles this. Raw device/partition and its own FS business logic?
- elygre 8y ago
  While oracle supports raw devices, and have their own reasonably good way of managing that, most installations I see used is files for storage. (Which leaves the question of how oracle handles this unanswered, of course)
- masklinn 8y ago
  Direct IO is the default for raw devices, and while apparently not the default otherwise IIRC it's pretty widely used by Oracle shops.
macdice 8y ago
For the record and not mentioned in Tomas's talk: the PostgreSQL release due out next week will add a PANIC after any fsync failure (in other words, no longer retry). The same thing has been done by MySQLand MongoDB and probably everyone else doing (optional) buffered IO for database-ish stuff who followed this stuff on LWN etc.
haberman 8y ago
> Both Chinner and Ts'o, along with others, said that the proper solution is for PostgreSQL to move to direct I/O (DIO) instead. Wait, is "direct I/O" the same as O_DIRECT? The same O_DIRECT that Linus skewered in 2007? > There really is no valid reason for EVER using O_DIRECT. You need a buffer whatever IO you do, and it might as well be the page cache. There are better ways to control the page cache than play games and think that a page cache isn't necessary. https://lkml.org/lkml/2007/1/10/233 https://lkml.org/lkml/2007/1/10/233 > Side note: the only reason O_DIRECT exists is because database people are too used to it, because other OS's haven't had enough taste to tell them to do it right, so they've historically hacked their OS to get out of the way. https://lkml.org/lkml/2007/1/10/235 https://lkml.org/lkml/2007/1/10/235 More background from 2002-2007: https://yarchive.net/comp/linux/o_direct.html https://yarchive.net/comp/linux/o_direct.html
- anarazel 8y ago
  Turns out sometimes people other than Linus have more experience with IO than Linus. I think there's pretty good reasons to go for DIO for a database. But only when there's a good sysadmin/DBA and when the system is dedicated to the database. There's considerable performance gains in going for DIO (at the cost of significant software complexity), but it's much more sensitive to bad tuning and isn't at all adaptive to overall system demands.
  - pgaddict 8y ago
    Yeah, which is one of the reasons PostgreSQL went with buffered I/O, not to have to deal with this complexity. And it served us pretty well over time, I think.
    - anarazel 8y ago
      I don't think that's really true. It worked well enough, true, but I think it allowed us to not fix deficiencies in a number of areas that we should just have fixed. IOW, I think we survived despite not offering DIO (because other things are good), rather than because of it.
ciudilo 8y ago
FWIW MongoDB fixed[1] this last year in their WiredTiger storage engine. [1] https://jira.mongodb.org/browse/WT-4045 https://jira.mongodb.org/browse/WT-4045
blaisio 8y ago
I don't really understand why people went with this headline and not "PostgreSQL developers discover most programs have used fsync incorrectly for decades, including PostgreSQL".
- macdice 8y ago
  I agree, the initial report on pgsql-hackers had a subject that blamed PostgreSQL too strongly and I think that carried through to articles and blogs and Twitter etc. You are right, this affects other things too. And indeed several projects took action to remove any notion of fsync retry, referencing the LWN article etc. POSIX is probably underspecified. AN Austin Group defect report would be interesting...
- giovannibajo1 8y ago
  Because most normal programs do open-write-sync-close, and that mostly works as expected. Postgres does open-write-close and then later, in another unrelated process, open-fsync-close. They discovered this doesn’t always work, because if somebody somewhere does a fsync on that file for any reason, their process might miss the error as it doesn’t stick.
- howard941 8y ago
  It's too long by 26 characters?
lelf 8y ago
https://wiki.postgresql.org/wiki/Fsync_Errors https://wiki.postgresql.org/wiki/Fsync_Errors Only FreeBSD & Illumos do the sane thing.
- zaarn 8y ago
  The wiki uses the word "presumably". I take that as "the devs say it works but nobody tested it".
mehrdadn 8y ago
Am I misunderstanding this or does this mean Linux literally does not provide any way to ensure writes are flushed to a file in the presence of transient errors? Does anyone know if Windows's FlushFileBuffers is susceptible to this as well? (P.S., interesting bit about FILE_FLAG_WRITE_THROUGH not working as you might expect: https://blogs.msdn.microsoft.com/oldnewthing/20170510-00/?p=95505 https://blogs.msdn.microsoft.com/oldnewthing/20170510-00/?p=...)
ausjke 8y ago
I read this at lwn.net a while ago but it seems there is no fix to it. How is MySQL doing? I believe Oracle etc are not having this problem as they deal with disk directly.
- jeltz 8y ago
  I think that all databases are affected by this to some degree when not using Direct-IO, and I think Oracle and MySQL can both run with or without direct-IO.
pyrus 8y ago
Does anyone know if FoundationDB is affected?
- ryanworl 8y ago
  FoundationDB does not use buffered IO, so no.
mindslight 8y ago
The kernel itself isn't really a transaction manager. If there is an I/O error on a file, then I'd only expect it to percolate up in that immediate "session". When should that error flag be expected to carry over until, filesystem remount? Or even longer, with the talk of storing a flag in the superblock? Specifically it seems like asking for trouble to open() a path a second time, and expect fsync() calls to apply to writes done on the other file descriptor object - there's no guarantee they're even the same file [0]! At the very least, pass the actual file descriptor to the other process. Linux's initial behavior was in violation of what I've said here, but the patch to propagate the error to every fd open at the time of the error should resolve this to satisfaction. I would think about the only reasonable assumption you could make about concurrent cross-fd syncing is that after a successful fsync(fd), any successful read(fd) should return data that was on disk at the time of that fsync(fd) or later. In other words, dirty pages shouldn't be returned by read() and then subsequently fail being flushed out. disclaimer: It's been a while since I've written syscall code and I've had the luxury of never really having to really lean into the spec to obtain every last bit of performance. Given how many guarantees the worse-is-better philosophy forewent, I don't see much point to internalize what few are there. [0] Ya sure, you can assert that the user shouldn't be otherwise playing around with the files, I'm just pointing out that translating path->fd isn't a pure function.

676 points by lelf 8y ago

> In short, PostgreSQL assumes that a successful call to fsync() indicates that all data written since the last successful call made it safely to persistent storage. But that is not what the kernel actually does. When a buffered I/O write fails due to a hardware-level error, filesystems will respond differently, but that behavior usually includes discarding the data in the affected pages and marking them as being clean. So a read of the blocks that were just written will likely return something other than the data that was written. > Google has its own mechanism for handling I/O errors. The kernel has been instrumented to report I/O errors via a netlink socket; a dedicated process gets those notifications and responds accordingly. This mechanism has never made it upstream, though. Freund indicated that this kind of mechanism would be "perfect" for PostgreSQL, so it may make a public appearance in the near future. https://lwn.net/Articles/752063/ https://lwn.net/Articles/752063/ A real-life example can be found at https://stackoverflow.com/questions/42434872/writing-programs-to-cope-with-i-o-errors-causing-lost-writes-on-linux https://stackoverflow.com/questions/42434872/writing-program...

metildaa 8y ago
Sounds like Google is continuing to act poorly when it comes to upstreaming their code.
- 420codebro 8y ago
  I like to beat on them just like everyone else but what is the expectation? Everything they write if it runs at an OS level?
  - metildaa 8y ago
    Google should be working with the kernel team to get their code mainlined, rather than keep their patches out of tree for years on end.
    - mixmastamyk 8y ago
      Perhaps they tried. Do you have evidence otherwise?
solidsnack9000 8y ago
The linked LWN article (from April 2018) is a great summary of the problem and potential solutions and its cause: Ted Ts'o, instead, explained why the affected pages are marked clean after an I/O error occurs; in short, the most common cause of I/O errors, by far, is a user pulling out a USB drive at the wrong time. If some process was copying a lot of data to that drive, the result will be an accumulation of dirty pages in memory, perhaps to the point that the system as a whole runs out of memory for anything else. So those pages cannot be kept if the user wants the system to remain usable after such an event.
- anarazel 8y ago
  That justification is bogus however. There's already separate logic for the case the entire underlying device vanishes.
  - Dylan16807 8y ago
    Also USB bus resets are not unheard of. Or moving devices from one port to another. If the device comes back within a minute or two you probably shouldn't throw out those writes.
    - 0x0 8y ago
      If someone quickly pulls an usb drive, plugs it in another system, and then plugs it back in to the original system, then flushing writes could cause massive data corruption if those writes are relative to an outdated idea of what's on the block device. Sounds like a misfeature to me
      - zozbot123 8y ago
        > If someone quickly pulls an usb drive, plugs it in another system, and then plugs it back in to the original system, then flushing writes could cause massive data corruption That's user error, though. The kernel should react to removable media being pulled by sending a wall message to the appropriate session/console, stating something similar to "Please IMMEDIATELY place media [USB_LABEL] back into drive!!", with [Retry] and [Cancel] options. That way, the user knows what to expect -- OS's used to do this as a matter of course when removable media was in common use. In fact, you could even generalize this, by asking the user to introduce some specific media (identified by label) when some mountpoint is accessed, even if no operations were actually in progress.
puzzle 8y ago
Bigtable, and presumably Spanner, goes beyond that. Data chunks have checksums and they're immediately re-read and verified after compactions, because it turns out that errors do happen and corruption also happens, even when you use ECC — roughly once every 5PB compacted, per Jeff Dean figures.

deleted 8y ago

[deleted]

chubot 8y ago

Doesn't this affect all databases? Or is it a different issue? https://www.sqlite.org/atomiccommit.html https://www.sqlite.org/atomiccommit.html 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. Also this seems related: https://danluu.com/file-consistency/ https://danluu.com/file-consistency/ That results in consistent behavior and guarantees that our operation actually modifies the file after it's completed, as long as we assume that fsync actually flushes to disk. OS X and some versions of ext3 have an fsync that doesn't really flush to disk. OS X requires fcntl(F_FULLFSYNC) to flush to disk, and some versions of ext3 only flush to disk if the the inode changed (which would only happen at most once a second on writes to the same file, since the inode mtime has one second granularity), as an optimization. The linked OSDI '14 paper looks good: We find that applications use complex update protocols to persist state, and that the correctness of these protocols is highly dependent on subtle behaviors of the underlying file system, which we term persistence properties. We develop a tool named BOB that empirically tests persistence properties, and use it to demonstrate that these properties vary widely among six popular Linux file systems.

anarazel 8y ago
> Doesn't this affect all databases? Yes, most. And several did similar changes (crash-restart -> recovery) to handle it too. It's possible to avoid the issue by using direct IO too, but often that's not the default mode of $database.
networkimprov 8y ago
Notably, Go had failed to use F_FULLFSYNC on MacOS until I reported it. Fix landing in 1.12, but won't be back-ported. https://github.com/golang/go/issues/26650 https://github.com/golang/go/issues/26650

wuxb 8y ago

It's a perfect scenario to hear some rants from Linus. While, the "code" has changed and he really has to calm down for this :)

EGreg 8y ago

Have there been any real-world consequences from this, and how can they be prevented? Does MySQL have the same flaw?

macdice 8y ago
The original report from Craig Ringer was based on a real system that experienced a failure like this on thin provisioning. MySQL in buffered IO mode surely has the same problem, and has implemented the same solution: PANIC rather than retrying. Same for WiredTiger (MongoDB). Other systems using direct (non buffered) IO are not affected.
j16sdiz 8y ago
This can happen when io device have intermittent failure. Bad device or faulty cable.
- pgaddict 8y ago
  Interestingly enough, such issues are becoming more common. It's not just about devices being less reliable, but e.g. thin provisioning being used more widely etc.

bbunix 8y ago

Historic note (like from the 80's) - any time a machine was rebooted we'd type sync; sync; sync; reboot - the explanation was that the only guarantee was that the second sync wouldn't start until the first sync successfully completed, plus one for good luck...

yyx 8y ago
https://utcc.utoronto.ca/~cks/space/blog/unix/TheLegendOfSync https://utcc.utoronto.ca/~cks/space/blog/unix/TheLegendOfSyn... people were told 'do several sync commands, typing each by hand' But this mutated to just 'sync three times', so of course people started writing 'sync; sync; sync'
- sgt 8y ago
  I still type sync (just once) before I reboot. Just a habit.
  - beefhash 8y ago
    I always get some kind of error message from dmraid about having been unable to stop the RAID array on shutdown/reboot. I thus manually do a sync(1) in hopes that the data survives. Hasn't failed me thus far, at least.
  - slavik81 8y ago
    The only machine I ever developed on that needed a sync before reboot was an ancient SPARC workstation. I forgot to sync almost every time, but it would always boot after a fsck.
  - noir_lord 8y ago
    The last thing I do before I leave my work desktop for the day is ./eod.sh && sync && sync 20+ year old habits die hard.
Jerry2 8y ago
I have a flash drive that I sometimes put a video on to watch it on a small TV in the basement and I've noticed that Linux doesn't copy the file right away. The 'cp' does finish quickly but the data is not on the flash drive yet. You either have to eject and wait or sync and wait for it to actually transfer. Needless to say, this tripped me up few times and videos weren't fully transferred.

anarazel 8y ago

I think it's worthwhile to note that this, even before both kernel and postgres changes, really only is an issue if there are very serious storage issues, commonly causing more corruption than just forgetting the data due to be fsynced. The kernel has its own timeout / retry logic and if those retries succeed, there's no issue. Additionally, most filesystems remount read-only if there's any accompanying journaling errors, and in a lot of cases PG IO will also have a metadata effect.

waz0wski 8y ago
i/o errors are an ongoing issue for AWS M5 and C5 instance types with their cloudy nvme devices, they have a tendency to randomly disappear or have extended timeouts https://bugs.launchpad.net/ubuntu/+source/linux/+bug/1788035 https://bugs.launchpad.net/ubuntu/+source/linux/+bug/1788035 unfortunately the upstream patches being backported don't provide any real write guarantee

xorgar831 8y ago

The meta point here is that just in OSS folks assume that the code has been reviewed since it's open, but the reality is that unless someone actually does you really don't know. The popularity of a project doesn't mean there aren't fatal flaws.

fxfan 8y ago
This is a contrarian view and I will sound phobic but for this very reason- false sense of security + possibility of malicious check ins- I now place less emphasis on open/closed source and more on repute. I am happier to download exe's from good companies these days than a package from arch-aur
ascar 8y ago
Personally I assume if a project is at least somewhat popular it works most of the time and frequent/serious bugs are reported and researchable. For that to work I also report or second bugs I encounter. That doesn't mean there aren't any bugs in even the most popular OSS, especially in edge cases and rare scenarios. With closed source though I often don't know the popularty and how many/what kind of bugs have been reported, but just the reputation of the vendor. I prefer a popular software from a highly reputable vendor over a somewhat popular OSS. But I also prefer the most popular and battletested OSS, like postgresql and linux, over any closed system, e.g. SQL Server and Windows.
loeg 8y ago
No need to bring out that dead horse for a beating. No one in this thread has yet made the claim that OSS code is flawless; you're picking a fight with a straw man.
- zapzupnz 8y ago
  It's a genuine issue, and one that is made time and again by people who think "open == security" whenever there's a discussion about something like Google or iMessage, when the armchair security experts come out of the woodwork to promote their favourite whatever-it-is. Sure, it mightn't be made in this thread yet, but that doesn't make it an irrelevant, invalid, or uninteresting observation. I think that the spirit of discussion, so integral to what separates HN from other websites, means we should not poo-poo this line of inquiry just because you're bored of it.

georgebarnett 8y ago

Years ago (2010 iirc), I reported to the Postgres list, with a patch, that (one of) the reason Postgres “didn’t work on NFS” was because it couldn’t deal with short writes. I got told the usual “you’re holding it wrong” instead of an acknowledgement of PG not sticking to the spec. I patched my own systems (wrap the calls in a loop as per standard practice) and then proceeded to run literally hundreds of thousands of PostgreSQL instances on NFS for many more years with no problems. The patch was eventually integrated it seems but I never found out why because I lost interest in trying to work with the community after that experience.

anarazel 8y ago
The relevant thread: https://www.postgresql.org/message-id/46ED7555-9383-45D2-A5C0-6373BE2FDCB6%40atlassian.com https://www.postgresql.org/message-id/46ED7555-9383-45D2-A5C... There's literally one person saying that NFS is crapshot anyway, and several +1s for retries. And the former was accompanied with several questions about the concrete implementation.
- georgebarnett 8y ago
  One the messages told me to go lobby NFS vendors. Many others were as you note bikeshedding the commit. Only one, if I recall, questioned why it was a controversial patch at all. If you check the patch that was committed eventually, IIRC it’s identical to what I proposed. As I said, I don’t look back particularly favourably on my interactions with the community.
  - pgaddict 8y ago
    Which of the messages on the thread do you consider as bikeshedding? I personally don't see any - that's not to say I agree with everything said on the thread, but overall it seems like a reasonable discussion. IMHO it's a bit strange to assume everyone will agree with your patch from the very beginning. People may lack proper understanding of the issue, or they may see it from a different angle, and so on. Convincing others that your solution is the right one is an important part of getting patch done. I don't see anything wrong with that.

pkaye 8y ago

There is an old paper http://pages.cs.wisc.edu/~remzi/Classes/736/Papers/iron.pdf http://pages.cs.wisc.edu/~remzi/Classes/736/Papers/iron.pdf that analyzes how various filesystems do error handling and not too long ago it was fairly bad. My own experience was some of the older Windows would not even check if a write command failed. I used to laugh when developers would state how robust their databases were when the underlying filesystem does not even check for many errors. Hopefully things are better now.

beefhash 8y ago

OpenBSD has brought forth a patch earlier this month to try and make fsync(2) less of a mess to use on OpenBSD[1], though it hasn't been committed yet. [1] https://marc.info/?l=openbsd-tech&m=154897756917794&w=2 https://marc.info/?l=openbsd-tech&m=154897756917794&w=2

anarazel 8y ago
Note that that patch doesn't really fix the issue. You can write();close();open();fsync(); and you'll miss the issue if the OS failed during writeback before the open(). That's worse than on new-ish linux, where at least it'll only forget the error if there's a lot of memory pressure.

pr3dr49 8y ago

I can see a mention of SQLite and MySQL. Also worth mentioning since this can affect any system: https://www.firebirdsql.org/pdfmanual/html/gfix-sync.html https://www.firebirdsql.org/pdfmanual/html/gfix-sync.html https://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/qsg3-safety.html#qsg3-safety-howtocorrupt https://www.firebirdsql.org/file/documentation/reference_man... I wonder how Oracle handles this. Raw device/partition and its own FS business logic?

elygre 8y ago
While oracle supports raw devices, and have their own reasonably good way of managing that, most installations I see used is files for storage. (Which leaves the question of how oracle handles this unanswered, of course)
masklinn 8y ago
Direct IO is the default for raw devices, and while apparently not the default otherwise IIRC it's pretty widely used by Oracle shops.

macdice 8y ago

For the record and not mentioned in Tomas's talk: the PostgreSQL release due out next week will add a PANIC after any fsync failure (in other words, no longer retry). The same thing has been done by MySQLand MongoDB and probably everyone else doing (optional) buffered IO for database-ish stuff who followed this stuff on LWN etc.

haberman 8y ago

> Both Chinner and Ts'o, along with others, said that the proper solution is for PostgreSQL to move to direct I/O (DIO) instead. Wait, is "direct I/O" the same as O_DIRECT? The same O_DIRECT that Linus skewered in 2007? > There really is no valid reason for EVER using O_DIRECT. You need a buffer whatever IO you do, and it might as well be the page cache. There are better ways to control the page cache than play games and think that a page cache isn't necessary. https://lkml.org/lkml/2007/1/10/233 https://lkml.org/lkml/2007/1/10/233 > Side note: the only reason O_DIRECT exists is because database people are too used to it, because other OS's haven't had enough taste to tell them to do it right, so they've historically hacked their OS to get out of the way. https://lkml.org/lkml/2007/1/10/235 https://lkml.org/lkml/2007/1/10/235 More background from 2002-2007: https://yarchive.net/comp/linux/o_direct.html https://yarchive.net/comp/linux/o_direct.html

anarazel 8y ago
Turns out sometimes people other than Linus have more experience with IO than Linus. I think there's pretty good reasons to go for DIO for a database. But only when there's a good sysadmin/DBA and when the system is dedicated to the database. There's considerable performance gains in going for DIO (at the cost of significant software complexity), but it's much more sensitive to bad tuning and isn't at all adaptive to overall system demands.
- pgaddict 8y ago
  Yeah, which is one of the reasons PostgreSQL went with buffered I/O, not to have to deal with this complexity. And it served us pretty well over time, I think.
  - anarazel 8y ago
    I don't think that's really true. It worked well enough, true, but I think it allowed us to not fix deficiencies in a number of areas that we should just have fixed. IOW, I think we survived despite not offering DIO (because other things are good), rather than because of it.

ciudilo 8y ago

FWIW MongoDB fixed[1] this last year in their WiredTiger storage engine. [1] https://jira.mongodb.org/browse/WT-4045 https://jira.mongodb.org/browse/WT-4045

blaisio 8y ago

I don't really understand why people went with this headline and not "PostgreSQL developers discover most programs have used fsync incorrectly for decades, including PostgreSQL".

macdice 8y ago
I agree, the initial report on pgsql-hackers had a subject that blamed PostgreSQL too strongly and I think that carried through to articles and blogs and Twitter etc. You are right, this affects other things too. And indeed several projects took action to remove any notion of fsync retry, referencing the LWN article etc. POSIX is probably underspecified. AN Austin Group defect report would be interesting...
giovannibajo1 8y ago
Because most normal programs do open-write-sync-close, and that mostly works as expected. Postgres does open-write-close and then later, in another unrelated process, open-fsync-close. They discovered this doesn’t always work, because if somebody somewhere does a fsync on that file for any reason, their process might miss the error as it doesn’t stick.
howard941 8y ago
It's too long by 26 characters?

lelf 8y ago

https://wiki.postgresql.org/wiki/Fsync_Errors https://wiki.postgresql.org/wiki/Fsync_Errors Only FreeBSD & Illumos do the sane thing.

zaarn 8y ago
The wiki uses the word "presumably". I take that as "the devs say it works but nobody tested it".

mehrdadn 8y ago

Am I misunderstanding this or does this mean Linux literally does not provide any way to ensure writes are flushed to a file in the presence of transient errors? Does anyone know if Windows's FlushFileBuffers is susceptible to this as well? (P.S., interesting bit about FILE_FLAG_WRITE_THROUGH not working as you might expect: https://blogs.msdn.microsoft.com/oldnewthing/20170510-00/?p=95505 https://blogs.msdn.microsoft.com/oldnewthing/20170510-00/?p=...)

ausjke 8y ago

I read this at lwn.net a while ago but it seems there is no fix to it. How is MySQL doing? I believe Oracle etc are not having this problem as they deal with disk directly.

jeltz 8y ago
I think that all databases are affected by this to some degree when not using Direct-IO, and I think Oracle and MySQL can both run with or without direct-IO.

pyrus 8y ago

Does anyone know if FoundationDB is affected?

ryanworl 8y ago
FoundationDB does not use buffered IO, so no.

mindslight 8y ago

The kernel itself isn't really a transaction manager. If there is an I/O error on a file, then I'd only expect it to percolate up in that immediate "session". When should that error flag be expected to carry over until, filesystem remount? Or even longer, with the talk of storing a flag in the superblock? Specifically it seems like asking for trouble to open() a path a second time, and expect fsync() calls to apply to writes done on the other file descriptor object - there's no guarantee they're even the same file [0]! At the very least, pass the actual file descriptor to the other process. Linux's initial behavior was in violation of what I've said here, but the patch to propagate the error to every fd open at the time of the error should resolve this to satisfaction. I would think about the only reasonable assumption you could make about concurrent cross-fd syncing is that after a successful fsync(fd), any successful read(fd) should return data that was on disk at the time of that fsync(fd) or later. In other words, dirty pages shouldn't be returned by read() and then subsequently fail being flushed out. disclaimer: It's been a while since I've written syscall code and I've had the luxury of never really having to really lean into the spec to obtain every last bit of performance. Given how many guarantees the worse-is-better philosophy forewent, I don't see much point to internalize what few are there. [0] Ya sure, you can assert that the user shouldn't be otherwise playing around with the files, I'm just pointing out that translating path->fd isn't a pure function.