Last Comment Bug 857888 - webappsstore.sqlite is larger than it should be
: webappsstore.sqlite is larger than it should be
Status: NEW
:
Product: Core
Classification: Components
Component: DOM (show other bugs)
: unspecified
: All All
: -- normal with 8 votes (vote)
: ---
Assigned To: Nobody; OK to take it and work on it
:
: Andrew Overholt [:overholt]
Mentors:
Depends on: 527667 742822 1286798
Blocks: 1042372
  Show dependency treegraph
 
Reported: 2013-04-03 20:15 PDT by Richard Newman [:rnewman]
Modified: 2016-08-22 08:05 PDT (History)
23 users (show)
See Also:
Crash Signature:
(edit)
QA Whiteboard:
Iteration: ---
Points: ---
Has Regression Range: ---
Has STR: ---
+


Attachments

Description Richard Newman [:rnewman] 2013-04-03 20:15:26 PDT
I don't use the Marketplace, or pin web apps to my home screen; I browse. My Aurora profile on my phone has

  webappsstore.sqlite               4.1MB
  webappsstore.sqlite-wal           1.5MB 
  chromeappsstore.sqlite             96KB

That's bigger than browser.db, and I have a lot of bookmarks and history!

The webappsstore2 table has 498 rows containing what looks like junk: page javascript, piles of JSON, random fragments of URLs.

What is this thing, and why is it hanging around in my profile directory?
Comment 1 Richard Newman [:rnewman] 2013-04-03 20:17:19 PDT
More importantly, is a sqlite database with a big WAL the best storage system for this? And how does it clean itself up?
Comment 2 Wesley Johnston (:wesj) 2013-04-03 20:26:23 PDT
These files are related to DOMStorage and have nothing to do with webapps. They're just badly named. chromeappsstore is deprecated. I'm not sure why it stays around.
Comment 3 Richard Newman [:rnewman] 2013-04-03 20:55:48 PDT
Ok, let's leave this bug around as a general bucket for annoyances with DOMStorage space. I'll file a bug to have chromeappstore deleted after migration.
Comment 4 Stefan Lasiewski 2013-04-06 20:27:04 PDT
On my system, webappstore.sqlite* uses nearly 10MB of space. It's huge!

On my low-end ARMv6 phone, I already have a big enough problem with Firefox being over 30MB, but this 10MB worth of files uses way too much space.

