Setup process to cleanup old top_crashes_by_signature data

RESOLVED FIXED

Status

--
major
RESOLVED FIXED
9 years ago
5 years ago

People

(Reporter: ozten, Assigned: aravind)

Tracking

Details

(Reporter)

Description

9 years ago
top_crashes_by_signature has data older than 56 days worth per product

A product dimension should never have more than 56 days of information as covered in this wiki page:
http://code.google.com/p/socorro/wiki/SocorroUpgrade#Top_Crashes_By_Signature_Periodic_Cleanup

dm-breakpad-stagedb is a snapshot of prod and has the following:
96 days 01:00:00	173
82 days 12:00:00	177
82 days 10:00:00	195
82 days 09:00:00	80
82 days	193
81 days	190
81 days	192
81 days	188
79 days 19:00:00	197
78 days 11:00:00	40
78 days	191
70 days 10:00:00	199
70 days	202
66 days 19:00:00	203
(Based on MAX(window_end) - MIN(window_end) per productdims_id).

We need to put in an automated process to clean out these tables.
we may find that the 56 day limit may not be necessary once the reports table goes away and the database has its resources freed up.  I also have the dev branch that partitions the top_crashes_by_signature table by product_id.  That would help a lot.
(Reporter)

Comment 2

9 years ago
Running the query from the wiki on dm-breakpad took 16 minutes and removed 3,241,221 rows.
Not exactly sure what the action here is, punting to Aravind.
Assignee: server-ops → aravind
(Reporter)

Comment 4

9 years ago
Ping.
There is SQL to clean out old entries. We should run this periodically.
(Assignee)

Comment 5

9 years ago
Ran it in production.

breakpad=> delete from top_crashes_by_signature
breakpad-> where id in
breakpad->     (select
breakpad(>          tcbs.id
breakpad(>      from
breakpad(>          top_crashes_by_signature tcbs
breakpad(>             join product_visibility pv on tcbs.productdims_id = pv.productdims_id
breakpad(>      where
breakpad(>          tcbs.window_end < (case when now() < pv.end_date then now()
breakpad(>                                  else pv.end_date end) - interval '56 days');

DELETE 4714876

Also set up a cron job to run every sunday at 1:20 AM.
Status: NEW → RESOLVED
Last Resolved: 9 years ago
Resolution: --- → FIXED
(Reporter)

Comment 6

9 years ago
Awesome, thanks aravind!
Component: Server Operations: Web Operations → WebOps: Other
Product: mozilla.org → Infrastructure & Operations
You need to log in before you can comment on or make changes to this bug.