718309 - Bookmark freezes - Bookmark library unmanageable any more

Status: RESOLVED INCOMPLETE

(Firefox :: Sync, defect)

Description

[Atlanx]

User Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv:9.0.1) Gecko/20100101 Firefox/9.0.1
Build ID: 20111220165912

Steps to reproduce:

Since some Firefox Versions ( i think since the Synchronize Feature was integratet ) the management of Bookmarks went unuseabel because every action in the bookmark library window ( delete, move, edit name/address/tags ) is followed by a freeze of the library window for 10 to 20seconds.

Comment 1

[Atlanx]

Steps to reproduce:

Open the Bookmark Library(Lesezeichen > Alle Lesezeichen anzeigen).

Open an bookmark folder (for example Lesezeichen-Symbolleiste)

Click on one of your Bookmarks.

Try to move it up or down in your Bookmarks or trie to change the Name of the bookmark.

Now try to do the same on an other Bookmark. You will not be able to do it because the library window is noch reacting until Firefox has done "something" in behind ( i think its synchronizing).

you have to wait 7 to 20 seconds. 

So, it is unpracticable to manage a bigger amount of Bookmarks.

Comment 2

[Marco Bonardo [:mak]]

Firefox 10 and 11 contain some fixes for these kind of hangs, in case you want to try them and report eventual improvements. If you plan to go back to stable once tested, I suggest making a backup of your profile before testing them. 

Also, may you check if Safe mode makes a difference? So we can exclude add-ons interaction. http://support.mozilla.org/kb/safe+mode

Comment 3

[Atlanx]

Confirmed. Same problem in Safe mode.

Comment 4

[Atlanx]

Test with new Firefox 10 Final (MacOS X):
-----------------------------------------

Much better now(compared to Firefox 9.0.1). 
Waiting time is now "only" around 5 seconds.

Thats much better - but still needs improvement.

5 seconds per action is still too much time for organizing a bigger amount of Bookmarks.

Comment 5

[Marco Bonardo [:mak]]

firefox 11 has even more improvements in this area, though not that large as the one in 10. I agree that there's still work to do here, I have some patches lying around that will further improve the situation.

Comment 6

[Atlanx]

Test with new Firefox 11 Final (MacOS X):
-----------------------------------------

Waiting time after changing the name of an entry
is still around 5 seconds.

5 seconds per action is still too much time for organizing 
a bigger amount of Bookmarks.

- The Question is - Why does it take this long time? 
Is Firefox synchronizing immediately after every little change?

And when yes - Why?

Why is it not synchronizing when there is no user input for 
longer time?

Comment 7

[Marco Bonardo [:mak]]

may be a lot of things, the first thing off my head is that you may have a large number of tags?

Comment 8

[Marco Bonardo [:mak]]

may you also try to cleanup with this https://addons.mozilla.org/firefox/addon/places-maintenance/ ?

It's not really usual that takes so much time to change a title, it's instant for most users.

Comment 9

[Atlanx]

Tags i have probably at 3 or 4 entries only. So this should not be the problem.

I will try the cleanup but i don't expect too much because still used 
http://code.google.com/p/sqlite-manager/ 
and 
http://www.crystalidea.com/speedyfox to compress the databases.

I have no real clue why my places.sqlite is still so big : 62,9 MB (62.914.560 Byte)

Comment 10

[Marco Bonardo [:mak]]

please use the above add-on and paste here the statistics.

Comment 11

[Atlanx]

Yes, i tried it now.

Before:
 places.sqlite 62,9MB

After (Preset: ALL):
 
 places.sqlite 52,4MB

 Nice, made it 10MB smaller. Very impressive.
 
> Integrity check
+ The database is sane
> Reindex
+ The database has been reindexed
> Orphans expiration
+ Database cleaned up
> Coherence check
+ The database is coherent
> Vacuum
Initial database size is 61440 KiB
+ The database has been vacuumed
Final database size is 51200 KiB
> Statistics
Database size is 51200 KiB
user_version is 16
page_size is 4096
cache_size is 1308
journal_mode is wal
synchronous is 1
History can store a maximum of 14564 unique pages
Table moz_bookmarks has 2930 records
Table moz_bookmarks_roots has 5 records
Table moz_keywords has 1 records
Table sqlite_sequence has 1 records
Table moz_favicons has 6984 records
Table moz_annos has 594 records
Table moz_anno_attributes has 32 records
Table moz_items_annos has 2960 records
Table moz_places has 74542 records
Table moz_historyvisits has 113229 records
Table moz_inputhistory has 508 records
Table sqlite_stat1 has 21 records
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_inputhistory_1
Index moz_bookmarks_itemindex
Index moz_bookmarks_parentindex
Index moz_bookmarks_itemlastmodifiedindex
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_annos_placeattributeindex
Index moz_items_annos_itemattributeindex
Index moz_places_url_uniqueindex
Index moz_bookmarks_guid_uniqueindex
Index moz_places_guid_uniqueindex
Index moz_favicons_guid_uniqueindex

