Launchpad itself

Timeout on Distribution:+bugs search

Bug #661988 reported by Micah Gersten
30
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Launchpad itself
Fix Released
Critical
Robert Collins
Launchpad itself 11.02

Bug Description

bug search across all ubuntu

recent:
OOPS-1842C1473

https://bugs.edge.launchpad.net/ubuntu/+bugs?field.searchtext=grub_xputs&orderby=-importance&search=Search&field.status%3Alist=NEW&field.status%3Alist=INCOMPLETE_WITH_RESPONSE&field.status%3Alist=INCOMPLETE_WITHOUT_RESPONSE&field.status%3Alist=CONFIRMED&field.status%3Alist=TRIAGED&field.status%3Alist=INPROGRESS&field.status%3Alist=FIXCOMMITTED&field.assignee=&field.bug_reporter=&field.omit_dupes=on&field.has_patch=&field.has_no_package=

SQL time: 12539 ms
Non-sql time: 709 ms
Total time: 13248 ms
Statement Count: 69

~7.5 seconds in one query to get series_bug_count. ~4 seconds in the query for the bug listing.

on staging:

 Aggregate (cost=7733840.35..7733840.36 rows=1 width=0) (actual time=3435.484..3435.484 rows=1 loops=1)
   -> Nested Loop Left Join (cost=0.00..7733840.31 rows=16 width=0) (actual time=3369.348..3435.474 rows=1 loops=1)
         -> Nested Loop Left Join (cost=0.00..7733835.04 rows=16 width=4) (actual time=3369.343..3435.469 rows=1 loops=1)
               -> Nested Loop (cost=0.00..7733830.41 rows=16 width=8) (actual time=3369.339..3435.464 rows=1 loops=1)
                     Join Filter: ((((bug.fti)::tsvector @@ '''kpassgen'''::tsquery) OR ((public.bugtask.fti)::tsvector @@ '''kpassgen'''::tsquery) OR (public.bugtask.targetnamecache ~~* '%kpassgen%'::text)) AND ((public.bugtask.status = 10) OR ((public.bugtask.status = 15) AND (bug.date_last_message IS NOT NULL) AND (public.bugtask.date_incomplete <= bug.date_last_message)) OR ((public.bugtask.status = 15) AND ((bug.date_last_message IS NULL) OR (public.bugtask.date_incomplete > bug.date_last_message))) OR (public.bugtask.status = 20) OR (public.bugtask.status = 21) OR (public.bugtask.status = 22) OR (public.bugtask.status = 25)))
                     -> Index Scan using bugtask__distribution__sourcepackagename__idx on bugtask (cost=0.00..75418.40 rows=158746 width=160) (actual time=0.099..1007.335 rows=184186 loops=1)
                           Index Cond: (distribution = 1)
                           Filter: ((status = 10) OR ((date_incomplete IS NOT NULL) AND (status = 15)) OR (status = 15) OR (status = 20) OR (status = 21) OR (status = 22) OR (status = 25))
                     -> Index Scan using bug_pkey on bug (cost=0.00..48.20 rows=1 width=386) (actual time=0.009..0.009 rows=0 loops=184186)
                           Index Cond: (bug.id = public.bugtask.bug)
                           Filter: ((bug.duplicateof IS NULL) AND ((NOT bug.private) OR (SubPlan 1)))
                           SubPlan 1
                             -> Unique (cost=46.10..46.11 rows=2 width=4) (actual time=0.046..0.046 rows=1 loops=8187)
                                   -> Sort (cost=46.10..46.11 rows=2 width=4) (actual time=0.045..0.045 rows=1 loops=8187)
                                         Sort Key: bugsubscription.bug
                                         Sort Method: quicksort Memory: 25kB
                                         -> Append (cost=0.00..46.09 rows=2 width=4) (actual time=0.027..0.042 rows=1 loops=8187)
                                               -> Nested Loop (cost=0.00..33.34 rows=1 width=4) (actual time=0.025..0.031 rows=1 loops=8187)
                                                     -> Index Scan using bugsubscription_bug_idx on bugsubscription (cost=0.00..7.75 rows=4 width=8) (actual time=0.009..0.012 rows=4 loops=8187)
                                                           Index Cond: (bug = $0)
                                                     -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=29867)
                                                           Index Cond: ((public.teamparticipation.team = bugsubscription.person) AND (public.teamparticipation.person = 2497367))
                                               -> Nested Loop (cost=0.00..12.73 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=8187)
                                                     -> Index Scan using bugtask__bug__idx on bugtask (cost=0.00..6.33 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=8187)
                                                           Index Cond: (bug = $0)
                                                     -> Index Scan using teamparticipation_team_key on teamparticipation (cost=0.00..6.39 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=8487)
                                                           Index Cond: ((public.teamparticipation.team = public.bugtask.assignee) AND (public.teamparticipation.person = 2497367))
               -> Index Scan using sourcepackagename_pkey on sourcepackagename (cost=0.00..0.28 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)
                     Index Cond: (public.bugtask.sourcepackagename = sourcepackagename.id)
         -> Index Scan using product_pkey on product (cost=0.00..0.32 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)
               Index Cond: (public.bugtask.product = product.id)
 Total runtime: 3435.810 ms
