Patched libsqlite.so to disable sync and improve SQLite performance - Asus Eee Pad Transformer Prime

Based on this thread:
http://forum.xda-developers.com/showthread.php?t=1595531
and this one:
http://forum.xda-developers.com/showthread.php?t=903507
After quite some reading and messing about it worked...
I have patched sqlite3.c from CM9 ICS source and recompiled CM9 (for nexus, the tutorial used that one... but files seem interchangable).
!!!WARNING!!!
!!!Use this at your own risk! I will not be responsible for any messed up primes, errors and/or loss of data!!!
This can mess up your prime! Only do this if you have a working CWM backup and can restore that backup from recovery! Root is required at this time to replace the files...
I have no idea what this does with data integrity, if you value your data extremely it might be wise not to try this!
!!!WARNING!!!
*RISK*
As explained by Hobbesian in this post: http://forum.xda-developers.com/showpost.php?p=27121811&postcount=39
Fsync off means that SQLite is unbound by the need to check that data has been written to disk successfully, it also means the operating system can freely re-order the writes before they are comitted to flash memory, which in the event of a hardlock will very likely result in database corruption, of the unrecoverable sort.
Fsync in itself is often used -deliberately- to incur an I/O buffer, so whilst it might seem like a great idea to turn it off, what you're essentially doing is risking the CPU going faster than the device controller can write to memory. This is bad. I cannot begin to imagine how many klaxons would go off if you're working on something remotely important and use this patch.
*RISK*
I hereby attach the zip containing the libsqlite.so and libsqlite_jni.so.
1. Copy the files (using root explorer for example) to the /system/lib folder, replacing the original files. (Mount that folder as RW if necessary).
2. Set permissions to the same as the other files, rw-r--r--.
3. Reboot.
If something went wrong your prime will not boot but will be stuck at the booting screen, in that case recover to a working backup using CWM.
SQLite benchmark went from 100+ secs to 20-25 seconds. Quadrant IO bench improved as well.
Have yet to decide on realtime performance but I actually believe the internal memory is pretty mediocre, physically... So this only helps partially in that case.

---

It's fun to play with sync settings but not if you value your data. I would strongly advise against it except for testing and benchmark purposes...

---

Thanks, but Iḿ awarre of possible consequences... But I believe the conclusion in earlier versions was that, even though there are indeed risks, they aren't as huge as you'd might expect. Mainly with unexpected reboots or shutting down the risk is big(ger).
But even if itś just for testing/benching... I would still very much like to see the results.
Btw, it should be ARM v7 even... that is the proper instruction set. Updated starting post.

---

Ugh, got it to build for arm-v7a but of course it looks (completely) different from the module with the prime. Structure the same, but compiled different enough. So thought I'd compule the library and install it on the prime but that resulted in the prime not booting...
Hmmm

---

I'd love to help, but I have zero knowledge in anything SQLite...
I wish you good luck!
I think everyone is hoping that the slow random write speeds are purely software related. And it makes sense that they are. The SQLite benchmarking thread is giving a lot of details on what is making our performance so sluggish, but not why.

---

Next step, compile complete CM9 source (hopefully including libsql src ) so I can use that as a base...
Copying the libsqlite.so and libsqlite-jni.so from an AOKP rom worked fine, so perhaps this is a (not the easiest) way to get things to work.
And ah well, otherwise I learnt how to compile a rom

---

xTRICKYxx said:
I'd love to help, but I have zero knowledge in anything SQLite...
I wish you good luck!
I think everyone is hoping that the slow random write speeds are purely software related. And it makes sense that they are. The SQLite benchmarking thread is giving a lot of details on what is making our performance so sluggish, but not why.
Click to expand...
Click to collapse
Same here, thanks for taking time out to tinker with our primes...........hmm, im not intirelly sure if i should refrase that???
GD LUCK

---

dagrim1 said:
Next step, compile complete CM9 source (hopefully including libsql src ) so I can use that as a base...
Copying the libsqlite.so and libsqlite-jni.so from an AOKP rom worked fine, so perhaps this is a (not the easiest) way to get things to work.
And ah well, otherwise I learnt how to compile a rom
Click to expand...
Click to collapse
Are you actually changing rom code to remove fsync?

---

