4 ms·
This section was particularly interesting (I add my own thoughts below it): >Unfortunately, most consumer-grade mass storage devices lie about syncing. Disk dr
by logicallee 7y ago
This section was particularly interesting (I add my own thoughts below it):
>Unfortunately, most consumer-grade mass storage devices lie about syncing. Disk drives will report that content is safely on persistent media as soon as it reaches the track buffer and before actually being written to oxide. This makes the disk drives seem to operate faster (which is vitally important to the manufacturer so that they can show good benchmark numbers in trade magazines). And in fairness, the lie normally causes no harm, as long as there is no power loss or hard reset prior to the track buffer actually being written to oxide. But if a power loss or hard reset does occur, and if that results in content that was written after a sync reaching oxide while content written before the sync is still in a track buffer, then database corruption can occur.
>USB flash memory sticks seem to be especially pernicious liars regarding sync requests. One can easily see this by committing a large transaction to an SQLite database on a USB memory stick. The COMMIT command will return relatively quickly, indicating that the memory stick has told the operating system and the operating system has told SQLite that all content is safely in persistent storage, and yet the LED on the end of the memory stick will continue flashing for several more seconds. Pulling out the memory stick while the LED is still flashing will frequently result in database corruption.
-----
If you were a human rather than sqlite you would stop believing that a sync has occurred immediately, trust it a little bit after a few seconds, and if it's been a minute since you yourself last touched the file, you would think "surely it has been written by now".
So it seems by tracking wall time, the library could do better than have to rely on the drive, and could be more on guard against lying drives.
- fencepost 7y agoI'd also wonder about how old this part is and whether it's partially based on Windows. USB behavior on Windows used to be optimized for speed and would do caching (hence the need to eject flash drives before removal) but in one of the Win10 updates it was changed to safe removal which basically disables write caching on removable devices. So, at least on Windows, there was absolutely a time where the OS would report completion before things were actually written.
- kardos 7y ago> So it seems by tracking wall time, the library could do better than have to rely on the drive, and could be more on guard against lying drives. Can you elaborate about tracking wall time --- how would you determine the real sync timing? Picking an arbitrary large delay (a minute) would trash the write performance
- logicallee 7y agoWhy would it trash the write performance to pick an arbitrarily large delay, such as one minute? Can you give an example? I don't know sqlite internals but this document is about "corrupting" a database (not just losing some data.) So I imagine it could be set up so that any power loss that keeps data from being written within a specified arbitrarily large delay (such as one minute) won't actually corrupt the database - just lose data.
- kardos 7y agoWouldn't you need to wait the 1 minute before the next write can happen?
- logicallee 7y agoI don't think so. I think you could treat it as a buffer that might still be lost on power failure. Doesn't mean you can't add more to that buffer or have to wait. It's not like that buffer has a limit.
- magila 7y agoThe bit about disk drive lying about syncing writes is a persistent myth. There have probably been some HDDs at some point which had notable bugs in their flush command, but in general drives do actually write data to disk before reporting complete status for a flush. I spent several years working for a major HDD manufacturer and their firmware QA process included a test rig which would repeatedly pull power to the drive while sending write and flush commands. The drive would then be powered up and the data on the drive examined to verify that all data which should have been flushed actually made it to disk. I think this myth persists mostly because it can be surprising difficult to coax an OS into sending a flush command for much the same reason disk drives get accused of lying about it: performance. These days the most reliably solution is to reconfigure the drive with write caching disabled. This way the drive will ensure every write hits the disk before reporting completion to the host. Write caching remains enabled by default on SATA drives mostly for historical reasons. Back before command queuing was commonly supported write caching gave a large speedup. These days everything supports NCQ so disabling write caching only has a small performance impact.
- xenadu02 7y agoThis is absolutely not a myth. There have been drives that behaved this way, including from "name brand" manufacturers. Maybe those were firmware bugs, maybe they stopped doing it, but it is not something made up. Of course there have also been controller bugs, file system bugs, and so on over the years. A decade ago the behavior of most file systems was quite poor in the face of underlying hardware failures but a lot of work has gone into improving the situation. SSDs are slowing improving too. Most of them have claimed to go into read-only mode when enough blocks wear out but in reality most of them would crash, fail to respond, or just lose data. It turns out manufacturers tend to be a bit loose in their claims until independent third-parties put their claims to the test. More recent models seem to be a bit better behaved in failure states.