568872 - Run some queries on Socorro prod db

Status: RESOLVED FIXED

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

Description

[Laura Thomson :laura]

* Total for a day
select count(id) from reports
where date_processed  = '2010-05-26'     
and product ='Firefox' 
and version = '3.6.4';

* Crashes attributable to Firefox
select count(id) from reports
where date_processed  = '2010-05-26'    
and product ='Firefox' 
and version = '3.6.4'
and process_type IS NULL
and hangid IS NULL;

* Total hangs
select count(id) from reports
where date_processed  = '2010-05-26'    
and product ='Firefox' 
and version = '3.6.4'
and hangid IS NOT NULL;

* Plugin related reports
select count(id) from reports
where date_processed  = '2010-05-26'    
and product ='Firefox' 
and version = '3.6.4'
and process_type ='plugin';

* Plugin crashes
select count(id) from reports
where date_processed  = '2010-05-26'    
and product ='Firefox' 
and version = '3.6.4'
and process_type = 'plugin'
and hangid IS NULL;

* Plugin hangs
select count(id) from reports
where date_processed  = '2010-05-26'    
and product ='Firefox' 
and version = '3.6.4'
and process_type ='plugin'
and hangid IS NOT NULL;

* Firefox hangs
select count(id) from reports
where date_processed  = '2010-05-26'
and product ='Firefox'
and version = '3.6.4'
and process_type IS NULL
and hangid IS NOT NULL;

Comment 1

[Laura Thomson :laura]

Note: probably a bug for timellis.

Comment 2

[timellis]

Hmm. I ran them and they all returned zero. Here's the SQL I ran:

select '* Total for a day' as status;
select count(id) from reports
where date_processed  = '2010-05-26'     and product ='Firefox' and version = '3.6.4';
select '* Crashes attributable to Firefox' as status;
select count(id) from reports
where date_processed  = '2010-05-26'    and product ='Firefox' and version = '3.6.4' and process_type IS NULL and hangid IS NULL;
select '* Total hangs' as status;
select count(id) from reports
where date_processed  = '2010-05-26'    and product ='Firefox' and version = '3.6.4' and hangid IS NOT NULL;
select '* Plugin related reports' as status;
select count(id) from reports
where date_processed  = '2010-05-26'    and product ='Firefox' and version = '3.6.4' and process_type ='plugin';
select '* Plugin crashes' as status;
select count(id) from reports
where date_processed  = '2010-05-26'    and product ='Firefox' and version = '3.6.4' and process_type = 'plugin' and hangid IS NULL;
select '* Plugin hangs' as status;
select count(id) from reports
where date_processed  = '2010-05-26'    and product ='Firefox' and version = '3.6.4' and process_type ='plugin' and hangid IS NOT NULL;
select '* Firefox hangs' as status;
select count(id) from reports
where date_processed  = '2010-05-26' and product ='Firefox' and version = '3.6.4' and process_type IS NULL and hangid IS NOT NULL;

And here's the result:

[root@tm-breakpad01-master01 13:46:44 ~/tmp]
:) cat tmp.sql | sudo -u postgres psql -t breakpad
could not change directory to "/root/tmp"
 * Total for a day
     0

 * Crashes attributable to Firefox
     0

 * Total hangs
     0

 * Plugin related reports
     0

 * Plugin crashes
     0

 * Plugin hangs
     0

 * Firefox hangs
     0

Comment 3

[timellis]

Modified SQL:

select '* Total for a day' as status;
select count(id) from reports
where date_processed < '2010-05-27' and date_processed  >= '2010-05-26'     and product ='Firefox' and version = '3.6.4';
select '* Crashes attributable to Firefox' as status;
select count(id) from reports
where date_processed < '2010-05-27' and date_processed  >= '2010-05-26'    and product ='Firefox' and version = '3.6.4' and process_type IS NULL and hangid IS NULL;
select '* Total hangs' as status;
select count(id) from reports
where date_processed < '2010-05-27' and date_processed  >= '2010-05-26'    and product ='Firefox' and version = '3.6.4' and hangid IS NOT NULL;
select '* Plugin related reports' as status;
select count(id) from reports
where date_processed < '2010-05-27' and date_processed  >= '2010-05-26'    and product ='Firefox' and version = '3.6.4' and process_type ='plugin';
select '* Plugin crashes' as status;
select count(id) from reports
where date_processed < '2010-05-27' and date_processed  >= '2010-05-26'    and product ='Firefox' and version = '3.6.4' and process_type = 'plugin' and hangid IS NULL;
select '* Plugin hangs' as status;
select count(id) from reports
where date_processed < '2010-05-27' and date_processed  >= '2010-05-26'    and product ='Firefox' and version = '3.6.4' and process_type ='plugin' and hangid IS NOT NULL;
select '* Firefox hangs' as status;
select count(id) from reports
where date_processed < '2010-05-27' and date_processed  >= '2010-05-26' and product ='Firefox' and version = '3.6.4' and process_type IS NULL and hangid IS NOT NULL;

Result:

 * Total for a day
 22122
   
 * Crashes attributable to Firefox
  2183

 * Total hangs
 16571

 * Plugin related reports
 11699

 * Plugin crashes
  3368

 * Plugin hangs
  8331

 * Firefox hangs
  8240

