873796 - Slow SQL Statements on Helper Threads (Windows x64 Firefox Nightly)

Status: RESOLVED WORKSFORME

(Toolkit :: Places, defect)

Description

[Brian Carpenter [:geeknik]]

I'm running the 15 May 2013 Windows x64 build of Firefox Nightly (Built from http://hg.mozilla.org/mozilla-central/rev/b30552dbb013) on Windows 8 Pro x64 and found these slow SQL statements in about:telemetry. I only have around 50 bookmarks in the bookmarks toolbar only (rest of my bookmarks are on pinboard.in). I let Firefox control the History (at least 6 months worth of history is available).

Hits 	Avg. Time (ms) 	Statement
8 	478 	ANALYZE moz_places /* places.sqlite */

134 	1114 	SELECT h.url, h.title, f.url, EXISTS(SELECT 1 FROM moz_bookmarks WHERE fk = h.id) AS bookmarked, ( SELECT title FROM moz_bookmarks WHERE fk = h.id AND title NOTNULL ORDER BY lastModified DESC LIMIT 1 ) AS btitle, ( SELECT GROUP_CONCAT(t.title, :private) FROM moz_bookmarks b JOIN moz_bookmarks t ON t.id = +b.parent AND t.parent = :parent WHERE b.fk = h.id ) AS tags, h.visit_count, h.typed, h.id, :query_type, t.open_count FROM moz_places h LEFT JOIN moz_favicons f ON f.id = h.favicon_id LEFT JOIN moz_openpages_temp t ON t.url = h.url WHERE h.frecency <> 0 AND AUTOCOMPLETE_MATCH(:searchString, h.url, IFNULL(btitle, h.title), tags, h.visit_count, h.typed, bookmarked, t.open_count, :matchBehavior, :searchBehavior) ORDER BY h.frecency DESC, h.id DESC LIMIT :maxResults /* places.sqlite */

1 	104 	UPDATE moz_places SET frecency = CALCULATE_FRECENCY(:page_id) WHERE id = :page_id /* places.sqlite */

8 	567 	ANALYZE moz_historyvisits /* places.sqlite */

3 	379 	/* do not warn (bug no): can't use an index */ SELECT h.url FROM moz_places h WHERE h.frecency <> 0 AND h.typed = 1 AND AUTOCOMPLETE_MATCH(:searchString, h.url, h.title, :private, h.visit_count, h.typed, 0, 0, :matchBehavior, :searchBehavior) ORDER BY h.frecency DESC, h.id DESC LIMIT 1 /* places.sqlite */

Comment 1

[Brian Carpenter [:geeknik]]