After it Firefox "feels" faster.
But moving entries in Bookmarks takes still 6 seconds of freeze.

Comment 12

[Marco Bonardo [:mak]]

this "Table moz_items_annos has 2960 records" looks strange, do you use (or you used) XMarks or some other add-on syncing your bookmarks? Those look like GUIDs annotations (one per bookmark) used to sync.

It's also strange you are quite over the pages limit, those should have been expired :/

Comment 13

[Atlanx]

I only used weave bevore it was integratet in Firefox. 
I did not use any other kind of sync addon.

The only add-on i have in suspect for givin an inpact on the databases is http://brief.mozdev.org/
I have 20 RSS-Feeds abonized in it.

What is "pages limit" and what you mean with expired?

Comment 14

[Atlanx]

Interesting thing.

Under Windows 7 the size of places.sqlite is only 10MB. 
Shouldn't it have the same size as under Mac OS X because
i synchronize Bookmarks & Passwords with Firefox Sync.

But i don't synchronize Chronic.

Comment 15

[Atlanx]

places.sqlite under Windows:

> Integrity check
+ The database is sane
> Reindex
+ The database has been reindexed
> Orphans expiration
+ Database cleaned up
> Coherence check
+ The database is coherent
> Vacuum
Initial database size is 10240 KiB
+ The database has been vacuumed
Final database size is 10240 KiB
> Statistics
Database size is 10240 KiB
user_version is 16
page_size is 32768
cache_size is 128
journal_mode is wal
synchronous is 1
History can store a maximum of 80699 unique pages
Table moz_places has 3931 records
Table moz_historyvisits has 2003 records
Table moz_inputhistory has 16 records
Table moz_bookmarks has 2877 records
Table moz_bookmarks_roots has 5 records
Table moz_keywords has 1 records
Table sqlite_sequence has 1 records
Table moz_favicons has 230 records
Table moz_annos has 2 records
Table moz_anno_attributes has 12 records
Table moz_items_annos has 609 records
Table sqlite_stat1 has 15 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 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
Index moz_favicons_guid_uniqueindex

Comment 16

[Atlanx]

Ok, i made a new profile now. 
(First i synchronized a new FF on a differten PC with Chronic & Bookmarks.)

After installation(FF11/Mac) the size of the new
places.sqlite = 10,5MB

After synchronize my data back to MacFF the size of the new
places.sqlite = 10,5MB

(this is not a typo - the size really didnt change)

And now the time of freeze is around 4 seconds
if i do anything in the Bookmarks Manager.

This is 1 second better. (CPU: 2,4GHz Intel Core 2 Duo / 4GB Ram)
So, the last reason i can imagine: 
I have to many Bookmarks (Check Places counts: 2008 Bookmarks)
So, is 2008 Bookmarks too many for Firefox?

Comment 17

[Atlanx]

places.sqlite now: 10,5MB

> Integrity check
+ The database is sane
> Reindex
+ The database has been reindexed
> Orphans expiration
+ Database cleaned up
> Coherence check
+ The database is coherent
> Vacuum
Initial database size is 10240 KiB
+ The database has been vacuumed
Final database size is 10240 KiB
> Statistics
Database size is 10240 KiB
user_version is 16
page_size is 32768
cache_size is 128
journal_mode is wal
synchronous is 1
History can store a maximum of 100664 unique pages
Table moz_places has 7662 records
Table moz_historyvisits has 8913 records
Table moz_inputhistory has 1 records
Table moz_bookmarks has 2851 records
Table moz_bookmarks_roots has 5 records
Table moz_keywords has 1 records
Table sqlite_sequence has 1 records
Table moz_favicons has 16 records
Table moz_anno_attributes has 12 records
Table moz_annos has 0 records
Table moz_items_annos has 542 records
Table sqlite_stat1 has 15 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 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 18

[Marco Bonardo [:mak]]