(32 rows)

for

SELECT COUNT(*)
FROM BugTask
LEFT JOIN Bug ON BugTask.bug = Bug.id
LEFT JOIN Product ON BugTask.product = Product.id
LEFT JOIN SourcePackageName ON BugTask.sourcepackagename = SourcePackageName.id
WHERE Bug.id = BugTask.bug
  AND BugTask.distribution = 1
  AND ((BugTask.status = 10)
       OR (BugTask.status = 15)
       AND (Bug.date_last_message IS NOT NULL
            AND BugTask.date_incomplete <= Bug.date_last_message)
       OR (BugTask.status = 15)
       AND (Bug.date_last_message IS NULL
            OR BugTask.date_incomplete > Bug.date_last_message)
       OR (BugTask.status = 20)
       OR (BugTask.status = 21)
       OR (BugTask.status = 22)
       OR (BugTask.status = 25))
  AND Bug.duplicateof IS NULL
  AND (Bug.fti @@ ftq('kpassgen')
       OR BugTask.fti @@ ftq('kpassgen')
       OR BugTask.targetnamecache ILIKE '%' || 'kpassgen' || '%')
  AND (Bug.private = FALSE
       OR EXISTS
         (SELECT BugSubscription.bug
          FROM BugSubscription,
               TeamParticipation
          WHERE TeamParticipation.person = 2497367
            AND TeamParticipation.team = BugSubscription.person
            AND BugSubscription.bug = Bug.id
          UNION SELECT BugTask.bug
          FROM BugTask,
               TeamParticipation
          WHERE TeamParticipation.person = 2497367
            AND TeamParticipation.team = BugTask.assignee
            AND BugTask.bug = Bug.id));

See original description

Related branches

lp:~lifeless/launchpad/bug-661988
Tim Penhey (community): Approve (code)
Ian Booth (community): Approve (code*)
Diff: 446 lines (+72/-127)
9 files modified
lib/lp/bugs/browser/tests/test_buglisting.py (+2/-2)
lib/lp/bugs/configure.zcml (+1/-5)
lib/lp/bugs/interfaces/bugtask.py (+1/-0)
lib/lp/bugs/model/bugtask.py (+28/-43)
lib/lp/bugs/model/tests/test_bugtask.py (+18/-8)
lib/lp/bugs/tests/test_bugsearch_conjoined.py (+6/-2)
lib/lp/bugs/tests/test_bugtask_search.py (+5/-64)
lib/lp/registry/browser/milestone.py (+8/-0)
lib/lp/registry/browser/tests/test_milestone.py (+3/-3)
Micah Gersten (micahg)
tags: added: timeout
description: updated
Deryck Hodge (deryck)
summary: - Timeout on bug search
+ Timeout on Distribution:+bugs search
Changed in malone:
status: New → Triaged
importance: Undecided → High
description: updated
Deryck Hodge (deryck)
description: updated
Revision history for this message
Stuart Bishop (stub) wrote :

Timings are showing that BugTask.targetnamecache can no longer be queried fast enough - there are just too many bug tasks now.

launchpad_prod_3=# select count(*) from BugTask where BugTask.targetnamecache ilike '%grub%';
 count
-------
  3183
(1 row)

Time: 1668.405 ms

We can't shave this time off the queries - we need a better way of doing the substring search, or drop the substring search.

