11 ms·
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
by 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 agoNotably, 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
- dis-sys 8y agoI think it is a different matter. MacOS documented its abnormal fsync behavior, Golang just didn't follow what was clearly described in those documents. Linux didn't document what happens on fsync failure, there is really nothing for applications to follow. Also note that, the strange MacOS fsync() behavior is obvious, its fsync latency on the most recent mbp by default is close to the one observed on Intel Optane when we all know that mbp comes with much cheaper/slower SSD compared to Intel Optane. The same can't be said for the Linux fsync issue here.
- masklinn 8y agoAccording to https://wiki.postgresql.org/wiki/Fsync_Errors https://wiki.postgresql.org/wiki/Fsync_Errors MacOS also has the Linux issue anyway.
- loeg 8y agoYeah, IMO it's a Linux fsync bug. fsync() should not succeed if writes failed. fsync() should not clean dirty pages if writes failed. Both of these behaviors directly contravene the goals of user applications invoking fsync() as well as any reasonable API contract for safely persisting data. Arguably, POSIX needs a more explicit fsync interface. I.e., sync these ranges; all dirty data, or just dirty data since last checkpoint; how should write errors be handled; etc. That doesn't excuse that Linux's fsync is totally broken and designed to eat data in the face of hardware errors. That Dan Luu blog post you linked is fantastic and one I really enjoyed.
- macdice 8y agoI worked on PostgreSQL's investigation and response to this stuff and I agree with you FWIW. But apparently only FreeBSD (any filesystem) and ZFS (any OS) agree with us. Other systems I looked at throw away buffers and/or mark them clean, and this goes all the way back to ancient AT&T code. Though no one has commented on any closed kernel's behaviour. I doubt anyone worried much when disks just died completely in the good old days. This topic is suddenly more interesting now as virtualisation and network storage create more kinds of transient failures, I guess.
- IgorPartola 8y agoYet another reason to use ZFS I guess.
- loeg 8y agoI'm a FreeBSD dev, that probably colors my opinion :-).
- pgaddict 8y agoThere have been bugs involved on both sides. On the kernel side the error reporting was not working reliably in some cases (see [1] for details), so the application using fsync may not actually get the error at all. Hard to handle an error correctly when you don't even get notified about it. [1] https://www.youtube.com/watch?v=74c19hwY2oE https://www.youtube.com/watch?v=74c19hwY2oE On the PostgreSQL side, it was the incorrect assumption that fsync retries past writes. It's an understandable mistake, because without the retry it's damn difficult to write an application using fsync correctly. And of course we've found a bunch of other bugs in the ancient error-handling code (which just confirms the common wisdom that error-handling is the least tested part of any code base).
- hyc_symas 8y ago> Doesn't this affect all databases? Didn't affect LMDB. If an fsync fails the entire transaction is aborted/discarded. Retrying was always inherently OS-dependent and unreliable, better to just toss it all and start over. Any dev who actually RTFM'd and followed POSIX specs would have been fine. LMDB's crash reliability is flawless.
- evdubs 8y agoMr. Chu, I hope you never lose your tenacity with respect to writing blurbs on LMDB's performance and reliability. I have enjoyed the articles comparing LMDB with other databases' performance and hope you continue to point the spotlight on the superior design decisions of LMDB.
- hyc_symas 8y agoThanks, glad you're getting something out of those writeups. Hopefully it helps other developers learn a better path.
- diroussel 8y agoWhat if another process calls fsync? It will get the error. Then when LMDB calls fsync no error will be reported. And thus the transaction will not be retried. Is this scenario dealt with?
- anarazel 8y agoNewer versions of linux (but not plenty of other OSs) guarantee that each write failure will be signalled once to each file descriptor open before the error occurred. So that ought to be handled, unless the FD is closed inbetween.
- hyc_symas 8y agoLMDB is single-writer. Only one process can acquire the writelock at a time, and only the writer can call fsync() so this scenario doesn't arise. If you open the datafile with write access from another process without using the LMDB API, you're intentionally shooting yourself in the foot and all bets are off.
- masklinn 8y ago> Doesn't this affect all databases? Or is it a different issue? Possibly, https://wiki.postgresql.org/wiki/Fsync_Errors https://wiki.postgresql.org/wiki/Fsync_Errors notes both MySQL and MongoDB had to be changed. Note that the issue here different from either of the bits you quote. The problem here is that if you fsync(2) it fails and you fsync(2) again, on many systems the second call will always succeed because the first one has invalidated/cleared all extant buffers, and thus there's nothing for the second one to sync. Which is a success. AKA because of systems' shortcuts an fsync success effectively means "all writes since the last fsync have succeeded", not "all writes since the last fsync success have succeeded". Writes between a success and a failure may be irrecoverably lost
- jandrewrogers 8y ago> Doesn't this affect all databases? No, this is an artifact of storage engine design. Direct I/O is the norm for high-performance storage engines -- they don't use kernel cache or buffered I/O at all -- and many will bypass the file system given the opportunity (i.e. operate on raw block devices directly) which eliminates other classes of undesirable kernel behavior. Ironically, working with raw block devices requires much less code. Fewer layers of abstraction between your database and the storage hardware make it easier to ensure correct behavior and high performance. Most open source storage engines leave those layers in because it reduces the level of expertise and sophistication required of the code designers -- it allows a broader pool of people to contribute -- though as this case shows it doesn't not necessarily make it any easier to ensure correctness.
- loeg 8y ago> Most open source storage engines leave those layers in because it reduces the level of expertise and sophistication required of the code designers -- it allows a broader pool of people to contribute -- though as this case shows it doesn't not necessarily make it any easier to ensure correctness. Another reason is that it's easier to deploy — you can just use some variable space on your filesystem rather than shaving off a new partition.
- jandrewrogers 8y agoIn practice, implementations can be deployed either way. The storage hardware is always dedicated to the database for large-scale or performance-sensitive databases anyway, making it less of an inconvenience. For some common environments, raw block devices substantially increase throughput versus the filesystem, so there are real advantages to doing so when it makes sense.
- anarazel 8y agoYea, but the largest portion of databases these days is not deployed on dedicated hardware that's been carefully capacity planned, and has a dedicated DBA.
- 8y ago
- coolestuk 8y ago"have an fsync that doesn't really flush to disk. OS X requires fcntl(F_FULLFSYNC) to flush to disk" I remember highlighting this problem to the Firebird db developers maybe 13 years ago. AFAIR they were open to the problem I'd pointed out and went about fixing it on other platforms so that the db behaved everywhere as it did on OS X. I'm probably in the bottom 5% of IT professionals on this site. I'm rather amazed to find out that so late in the day Postgres has come round to fixing this. I haven't used Firebird in years and can't find a link to the discussion (could have been via email).
- anarazel 8y agoPostgres has offered F_FULLFSYNC on OSX since 2005?