744492 - Revise math for explosiveness

Status: RESOLVED FIXED

(Socorro :: Database, task)

Description

[[:jberkus] Josh Berkus]

Per Kairo's direction and discussion on bug 733021, revise the math in the update_explosiveness stored procedure.

Comment 1

[Robert Kaiser]

(In reply to [:jberkus] Josh Berkus from comment #20)
> The averaging of ADU happens further
> down in the query.

I'm not really sure how/if I can read that out of the query.

> Limit and order will be applied by the UI/Middleware code.

OK, sounds good.

> So, one problem which I realized with the test query; currently, if there
> are no rows in the comparison set (e.g. no crash reports for the prior 9
> days) for a specific signature, then we get no results.  I don't think
> that's what you intend (since a signature jumping from nothing to 400 would
> be explosive indeed), so I'll be fixing that too.

IIRC, my current code always fills up to full set of 10 days with zero values.

(In reply to [:jberkus] Josh Berkus from comment #22)
> So, there's a problem with your three-day formula.  I can't do a STDDEV
> unless there's at least 3 days of data.  For many signatures, there's less
> than that.
> 
> How do you want to modify the formula for signatures where an stddev isn't
> possible?

As said above, my current code always fill up a "full" set with zero values, with that it can always work correctly. Not sure if you can do that reasonably in SQL.

Comment 2

[[:jberkus] Josh Berkus]

> As said above, my current code always fill up a "full" set with zero values,
> with that it can always work correctly. Not sure if you can do that
> reasonably in SQL.

No problem.  I just haven't been able to code it yet.

Comment 3

[[:jberkus] Josh Berkus]

Attachment: one day explosiveness

Kairo,

I've attached the numbers from my revised math.  These are the explosiveness numbers for Firefox 11.0 (release) on April 10th.  The oneday file is the top 20 one-day explosivenesses, and the threeday file is the top 3-day explosivenesses.  Does this data look correct to you?

Comment 4

[[:jberkus] Josh Berkus]

Attachment: three day explosiveness

Comment 5

[Robert Kaiser]

The 1-day values mostly match https://crash-analysis.mozilla.com/rkaiser/2012-04-10/2012-04-10.firefox.11.explosiveness.html (which is for all 11.* so slightly different set than release but a very similar set) but the 3-day values look completely different. Can you give me the 10 rate values in addition? Those should be similar to the values in my table as well.

Comment 6

[[:jberkus] Josh Berkus]

"10 rate values"?   I'm not sure what statistic you're referring to.

Comment 7

[[:jberkus] Josh Berkus]

Oh!  Wait, found a math error in the code.  Will revise and give you new data.

Comment 8

[[:jberkus] Josh Berkus]

Attachment: revised threeday file

Kairo,

Revised three-day explosiveness.  This one looks much better numerically to me.  Fixed an erroneous () placement in the math.

Comment 9

[Robert Kaiser]

(In reply to [:jberkus] Josh Berkus from comment #8)
> Revised three-day explosiveness.  This one looks much better numerically to
> me.  Fixed an erroneous () placement in the math.

Are those for April 10 as well? The numbers look more believable but don't match my numbers from comment #5 very much, I thought the oneday values matched better in the other examples.

(In reply to [:jberkus] Josh Berkus from comment #6)
> "10 rate values"?   I'm not sure what statistic you're referring to.

I was referring to the crash rates of the analyzed day and the 9 days before, which my reports (and the reports we want to get in UI in the end) also show - I think crash_madu in your code.

Comment 10

[[:jberkus] Josh Berkus]

Attachment: new explosiveness chart

kairo,

so here's a file which includes the crash_madu for both the oneday and for the threeday (as oneday_rate and threeday_rate)  BTW, the UI mockup did not include displaying the daily crash rates, so you might want to check that you're getting the UI you expect.

Comment 11

[Robert Kaiser]

(In reply to [:jberkus] Josh Berkus from comment #10)
> so here's a file which includes the crash_madu for both the oneday and for
> the threeday (as oneday_rate and threeday_rate)

Now I'm confused. There should be 10 values that are a common pool for all those caclulations, I thought? Also, this is for 04-10 still or for a different date?

> BTW, the UI mockup did not
> include displaying the daily crash rates, so you might want to check that
> you're getting the UI you expect.

Attachment 597776 [details] includes them. There will be a collapsed and an expanded view, display of those daily rates being the difference between them.

Comment 12

[[:jberkus] Josh Berkus]

> Now I'm confused. There should be 10 values that are a common pool for all
> those caclulations, I thought? Also, this is for 04-10 still or for a
> different date?

It's still 4/10.  The values attached are for crash_madu for the final day for oneday, and the average crash_madu for the 3 day.

> 
> > BTW, the UI mockup did not
> > include displaying the daily crash rates, so you might want to check that
> > you're getting the UI you expect.
> 
> Attachment 597776 [details] includes them. There will be a collapsed and an
> expanded view, display of those daily rates being the difference between
> them.

Oh!  Feh, I didn't understand what I was looking at.  I'll need to revise the matview for those ... I thought I was supplying just aggregates.

Comment 13

[[:jberkus] Josh Berkus]

Attachment: New explosiveness Firefox 11

Attached is a new csv, this one explosiveness of Firefox 12.0 as of April 10.

Looking at the crash_madu per day, I realized why my figures are slight different from yours: due to the shrunken database on crash-stats-dev, we're missing the first two days of data (April 1 and 2).  This throws a bunch of the calculations off slightly, especially for the 3 day/7day.

Comment 14

[[:jberkus] Josh Berkus]

Attachment: New explosiveness Firefox 12b3

Here's another CSV, showing you a release with more volatile numbers.  This is Firefox 12.0b3 on April 10.

One interesting factor is that non-release versions actually have *more* explosive signatures.  This is due to having *much* lower ADU numbers.

Comment 15

[Laura Thomson :laura]

I'm assuming this won't ship today?  Moving to 8.

Comment 16

[[:jberkus] Josh Berkus]

Pushing this for 8.0.

Comment 17

[Robert Kaiser]

(In reply to [:jberkus] Josh Berkus from comment #13)
> Looking at the crash_madu per day, I realized why my figures are slight
> different from yours: due to the shrunken database on crash-stats-dev, we're
> missing the first two days of data (April 1 and 2).  This throws a bunch of
> the calculations off slightly, especially for the 3 day/7day.

Sure, and looking at those daily rates, one can easily derive such things as well as when a regression happened, etc. - that's why I find them useful in a final UI, even if they're hidden by default. :)

(In reply to [:jberkus] Josh Berkus from comment #14)
> One interesting factor is that non-release versions actually have *more*
> explosive signatures.  This is due to having *much* lower ADU numbers.

As long as there's no dampening due to the clamping, yes, that's intended. We need to look more closely at development versions anyhow. :)


That said, I wonder why there are any "\N" values for the 1day or 3day explosiveness values in your calculations - with using 0 values for missing daily rates, I think we should always get some value...