808295 - Backfill missing Socorro aggregation data for 2012-11-02

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Robert Kaiser]

Once again, we are missing aggregated (graph etc.) data for yesterday.

We need to backfill this data for any crash analysis work to be done on it.

Note that IIRC from what I heard in the last few backfills, you nowadays need to specify the date twice for the backfilling function to work correctly.

Comment 1

[Sheeri Cabral [:sheeri]]

Running backfill and including Nov 2 and 3, just in case.

breakpad=# select backfill_adu('2012-11-02'); 
backfill_adu --------------
 t
(1 row)

breakpad=# select backfill_adu('2012-11-03');
 backfill_adu 
--------------
 t
(1 row)

breakpad=# select backfill_matviews('2012-11-02','2012-11-03');
INFO:  backfilling reports_clean
INFO:  backfilling 06:00:00 of reports_clean starting at 2012-11-02 00:00:00+00
CONTEXT:  SQL statement "SELECT backfill_reports_clean( first_rc, last_rc )"
PL/pgSQL function "backfill_matviews" line 51 at PERFORM

Comment 2

[Sheeri Cabral [:sheeri]]

The backfill completed. Is everything good?

Also, is this something we could get a Nagios check on? If I recall correctly there's a query we can run to verify that all is OK, maybe we could run it once a day at 9 am Eastern time? That way we'll know and can run a backfill proactively?

Comment 3

[Laura Thomson :laura]

Can we do this so it checks and attempts re-run every hour, automatically, like we did pre-mobeta?  I'm not sure what's blocking that.

Comment 4

[Sheeri Cabral [:sheeri]]

Nothing blocking - I think we need to *write* a check for it, because the date is a dynamic parameter. It's not like a queue where we can say "if it's >N, page". We'd want something like:

if (count(*) from yesterday = 0) then page 

or something so we catch high and low spikes....like

count(*) from yesterday compared to count(*) from the day before has to be within 20% of each other? (so if the table has 1000 rows for 2 days ago, it should have between 800-1200 rows for yesterday)?

Also, um.....what's the query again? I don't think it's on https://mana.mozilla.org/wiki/display/websites/Backfilling+Matview+and+ADU+Data+For+Socorro but I could be wrong here.

Comment 5

[Robert Kaiser]

(In reply to Sheeri Cabral [:sheeri] from comment #2)
> The backfill completed. Is everything good?

Yes, seems so. Thanks, esp. for doing this on a weekend!

> Also, is this something we could get a Nagios check on? If I recall
> correctly there's a query we can run to verify that all is OK, maybe we
> could run it once a day at 9 am Eastern time? That way we'll know and can
> run a backfill proactively?

That would be really nice!

I usually do two checks: 1) See if we have data in the home page graph (we could query the tables for at least part of that data to see if something's there), 2) if that's missing, check if we have data for that day in raw_adu as if not we need to involved metrics as well.
I guess we could do nagios checks on both those things.

Comment 6

[Robert Helmer [:rhelmer]]

(In reply to Laura Thomson :laura from comment #3)
> Can we do this so it checks and attempts re-run every hour, automatically,
> like we did pre-mobeta?  I'm not sure what's blocking that.

There is an explanation in bug 792904, basically post-mobeta we need to run the daily jobs for the graphs to show up. We do still check for ADU hourly and load it as soon as it's available, but there's no way to automatically retry daily jobs right now.

Comment 7

[Sheeri Cabral [:sheeri]]

We are post-mobeta now. Is this bug still valid?

Comment 8

[Selena Deckelmann :selenamarie :selena]

This can be closed. Other bugs have been opened to talk through making daily matview runs more automated.  I'll mark it fixed for the original issue of aggregation data not being up-to-date.