On Mon, Feb 14, 2011 at 3:02 PM, Max Vlasov <max.vla...@gmail.com> wrote: > On Mon, Feb 14, 2011 at 8:42 PM, Jim Wilcoxson <pri...@gmail.com> wrote: > >> On Mon, Feb 14, 2011 at 10:56 AM, Pavel Ivanov <paiva...@gmail.com> wrote: >> >> So my question is, does it maintain the other 3 parts of ACID, so that >> >> the database will never be in a corrupted state after a power loss, >> >> even though it may be missing some writes that were confirmed? >> > >> > Jim, I think the answer to your question is already in Max's tests: >> > the USB drive is completely unreliable and can easily lead to database >> > corruption. I'll explain. Max's tests showed that there were >> > situations when database and journal were different not by one >> > transaction but by several ones. So if one makes changes to several >> > database pages (located in different disk sectors) and/or makes >> > transactions touching several database pages (which makes multi-page >> > journal files) then these pages can be way out of sync with each other >> > (including pages inside journal). And this will easily lead to >> > database corruption. >> >> You are right I think. I wrote my own test program and ran it on a >> Macbook Pro with a Seagate USB drive. Here is the Python test >> program: >> >> [jim@mb backup]$ time py t.py >> 4999 records inserted in 17.7488458157 seconds; 281.652117097 recs/sec >> >> > > Jim, your tests also shows (this time on a different os) that either you > have a fantastic hd with 18000 Rpm or just someone at Seagate _want_ you to > think you have a fantastic hd :) > > Just wondering, I know this maybe sounds fantastic, but I'm thinking whether > some "acid-passed harddrives" at sqlite.org can encourage manufacturers to > hold the horses. The logic would be like this: if some model is present in > either section then googling it will make this page very high in the google > results (due to high pagerank of sqlite.org). So they probably very quickly > notice that this page at least partly can affect their sales. Unfortunately > the technical side is more complex, the developers just can't rely on > e-mails from users, this should be some sqlite-originated tests performing > on a known configuration and it'd better be an oss os with known tuning. > Maybe some other, less fantastic form of such tests could be possible... > > Max
I think what would be useful is for drives to have 3 settings: a) the drive can do whatever it wants to optimize performance b) the drive can reorder writes, but not across a sync (ACI, but no D) c) the drive has to respect all syncs (ACID) If the drive mgfr wants to make a) the default, that's fine, but there should be an easy way to request the other 2 from an application. Users are not usually sophisticated enough to know when it's okay to cache writes or not okay. For my use of SQLite, b) would probably be fine, but a) apparently is not since it corrupts databases. Michael mentioned doing backups and forgetting about all of this, but many people are using USB drives to store their backups. So there is a bit of a Catch-22 here. Almost all modern-day filesystems are going to depend on some kind of journalling to prevent corrupted file systems, and as far I as know, journalling filesystems depend on syncs to maintain FS consistency. Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
