Thanks very much for that. Unfortunately WAL mode is not supported on CE - lacks certain structures apparently.
On 18 March 2015 at 12:26, Richard Hipp <drh at sqlite.org> wrote: > On 3/17/15, Nicholas Smit <niksmit at gmail.com> wrote: > > Hello. > > > > We have an app on CE 5, and CE 7. > > > > We are keen to move from using SQL CE, to Sqlite, for our main data, as > > Sqlite is superior in many ways. > > > > The app runs on a mobile device, where power can be removed at any time. > > The data is stored on SD cards, with FAT filesystem. > > > > Sometimes the IO sub-system is known to be slow. As such, corruption and > > data loss is our number one fear. Our main aim is to keep the IO to a > > minimum, thus reducing the risk of corruption anywhere. Corruption of > the > > file itself, but also the filesystem, leading to problems accessing the > > file. > > > > Of course this fear has nothing to do with Sqlite per se, but we're > trying > > to understand how the risk might *change*, as we move from SQL CE to > > Sqlite. > > > > With Sqlite, we're using Synchronous=FULL (as we don't want to lose > data), > > Journal Mode=Persist (to inhibit unnecessary IO on the journal file). > > > > In SQL CE, the minimum interval for flushes to disk is 1 second, which is > > what we've been using. > > > > My question is (finally I get to it!) with these settings, it sounds like > > if we do (say) 3 transactions in a particular second, we will end up > doing: > > > > - 3 updates to the FAT to obtain the exclusive lock > > - 3 writes to the sqlite journal > > - 3 writes to the sqlite main db file > > - 3 updates to the FAT to release the exclusive lock, update the last > > modified file date, etc. > > > > > > So a total of 12 writes to the file system. (excluding any reads etc > > required in the actual transaction). > > > > Whereas in SQL CE this would have been about 1 (to just append the new > > data for all 3, at the end of the flush interval. It presumably keeps the > > file open, so no FAT updates.). > > > > a) Is my understanding roughly correct here in terms of the IO? > > I do not think file locking and unlocking involves any I/O. The locks > are in-memory data structures that do not persist to disk in any way. > So there would only be 6 I/Os (the way you are counting them). > > Really, though, each of the remaining 6 I/Os consists of multiple > WriteFile() calls. > > > b) If so, is there any way to reduce the IO effort? > > (1) Combine the writes into a single transaction. > (2) Use WAL mode. > (3) Both of the above. > > > c) Can anyone comment on whether Windows CE VFS does actually honour the > > fsync commands of sqlite, or does the OS perhaps buffer them anyway? > > > I dunno. > > But even if WinCE does handle the FlushFileBuffers() calls correctly, > there is no guarantee that the hardware will. You can reduce your risk > by using WAL mode, which is much less vulnerable to dodgy hardware > than is the default rollback mode. > > > > -- > D. Richard Hipp > drh at sqlite.org >