hairdewx said:
Are you actually changing rom code to remove fsync?
Click to expand...
Click to collapse
Ehm, basically... Not rom code itself but one of the libraries in the rom (for which I hope the code is in there as well).
Background info:
http://forum.xda-developers.com/showthread.php?t=1000899
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.
-----------------------------
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.
Click to expand...
Click to collapse
Patch for libsqlite.c:
http://forum.xda-developers.com/showpost.php?p=12695464&postcount=1966
Code:
diff -urNp external/sqlite/dist.origin//sqlite3.c external/sqlite/dist//sqlite3.c
--- external/sqlite/dist.origin//sqlite3.c 2011-01-07 15:26:32.000000000 +0800
+++ external/sqlite/dist//sqlite3.c 2011-01-07 15:27:55.000000000 +0800
@@ -34761,7 +34761,7 @@ SQLITE_PRIVATE int sqlite3PagerOpen(
pPager->readOnly = (u8)readOnly;
/* pPager->needSync = 0; */
assert( useJournal || pPager->tempFile );
- pPager->noSync = pPager->tempFile;
+ pPager->noSync = 1;
pPager->fullSync = pPager->noSync ?0:1;
pPager->sync_flags = SQLITE_SYNC_NORMAL;
/* pPager->pFirst = 0; */
@@ -65838,7 +65838,7 @@ static void attachFunc(
sqlite3PagerLockingMode(pPager, db->dfltLockMode);
sqlite3PagerJournalMode(pPager, db->dfltJournalMode);
}
- aNew->safety_level = 3;
+ aNew->safety_level = 1;
aNew->zName = sqlite3DbStrDup(db, zName);
if( rc==SQLITE_OK && aNew->zName==0 ){
rc = SQLITE_NOMEM;
@@ -97124,7 +97124,7 @@ static int openDatabase(
** database it is 'NONE'. This matches the pager layer defaults.
*/
db->aDb[0].zName = "main";
- db->aDb[0].safety_level = 3;
+ db->aDb[0].safety_level = 1;
db->aDb[1].zName = "temp";
db->aDb[1].safety_level = 1;
So the basic idea is to limit IO-interaction by disabling fsync.

---

Interesting, I figured it would have been in one of the kernel modules.

---

Ok, I have been able to compile the CM9 source (for the galaxy nexus, because the tutorial was for that one) including the sqlite source...
First test was now 36,6 secs which is pretty nice, have to check for other issues (and will compile for TF201 if I can soon). The semi patch caused a number of force closes so we'll have to see how this one goes.
But at least happy I was able to compile

---

Wow ... thanks for posting this ... I've edited the sqlite source and compiled the binary for AOKP B38 below are my setup details and test results. So far everything is stable, no FCs in the past 45 min and things are much faster. I have no idea whats up with Antutu's SD Card reads, that's outrageously high lol ... Can't say I trust that ;o) . Otherwise great hack. I wouldn't recommend running this if you have a need for solid data stability, I haven't seen anything yet, but ending all those syncs may cause problems down the road, I don't know. Since I flash regularly I don't really mind it, loving the speed! Also found some other sqlite optimization tips( Cache size and some others ) I may try to add in after I run it for a day as is and see how stability is. Again, thanks to the OP, great find!( so far )
Code:
Device: Transformer Prime(TF201)
ROM: AOKP B38
Kernel: Motleys running at 1.8 set to performance governer for testing
Tests: ( All tests done Run 1 after fresh boot, Run 2 immediatly after, all set to performance governer )
Couldn't get the damn formating to look right in the post ... check the image for results.

---

RubenRybnik said:
Wow ... thanks for posting this ... I've edited the sqlite source and compiled the binary for AOKP B38 below are my setup details and test results. So far everything is stable, no FCs in the past 45 min and things are much faster. I have no idea whats up with Antutu's SD Card reads, that's outrageously high lol ... Can't say I trust that ;o) . Otherwise great hack. I wouldn't recommend running this if you have a need for solid data stability, I haven't seen anything yet, but ending all those syncs may cause problems down the road, I don't know. Since I flash regularly I don't really mind it, loving the speed! Also found some other sqlite optimization tips( Cache size and some others ) I may try to add in after I run it for a day as is and see how stability is. Again, thanks to the OP, great find!( so far )
Code:
Device: Transformer Prime(TF201)
ROM: AOKP B38
Kernel: Motleys running at 1.8 set to performance governer for testing
Tests: ( All tests done Run 1 after fresh boot, Run 2 immediatly after, all set to performance governer )
Couldn't get the damn formating to look right in the post ... check the image for results.
Click to expand...
Click to collapse
What about actual use performance? Is web browsing lag gone? What about switching apps?
I'm a little hesitant about the data stability... with my luck I'll have rock solid stability until the ONE TIME I actually need it and lose important data

---

Updated main post...

---

This should be in dev section?
Looks promising, thanks OP.

---

Sounds very promising. Rooted people, start testing!

---

sweet. going to try it tonight!
---------- Post added at 01:33 PM ---------- Previous post was at 01:13 PM ----------
RubenRybnik said:
Wow ... thanks for posting this ... I've edited the sqlite source and compiled the binary for AOKP B38 below are my setup details and test results. So far everything is stable, no FCs in the past 45 min and things are much faster. I have no idea whats up with Antutu's SD Card reads, that's outrageously high lol ... Can't say I trust that ;o) . Otherwise great hack. I wouldn't recommend running this if you have a need for solid data stability, I haven't seen anything yet, but ending all those syncs may cause problems down the road, I don't know. Since I flash regularly I don't really mind it, loving the speed! Also found some other sqlite optimization tips( Cache size and some others ) I may try to add in after I run it for a day as is and see how stability is. Again, thanks to the OP, great find!( so far )
Click to expand...
Click to collapse
Regarding your screenshot, the 1k inserts: After the SQL Patch those scores are on par with the other devices where we've seen benchmarks from (less than 1 second, which is literally 100 times better.. )
I don't believe in coincidence
Can it be that on those devices FSync is allready disabled, or bypassed only in some cases?
Anyhow i am realy curious if this makes stuff feel better

