Closed Bug 738323 Opened 12 years ago Closed 12 years ago

Need startup crash percentage on TCBS

Categories

(Socorro Graveyard :: Middleware, defect)

Product:
Socorro Graveyard
Component:
Middleware
Type:
defect
Priority:
Not set
Severity:
normal

Tracking

(Not tracked)

Status:
RESOLVED FIXED

People

(Reporter: espressive, Assigned: adrian)

References

Details

(Whiteboard: [qa-]) 

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
Blocks: 738305
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.
[:jberkus] Pinging adrian as he did the new style service for tcbs I believe.
@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"])
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.
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
Adrian,

Wait, never mind.  The above is wrong.  Will correct.
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
Taking the bug to work on the middleware part.
Assignee: josh → adrian
Component: Database → Middleware
QA Contact: database → middleware
Target Milestone: 3 → 4
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.
Status: NEW → RESOLVED
Closed: 12 years ago
Resolution: --- → FIXED
Steps to QA
-----------

1. Verify that bug 732067 works fine. Does that mean [qa-]? :)
Whiteboard: [qa-]
Product: Socorro → Socorro Graveyard
You need to log in before you can comment on or make changes to this bug.