Running the 6 June 2013 Win64 Nightly (Built from http://hg.mozilla.org/mozilla-central/rev/204de5b7e0a6):

Hits 	Avg. Time (ms) 	Statement
3 	322 	/* do not warn (bug no): can't use an index */ SELECT h.url FROM moz_places h WHERE h.frecency <> 0 AND h.typed = 1 AND AUTOCOMPLETE_MATCH(:searchString, h.url, h.title, :private, h.visit_count, h.typed, 0, 0, :matchBehavior, :searchBehavior) ORDER BY h.frecency DESC, h.id DESC LIMIT 1 /* places.sqlite */

1 	491 	ANALYZE moz_historyvisits /* places.sqlite */

1 	398 	ANALYZE moz_places /* places.sqlite */

5 	1462 	SELECT h.url, h.title, f.url, EXISTS(SELECT 1 FROM moz_bookmarks WHERE fk = h.id) AS bookmarked, ( SELECT title FROM moz_bookmarks WHERE fk = h.id AND title NOTNULL ORDER BY lastModified DESC LIMIT 1 ) AS btitle, ( SELECT GROUP_CONCAT(t.title, :private) FROM moz_bookmarks b JOIN moz_bookmarks t ON t.id = +b.parent AND t.parent = :parent WHERE b.fk = h.id ) AS tags, h.visit_count, h.typed, h.id, :query_type, t.open_count FROM moz_places h LEFT JOIN moz_favicons f ON f.id = h.favicon_id LEFT JOIN moz_openpages_temp t ON t.url = h.url WHERE h.frecency <> 0 AND AUTOCOMPLETE_MATCH(:searchString, h.url, IFNULL(btitle, h.title), tags, h.visit_count, h.typed, bookmarked, t.open_count, :matchBehavior, :searchBehavior) AND +h.visit_count > 0 ORDER BY h.frecency DESC, h.id DESC LIMIT :maxResults /* places.sqlite */

Comment 2

[(mostly gone) XtC4UaLL [:xtc4uall]]

Over here get the same/a similar Distribution (on a lower ms Level):

1 	248 	UPDATE moz_places SET frecency = CALCULATE_FRECENCY(id) WHERE frecency < 0 /* places.sqlite */

3 	158 	/* do not warn (bug no): can't use an index */ SELECT h.url FROM moz_places h WHERE h.frecency <> 0 AND h.typed = 1 AND AUTOCOMPLETE_MATCH(:searchString, h.url, h.title, :private, h.visit_count, h.typed, 0, 0, :matchBehavior, :searchBehavior) ORDER BY h.frecency DESC, h.id DESC LIMIT 1 /* places.sqlite */

23 	297 	SELECT h.url, h.title, f.url, EXISTS(SELECT 1 FROM moz_bookmarks WHERE fk = h.id) AS bookmarked, ( SELECT title FROM moz_bookmarks WHERE fk = h.id AND title NOTNULL ORDER BY lastModified DESC LIMIT 1 ) AS btitle, ( SELECT GROUP_CONCAT(t.title, :private) FROM moz_bookmarks b JOIN moz_bookmarks t ON t.id = +b.parent AND t.parent = :parent WHERE b.fk = h.id ) AS tags, h.visit_count, h.typed, h.id, :query_type, t.open_count FROM moz_places h LEFT JOIN moz_favicons f ON f.id = h.favicon_id LEFT JOIN moz_openpages_temp t ON t.url = h.url WHERE h.frecency <> 0 AND AUTOCOMPLETE_MATCH(:searchString, h.url, IFNULL(btitle, h.title), tags, h.visit_count, h.typed, bookmarked, t.open_count, :matchBehavior, :searchBehavior) ORDER BY h.frecency DESC, h.id DESC LIMIT :maxResults /* places.sqlite */

1 	140 	COMMIT TRANSACTION /* places.sqlite */
1 	409 	UPDATE moz_places SET frecency = ROUND(frecency * .975) WHERE frecency > 0 /* places.sqlite */ 

Places Stats:
> Statistics
Database size is 30720 KiB
user_version is 22
page_size is 32768
cache_size is -2048
journal_mode is wal
synchronous is 1
History can store a maximum of 87202 unique pages
Table moz_places has 44832 records
Table moz_historyvisits has 74924 records
Table moz_inputhistory has 354 records
Table moz_bookmarks has 9138 records
Table moz_bookmarks_roots has 5 records
Table moz_keywords has 101 records
Table sqlite_sequence has 1 records
Table moz_favicons has 2345 records
Table moz_anno_attributes has 23 records
Table moz_annos has 2502 records
Table moz_items_annos has 1973 records
Table sqlite_stat1 has 15 records
Table moz_hosts has 5858 records
Index sqlite_autoindex_moz_inputhistory_1
Index sqlite_autoindex_moz_bookmarks_roots_1
Index sqlite_autoindex_moz_keywords_1
Index sqlite_autoindex_moz_favicons_1
Index sqlite_autoindex_moz_anno_attributes_1
Index sqlite_autoindex_moz_hosts_1
Index moz_places_faviconindex
Index moz_places_hostindex
Index moz_places_visitcount
Index moz_places_frecencyindex
Index moz_places_lastvisitdateindex
Index moz_historyvisits_placedateindex
Index moz_historyvisits_fromindex
Index moz_historyvisits_dateindex
Index moz_bookmarks_itemindex
Index moz_bookmarks_parentindex
Index moz_bookmarks_itemlastmodifiedindex
Index moz_places_url_uniqueindex
Index moz_places_guid_uniqueindex
Index moz_bookmarks_guid_uniqueindex
Index moz_annos_placeattributeindex
Index moz_items_annos_itemattributeindex

Sync is used, FWIW.

Comment 3

[Brian Carpenter [:geeknik]]

Just wanted to drop in with an update with the latest Win64 Nightly (Built from http://hg.mozilla.org/mozilla-central/rev/cf378dddfac8):

3 	101 	INSERT INTO moz_places (url, title, rev_host, hidden, typed, frecency, guid) VALUES (:url, :title, :rev_host, :hidden, :typed, :frecency, :guid)
35 	588 	SELECT h.url, h.title, f.url, EXISTS(SELECT 1 FROM moz_bookmarks WHERE fk = h.id) AS bookmarked, ( SELECT title FROM moz_bookmarks WHERE fk = h.id AND title NOTNULL ORDER BY lastModified DESC LIMIT 1 ) AS btitle, ( SELECT GROUP_CONCAT(t.title, :private) FROM moz_bookmarks b JOIN moz_bookmarks t ON t.id = +b.parent AND t.parent = :parent WHERE b.fk = h.id ) AS tags, h.visit_count, h.typed, h.id, :query_type, t.open_count FROM moz_places h LEFT JOIN moz_favicons f ON f.id = h.favicon_id LEFT JOIN moz_openpages_temp t ON t.url = h.url WHERE h.frecency <> 0 AND AUTOCOMPLETE_MATCH(:searchString, h.url, IFNULL(btitle, h.title), tags, h.visit_count, h.typed, bookmarked, t.open_count, :matchBehavior, :searchBehavior) ORDER BY h.frecency DESC, h.id DESC LIMIT :maxResults
3 	424 	DELETE FROM moz_places WHERE id IN ( SELECT p_id FROM expiration_notify WHERE p_id NOTNULL )
1 	109 	DELETE FROM moz_places WHERE id IN (390149,391719,391158,392474,393165,392907,392486,391718,392105,392221,392263,391717,393469,391347,392904,392575,392905,392909,393488,392294,392908,389513,390647,390908,392906,390167)
1 	520 	SELECT h.id, url, guid, visit_date, visit_type, (SELECT count(*) FROM moz_historyvisits WHERE place_id = h.id) as full_visit_count, EXISTS(SELECT 1 FROM moz_bookmarks WHERE fk = h.id) as bookmarked FROM moz_historyvisits JOIN moz_places h ON place_id = h.id WHERE visit_type = 7
5 	180 	ANALYZE moz_historyvisits
9 	236 	/* do not warn (bug no): can't use an index */ SELECT h.url FROM moz_places h WHERE h.frecency <> 0 AND h.typed = 1 AND AUTOCOMPLETE_MATCH(:searchString, h.url, h.title, :private, h.visit_count, h.typed, 0, 0, :matchBehavior, :searchBehavior) ORDER BY h.frecency DESC, h.id DESC LIMIT 1
5 	300 	ANALYZE moz_places 

> Statistics
Database size is 61440 KiB
user_version is 23
page_size is 32768
cache_size is -2048
journal_mode is wal
synchronous is 1
History can store a maximum of 104858 unique pages
Table moz_places has 105251 records
Table moz_historyvisits has 171560 records
Table moz_inputhistory has 234 records
Table moz_hosts has 11185 records
Table moz_bookmarks has 293 records
Table moz_bookmarks_roots has 5 records
Table moz_keywords has 0 records
Table sqlite_sequence has 1 records
Table moz_favicons has 7761 records
Table moz_anno_attributes has 9 records
Table moz_annos has 51 records
Table moz_items_annos has 25 records
Table sqlite_stat1 has 15 records
Index sqlite_autoindex_moz_inputhistory_1
Index sqlite_autoindex_moz_hosts_1
Index sqlite_autoindex_moz_bookmarks_roots_1
Index sqlite_autoindex_moz_keywords_1
Index sqlite_autoindex_moz_favicons_1
Index sqlite_autoindex_moz_anno_attributes_1
Index moz_places_faviconindex
Index moz_places_hostindex
Index moz_places_visitcount
Index moz_places_frecencyindex
Index moz_places_lastvisitdateindex
Index moz_historyvisits_placedateindex
Index moz_historyvisits_fromindex
Index moz_historyvisits_dateindex
Index moz_bookmarks_itemindex
Index moz_bookmarks_parentindex
Index moz_bookmarks_itemlastmodifiedindex
Index moz_places_url_uniqueindex
Index moz_places_guid_uniqueindex
Index moz_bookmarks_guid_uniqueindex
Index moz_annos_placeattributeindex
Index moz_items_annos_itemattributeindex

Comment 4

[Brian Carpenter [:geeknik]]

Closed because it's 3 years later and despite Mozilla asking for this information, nobody cared about it. Moving on.