826476 - Modify sqlite database pragmas to possibly improve performance

Status: RESOLVED FIXED

(Firefox for Android Graveyard :: Data Providers, defect)

Description

[Mark Finkle (:mfinkle) (use needinfo?)]

Attachment: WIP

We currently use our subclass of SQLiteOpenHelper.onOpen to tweak some sqlite pragmas, originally done in bug 721320.

Some notes:
1. We treat the "PRAGMA synchronous" as mutually exclusive with support for enableWriteAheadLogging, but sqlite doesn't. We should always set "PRAGMA synchronous". Desktop Firefox also does this.
2. tabs.db is not a long-lived durable DB. We refresh the entire DB every time we update it. I think we can get away with less safety and get better performance. The patch uses "PRAGMA synchronous=OFF" and "PRAGMA read_uncommitted=true" to get better performance.

I think we should consider using "PRAGMA read_uncommitted=true" for browser.db too, but we need some testing first.

PRAGMA details here: http://www.sqlite.org/pragma.html

Note: For the Gecko sqlite usage, we use "PRAGMA synchronous=OFF" for all DB access:
http://mxr.mozilla.org/mozilla-central/source/mobile/android/app/mobile.js#42
http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageConnection.cpp#626

Comment 1

[Kartikaya Gupta (email:[e-mail])]

Comment on attachment 697662 [details] [diff] [review]
WIP

Review of attachment 697662 [details] [diff] [review]:
-----------------------------------------------------------------

I don't see any problems with this, but wondering why you set the read_uncommitted pragma to true. Do you expect that to be significantly faster?

Comment 2

[Mark Finkle (:mfinkle) (use needinfo?)]

Attachment: patch

I'm ready to try this on Nightly. I removed the "read_uncommitted" pragma. I expected some improvement from this, but the Tabs DB is too small to see much difference. No sense in adding it.

Comment 3

[Kartikaya Gupta (email:[e-mail])]

Comment on attachment 699801 [details] [diff] [review]
patch

Review of attachment 699801 [details] [diff] [review]:
-----------------------------------------------------------------

Do we have appropriate telemetry in place to track if this helps?

Comment 4

[Mark Finkle (:mfinkle) (use needinfo?)]

https://hg.mozilla.org/integration/mozilla-inbound/rev/018998c48bbe

Comment 5

[Ed Morley [:emorley]]

https://hg.mozilla.org/mozilla-central/rev/018998c48bbe