(In reply to Atlanx from comment #16)
> After installation(FF11/Mac) the size of the new
> places.sqlite = 10,5MB
> 
> After synchronize my data back to MacFF the size of the new
> places.sqlite = 10,5MB
> 
> (this is not a typo - the size really didnt change)

That's fine, we chunk grow the database in 10MB steps, so it's expected.

> So, the last reason i can imagine: 
> I have to many Bookmarks (Check Places counts: 2008 Bookmarks)
> So, is 2008 Bookmarks too many for Firefox?

Nope, we have users with tens thousands, that don't suffer any kind of slowdown.
Though, as you said you have many RSS, and that may be an issue (that could be solved in Firefox 13) cause those keep updating in background.

May you install the about:telemetry add-on, and, after an hang happens, go to about:telemetry and paste here the list of slow SQL statements that is reported on the top of the page?

Comment 19

[Atlanx]

On top of a:t result page is only CACHE_DEVICE_SEARCH and CACHE_DISK_SEARCH.
Can you clarify which field you mean?

(If i search for "slow" at the a:t result page i get no hit)

There are some fields with MOZ_SQLITE_ starting in the under half ot the page.

Comment 20

[Marco Bonardo [:mak]]

there should be a "Slow SQL Statements" section at the top of the page, before any histogram and after the search field row.

Comment 21

[Atlanx]

Attachment: about:telemetry data

Telemetry Data appeared today. (FF12 / Mac)

Comment 22

[Marco Bonardo [:mak]]

Comment on attachment 617435 [details]
about:telemetry data

I don't see anything particularly bad there, some items annotations queries, supposed to disappear with FF14, but just a few of them.
I have no further ideas offhand, if you're comfortable with sharing your places.sqlite file I may directly look at it.  Since it contains all of your bookmarks and history, that is privacy sensitive data, you should at a maximum send it to me by mail, provided its size allows that.

Comment 23

[Atlanx]

Telemetry direct after seeing wheel-of-death for some seconds (website browsing):

Slow SQL Statements on Main Thread Hits	Avg. Time (ms)	Statement
4	263	SELECT ROUND(( strftime('%s','now','localtime','utc') - ( SELECT visit_date FROM moz_historyvisits ORDER BY visit_date ASC LIMIT 1 )/1000000 )/86400) AS daysOfHistory
2	187	UPDATE moz_bookmarks SET lastModified = :date WHERE id = :item_id
4	380	UPDATE moz_hosts SET permission = ?2, expireType= ?3, expireTime = ?4 WHERE id = ?1
1	162	INSERT OR REPLACE INTO webappsstore2 SELECT scope, key, value, secure, owner FROM webappsstore2_temp WHERE scope = :scope AND modified = 1
5	169	DELETE FROM moz_items_annos WHERE item_id = :item_id AND anno_attribute_id = (SELECT id FROM moz_anno_attributes WHERE name = :anno_name)
1	346	INSERT INTO webappsstore2_temp (scope, key, value, secure, owner) SELECT scope, key, value, secure, owner FROM webappsstore2 WHERE scope = :scope AND NOT EXISTS ( SELECT scope, key FROM webappsstore2_temp WHERE scope = webappsstore2.scope AND key = webappsstore2.key )
Slow SQL Statements on Other Thread Hits	Avg. Time (ms)	Statement
20	312	SELECT COUNT(1) FROM moz_classifier
9	693	INSERT OR REPLACE INTO moz_classifier VALUES (?1, ?2, ?3, ?4, ?5, ?6)
1	214	UPDATE moz_places SET frecency = CALCULATE_FRECENCY(:page_id) WHERE id = :page_id
1	136	SELECT domain, partial_data, complete_data FROM moz_classifier
4	233	SELECT * FROM moz_classifier WHERE domain=?1 AND table_id=?2 AND chunk_id=?3
46	207	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, ',') 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
2	138	SELECT h.id, h.favicon_id, h.guid, (SELECT h.url FROM moz_bookmarks b WHERE b.fk = h.id UNION ALL SELECT (SELECT url FROM moz_places WHERE id = COALESCE(greatgrandparent.place_id, grandparent.place_id, parent.place_id)) FROM moz_historyvisits self JOIN moz_bookmarks b ON b.fk = COALESCE(greatgrandparent.place_id, grandparent.place_id, parent.place_id) LEFT JOIN moz_historyvisits parent ON parent.id = self.from_visit LEFT JOIN moz_historyvisits grandparent ON parent.from_visit = grandparent.id AND parent.visit_type IN (5, 6) LEFT JOIN moz_historyvisits greatgrandparent ON grandparent.from_visit = greatgrandparent.id AND grandparent.visit_type IN (5, 6) WHERE self.visit_type IN (5, 6) AND self.place_id = h.id LIMIT 1 ) FROM moz_places h WHERE h.url = :page_url
1	172	DELETE FROM moz_subs WHERE table_id=?1 AND chunk_id=?2
8	334	DELETE FROM moz_classifier WHERE id=?1
1	116	SELECT guid FROM moz_bookmarks WHERE id = :item_id
7	322	UPDATE moz_places SET title = :page_title WHERE id = :page_id
15	216	DELETE FROM moz_classifier WHERE table_id=?1 AND chunk_id=?2