Comment 4

[chris hofmann]

re:
> * Total for a day: 22122

the scripts that produce 
http://people.mozilla.com/~chofmann/crash-stats/20100527/crash-counts.txt

show 

  day      adu  total_for_the_day
20100527 629265 23009

have been using the pattern match for version  that looks like

$8 ~ /^3.6.4$/  and results 3.6b4 getting included.

22290 3.6.4
 719  3.6b4

so the data in from the sql looks like the correct overall count.

that version match might also explain the other small differences.

Comment 5

[chris hofmann]

I reran the scripts with the version correction and updated the crash-counts report so the totals_for_the_day values now match.

looking at the other differences now.

Comment 6

[chris hofmann]

after the version change to weed out 3.6.4b4 in the .csv analysis scripts here are the numbers for comparison.  

                                   sql    .csv+awk

* Total for a day                 22122    22290
* Crashes attributable to Firefox  2183     2363
* Total hangs                     16571     8153+8249=16,402
* Plugin related reports          11699    11678
* Plugin crashes                   3368     3525
* Plugin hangs                     8331     8153
* Firefox hangs                    8240     8249

the numbers are closer but still some unexplained differences with crashes slight higher and hangs slight lower in the .csv sample.  when we were running backlogs that might have explained some differences in when the .csv was captured early this morning and when the sql was run just now, but I don't think we are running backlogs now.  maybe there is still some lag in submissions from the client where the user goes off line after a crash and submits later.

there is also still some work to figure out how we want to want to account for the hangs.  we currently get duplicate hang reports, and we are missing one or the other side of the hang pairs.  these are likely to be two different problems that are sort of balancing themselves out right now by probably just by coincidence (see bug 568849).   we could just count valid hang pairs, or we could also add in the half pairs as one hang, and weed out to just count a single pair from the duplicates.   we should probably just watch the data for a bit more to understand what is going on there.

Comment 7

[Austin King [:ozten]]

(In reply to comment #6)
In addition to timing of date_processed, there may be subtleties around what crashes get counted because of OS (null/Solaris).

Comment 8

[chris hofmann]

one other idea would be to start running the sql in comment 3 at about the same time the .csv files are created each morning.  that would help to narrow and differences in the samples used the two methods.

the output could just be dumped into the nightly directories under http://people.mozilla.com/crash_analysis/ next to the .csv files.

we will probably want to keep a closer eye on this stuff until 3.6.4 gets out the door and we understand how Out Of Process Plugins is performing.

arvind could you add this sql to the nightly script and generate another small report each night that looks like comment 3?

Comment 9

[chris hofmann]

I just noticed that comment 6 is a bad comparison.  the sql is looking at 2010 05 26 and I was looking at the data from the .csv files from the 27th.

if you compare the numbers from the .csv files on the 26th they now match up ok.



If you look at the line for 20100526 
http://people.mozilla.com/~chofmann/crash-stats/20100527/crash-counts.txt

you can see

 adus 633796 

 total  22120 
 firefox crahes 2181 0.344 
 plugin crashes 3368 
 hangs firefox  [8240 
 and plugin hangs 8331] 

these values aren't in the sql report/are calcuated differently
  plugin crahes and hangs  11608 
  fx crash + plugin crash + plugin hangs 13880	

  2.190  --  0.157 0.836 0.243 0.594

arvind I think this means we don't really need to run the sql nightly.  we can use the reports I'm generating until we get something more formal in the reporting system.

Comment 10

[chris hofmann]

can we get the script to run again including data from 
2010 06 08 and then again on 2010 06 07

there has been an unexplained increase in the number of crashes on 3.6.4 in the last two days causing a bump in the the crashes per 100 users that I've been calculating.

looking for reasons in that bump, and running these reports might help.  

this should get high priority since we are in the final stages of planing the release of 3.6.4

Comment 11

[chris hofmann]

http://people.mozilla.com/~chofmann/crash-stats/20100608/crash-counts.txt and 
http://people.mozilla.com/~chofmann/crash-stats/20100607/crash-counts.txt

are the reports to compare against. up until 06 06 things were running pretty stable at 600k ADUs, 2300 crashes, and 0.3 crashes per 100 users.

Comment 12

[timellis]

For the 7th:

:) cat tmp.sql | sed -e 's/LOWDATE/2010-06-07/' | sed -e 's/HIGHDATE/2010-06-08/' | sudo -u postgres psql breakpad | egrep -v 'status|----|row'
 * Total for a day

 30323

 * Crashes attributable to Firefox

  7297

 * Total hangs

 19190

 * Plugin related reports

 13480

 * Plugin crashes

  3836

 * Plugin hangs

  9644

 * Firefox hangs

  9546


For the 8th:
:) cat tmp.sql | sed -e 's/LOWDATE/2010-06-08/' | sed -e 's/HIGHDATE/2010-06-09/' | sudo -u postgres psql breakpad | egrep -v 'status|----|row|count'
 * Total for a day

 44122

 * Crashes attributable to Firefox

 18378

 * Total hangs

 21816

 * Plugin related reports

 14890

 * Plugin crashes

  3928

 * Plugin hangs

 10962

 * Firefox hangs

 10854