Attend XDA's Second Annual Developer Conference, XDA:DevCon 2014!
5,769,404 Members 40,091 Now Online
XDA Developers Android and Mobile Development Forum

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

Tip us?
 
ownhere
Old
(Last edited by ownhere; 21st January 2011 at 03:02 AM.)
#1  
Senior Member - OP
Thanks Meter 288
Posts: 213
Join Date: Jun 2010
Location: Beijing
Default [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.:03repairesmsdb.txt
The Following 14 Users Say Thank You to ownhere For This Useful Post: [ Click to Expand ]
 
Muxeu
Old
(Last edited by Muxeu; 8th January 2011 at 02:25 PM.)
#2  
Muxeu's Avatar
Senior Member
Thanks Meter 137
Posts: 459
Join Date: Jul 2010
Location: Kharkov
Patched libsqlite.so for SenseROM.
I got libsqlite.so from Pays 1.1+Z HD rom.
Attached Files
File Type: rar libsqlite_patched.rar - [Click for QR Code] (222.6 KB, 735 views)
 
melethron
Old
(Last edited by melethron; 7th January 2011 at 11:17 PM.)
#3  
Senior Member
Thanks Meter 192
Posts: 854
Join Date: Sep 2010
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
Old
#4  
Senior Member
Thanks Meter 151
Posts: 721
Join Date: Oct 2006
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.
The Following User Says Thank You to dipje For This Useful Post: [ Click to Expand ]
 
ownhere
Old
#5  
Senior Member - OP
Thanks Meter 288
Posts: 213
Join Date: Jun 2010
Location: Beijing
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.

Quote:
Originally Posted by dipje View Post
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
Old
#6  
Senior Member - OP
Thanks Meter 288
Posts: 213
Join Date: Jun 2010
Location: Beijing
Quote:
Originally Posted by melethron View Post
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
Old
(Last edited by melethron; 8th January 2011 at 10:25 AM.)
#7  
Senior Member
Thanks Meter 192
Posts: 854
Join Date: Sep 2010
Quote:
Originally Posted by ownhere View Post
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
Old
#8  
Senior Member - OP
Thanks Meter 288
Posts: 213
Join Date: Jun 2010
Location: Beijing
Quote:
Originally Posted by melethron View Post
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
Old
#9  
Senior Member
Thanks Meter 192
Posts: 854
Join Date: Sep 2010
Ok. Still a bit risky but its really good to have this optional. Great work .

Sent from my HTC Desire using XDA App
 
_bryan_
Old
(Last edited by _bryan_; 8th January 2011 at 02:36 PM.)
#10  
Member
Thanks Meter 5
Posts: 52
Join Date: Nov 2007
great work!

patch for miui, flash from recovery.
the binary is slightly different,so I made this patch.
Attached Files
File Type: zip speedup_sqllite.zip - [Click for QR Code] (393.9 KB, 836 views)

The Following 2 Users Say Thank You to _bryan_ For This Useful Post: [ Click to Expand ]
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes