Last Comment Bug 428300 - status page is slow
: status page is slow
Product: Socorro
Classification: Server Software
Component: General (show other bugs)
: Trunk
: All All
-- normal (vote)
: ---
Assigned To: Ted Mielczarek [:ted.mielczarek]
: socorro
Depends on:
  Show dependency treegraph
Reported: 2008-04-10 06:11 PDT by Ted Mielczarek [:ted.mielczarek]
Modified: 2011-12-28 10:40 PST (History)
1 user (show)
See Also:
QA Whiteboard:
Iteration: ---
Points: ---

use jobs instead (622 bytes, patch)
2008-04-10 06:46 PDT, Ted Mielczarek [:ted.mielczarek]
morgamic: review+
Details | Diff | Splinter Review
with more stuffs (2.16 KB, patch)
2008-04-10 12:04 PDT, Ted Mielczarek [:ted.mielczarek]
no flags Details | Diff | Splinter Review

Description User image Ted Mielczarek [:ted.mielczarek] 2008-04-10 06:11:25 PDT
Why is the status page so slow? It's doing two queries, one or both of them must suck. I would guess it's the reports query, since that table is much bigger.
Comment 1 User image Ted Mielczarek [:ted.mielczarek] 2008-04-10 06:14:18 PDT

is the reports query.

        jobs.c.completeddatetime == None

is the jobs query. is that reports query screwing us by not having a date limit? I would think 'limit 1' would be good.

Comment 2 User image Ted Mielczarek [:ted.mielczarek] 2008-04-10 06:34:36 PDT
explain select date_processed from reports order by date_processed desc limit 1;

 Limit  (cost=2655.91..2655.91 rows=1 width=8)
   ->  Sort  (cost=2655.91..2667.54 rows=4652 width=8)
         Sort Key: public.reports.date_processed
         ->  Result  (cost=0.00..2372.52 rows=4652 width=8)
               ->  Append  (cost=0.00..2372.52 rows=4652 width=8)
                     ->  Seq Scan on reports  (cost=0.00..10.20 rows=20 width=8)
                     ->  Seq Scan on reports_part1 reports  (cost=0.00..2362.32 rows=4632 width=8)

Yeah, that's bad. We don't have an index on date_procesed. Can we just get this from the jobs table instead? Or store it somewhere else?
Comment 3 User image Ted Mielczarek [:ted.mielczarek] 2008-04-10 06:46:22 PDT
Created attachment 314843 [details] [diff] [review]
use jobs instead

This is approximately a million billion times better. Ok, it's still doing a seq scan on jobs, but that table is much smaller than reports. We could toss an index on here to make it better, but that's of questionable merit. Adding an index to reports to fix this problem just seems silly.
Comment 4 User image Michael Morgan [:morgamic] 2008-04-10 08:17:19 PDT
Comment on attachment 314843 [details] [diff] [review]
use jobs instead

Could we add # of items in the queue, # of threads and oldest item in queue?
Comment 5 User image Ted Mielczarek [:ted.mielczarek] 2008-04-10 08:23:04 PDT
# items in queue is already on that page. I'll make it grab the other info.
Comment 6 User image Ted Mielczarek [:ted.mielczarek] 2008-04-10 12:04:53 PDT
Created attachment 314915 [details] [diff] [review]
with more stuffs

Yeah, I didn't add # of processors because we don't have that table defined yet in the SQLAlchemy model, and I just don't feel like adding it. This adds OldestQueuedJob, AverageProcessTime (process end time - process start time), and AverageWaitTime (process end time - queue time).

Note You need to log in before you can comment on or make changes to this bug.