Closed Bug 873796 Opened 12 years ago Closed 8 years ago

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

Categories

(Toolkit :: Places, defect)

Product:
Toolkit
Component:
Places
Platform:
x86_64
Windows 8
Type:
defect
Priority:
Not set
Severity:
normal

Tracking

()

Status:
RESOLVED WORKSFORME

People

(Reporter: geeknik, Unassigned)

Details

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 */
Component: General → Places
Product: Firefox → Toolkit
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 */
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.
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
Closed because it's 3 years later and despite Mozilla asking for this information, nobody cared about it. Moving on.
Status: NEW → RESOLVED
Closed: 8 years ago
Resolution: --- → WORKSFORME
You need to log in before you can comment on or make changes to this bug.