976575 - Double # of processed crashes in count(*) on postgres, while raw_crashes was stable

Status: RESOLVED FIXED

(Socorro :: Database, task)

Description

[Selena Deckelmann :selenamarie :selena]

Something was weird with Pg processed crash storage the week of 2/10: 


breakpad=# select count(*) from raw_crashes_20140217; 
  count  
---------
 2538606
(1 row)

breakpad=# select count(*) from raw_crashes_20140210; 
  count  
---------
 2572858
(1 row)

breakpad=# select count(*) from processed_crashes_20140217; 
  count  
---------
 2538542
(1 row)

breakpad=# select count(*) from processed_crashes_20140210; 
  count  
---------
 1142554
(1 row)

breakpad=# select min(date_processed) from processed_crashes_20140210; 
              min              
-------------------------------
 2014-02-10 01:22:40.022007+00
(1 row)

breakpad=# select min(date_processed) from processed_crashes_20140217; 
              min              
-------------------------------
 2014-02-17 00:00:00.144338+00
(1 row)


There may be a perfectly logical explanation. Would like to know what the root cause was.

Comment 1

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

your data is from staging.  we didn't start writing the processed crashes to PG until 2/13, about a half week into the 20140210 partition.  

Recall that the date_processed field is a misnomer, it really ought to be date_collected.  A crash received on Monday and priority processed on Thursday will retain its Monday date and time. I think this query shows the distribution quite clearly:

breakpad=# select date_trunc('HOUR', date_processed), count(*) from processed_crashes_20140210 group by 1 order by 1;
       date_trunc       | count 
------------------------+-------
 2014-02-10 02:00:00+00 |     2
 2014-02-10 03:00:00+00 |     1
 2014-02-10 04:00:00+00 |     3
 2014-02-10 06:00:00+00 |     1
 2014-02-10 09:00:00+00 |     1
 2014-02-10 10:00:00+00 |     1
 2014-02-10 11:00:00+00 |     2
 2014-02-10 15:00:00+00 |     2
 2014-02-10 16:00:00+00 |     2
 2014-02-10 17:00:00+00 |     1
 2014-02-10 18:00:00+00 |     5
 2014-02-10 19:00:00+00 |     2
 2014-02-10 20:00:00+00 |     1
 2014-02-10 21:00:00+00 |     1
 2014-02-10 22:00:00+00 |     3
 2014-02-10 23:00:00+00 |     1
 2014-02-11 02:00:00+00 |     1
 2014-02-11 07:00:00+00 |     1
 2014-02-11 09:00:00+00 |     1
 2014-02-11 10:00:00+00 |     1
 2014-02-11 11:00:00+00 |     2
 2014-02-11 13:00:00+00 |     3
 2014-02-11 14:00:00+00 |     1
 2014-02-11 15:00:00+00 |     1
 2014-02-11 16:00:00+00 |     4
 2014-02-11 17:00:00+00 |     4
 2014-02-11 18:00:00+00 |     1
 2014-02-11 19:00:00+00 |     3
 2014-02-11 20:00:00+00 |     2
 2014-02-11 21:00:00+00 |     2
 2014-02-11 22:00:00+00 |     1
 2014-02-11 23:00:00+00 |     1
 2014-02-12 00:00:00+00 |     2
 2014-02-12 01:00:00+00 |     2
 2014-02-12 03:00:00+00 |     2
 2014-02-12 04:00:00+00 |     1
 2014-02-12 07:00:00+00 |     1
 2014-02-12 08:00:00+00 |     1
 2014-02-12 09:00:00+00 |     2
 2014-02-12 10:00:00+00 |     1
 2014-02-12 11:00:00+00 |     1
 2014-02-12 12:00:00+00 |     3
 2014-02-12 13:00:00+00 |     2
 2014-02-12 14:00:00+00 |     3
 2014-02-12 15:00:00+00 |     2
 2014-02-12 16:00:00+00 |     7
 2014-02-12 17:00:00+00 |     5
 2014-02-12 18:00:00+00 |     4
 2014-02-12 19:00:00+00 |     3
 2014-02-12 20:00:00+00 |     3
 2014-02-12 22:00:00+00 |     2
 2014-02-12 23:00:00+00 |     1
 2014-02-13 00:00:00+00 |     1
 2014-02-13 01:00:00+00 |     2
 2014-02-13 02:00:00+00 |     3
 2014-02-13 03:00:00+00 |     4
 2014-02-13 04:00:00+00 |     1
 2014-02-13 05:00:00+00 |     3
 2014-02-13 06:00:00+00 |     3
 2014-02-13 07:00:00+00 |     3
 2014-02-13 08:00:00+00 |     2
 2014-02-13 09:00:00+00 |     3
 2014-02-13 10:00:00+00 |     3
 2014-02-13 11:00:00+00 |     3
 2014-02-13 12:00:00+00 |     2
 2014-02-13 13:00:00+00 |     7
 2014-02-13 14:00:00+00 |     5
 2014-02-13 15:00:00+00 |     6
 2014-02-13 16:00:00+00 |     6
 2014-02-13 17:00:00+00 |    15
 2014-02-13 18:00:00+00 |    10
 2014-02-13 19:00:00+00 | 18502
 2014-02-13 20:00:00+00 | 17121
 2014-02-13 21:00:00+00 | 16263
 2014-02-13 22:00:00+00 | 13997
 2014-02-13 23:00:00+00 | 11828
 2014-02-14 00:00:00+00 | 11571
 2014-02-14 01:00:00+00 | 10789
 2014-02-14 02:00:00+00 | 10243
 2014-02-14 03:00:00+00 | 10332
 2014-02-14 04:00:00+00 | 10462
 2014-02-14 05:00:00+00 | 11425
 2014-02-14 06:00:00+00 | 13036
 2014-02-14 07:00:00+00 | 14664
 2014-02-14 08:00:00+00 | 15421
 2014-02-14 09:00:00+00 | 16277
 2014-02-14 10:00:00+00 | 16805
 2014-02-14 11:00:00+00 | 17356
 2014-02-14 12:00:00+00 | 18279
 2014-02-14 13:00:00+00 | 19936
 2014-02-14 14:00:00+00 | 20153
 2014-02-14 15:00:00+00 | 20634
 2014-02-14 16:00:00+00 | 20441
 2014-02-14 17:00:00+00 | 19875
 2014-02-14 18:00:00+00 | 18580
 2014-02-14 19:00:00+00 | 17760
 2014-02-14 20:00:00+00 | 17293
 2014-02-14 21:00:00+00 | 14915
...

Comment 2

[Selena Deckelmann :selenamarie :selena]

(In reply to K Lars Lohn [:lars] [:klohn] from comment #1)
> your data is from staging.  

It doesn't affect the rest of what you said - but just to be clear, I did not get the counts from staging. :)

> we didn't start writing the processed crashes to
> PG until 2/13, about a half week into the 20140210 partition.  

Ah! Duh. Thanks