755297 - Database changes to support rapid betas

Status: RESOLVED FIXED

(Socorro :: Database, task, P1)

Description

[Laura Thomson :laura]

As per
https://wiki.mozilla.org/Socorro:Rapid_Betas:Requirements

Changes are as follows:
- Update matviews and stored procs to remove separate beta tracking
- Update matviews and storeid procs for ADU processing to reflect new model
- Create matviews and stored procs for Top Crashers by Build Date
- Create matviews and stored procs for Crashers per user by Build Date

Comment 1

[[:jberkus] Josh Berkus]

also:

- refactor data source for daily graphs and home page
- refactor data source for TCBS

Comment 2

[[:jberkus] Josh Berkus]

So, we can't really remove separate beta tracking, because:

a) old versions will still use it
b) non-Firefox products will still use it

So changing the code is going to be phased -- we'll need to add support for rapid betas, and remove support for non-rapid betas in some future version.

Comment 3

[[:jberkus] Josh Berkus]

so, task list:

table products:
	add rapid_beta_version
	
function update_product_versions:
	add code to handle rapid betas
	
table tcbs
	unspecified changes to support refactor of mware/UI
	
modify update_tcbs
	to support above.
	
tables daily_crashes, product_adu => crashes_per_adu
	unify tables into a single matview
	refactor tables to support better UI/mware design
	
modify functions daily_adu, update_daily_crashes
	to support above.
	
add table tcbs_build
	new table which aggregates tcbs by build date, not crash date
	
add table crashes_per_adu_build
	new table which gives crashes/ADU for each build, 
	not just each release
	
add function update_tcbs_build
	to maintain matview
	
add function update_crashes_per_adu_build
	to maintain matview

Comment 4

[[:jberkus] Josh Berkus]

Questions for Laura:

1. Can Release/ESR versions be excluded from the by-build TCBS and Crashes tables?  Looking at graphs by build date for these would be quite useless (one huge spike) and excluding them would considerably shrink the sizes of these matviews.

2. Who will be working on the UI/Mware code for the TCBS, home page and daily graph screens?  We need to collaborate on refactoring.

Comment 5

[Robert Kaiser]

