[dev][sqlite3] 21/Jan, how to disable sync in sqlite3 without pragma

WARN:The following steps only for developers. The following changes may cause instability or even cause the phone can not be used.

Android phones as the underlying database using sqlite3. sqlite3 writes efficiency is very low, because the sync feature turned on by default, and fsync() must be performed after each insertion, the resulting system efficiency is low, and the disk life is reduced.

I try to disable sync feature by default in exchange for greater IO performance and reduce disk consumption. While doing so may result in data integrity problems, but I still like to use it because most of the sqlite insert action can be completed within a few seconds, not too much to consider issues such as sudden power-down.

After modified, the time of insert 2000 records to sqlite3 db, from 1m11s reduce to 2s.
Attachment is my sqlite3 patch, it is for CM or AOSP.
For SenseROM, I can not simply replace libsqlite.so from AOSP, so I do hexedit with it.Do follow modify for SenseROM libsqlite.so:

replace
c6 f7 66 fd 03 20 78 72
with
c6 f7 66 fd 01 20 78 72

replace
cc f8 0c 00 03 20 a3 68
with
cc f8 0c 00 01 20 a3 68

replace
5f fa 81 fc 83 f0 01 06
with
5f fa 81 fc 01 23 00 26
EDIT(2011/01/07 23:40):
replace
5f fa 81 fc 83 f0 01 06
with
5f fa 81 fc 01 26 00 bf

replace
23 72 00 23 66 72 02 26
with
26 72 00 23 63 72 02 26

EDIT(2011/01/10 16:45):
HD WWE RUU 1.72.405.3 patched libsqlite.so:http://www.multiupload.com/N9FLJYAC77, not test!

EDIT(2011/01/10 22:24):
HD WWE RUU 1.72.405.3 patched version v2 as attachement
patch file upgrade to V2

EDIT(2011/01/21 10:59):
add auto repaire script for mms/sms db corrupt. If you met data loss with my sqlitemod, then place this script to /system/etc/init.d/, and chmod 755 to it.
this script will be check your mms/sms db at boot time, and auto fix it if found table loss.:View attachment 03repairesmsdb.txt

libsqlite patched for gingerbread 2.3.1 aosp/cm roms

http://www.multiupload.com/L7GNJI5XHW

Thanks to ownhere and animehq, I have patched sqlite3 for the latest versions of CM7.
CM7 instructions:
http://bit.ly/i2SxsK - push it to /system/lib and then add lib/libsqlite.so to custom_backup_list.txt to mean that it is always backed up between rom upgrades and no need to push it again, until it breaks...

great work!

patch for miui, flash from recovery.
the binary is slightly different,so I made this patch.

but won't cause this (micro)-lags like any other kind of disk-buffering?

The 2000 insert statements will go in 2s because they are still in RAM.

After a while of doing nothing and / or suddenly needing more RAM, the changes will (finally) be written to disk, causing the system to be unresponsive for the duration of the write.

If I receive or send a new sms / text for example, there are a few records inserted. With sync on, it might take a fraction longer, but the changes are there and the user 'understands' it. There is some activity.

Without sync-ing, all those little changes add up until suddenly they need to be really written and the system lags for a full second or so because it needs to write them all at once.

And what are the moments when there are a lot of database writes during normal use anyway?

Another example: I'm restoring my sms messages with SMS Backup & Restore. I've got a lot of messages, so it takes a while. This is normal. Now, with syncing (default) it takes a while, but when the app says it's done... it's really done. Switching back to your homescreen and launching another app will go smoothly.
With syncing off, the restore operation will complete sooner, but the moment you start another app (or even go back to your homescreen) more RAM is needed and the changes will be written to disk, causing a simple tap to launch an app to feel laggy or sluggish because the latest changes from the restore operation are written to disk (which the user understands to be done already).

Buffering like this helps a lot if something needs to be written while concurrently something else is constantly reading. The write-changes are kept in memory so the disk I/O is completely free for the reading operations. When they are done, the write-changes can actually be commited to disk, and won't interfere with the read operations. On a multi-tasking desktop computer, this is normal.

But when will something like that be needed / happening on a smartphone?

Thinking about the user experience, when a special process is running (restoring backups, installing an app, etc..) the user expects / understands that it takes a moment. The user surely does not want that operation to complete quickly, only to have a lagging keyboard 5 secs afterwards, right?

Thinking about 2000+ inserts is nice, but I really don't see when a situation like that occurs on an Android phone, and I much rather have operations be really done than interfering later on.