---

Tempie007 said:
Regarding your screenshot, the 1k inserts: After the SQL Patch those scores are on par with the other devices where we've seen benchmarks from (less than 1 second, which is literally 100 times better.. )
I don't believe in coincidence
Can it be that on those devices FSync is allready disabled, or bypassed only in some cases?
Anyhow i am realy curious if this makes stuff feel better
Click to expand...
Click to collapse
Posted this in the TFP's AOKP thread in that 'other' forum:
I'm seeing some great results personally. Chrome on prime seems really responsive, apps launch pretty much instantly, haven't seen a desktop redraw on Nova the whole time. I have an image folder with about 300 wallpaper images, opening that in quickpic in the past has been pretty slow( several seconds ), now that is almost instantly done, all thumbnails before I blink lol.
Now I'm not saying this is all the sql patch I did at all, unfortunately I didn't put much time into using Montleys new kernel in a 'real world' case, I only benchmarked with his kernel then applied sql patch and benchemarked again( still using his kernel ). So perhaps I'll flash back tonight just to see if I can gauge how much the patch vs kernel is doing.( Thanks again Motley! ).
As for side effects, I installed Chrome last night so I could test, had about a 2 second freeze at the end of it installing, but came back and was fine after. Haven't seen any other freezes. I did 'loose' wifi for about 20 min last night. I was connected to the wifi the entire time, but even a reboot and net wouldn't work on the Prime, not sure what happened, or if it's related, but seemed to have 'fixed' itself after 20 min last night.
Click to expand...
Click to collapse

---

Tempie007 said:
sweet. going to try it tonight!
---------- Post added at 01:33 PM ---------- Previous post was at 01:13 PM ----------
Regarding your screenshot, the 1k inserts: After the SQL Patch those scores are on par with the other devices where we've seen benchmarks from (less than 1 second, which is literally 100 times better.. )
I don't believe in coincidence
Can it be that on those devices FSync is allready disabled, or bypassed only in some cases?
Anyhow i am realy curious if this makes stuff feel better
Click to expand...
Click to collapse
If it is, it is not done in the library itself...
I also tried the libsqlite files from the SGS3 for example and copied them to the prime, but that didn't help much... So if fsync is handled differently on those devices it is done in a different place.
This is just a workaround for (part of) the problem... The real issue is elsewhere. I mean, the CM9/AOKP/AOSP roms use the same source for the differnt devices yet the issues are not the same on all of them.
So somewhere in the kernel code? Or another level, the controller for example. As the HTC One X performs decent it seems that it's not (JUST) the tegra3 but some of the other components.
Ah well, maybe tonight I can try the full wipe and reinstall and see if that helps anything.
Booting time doesn;t improve with this mod, titanium restores improve a bit but nothing extreme (20-25% faster is my guess) so there IS more going on. My guess is the used memory is at least part of the problem.
Some SD benchmarking indicating the memory used isn't the best one :/ (Done with the 'Device Performance' benchmarking app)
Asus TF201:
[email protected]/mnt/sdcard (=internal)
Max Write: 14,4
Mean Write: 12,1
Min Write: 9,4
Max Read: 28,8
Mean Read: 23,2
Min Read: 14,9
[email protected]/Removable/MicroSd (=external)
Max Write: 14,7
Mean Write: 11,4
Min Write: 9,9
Max Read: 14,6
Mean Read: 12,7
Min Read: 9,0
SGS3:
[email protected]/mnt/sdcard (=internal)
Max Write: 26,7
Mean Write: 25,3
Min Write: 23,9
Max Read: 43,8
Mean Read: 42,2
Min Read: 39,4
[email protected]/mnt/extSdCard (=external)
Max Write: 10,6
Mean Write: 10,0
Min Write: 9,6
Max Read: 13,8
Mean Read: 10,6
Min Read: 6,2

---

Is it possible to make an intelligent fsync where it basically syncs after some clock time after the last db write or inserts an fsync with a high count of writes?