(In reply to [:jberkus] Josh Berkus from comment #4)
> 1. Can Release/ESR versions be excluded from the by-build TCBS and Crashes
> tables?

I'm not Laura, but I think the answer to this is yes. We can exclude everything from this that doesn't have multiple builds per week by design.

Comment 6

[[:jberkus] Josh Berkus]

More work:

- Fold old releases and crashes into new RR data structure as preparation for purging OldTCBS.

INCLUDES:

- Modify all cronjobs in order to handle old crashes and munge them to support new data structures.

Comment 7

[[DEACTIVATED] Adrian Gaudebert]

(In reply to [:jberkus] Josh Berkus from comment #4)
> 2. Who will be working on the UI/Mware code for the TCBS, home page and
> daily graph screens?  We need to collaborate on refactoring.

Josh, I'll be working on the middleware, so I'd like to know what you plan to do for TCBS. Are you going to create a new, different view or will you just update the existing one? 

Any spec you have could be usefull for me as I need to prepare this work. We can also work as we did for releases stuff, you do something and I test it locally.

Comment 8

[[:jberkus] Josh Berkus]

Adrian,

New views home_page_view and home_page_build_view are now loaded on Mobeta database accessible by Crash-Stats-Dev.  You can create new mware services based on these.  Detail:

-- View product_info now has the column has_builds.  This can be used to determine if the "by build" graph should be accessible for that particular version.  If you need this column in product_selector instead, let me know and I'll add it.

-- view home_page_graph_view *replaces* daily_crashes as the source data for the home page graph.  It already contains a calculated column crash_hadu, which means "crashes per hundred active daily users" so you can stop calculating that in the middleware.

-- view home_page_graph_build_view supplies graph data for the home page for the "by build" view.  This looks just the same as home_page_graph_view, except that it replaces the "report_date" column with "build_date".  Note that data in this view will ONLY be available for product/versions marked as has_builds (e.g. aurora, nightly, rapid beta).

Comment 9

[[:jberkus] Josh Berkus]

More additions:

crashes_by_user: this is the replacement for daily_crashes/product_adu.  This matview should allow you to run the Crashes By User page.

crashes_by_user_build: same thing as above, only by build date this time.

tcbs_build: this view is the same as TCBS, only it also has a build_date column.

We should discuss how to use these tommorrow.  See you then!

Oh, I've updated the table definitions; I had to modify some things while working on them: https://wiki.mozilla.org/Socorro:Rapid_Betas:Database_Work

Comment 10

[[:jberkus] Josh Berkus]

Database migration code for Mobeta is, as far as I can test so far, done:
https://github.com/mozilla/socorro/pull/698

Note that there were some functionality I could not write tests for due to limitations of "fakedata" to date.  However, having rhelmer's fakedata for testing allowed me to find and squash over a dozen bugs which would have been blockers in staging otherwise.  Mobeta would be at least another week without it.

On Sunday, I will apply the Mobeta changes to Crash-Stats-Dev's database, so that it can be used for development and testing.  Note that these are already available in the "mobeta" database.

Stuff that's not tested:
- Matview data for hybrid rapid beta parent/child versions: here I was limited by time to improve fakedata so I could test this kind of release.
- by-build-date graphing because of limitations of fakedata.

Stuff that's not built:
- fakedata will need to be extensively revised after Mobeta release.  Sorry!
- Cron jobs (bug filed)
- Overhaul of backfill program for backfilling matviews (bug filed, scheduled for release after Mobeta).
- Overhaul of MiniDB generator for post-Mobeta (bug filed, scheduled for release after Mobeta).
- New schema manager for managing from-scratch database creation and testing (i.e. replacement for setupdb_app.py).

Comment 11

[[DEACTIVATED] Adrian Gaudebert]

Josh, I found a difference between what you wrote in the wiki [1] and what is in the mobeta branch: table home_page_graph_build is missing the 'crash_hadu' field in the current schema.sql. Is the wiki or the code right? As that field is in the home_page_graph table I expect it to be in _build as well, am I right?

Also, I made a few changes to the schema.sql script so it works in my local VM [2].

[1] https://wiki.mozilla.org/Socorro:Rapid_Betas:Database_Work
[2] https://github.com/mozilla/socorro/commit/6a9ce973f181fb93b67b6c5f8068dd584f9afec7

Comment 12

[[DEACTIVATED] Adrian Gaudebert]

Josh, I figured out the crash_hadu thing. But there are two problems that I need you to look over: 

1. I had to recreate the plperl language in schema.sql in order to run unit tests. According to rhelmer and your commits, I shouldn't have had to do that. 

2. In the schema.sql file on the mobeta branch, the add_new_release function is not up-to-date. 

Those two points make me think that maybe the schema.sql file is simply not the right one. When you have the time, can you please generate a new one from a DB that is updated for mobeta? I believe the 16 upgrade was not applied to the mobeta db, and maybe you generated the schema.sql before applying your fix for the plperl language?

Comment 13

[[:jberkus] Josh Berkus]

> 1. I had to recreate the plperl language in schema.sql in order to run unit
> tests. According to rhelmer and your commits, I shouldn't have had to do
> that. 

I'm going to need a step-by-step on this, with error messages.

> 2. In the schema.sql file on the mobeta branch, the add_new_release function
> is not up-to-date. 

Will fix.

> Those two points make me think that maybe the schema.sql file is simply not
> the right one. When you have the time, can you please generate a new one
> from a DB that is updated for mobeta? I believe the 16 upgrade was not
> applied to the mobeta db,

That's possible.

 and maybe you generated the schema.sql before
> applying your fix for the plperl language?

That's unlikely.

Comment 14

[[:jberkus] Josh Berkus]

Per discussion on IRC, the above issues have been fixed.  This is just waiting on merge to master to close the bug.

Comment 15

[[:jberkus] Josh Berkus]

Merged to master.