Comment 24

[Atlanx]

Test with new Firefox 13 Final (MacOS X):
-----------------------------------------

WUWUWUWUWUWU - Congratulations!!!

I don't know which fixes where made from 
12 to 13 but its GREAT now!

Nearly no freezing at all.

Move 1 item 
Move second item.
No freezing.
Move Move Move - no freezing.

Sometimes still a 2 second freezing but thats ok.

Or if you move a bunch of Bookmarks together. 
Sometimes 2 second freezing.

But thats really a big jump from unuseable to useable.

All work which was put in this was worth it!

GoGo - have a Party! The Cake is not a lie!

Comment 25

[Thomas Wolff]

I've tried with Firefox 13 and I wonder how this can be closed. 
I still have freezes of ~10seconds (in words: ten) whenever I move a group of bookmarks into a folder.
Unbelievable, unacceptable, unusable!

Comment 26

[Marco Bonardo [:mak]]

you probably just see another bug, not this one, provided it's not due to an add-on.

Comment 27

[Thomas Wolff]

Maybe its bug #755669, I just posted some additional diagnostics there (as you had requested).

Symptoms are similar anyway, and I'm quite enervated that this is one instance in a long series of nuisances since the bookmarks system was turned into a techno-driven, not user-aware mess with Firefox 3, ignoring any user-focused features and reasonable requests.

Comment 28

[Atlanx]

Brrrrr- Regression at Firefox 16.0.1

Sorry, i have to say that "long waiting" is back in Firefox 16.0.1

Every action in the bookmarks on Firefox 16.0.1 takes perceived(felt) 5 seconds of freezing.

Comment 29

[Atlanx]

about:telemetry data

 Slow SQL Statements on Main Thread Hits	

Avg. Time (ms)	Statement
1	270	INSERT OR REPLACE INTO webappsstore2 SELECT scope, key, value, secure, owner FROM webappsstore2_temp WHERE scope = :scope AND modified = 1
3	155	Untracked SQL for places.sqlite
1	311	Untracked SQL for chromeappsstore.sqlite
2	216	Untracked SQL for lazarus.sqlite
1	196	SELECT value, ROUND( timesUsed / MAX(1.0, (lastUsed - firstUsed) / :timeGroupingSize) * MAX(1.0, :maxTimeGroupings - (:now - lastUsed) / :timeGroupingSize) * MAX(1.0, :agedWeight * (firstUsed < :expiryDate)) / :bucketSize , 3) AS frecency, 1 AS boundaryBonuses FROM moz_formhistory WHERE fieldname=:fieldname AND (value LIKE :valuePrefix ESCAPE '/') ORDER BY ROUND(frecency * boundaryBonuses) DESC, UPPER(value) ASC


Slow SQL Statements on Other Thread Hits	

Avg. Time (ms)	Statement
1	583	SELECT id, title, hidden, typed, guid FROM moz_places WHERE url = :page_url
6	173	UPDATE moz_places SET title = :page_title WHERE id = :page_id
9	254	Untracked SQL for places.sqlite
1	113	INSERT OR REPLACE INTO moz_classifier VALUES (?1, ?2, ?3, ?4, ?5, ?6)
11	333	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, ',') 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
12	124	SELECT COUNT(1) FROM moz_classifier
1	366	ANALYZE moz_places

Comment 30

[Marco Bonardo [:mak]]

there's nothing interesting in the reported queries, times are in the expected range.
I also think basically nothing changed between these versions, regarding bookmarks performance.
Do you have any add-ons that may use bookmarks?

Comment 31

[Atlanx]

Too-Many-Tabs 1.3.8 https://addons.mozilla.org/de/firefox/addon/toomanytabs-saves-your-memory/
& 
Brief 1.7a1 http://brief.mozdev.org/

could have effect on Bookmarks probably.

Comment 32

[(dormant account)]

Sync is doing main thread io calling nsNavHistory::GetDaysOfHistory(eg SELECT ROUND(( strftime('%s','now','localtime','utc') - ( SELECT visit_date FROM moz_historyvisits ORDER BY visit_date ASC LIMIT 1 )/1000000 )/86400) AS daysOfHistory  /* places.sqlite */). This is causing contention & freezes

Comment 33

[Marco Bonardo [:mak]]

I'm resolving this cause there are too many reports mixed up, please file single reports for each slow query on mainthread you see or with clear steps to reproduce.
Regardless, looks like the long hang reported in comment 0 has been fixed time ago (apart some regression that looks like sneaked into the product later), so this is just collecting reports of other slowdowns that may end up being ignored just cause they are not properly tracked.