912580 - Optimize queries for symbols db

Status: RESOLVED WONTFIX

(Socorro :: General, task)

Description

[(not currently active) Ted Mielczarek]

I put together the set of queries that we should need for using the symbols db for minidump processing:
https://github.com/selenamarie/symbols/blob/master/query.py

We need to make sure the schema is optimized for these queries, and also optimize the queries themselves.

That script also has the ability to be run directly and fed a log from an instrumented minidump_stackwalk to replay the queries it would need to process a specific dump.

I'll attach a sample MDSW query log today, and we'll work on getting a more representative sample of query logs soon. My plan was to grab a sample of dumps from prod and run them through the instrumented MDSW on dev and save all the logs.

Comment 1

[(not currently active) Ted Mielczarek]

Attachment: Firefox 18.0.2 Linux MDSW query log

This is a MDSW query log from processing a dump from Firefox 18.0.2 on x86 Linux. The dump itself is bp-9b879c52-b769-4620-8cd4-8c56a2130222, the symbols in use are these:
http://symbols.mozilla.org/firefox/firefox-18.0.2-Linux-20130201065344-x86-symbols.txt

Comment 2

[(not currently active) Ted Mielczarek]

Attachment: Firefox 18.0.2 Windows MDSW query log

This is a MDSW query log from processing a dump from Firefox 18.0.2 on x86 Windows. The dump itself is bp-cd755ee6-f77e-45f5-b433-5aa662130213, the symbols in use are these:
http://symbols.mozilla.org/firefox/firefox-18.0.2-WINNT-20130201065344-x86-symbols.txt

The additional symbols from symbols_os are used as well:
d3d9.pdb/F55AEB50462B473F9733515DC6532DAF2/d3d9.sym
dbghelp.pdb/6B19F261EE434AF59623D1791CE5A7D72/dbghelp.sym
msvcr100.i386.pdb/B74A49314FD64956BAF50772F83598012/msvcr100.i386.sym
msiltcfg.pdb/2E23B94E6C584AC5993CCD655D8D58D21/msiltcfg.sym
winmm.pdb/7AFD98FCAAD34F6B8B31A4667C4E2BFC2/winmm.sym
rasman.pdb/DF9A860877F94FECBB7A2CF29C7D5EDD2/rasman.sym
MMDevAPI.pdb/941E45A63E8F457D90CE29CC508C34B92/MMDevAPI.sym
mswsock.pdb/5D01BC6D4DA84939BAF8725D60D595A62/mswsock.sym
user32.pdb/DD74D86F12624845A42A6A5BAAB4D7A82/user32.sym
ws2_32.pdb/18C960B958404C4F80F686EE978A33D42/ws2_32.sym
kernel32.pdb/56825ACB653E4569BCF9213150EC3FEF2/kernel32.sym
ntdll.pdb/093D2CD7F95B4CC6B5318D405CC315662/ntdll.sym
kernelbase.pdb/82BBF29D97654220BAAF1F90B0C0A96A2/kernelbase.sym

Comment 3

[Selena Deckelmann :selenamarie :selena]

Do we know how long the MDSW took for both/either of those dumps? 

Here's the current state of things on my laptop with all the symbols loaded for each build: 

(symbols)selena@wuzetian:symbols #1500 15:39 ♥: time python symbolsdb/query.py fixtures/firefox-18.0.2-WINNT-query.log  >> /tmp/test

real	0m11.936s
user	0m5.132s
sys	0m0.220s

(symbols)selena@wuzetian:symbols #1501 15:40 ♥: time python symbolsdb/query.py fixtures/firefox-18.0.2-Linux-query.log  >> /tmp/test
Traceback (most recent call last):
  File "symbolsdb/query.py", line 119, in <module>
    main()
  File "symbolsdb/query.py", line 81, in main
    ts, what, rest = line.split(",", 2)
ValueError: need more than 1 value to unpack

real	1m1.334s
user	0m2.564s
sys	0m0.112s

(need to figure out the bug above :)

I've basically done zero query optimization and no database tuning, so this is a worst-case baseline. I added a couple naive indexes for the queries I saw, and quite a bit of work can be done to improve this.

I'd like to know how fast I *need* to make it, though :)

Comment 4

[(not currently active) Ted Mielczarek]

I don't have exact timings, but the first column in these CSVs is a timestamp, so you can probably just subtract max-min to get a rough estimate.

Comment 5

[(not currently active) Ted Mielczarek]

Actually, that's probably not a fair estimate since I was running these on my local machine and the symbol files may already have been cached etc. A better estimate would be to take some arbitrary production crashes for specific Firefox versions, run them through staging, collect the execution time of MDSW in that environment, and then also run them through the instrumented MDSW to produce these CSV logs so you can compare.

Comment 6

[Selena Deckelmann :selenamarie :selena]

We did some work on this but the space required to store symbols in Postgres is prohibitive.