Open Bug 923889 Opened 6 years ago Updated 23 days ago

Investigate strategies to detect and add missing indices to places.sqlite

Categories

(Toolkit :: Places, defect, P3)

24 Branch
x86_64
Linux
defect

Tracking

()

People

(Reporter: james.cook, Unassigned)

References

(Blocks 1 open bug)

Details

(Keywords: perf)

User Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.65 Safari/537.36

Steps to reproduce:

Start firefox.


Actual results:

Firefox hangs, using 100% CPU, for at least several minutes.

After some digging around, I narrowed down the problem to an SQL query to places.sqlite.

I ran a debug build with NSPR_LOG_MODULES=mozStorage:5 (thanks to 684513) and it hangs on this query:

INSERT OR IGNORE INTO moz_hosts (host, frecency) SELECT fixup_url(get_unreversed_host(h.rev_host)) AS host, (SELECT MAX(frecency) FROM moz_places WHERE rev_host = h.rev_host OR rev_host = h.rev_host || 'www.' ) AS frecency FROM moz_places h WHERE LENGTH(h.rev_host) > 1 GROUP BY h.rev_host

I ran the same query in the sqlite CLI (replacing fixup_url(get_unreversed_host(h.rev_host)) with just h.rev_host) and got the same result: the query didn't finish (I gave it a couple of minutes).


Some stats on my places.sqlite:
- It may be several years old.
- It is about 80 megabytes.
sqlite> SELECT COUNT(*) FROM moz_hosts;
6716
sqlite> SELECT COUNT(*) FROM moz_places;
104923

I'm using Firefox 24 on NixOS.  To try a different set-up, I copied my profile to my Ubuntu computer, and it also hanged there.  I also tried Firefox 23 and saw it hang.
I fixed by own problem by running:

CREATE INDEX moz_places_hostindex ON moz_places (rev_host);

Here was the schema of moz_places before I ran that command:
sqlite> .schema moz_places
CREATE TABLE moz_places (id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, user_title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER, guid TEXT);
CREATE INDEX moz_places_faviconindex ON moz_places (favicon_id);
CREATE INDEX moz_places_frecencyindex ON moz_places (frecency);
CREATE INDEX moz_places_lastvisitdateindex ON moz_places (last_visit_date);
CREATE UNIQUE INDEX moz_places_url_uniqueindex ON moz_places (url);
CREATE UNIQUE INDEX moz_places_guid_uniqueindex ON moz_places (guid);

My places.sqlite might have been created earlier than 2007.

Leaving the bug report open, since I shouldn't have had to run the sqlite command manually.
Component: Untriaged → Places
Product: Firefox → Toolkit
This already happened in the past to another user, for whatever reason some user is missing some indices, we could probably use the Places maintenance service to verify indices and recreate them. Though would be very nice to find the cause.

Did you ever modified manually the database, or install some add-on that could have acted on the database, or used a third party app/add-on to access it?
notice that you are also missing
CREATE INDEX moz_places_visitcount ON moz_places (visit_count)
I think in the past I have only inspected the database without modifying it, and I don't remember using any suspicious add-ons.  But it's hard to be sure [0].

Looks like I've been missing those indexes for more than two years (I just dug up a back-up of my places.sqlite file from March 2011).  The lack of those SQL indexes did not prevent me from using Firefox in that time.  I'm not sure what triggered the change in behaviour.


[0] I have in the past used some unofficial Firefox builds such as Iceweasel and NixOS's build which sometimes has bugs.  In terms of add-ons, I remember using HTTPS Everywhere and Firebug and something that controls text wrapping; I'm generally wary of things that want to touch my history or bookmarks.
could just be that moving the same profile across different products has caused mis-alignment of the database schema.
Keywords: perf
Summary: firefox hangs on startup due to slow places.sqlite query → firefox hangs for several minutes on startup due to slow places.sqlite query (and missing SQL indexes?)
I'm morphing the bug to make it more actionable.
Status: UNCONFIRMED → NEW
Ever confirmed: true
Summary: firefox hangs for several minutes on startup due to slow places.sqlite query (and missing SQL indexes?) → Investigate strategies to detect and add missing indices to places.sqlite
Priority: -- → P3
Blocks: 1410877
You need to log in before you can comment on or make changes to this bug.