966469 - Slow queries in netpredictions.sqlite

Status: RESOLVED DUPLICATE of bug 1009122

(Core :: Networking, defect)

Description

[Richard Newman [:rnewman]]

Was poking around in about:telemetry on my phone, saw these.

Hits Avg. Time (ms) Statement
8 126 COMMIT TRANSACTION /* netpredictions.sqlite */
1 138 BEGIN DEFERRED /* netpredictions.sqlite */
9 118 PRAGMA page_count; /* netpredictions.sqlite */
1 103 PRAGMA page_size; /* netpredictions.sqlite */

Comment 1

[Guilherme Lima]

On Windows 7, checking "Slow SQL Statements on Helper Threads" on about:telemetry I'm seeing, right now:

1 	109 	COMMIT TRANSACTION /* netpredictions.sqlite */ 
1 	2192 	VACUUM; /* netpredictions.sqlite */ 
1 	5198 	DELETE FROM moz_subresources WHERE id IN (SELECT id FROM moz_subresources ORDER BY last_hit ASC LIMIT :limit); /* netpredictions.sqlite */ 
1 	1109 	SELECT COUNT(id) FROM moz_subresources /* netpredictions.sqlite */ 
1 	21966 	DELETE FROM moz_pages WHERE id NOT IN (SELECT DISTINCT(pid) FROM moz_subresources); /* netpredictions.sqlite */

Nightly is open for a few hours.

Comment 2

[Richard Newman [:rnewman]]

Ouch. Those are some nasty numbers for a desktop.

Comment 3

[Virtual_ManPL [:Virtual] 🇵🇱 - (please needinfo? me - so I will see your comment/reply/question/etc.)]

It's also a problem on desktop
http://forums.mozillazine.org/viewtopic.php?f=23&t=2813183

Comment 4

[Tyler Downer [He/Him]]

We are seeing a rise in reports of Firefox.exe not closing quickly on exit, and some contributors on the support forum feels this may be the cause. One specific case, https://support.mozilla.org/en-US/questions/998449


Can evaluate for a 29.0.1? Thanks!

Comment 5

[Robert Kaiser]

netpredictions/Seer is a new feature in 29, not sure how much it even brings for the user yet.
We had a few crashes with that database that got fixed in the 29 cycle, if we still have that bad SQL slowness there, I wonder if it actually was ship-ready, though.

Comment 6

[Sylvestre Ledru [:Sylvestre]]

Does that impact also 30 & 31?

Comment 7

[Richard Newman [:rnewman]]

For more context, see also Bug 947745 (disk size regression), Bug 945779 (CPU regression), Bug 961209 and Bug 960834 (storing too much data).

Nick Hurley should have the scoop on whether 30 or 31 are better re this bug.

Comment 8

[Virtual_ManPL [:Virtual] 🇵🇱 - (please needinfo? me - so I will see your comment/reply/question/etc.)]

It would be nice to see some synthetic benchmark to compare performance with and without this feature.

Comment 9

[u408661]

(In reply to Virtual_ManPL [:Virtual] from comment #8)
> It would be nice to see some synthetic benchmark to compare performance with
> and without this feature.

We have one, see bug 881804 comment 103 (specifically the part about a 10-25% improvement in SpeedIndex on repeated page loads).

Comment 10

[u408661]

The bug to disable this feature on all branches is bug 1005958

Comment 11

[Virtual_ManPL [:Virtual] 🇵🇱 - (please needinfo? me - so I will see your comment/reply/question/etc.)]

(In reply to Sylvestre Ledru [:sylvestre] from comment #6)
> Does that impact also 30 & 31?

Yes.

(In reply to Nicholas Hurley [:hurley] from comment #9)
> (In reply to Virtual_ManPL [:Virtual] from comment #8)
> > It would be nice to see some synthetic benchmark to compare performance with
> > and without this feature.
> 
> We have one, see bug 881804 comment 103 (specifically the part about a
> 10-25% improvement in SpeedIndex on repeated page loads).

Thank you. Missed that one.

Comment 12

[Lukas Blakk [:lsblakk] use ?needinfo]

We've taken bug 1005958 for FF29.0.1, tracking this bug for the better fix in later versions.

Comment 13

[David G King]

Here is data from Telemetry on Firefox/Win 7 v30 beta channel soon after a restart of Firefox.

When I have Seer enabled via network.seer.enabled I often have Firefox and other applications reporting "Not Responding" and slow response times.

Slow SQL Statements on Main Thread Hits 	Avg. Time (ms) 	Statement
2 	942 	COMMIT TRANSACTION /* places.sqlite */
Slow SQL Statements on Helper Threads Hits 	Avg. Time (ms) 	Statement
1 	81826 	DELETE FROM moz_pages; /* netpredictions.sqlite */
4 	202 	UPDATE moz_hosts SET permission = ?2, expireType= ?3, expireTime = ?4 WHERE id = ?1 /* permissions.sqlite */
1 	146 	DELETE FROM moz_subresources WHERE id IN (SELECT id FROM moz_subresources ORDER BY last_hit ASC LIMIT :limit); /* netpredictions.sqlite */
1 	123 	/* c:/builds/moz2_slave/rel-m-beta-w32_bld-00000000000/build/storage/src/mozStorageConnection.cpp */ PRAGMA cache_size = -2048 /* netpredictions.sqlite */
3 	348 	COMMIT TRANSACTION /* places.sqlite */
1 	133 	SELECT DISTINCT scope FROM webappsstore2 /* webappsstore.sqlite */

Comment 14

[Lukas Blakk [:lsblakk] use ?needinfo]

Nick - what's the status of this bug? Is the main work being done elsewhere for the 'proper' fix here?  Do we need to look at doing the same disabling in FF30 at this stage? We're two weeks away from RC.

Comment 15

[u408661]

Lukas - this bug will (eventually) be resolved by the backend rewrite (bug 1009122). Until then, the seer has been disabled on all branches, so we don't need to worry about this for any upcoming releases.

Comment 16

[Lukas Blakk [:lsblakk] use ?needinfo]

Thanks Nick, no longer need to track in that case.

Comment 17

[Liz Henry (:lizzard) (relman/hg->git project)]

It looks like the activity on this is happening in bug 1009122 so I'm removing the qawanted tag. 
Nicholas, is it reasonable to WONTFIX this bug based on Comment 15?

Comment 18

[u408661]

(In reply to Liz Henry :lizzard from comment #17)
> Nicholas, is it reasonable to WONTFIX this bug based on Comment 15?

Yep. There are actually quite a few bugs that will have to be WONTFIXed, but I'll track the rest down once I finish bug 1009122 :)

Comment 19

[Virtual_ManPL [:Virtual] 🇵🇱 - (please needinfo? me - so I will see your comment/reply/question/etc.)]

more like marked as WORKSFORME or DUPLICATE, as they will be "FIXED" by rewriting Seer ;d