738323 - Need startup crash percentage on TCBS

Status: RESOLVED FIXED

(Socorro Graveyard :: Middleware, defect)

Description

[Schalk Neethling [:espressive]]

I need to indicate whether a signature on the top crashers page is a startup crasher by determining whether the uptime range < 1 min is greater than 50% so, either adding a flag to say is_startup_crasher == true or adding the percentage of crashes in the uptime range < 1 min

Comment 1

[[:jberkus] Josh Berkus]

This has been merged into master, and will be loaded on crash-stats-dev soon.

I've added the column "startup_count" to the table tcbs.  Unfortunately, you'll have to do some calculation as part of the query which gets data from tcbs, to wit:

...

COALESCE( ( sum(startup_count)::numeric / sum(report_count) ), 0 ) as startup_pct

... if you could remind me of the code which runs the top-level tcbs query, I could help more.

Comment 2

[Schalk Neethling [:espressive]]

[:jberkus] Pinging adrian as he did the new style service for tcbs I believe.

Comment 3

[[DEACTIVATED] Adrian Gaudebert]

@Josh, here is the SQL query ran for TCBS:


sql = """
    WITH tcbs_r as (
    SELECT tcbs.signature_id,
            signature,
            pv.product_name,
            version_string,
            sum(report_count) as report_count,
            sum(win_count) as win_count,
            sum(lin_count) as lin_count,
            sum(mac_count) as mac_count,
            sum(hang_count) as hang_count,
            plugin_count(process_type,report_count) as plugin_count,
            content_count(process_type,report_count) as content_count,
            first_report,
            version_list
    FROM tcbs
        JOIN signatures USING (signature_id)
        JOIN product_versions AS pv USING (product_version_id)
        JOIN signature_products_rollup AS spr
            ON spr.signature_id = tcbs.signature_id
            AND spr.product_name = pv.product_name
    WHERE pv.product_name = '%s'
        AND version_string = '%s'
        AND report_date BETWEEN '%s' AND '%s'
        %s
    GROUP BY tcbs.signature_id, signature, pv.product_name, version_string,
         first_report, spr.version_list
    ),
    tcbs_window AS (
        SELECT tcbs_r.*,
        sum(report_count) over () as total_crashes,
                dense_rank() over (order by report_count desc) as ranking
        FROM
            tcbs_r
    )
    SELECT signature,
                 report_count,
                 win_count,
                 lin_count,
                 mac_count,
                 hang_count,
                 plugin_count,
                 content_count,
                 first_report,
                 version_list,
            %s / total_crashes::float
            as percent_of_total
    FROM tcbs_window
    ORDER BY %s DESC
    LIMIT %s
    """ % (dbParams["product"], dbParams["version"], dbParams["startDate"],
           dbParams["to_date"], where, order_by, order_by,
           dbParams["limit"])

Comment 4

[[:jberkus] Josh Berkus]

Adrian,

Feh, I can't really test that from the plane.  I'll give you a revised query once I've some time on the ground.

Comment 5

[[:jberkus] Josh Berkus]

adrian,

try this:

    WITH tcbs_r as (
    SELECT tcbs.signature_id,
            signature,
            pv.product_name,
            version_string,
            sum(report_count) as report_count,
            sum(win_count) as win_count,
            sum(lin_count) as lin_count,
            sum(mac_count) as mac_count,
            sum(hang_count) as hang_count,
            plugin_count(process_type,report_count) as plugin_count,
            content_count(process_type,report_count) as content_count,
            first_report,
            version_list,
            sum(startup_count) as startup_count
    FROM tcbs
        JOIN signatures USING (signature_id)
        JOIN product_versions AS pv USING (product_version_id)
        JOIN signature_products_rollup AS spr
            ON spr.signature_id = tcbs.signature_id
            AND spr.product_name = pv.product_name
    WHERE pv.product_name = '%s'
        AND version_string = '%s'
        AND report_date BETWEEN '%s' AND '%s'
        %s
    GROUP BY tcbs.signature_id, signature, pv.product_name, version_string,
         first_report, spr.version_list
    ),    tcbs_window AS (
        SELECT tcbs_r.*,
        sum(report_count) over () as total_crashes,
                dense_rank() over (order by report_count desc) as ranking
        FROM
            tcbs_r
    )
    SELECT signature,
                 report_count,
                 win_count,
                 lin_count,
                 mac_count,
                 hang_count,
                 plugin_count,
                 content_count,
                 first_report,
                 version_list,
            %s / total_crashes::float
            as percent_of_total,
                 startup_count / total_crashes::float as startup_percent
    FROM tcbs_window
    ORDER BY %s DESC
    LIMIT %s

Comment 6

[[:jberkus] Josh Berkus]

Adrian,

Wait, never mind.  The above is wrong.  Will correct.

Comment 7

[[:jberkus] Josh Berkus]

ok, fixed:

    WITH tcbs_r as (
    SELECT tcbs.signature_id,
            signature,
            pv.product_name,
            version_string,
            sum(report_count) as report_count,
            sum(win_count) as win_count,
            sum(lin_count) as lin_count,
            sum(mac_count) as mac_count,
            sum(hang_count) as hang_count,
            plugin_count(process_type,report_count) as plugin_count,
            content_count(process_type,report_count) as content_count,
            first_report,
            version_list,
            sum(startup_count) as startup_count
    FROM tcbs
        JOIN signatures USING (signature_id)
        JOIN product_versions AS pv USING (product_version_id)
        JOIN signature_products_rollup AS spr
            ON spr.signature_id = tcbs.signature_id
            AND spr.product_name = pv.product_name
    WHERE pv.product_name = '%s'
        AND version_string = '%s'
        AND report_date BETWEEN '%s' AND '%s'
        %s
    GROUP BY tcbs.signature_id, signature, pv.product_name, version_string,
         first_report, spr.version_list
    ),    tcbs_window AS (
        SELECT tcbs_r.*,
        sum(report_count) over () as total_crashes,
                dense_rank() over (order by report_count desc) as ranking
        FROM
            tcbs_r
    )
    SELECT signature,
                 report_count,
                 win_count,
                 lin_count,
                 mac_count,
                 hang_count,
                 plugin_count,
                 content_count,
                 first_report,
                 version_list,
            %s / total_crashes::float
            as percent_of_total,
                  startup_count / report_count::float as startup_percent
    FROM tcbs_window
    ORDER BY %s DESC
    LIMIT %s

Comment 8

[[DEACTIVATED] Adrian Gaudebert]

Taking the bug to work on the middleware part.

Comment 9

[[DEACTIVATED] Adrian Gaudebert]

Pull request: https://github.com/mozilla/socorro/pull/464

Comment 10

[[github robot]]

Commit pushed to master at https://github.com/mozilla/socorro

https://github.com/mozilla/socorro/commit/1ab28e130e14dee9b243dbbb0313421ab36aa740
Merge pull request #464 from AdrianGaudebert/738323-add-startup-crash-tcbs

Fixes bug 738323 - Added startup_percent to TCBS middleware service.

Comment 11

[[DEACTIVATED] Adrian Gaudebert]

Steps to QA
-----------

1. Verify that bug 732067 works fine. Does that mean [qa-]? :)