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

ownhere · Jan 7, 2011

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

Muxeu · Jan 7, 2011

Patched libsqlite.so for SenseROM.
I got libsqlite.so from Pays 1.1+Z HD rom.

melethron · Jan 7, 2011

Sounds intressting. I like the idea of modding sqlite more than loop.

Loopfile is double flush (flush loop + flush file) and direct loop mount causes random reboots.

Ill test it it when i have time.

EDIT: you don't mind if i'll use it for my rom (as optional addon)

EDIT2: By the way, ... did you try 1.72 base of HD rom. Its really fast and sqlite3 is also newer version?

dipje · Jan 8, 2011

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.

ownhere · Jan 8, 2011

I think you are wrong.

With Android applications, the database operation is non-persistent, normal step in app is:
1.open db
2.do read/write
3.close db
with step3, the data in cache will be flush to disk. so user will not notice any sudden lag, always smooth.

With SYNC-ON, the db operation like this:
1.open db
2.1. write a record/do a transcation
2.2. fsync()
2.3. write a record/do a transcation
2.4. fsync()
.....
3.close db/fsync()

with SYNC-OFF, operation like this:
1.open db
2.1 write a record/do a transcation
2.2 write a record/do a transcation
....
3. close db/fsync()

So, no-sync can significantly save IO time.

dipje said:
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.

ownhere · Jan 8, 2011

melethron said:
Sounds intressting. I like the idea of modding sqlite more than loop.

Loopfile is double flush (flush loop + flush file) and direct loop mount causes random reboots.

Ill test it it when i have time.

EDIT: you don't mind if i'll use it for my rom (as optional addon)

EDIT2: By the way, ... did you try 1.72 base of HD rom. Its really fast and sqlite3 is also newer version?

my sqlite3 mod is against loop.

The main purpose of loopfile/loopdevice is speedup fsync() in sqlite3, But the normal file operations have been affected. So I directly modify sqlite3. The benefit is MTD db access can be speedup, too.
Anyone can use my MOD.

I use 1.32.832.6(HK RUU), because I don't like WWERUU include too many language, this will lead apk too large.

Offical new version sqlite3 must be disabled nosync for stability.

melethron · Jan 8, 2011

ownhere said:
my sqlite3 mod is against loop.
The main purpose of loopfile/loopdevice is speedup fsync() in sqlite3, But the normal file operations have been affected. So I directly modify sqlite3. The benefit is MTD db access can be speedup, too.
Anyone can use my MOD.
I use 1.32.832.6(HK RUU), because I don't like WWERUU include too many language, this will lead apk too large.
Offical new version sqlite3 must be disabled nosync for stability.

I dont have a problem with large apks. I deodexed my rom and complete system will fit in mtd4+5 then. Apart from that: the 1.72 base is REALLY MUCH faster. As soon as HK RUU 1.72 is available i recommend you use that one. You'll love it.

About the fsync if db is closed. When will it be closed then? After the app is closed?

ownhere · Jan 8, 2011

melethron said:
I dont have a problem with large apks. I deodexed my rom and complete system will fit in mtd4+5 then.

About the fsync if db is closed. When will it be closed then? After the app is closed?

Most apps close db after db operation. I try reboot 10+ times in 30 mins, no FC or data loss.

melethron · Jan 8, 2011

Ok. Still a bit risky but its really good to have this optional. Great work

.

Sent from my HTC Desire using XDA App

_bryan_ · Jan 8, 2011

great work!

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

melethron · Jan 8, 2011

_bryan_ said:
great work!

patch for miui, flash from recovery.
the binary is slightly different,so I made this patch.
you can also extract libsqlite.so from the zip and replace it with rootexplorer.

Not sure if replacing it "on the fly" is a good idea. Might cause issues if sqlite is runnin while it is replaced.

_bryan_ · Jan 8, 2011

melethron said:
Not sure if replacing it "on the fly" is a good idea. Might cause issues if sqlite is runnin while it is replaced.

yes，you are right.

madman_cro · Jan 8, 2011

so we need to put it in rom before flashing, right??
i will try on oxygen 1.4 and will post benchmark

madman_cro · Jan 8, 2011

madman_cro said:
so we need to put it in rom before flashing, right??
i will try on oxygen 1.4 and will post benchmark

tried it. dont see any lags, and to be honest dont see any improvments. will test some more

melethron · Jan 8, 2011

madman_cro said:
tried it. dont see any lags, and to be honest dont see any improvments. will test some more

Do you even use data2sd. In most cases there aren't lags if /data/data is on nand but there is if it is on sd-ext.

madman_cro · Jan 8, 2011

melethron said:
Do you even use data2sd. In most cases there aren't lags if /data/data is on nand but there is if it is on sd-ext.

no i dont use it . i only use a2sd

melethron · Jan 8, 2011

madman_cro said:
no i dont use it . i only use a2sd

Don't think you'll notice the difference then.

sibere · Jan 8, 2011

madman_cro said:
so we need to put it in rom before flashing, right??
i will try on oxygen 1.4 and will post benchmark

I'd say you can inject it in recovery mode. No need for flashing

melethron · Jan 9, 2011

Answer to this here (the discussion there went off topic).

Loop is not intended to mount a "real" filesystem. It is made to mount stuff like *.iso or *.img. Loop is only a "workaround" to skip fsync. Loop is simply abused to achieve a fsync skip. Skipping it directly in sqlite has following improvements over loop:

1. Less RAM: needed because there is no "loop" cache needed then.
2. Less I/O: Only one flush because there is no need to "double flush"
3. Less cputime/battery: Also because no double flush.
4. Less time to set it up: No setup needed for a loopfile.
5. More safety: Faster flush and the partition can be journaled without speed decrease (ext3/4 instead of fat)

About 5: You may still loose data on a powerloss because it is not yet synced but you wont have corrupted data because half written data will be fixed on a ordered journal.

Alex-V · Jan 9, 2011

ownhere said:
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

are these values the same in sqlite hd 1.72....

i can not find them...after c6 f7 66 it say can not find...if i open it in a hex editor

edit: or anyone can give me a patched version for 1.72..?

thx for answers...with kind regards...Alex

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

Senior Member

Attachments

Senior Member

Attachments

Senior Member

Senior Member

Senior Member

Senior Member

Senior Member

Senior Member

Senior Member

Senior Member

Attachments

Senior Member

Senior Member

Senior Member

Senior Member

Senior Member

Senior Member

Senior Member

Retired Recognized Developer

Senior Member

Inactive Recognized Developer

Similar threads

Top Liked Posts