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.
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
Ping. There is SQL to clean out old entries. We should run this periodically.
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
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.