# pwd
/data/data/org.mozilla.firefox/files/mozilla/foo.default
# du -ks * | sort -n
...
1049    startupCache
3549    safebrowsing
4096    webappsstore.sqlite
6072    adblockplus
6149    webappsstore.sqlite-wal
#
# du -ks /data/data/org.mozilla.firefox/files/mozilla*
34777   /data/data/org.mozilla.firefox/files/mozilla
Comment 5 Brad Lassey [:blassey] (use needinfo?) 2013-04-11 10:20:03 PDT
Doug, any plans to limit the space used by DOMStorage?
Comment 6 Doug Turner (:dougt) 2013-04-13 15:41:16 PDT
honza, could you take a look at this on mobile?  I think ben fixed indexed db to not pre-allocate.
Comment 7 Ben Turner (not reading bugmail, use the needinfo flag!) 2013-04-13 16:06:39 PDT
(In reply to Doug Turner (:dougt) from comment #6)
> I think ben fixed indexed db to not pre-allocate.

Well, sorta... The change I made was to allow a preference to control the default page_size, so you could change that from the default to something else on fennec.

SQLite requires one page for each table and index that are created, even if they're empty. Our default page_size is set to 32k, so obviously that can quickly grow bigger than you'd want. 

However, IndexedDB also compresses its data, something DOMStorage doesn't do. And, if these databases really do have "page javascript, piles of JSON" (comment 0) in them then maybe they're actually going to require that much space even with a smaller page size...
Comment 8 Richard Newman [:rnewman] 2013-04-13 17:02:34 PDT
This DB was uncompressed, as far as I can tell -- I was browsing through it with SQLite Debugger on the phone.

I think the solution here will have to involve cleanup, possibly linked to Fennec's existing memory pressure hooks, and perhaps with a UI component for management -- right now the only way to get this space back is to use Android Settings to blow away all of Fennec's data, which is pretty harsh. 


Phrasing this as a user story: as a user, I expect that visiting Fast Company's site once on my phone should not permanently deprive me of a measurable amount of storage.
Comment 9 Mark Finkle (:mfinkle) (use needinfo?) 2013-04-22 10:53:50 PDT
Do we have any quota or expiration system for DOMStorage?
Comment 10 Honza Bambas (:mayhemer) 2013-04-22 13:34:21 PDT
(In reply to Mark Finkle (:mfinkle) from comment #9)
> Do we have any quota or expiration system for DOMStorage?

We have quota (5MB per eTLD+1).  We don't have any expiration or eviction mechanism.
Comment 11 Daniele "Mte90" Scasciafratte 2014-08-10 13:41:00 PDT
I've see that webappstore.sqlite and netpredictions.sqlite have more than 100mb.
Maybe is strange?
Comment 12 Aaron Train [:aaronmt] 2014-08-10 13:54:35 PDT
That is horrible. Maybe we can try taking a look at your databases here.
Comment 13 Daniele "Mte90" Scasciafratte 2014-08-10 14:04:41 PDT
I'm uploading the files on my owncloud private server.
webappsstore.sqlite - 103.8mb
netpredictions.sqlite - 118.6mb
Zipped are 56 mb file. Funny. LOL
Comment 14 Daniele "Mte90" Scasciafratte 2014-08-10 14:20:30 PDT
This are the sqlite files https://it.owncube.com/public.php?service=files&t=215a555facdb716c7046d37ad114daee
Comment 15 Richard Newman [:rnewman] 2014-08-10 16:20:48 PDT
Bug 1045884 tracks eliminating netpredictions.sqlite entirely. It's no longer used.
Comment 16 Daniele "Mte90" Scasciafratte 2014-08-10 16:23:24 PDT
Uhm i'm using the 33 version, i wait the new version for remove this file.
Maybe an autoremove on the upgrade?
Comment 17 Aaron Train [:aaronmt] 2014-08-11 08:06:26 PDT
(In reply to Daniele "Mte90" Scasciafratte from comment #16)
> Uhm i'm using the 33 version, i wait the new version for remove this file.
> Maybe an autoremove on the upgrade?

We should remove or clean up the db in that bug, but there's no fix yet.
Comment 18 Daniele "Mte90" Scasciafratte 2014-08-18 05:46:04 PDT
For my huge webappsstore.sqlite any news?
PS: i see the ticket is for Firefox for android but my file are of Firefox desktop.
Comment 19 Aaron Train [:aaronmt] 2014-08-18 08:01:30 PDT
There is unfortunately no progress on this bug.
Comment 20 Richard Newman [:rnewman] 2014-08-26 14:25:01 PDT
Thank you for sharing your data.

Your (desktop) webappsstore.sqlite contains 7,834 entries for 1,625 unique scopes.

On average, each entry is 12KB, totaling about 95MB.

Data includes mediawiki module storage, JS file URLs, Twitter user hashes, YouTube settings, Chartbeat values from Delicious, LinkedIn JS caches, etc.

What's most interesting, beyond the long tail, are the biggest entries:

scope                    key         length(value)
-----------------------  ----------  -------------
moc.ksidllif.2.:http:80  filldisk    2500000
moc.ksidllif.1.:http:80  filldisk    2500000
moc.mvhh.scod.:http:80   search-en   2068396
ten.php.:http:80         search-en   2008770
ten.php.1ti.:http:80     search-it   2004574
ten.php.2ti.:http:80     search-it   2004574
moc.xoferif.ecalptekram  0::model_c  1376262
moc.rettiwt.:https:443   __typeahea  969012
gro.aidemikiw.snommoc.:  MediaWikiM  799795
moc.kcedteewt.bew.:http  typeaheadU  771099

So filldisk.com is using 5MB alone. Then there are a bunch of sites using 1-2MB, and a long tail down from there. There are over five hundred entries larger than 50KB.

marketplace.firefox.com is taking up 1.2MB.

About 3MB was recovered by vacuuming from the 104MB.

The only general solution to fat webappsstore.sqlite is eviction and expiration. It is unacceptable for thousands of sites to be forever storing hundreds of KB each, particularly on a phone.

I'll leave this assigned to Honza, and move it to Core :: DOM.

I'll file another bug to make marketplace.firefox.com less bloaty.
Comment 21 Daniele "Mte90" Scasciafratte 2014-09-01 01:26:37 PDT
Maybe a setting for set the timeout or a page for manually delete the datas.
Comment 22 Brad Lassey [:blassey] (use needinfo?) 2014-10-20 08:26:44 PDT
filter on [mass-p5]
Comment 23 Virtual_ManPL [:Virtual] - (ni? me) 2015-01-21 12:06:47 PST
Any update on this bug?
Comment 24 Honza Bambas (:mayhemer) 2015-01-23 07:45:06 PST
No.
Comment 25 C Roberts 2015-12-17 08:14:27 PST
FYI: My webappsstore.sqlite is 57mb, my places.sqlite is 71mb. We need a utility reduce the size of these, delete old history or keywords.
Comment 28 Honza Bambas (:mayhemer) 2016-02-11 03:16:15 PST
Note: Firefox 46 will be doing a vacuum during the database update.
Comment 29 Richard Newman [:rnewman] 2016-02-16 09:41:13 PST
Bug 1058934 just got WONTFIXed, so this bug could expand to "throw away everything stored for marketplace.firefox.com".


Re Comment 28: note that a sqlite VACUUM won't recover any space unless database content is first deleted or replaced by smaller values. (Indeed, temporarily it will double our space usage as sqlite rewrites the entire DB.)


The bulk of the issue here is that we don't ever delete DOM storage, so if you visit a site that stores something, you will lose that space until you uninstall Firefox from your phone or wipe your desktop profile.

Honza, Olli: do you have a plan for making progress here?
Comment 30 Olli Pettay [:smaug] 2016-02-16 14:12:35 PST
I'll leave storage handling (in its various forms) to mayhemer and janv.
(Happy to review patches anyhow.)
Comment 31 C Roberts 2016-02-17 04:03:01 PST
> Note: Firefox 46 will be doing a vacuum during the database update.

That doesn't fix the problem. VACUUM only releases space for *records that were deleted* in the database. We need a way to delete old records from within Firefox. I don't need stuff in these sqlite dbs from 3 years ago for a website I only went to once. I'd like to see each record in each of those databases, and the date it was last used or created. I'd like to be able to sort these records by that date, and select multiple records (it could be 100+ records) and delete the old ones. 

I've never gone to marketplace.firefox.com, I don't even know what it is. 

For now there is a 3rd party utility, and even a Firefox plugin (https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/?src=search) to deal with these sqlite databases, but that's too much trouble for the average user. 

Windows 3rd party util: Sqlite manager: https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/?src=search
Wikipedia page: https://en.wikipedia.org/wiki/SQLite
Comment 32 C Roberts 2016-02-17 04:07:31 PST
****, I can't edit my comment. When doing a VACUUM the db doubles in size temporarily. Make sure the user has enough disk space to do this, when doing a VACUUM on each db.

And what are my formatting options in Bugzilla? lol. I can't seem to find if it supports any markup of any kind. :)

Also, the Sqlite Manager above is not free. SQDatabase is free: http://sqdatabase.com/
Comment 33 Honza Bambas (:mayhemer) 2016-02-17 04:38:16 PST
I think we need bug 527667 here.

*** This bug has been marked as a duplicate of bug 527667 ***
Comment 34 Honza Bambas (:mayhemer) 2016-02-17 04:40:01 PST
To explain: there is currently no data about how old a record is.  That bug is about to add it.  We can then add a mechanism pruning old data automatically.  OTOH, localStorage has no expiration mechanism by default.  So hard to say how 'old' something has to be to remove it on users behalf.
Comment 35 Jan Varga [:janv] 2016-02-17 04:47:41 PST
The problem described in this bug is one of the reasons why we are moving localStorage to QuotaManager and temporary storage in bug 742822. It means that when temporary storage is full, quota manager starts evicting data for origins using LRU policy.

Also, at some point, we can also add a new feature that would delete data for origins that were not used for X days/months/years.
Comment 36 gvp9000 2016-02-17 07:18:22 PST
I closed firefox and deleted webappsstore.sqlite from the profile folder.
No side effect.
Comment 37 Richard Newman [:rnewman] 2016-02-17 09:15:33 PST
Thanks for the pointers, Honza and Jan.

Flipping this to depend on those two bugs -- one to add timestamping, one to add quotas.

Comment 34 and Comment 35 don't sound like they will directly solve this problem, but they're prerequisites, so un-duping this.
Comment 38 fireattack 2016-02-17 09:32:02 PST
(In reply to Richard Newman [:rnewman] from comment #37)
> Thanks for the pointers, Honza and Jan.
> 
> Flipping this to depend on those two bugs -- one to add timestamping, one to
> add quotas.
> 
> Comment 34 and Comment 35 don't sound like they will directly solve this
> problem, but they're prerequisites, so un-duping this.

If bug 742822 won't directly solve this, why it would directly solve bug 1238771?
Comment 39 Honza Bambas (:mayhemer) 2016-02-17 09:45:32 PST
(In reply to Richard Newman [:rnewman] from comment #37)
> Thanks for the pointers, Honza and Jan.
> 
> Flipping this to depend on those two bugs -- one to add timestamping, one to
> add quotas.
> 
> Comment 34 and Comment 35 don't sound like they will directly solve this
> problem, but they're prerequisites, so un-duping this.

Yep, it's better.  There is still more work to do when we have the timing info on each data row.  I agree.
Comment 40 joriswu 2016-08-19 01:01:29 PDT
+1, as Firefox on a new install slows down within one month to such an extent that many sites are hardly navigatable.

Note You need to log in before you can comment on or make changes to this bug.