CBL .Net 4.5 :: Can sqllite be configured to use "PRAGMA synchronous = OFF" and "PRAGMA journal_mode = MEMORY"?

[Sherry Ummen]

Hello,

   I have been testing the early build of Couchbase LIte .net and its seems to be dead slow. For obvious reasons that the profiling is not done yet. Thats fine.

Found using Intel VTune that its sqlite_step command which is hogging the time.

So I decided to add these two lines to the Couchbase Lite .net build and built by myself

 raw.sqlite3_exec(db, "PRAGMA synchronous = OFF");

raw.sqlite3_exec(db, "PRAGMA journal_mode = MEMORY");

the performance has improved drastically.

but this is what I have build by myself. 

Does couchbase lite somehow allow us to change the configuration of sqlite?

And whether sqlite configure to  "PRAGMA synchronous = OFF" and "PRAGMA journal_mode = MEMORY" is safe?

Thanks

-Sherry

[Jens Alfke]

On Aug 22, 2014, at 2:26 AM, Sherry Ummen <sherry...@gmail.com> wrote:

   I have been testing the early build of Couchbase LIte .net and its seems to be dead slow.

Can you look up the stack beyond the sqlite_step call to see what Couchbase Lite and application operations are taking the time? It's pretty common for sqlite_step to be a bottleneck, since I/O is so much slower (esp. on a mobile device) than memory. The question is what CBL operations are involved.

So I decided to add these two lines to the Couchbase Lite .net build and built by myself

 raw.sqlite3_exec(db, "PRAGMA synchronous = OFF");

raw.sqlite3_exec(db, "PRAGMA journal_mode = MEMORY");

Don't do that. The SQLite docs say:

With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. 

and:

The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt.

(Emphasis mine.) Basically you are sacrificing reliability for speed. I would never use these modes for anything but a disposable cache database that you're prepared to throw away and rebuild on launch. I've used SQLite for nearly ten years, and I have seen databases get corrupted by crashes when full durability is turned off. It's unlikely to happen to any individual user, but multiply that by thousands or millions of users, and you'll see it more often than  you want, especially because users are rightly furious when they lose data or can't even launch the app.

Also, I would not expect these modes to speed up sqlite_step — they improve write performance, whereas sqlite_step is a read operation. If these modes speed up your app drastically, that implies you're bottlenecked on writes. The most likely cause of this is that you're updating lots of documents without wrapping them all in a transaction. Make sure you're not doing that.

—Jens