565777 - Setup process to cleanup old top_crashes_by_signature data

Status: RESOLVED FIXED

(Infrastructure & Operations Graveyard :: WebOps: Other, task)

Description

[Austin King [:ozten]]

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.

Comment 1

[K Lars Lohn [:lars] [:klohn]]

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.

Comment 2

[Austin King [:ozten]]

Running the query from the wiki on dm-breakpad took 16 minutes and removed 3,241,221 rows.

Comment 3

[Shyam Mani [:fox2mike]]

Not exactly sure what the action here is, punting to Aravind.

Comment 4

[Austin King [:ozten]]

Ping.
There is SQL to clean out old entries. We should run this periodically.

Comment 5

[Aravind Gottipati [:aravind]]

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.

Comment 6

[Austin King [:ozten]]

Awesome, thanks aravind!