603660 - stage Socorro database updates for QA

Status: VERIFIED FIXED

(mozilla.org Graveyard :: Server Operations, task)

Description

[Austin King [:ozten]]

Please leave this ticket open, there will be several updates.

Please execute the following SQL and note how long it took to run, as well as it's output:

UPDATE reports_20100104 SET email = NULL;

SELECT product, version, signature, COUNT(id)
FROM reports_20100104
GROUP BY product, version, signature
ORDER BY COUNT(id) DESC
LIMIT 10;

SELECT min(date_processed), max(date_processed) FROM reports_20100104;

Comment 1

[Austin King [:ozten]]

The title says staging.. but I didn't mention in Comment#0.

This is for *STAGING* ONLY. Thanks.

Comment 2

[Justin Dow [:jabba]]

breakpad=# \timing
Timing is on.
breakpad=# UPDATE reports_20100104 SET email = NULL;
UPDATE 35897
Time: 3528.491 ms
breakpad=# SELECT product, version, signature, COUNT(id)
breakpad-# FROM reports_20100104
breakpad-# GROUP BY product, version, signature
breakpad-# ORDER BY COUNT(id) DESC
breakpad-# LIMIT 10;
   product   | version  |                         signature                         | count 
-------------+----------+-----------------------------------------------------------+-------
 Firefox     | 3.6b5    | UserCallWinProcCheckWow                                   |   341
 Firefox     | 3.6b5    | RtlpCoalesceFreeBlocks                                    |   166
 Firefox     | 3.6b5    | RtlpWaitForCriticalSection | RtlEnterCriticalSection      |   147
 Firefox     | 3.5.6    | UserCallWinProcCheckWow                                   |   142
 Firefox     | 3.5.6    | _PR_MD_SEND                                               |   122
 Firefox     | 4.0a1pre | @0x0 | NPSWF32.dll@0x1890d8                               |   107
 Thunderbird | 3.0      | EscapeFromSpaceLine(nsIOutputStream*, char*, char const*) |   104
 Firefox     | 3.5b4    | RtlpWaitForCriticalSection | RtlEnterCriticalSection      |   102
 Firefox     | 3.5.6    | RtlpWaitForCriticalSection | RtlEnterCriticalSection      |   102
 Firefox     | 3.6b5    | _PR_MD_SEND                                               |    96
(10 rows)

Time: 76.006 ms
breakpad=# SELECT min(date_processed), max(date_processed) FROM reports_20100104;
            min             |            max             
----------------------------+----------------------------
 2010-01-06 01:03:04.161551 | 2010-01-07 19:24:24.538063
(1 row)

Time: 26.264 ms
breakpad=#

Comment 3

[Austin King [:ozten]]

Stage has very little date, expanding the safe test data window:

UPDATE reports_20091228 SET email = NULL;
UPDATE reports_20100111 SET email = NULL;
UPDATE reports_20100118 SET email = NULL;
UPDATE reports_20100125 SET email = NULL;
UPDATE reports_20100201 SET email = NULL;

Comment 4

[Austin King [:ozten]]

Attachment: First run of fake data

Once Comment#3 is done, please run the attached SQL and pastebin the output.

Comment 5

[Justin Dow [:jabba]]

output from comment 3:
breakpad=# UPDATE reports_20091228 SET email = NULL;

UPDATE 99025
Time: 52901.868 ms
breakpad=# 
breakpad=# UPDATE reports_20100111 SET email = NULL;
ERROR:  relation "reports_20100111" does not exist
breakpad=# UPDATE reports_20100118 SET email = NULL;
UPDATE 35417
Time: 2969.579 ms
breakpad=# UPDATE reports_20100125 SET email = NULL;
UPDATE 31618
Time: 7141.110 ms
breakpad=# UPDATE reports_20100201 SET email = NULL;
UPDATE 61281
Time: 59129.480 ms
breakpad=#

Comment 6

[Justin Dow [:jabba]]

http://jabba.pastebin.mozilla.org/815051

Comment 7

[Austin King [:ozten]]

thanks.

Comment 8

[Austin King [:ozten]]

Attachment: Second run of fake data

This adds a second crash signature and different data for a second QA test case.

Comment 9

[Austin King [:ozten]]

@jabba - in stage, please run attachment#482720 [details] Thanks!

Comment 10

[Justin Dow [:jabba]]

Done:

-bash-3.2$ psql breakpad < bug603660.sql 
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
UPDATE 0
-bash-3.2$

Comment 11

[Austin King [:ozten]]

Thanks, can you also run and pastebin the output of:

SELECT date_processed, email FROM reports WHERE 
date_processed >= '2010-01-01' AND 
date_processed < '2010-01-30' AND
signature = 'RtlpCoalesceFreeBlocks' AND
email IS NOT NULL and email != ''
ORDER BY email;

Comment 12

[Austin King [:ozten]]

As reported in IRC
1) log permission issues (Python can't create new logs when rolling
2) db objects have permission issues

please comment here when these are fixed, so QA knows when it's worth testing.

Comment 13

[Justin Dow [:jabba]]

I think the db stuff is straightened out. I'm not sure about the log permission issues. It is very intermittent and due to some python log rolling issue that I can't reproduce on prod.

Comment 14

[Austin King [:ozten]]

(In reply to comment #13)
How is it intermittent? I have access to stage and can reproduce in 100% of the time?

Comment 15

[Austin King [:ozten]]

Poke for running SQL in comment#11.

Comment 16

[Austin King [:ozten]]

After you run SQL in comment#11 then please run 

DELETE FROM email_campaigns_contacts;
DELETE FROM email_contacts;
DELETE FROM email_campaigns;

this will reset the stage data for QA.

Thanks

Comment 17

[Justin Dow [:jabba]]

sql from comment#11 has been run: http://jabba.pastebin.mozilla.org/816062

And sql in comment#16 has been run as well:
breakpad=> DELETE FROM email_campaigns_contacts;
DELETE 27
breakpad=> DELETE FROM email_contacts;
DELETE 27
breakpad=> DELETE FROM email_campaigns;
DELETE 1
breakpad=>

Comment 18

[Austin King [:ozten]]

This is ready to test. My 2nd batch of test data has some issue, I'll file a new bug to add another test case.

Thanks for your help @jabba.

Comment 19

[Austin King [:ozten]]

Re-opening bug to add third set of test data.

Comment 20

[Austin King [:ozten]]

Attachment: third set of SQL

Please run against stage db and pastebin results.

Comment 21

[Austin King [:ozten]]

http://jabba.pastebin.mozilla.org/818243
Thanks!

Comment 22

[Stephen Donner [:stephend] Not actively reading bugmail]

Verified FIXED; Vishal and I worked through the three datasets [1] today, and everything worked.

[1] https://docs.google.com/document/pub?id=1_Rhg1n3ac-rvMkjow_5nCJJv6up5oqineue7cmHcBpE&pli=1