(but this isn't the whole problem)

Revision history for this message
Robert Collins (lifeless) wrote :
Download full text (4.9 KiB)

Three top queries take up 9 seconds of the page:

1 4377.0 1 SQL-launchpad-main-slave SELECT COUNT(*) FROM BugTask LEFT JOIN Bug ON BugTask.bug = Bug.id LEFT JOIN Product ON BugTask.product = Product.id LEFT JOIN SourcePackageName ON BugTask.sourcepackagename = SourcePackageName.id WHERE (1=1) AND Bug.id = BugTask.bug AND BugTask.distribution = 1 AND ((BugTask.status = 10) OR (BugTask.status = 15) AND (Bug.date_last_message IS NOT NULL AND BugTask.date_incomplete <= Bug.date_last_message) OR (BugTask.status = 15) AND (Bug.date_last_message IS NULL OR BugTask.date_incomplete > Bug.date_last_message) OR (BugTask.status = 20) OR (BugTask.status = 21) OR (BugTask.status = 22) OR (BugTask.status = 25)) AND Bug.duplicateof is NULL AND (Bug.fti @@ ftq('atheros') OR BugTask.fti @@ ftq('atheros') OR BugTask.targetnamecache ILIKE '%' || 'atheros' || '%') AND (Bug.private = FALSE OR EXISTS ( SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation WHERE TeamParticipation.person = 1475915 AND BugSubscription.person = TeamParticipation.team AND BugSubscription.bug = Bug.id))
2 2482.0 1 SQL-launchpad-main-slave SELECT COUNT(*) FROM BugTask LEFT JOIN Bug ON BugTask.bug = Bug.id LEFT JOIN Product ON BugTask.product = Product.id LEFT JOIN SourcePackageName ON BugTask.sourcepackagename = SourcePackageName.id WHERE (1=1) AND Bug.id = BugTask.bug AND ((BugTask.status = 10) OR (BugTask.status = 15) OR (BugTask.status = 20) OR (BugTask.status = 21) OR (BugTask.status = 22) OR (BugTask.status = 25)) AND BugTask.milestone = 27462 AND Bug.duplicateof is NULL AND (Bug.private = FALSE OR EXISTS ( SELECT BugSubscription.bug FROM BugSubscription, TeamParticipation WHERE TeamParticipation.person = 1475915 AND BugSubscription.person = TeamParticipation.team AND BugSubscription.bug = Bug.id))
3 1336.0 1 SQL-launchpad-main-slave SELECT BugTask.assignee, BugTask.bug, BugTask.bugwatch, BugTask.date_assigned, BugTask.date_closed, BugTask.date_confirmed, BugTask.date_fix_committed, BugTask.date_fix_released, BugTask.date_incomplete, BugTask.date_inprogress, BugTask.date_left_closed, BugTask.date_left_new, BugTask.date_triaged, BugTask.datecreated, BugTask.distribution, BugTask.distroseries, BugTask.id, BugTask.importance, BugTask.milestone, BugTask.owner, BugTask.product, BugTask.productseries, BugTask.sourcepackagename, BugTask.status, BugTask.statusexplanation, BugTask.targetnamecache, Product.answers_usage, Product.blueprints_usage, Product.owner, Product.translations_usage, Product.active, Product.autoupdate, Product.bug_reported_acknowledgement, Product.bug_reporting_guidelines, Product.bug_supervisor, Product.bugtracker, Product.date_next_suggest_packaging, Product.datecreated, Product.description, Product.development_focus, Product.displayname, Product.downloadurl, Product.driver, Product.enable_bug_expiration, Product.freshmeatproject, Product.homepage_content, Product.homepageurl, Product.icon, Product.id, Product.lastdoap, Product.license_approved, Product.license_info, Product.reviewed, Product.logo, Product.max_bug_heat, Product.mugshot, Product.name, Product.official_answers, Product.official_blueprints, Product.official_malone, Product.official_rosetta, P...

Read more...

Robert Collins (lifeless)
Changed in launchpad:
importance: High → Critical
Robert Collins (lifeless)
description: updated
Revision history for this message
Micah Gersten (micahg) wrote :

Just got one of these:
https://bugs.launchpad.net/ubuntu/+bugs?field.searchtext=kpassgen&orderby=-importance&search=Search&field.status%3Alist=NEW&field.status%3Alist=INCOMPLETE_WITH_RESPONSE&field.status%3Alist=INCOMPLETE_WITHOUT_RESPONSE&field.status%3Alist=CONFIRMED&field.status%3Alist=TRIAGED&field.status%3Alist=INPROGRESS&field.status%3Alist=FIXCOMMITTED&field.assignee=&field.bug_reporter=&field.omit_dupes=on&field.has_patch=&field.has_no_package=

OOPS-1859K248

Robert Collins (lifeless)
description: updated
Revision history for this message
Robert Collins (lifeless) wrote : Re: [Bug 661988] Re: Timeout on Distribution:+bugs search

Interestingly we have no index on BugTask.fti. All text searches are
sequential scans there among all candidate bugs.

Revision history for this message
Robert Collins (lifeless) wrote :

Have done a few experiments. We can halve the time on the count by dropping the joins to product and sourcepackage name - they are not being filtered on, so are totally unnecessary. For eager loading we can do a more efficient separate query anyway.

The query around bugtask.status is bonkers - when both incompletes are present we should just permit status=15 rather than constraining by date_incomplete.

The fti stuff is an issue too - doing a union like below constraints pgsql to do a fti lookup on bug itself - and this shaves another 50% off (getting down to 1s query).

SELECT bugtask.id
FROM BugTask
LEFT JOIN Bug ON BugTask.bug = Bug.id WHERE Bug.id = BugTask.bug
  AND BugTask.distribution = 1
  AND (BugTask.status in (10, 20, 21, 22, 25, 15)) AND Bug.duplicateof IS NULL
  AND Bug.fti @@ ftq('kpassgen')
  AND (Bug.private = FALSE
       OR EXISTS
         (SELECT BugSubscription.bug
          FROM BugSubscription,
               TeamParticipation
          WHERE TeamParticipation.person = 2497367
            AND TeamParticipation.team = BugSubscription.person
            AND BugSubscription.bug = Bug.id
          UNION SELECT BugTask.bug
          FROM BugTask,
               TeamParticipation
          WHERE TeamParticipation.person = 2497367
            AND TeamParticipation.team = BugTask.assignee
            AND BugTask.bug = Bug.id))
union SELECT bugtask.id
FROM BugTask
LEFT JOIN Bug ON BugTask.bug = Bug.id WHERE Bug.id = BugTask.bug
  AND BugTask.distribution = 1
  AND (BugTask.status in (10, 20, 21, 22, 25, 15)) AND Bug.duplicateof IS NULL
  AND (BugTask.fti @@ ftq('kpassgen')
       OR BugTask.targetnamecache ILIKE '%' || 'kpassgen' || '%')
  AND (Bug.private = FALSE
       OR EXISTS
         (SELECT BugSubscription.bug
          FROM BugSubscription,
               TeamParticipation
          WHERE TeamParticipation.person = 2497367
            AND TeamParticipation.team = BugSubscription.person
            AND BugSubscription.bug = Bug.id
          UNION SELECT BugTask.bug
          FROM BugTask,
               TeamParticipation
          WHERE TeamParticipation.person = 2497367
            AND TeamParticipation.team = BugTask.assignee
            AND BugTask.bug = Bug.id));

Revision history for this message
Stuart Bishop (stub) wrote :

For the query in Comment #5, there is no need to use LEFT OUTER JOINS and the ON join condition is duplicated in the WHERE clause (but this results in the same query plan, so this is just for clarity). Also, we should use IS FALSE rather than = FALSE, but in this case it again makes no difference.

A minor improvement to the plan can be gained by replacing UNION with UNION ALL in the EXIST clauses but this will be pretty trivial.

I've tried several different rewrites but can't really improve on this. Substring search on targetnamecache seems killer, but we knew this would never scale. We should consider just dropping this and relying on the fts match on this column.

Revision history for this message
Robert Collins (lifeless) wrote :

I've raised the substring search issue on the Ubuntu mailing list to fairly quiet response; based on that response I think we can get away with dropping it for now, and adding it as a specific targeted feature development task later. Alternatively we can go for a prefix search (which is nearly as good) using a prefix index - one has been created on qastaging temporarily to test this and it gives good results.

Revision history for this message
Robert Collins (lifeless) wrote :

I've implemented the change to use set based preloading - nearly all bug searches will have vastly redundant bug targets, so this is generally beneficial. I'm going to file some separate bugs based on this for the other bits that we can/should change.

Revision history for this message
Robert Collins (lifeless) wrote :

bug 607960 for the targetname cache search issue.

Robert Collins (lifeless)
Changed in launchpad:
assignee: nobody → Robert Collins (lifeless)
Revision history for this message
Robert Collins (lifeless) wrote :

See also bug 268508 - the targetname cache is arguably undesirable as a feature.

Revision history for this message
Robert Collins (lifeless) wrote :

Bug 714312 for the left join.

Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

Fixed in stable r12344 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/12344>.

Changed in launchpad:
milestone: none → 11.02
tags: added: qa-needstesting
Changed in launchpad:
status: Triaged → Fix Committed
Robert Collins (lifeless)
tags: added: qa-ok
removed: qa-needstesting
Curtis Hovey (sinzui)
Changed in launchpad:
status: Fix Committed → Fix Released
Revision history for this message
Robert Collins (lifeless) wrote :

The page still has issues, but I'm going to file a new bug about that, this one is sufficiently long.

Revision history for this message
Robert Collins (lifeless) wrote :

bug 717394 for the follow on.

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Duplicates of this bug

Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.