Timeout with large milestone with lots of private bugs

Bug #447418 reported by Andreas Hasenack on 2009-10-09
14
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Launchpad itself
High
Curtis Hovey

Bug Description

It has been a while (months) since I was last able to visualize the "later" milestone for landscape. This is the URL:

https://launchpad.net/landscape-project/+milestone/later

This milestone has hundreds of bugs, most private. If you only see a few when you open the URL, then you don't have the right to see the others and will likely NOT get the timeout.

Current oops : https://lp-oops.canonical.com/oops.py/?oopsid=OOPS-1689EB3170

SQL time: 7505 ms
Non-sql time: 7849 ms
Total time: 15354 ms
Statement Count: 390

We're getting spotty queries due for a few reasons.
browser.milestone._bugtasks which is a cached property
references a separate cached property
and both use the model object, which isn't cached, so we query once per path-into-the-model.

Curtis Hovey (sinzui) on 2009-12-14
affects: launchpad → launchpad-registry
Changed in launchpad-registry:
importance: Undecided → Low
status: New → Triaged
Curtis Hovey (sinzui) wrote :

Milestones bugs need a major reimplementation. batching this may help, but breaks the UI because the driver need to see all the milestones. I think bugs need to be loaded in an async proc like bug subscribers.

tags: added: timeout
Changed in launchpad-registry:
milestone: none → 3.1.13
importance: Low → High
Curtis Hovey (sinzui) wrote :

I am marking this high because of the oops, but the work involved makes it difficult to promise for January. I will try to plan this out over in my own time and finalise the schedule.

Brad Crittenden (bac) on 2009-12-16
Changed in launchpad-registry:
status: Triaged → In Progress
assignee: nobody → Brad Crittenden (bac)
Curtis Hovey (sinzui) on 2009-12-24
Changed in launchpad-registry:
assignee: Brad Crittenden (bac) → nobody
Curtis Hovey (sinzui) wrote :

This has proved very difficult to fix. The problem is in python, not SQL. Testing with hundreds of bugs does not reveal a performance problem. The issue appears to be related specifically with private bugs. Batching bugs breacks the activities protlet...it must become a async call. The bugs should also be an async call because release managers need to see all the bugs in a single view.

Changed in launchpad-registry:
milestone: 10.01 → 10.02
status: In Progress → Triaged
Curtis Hovey (sinzui) wrote :

Given that this page counts and lists mnay bugs that may be private, The view should implement a private bug cache like private branches. The code-team added a rule to store all the private branches the user has access to in the request so that the security on the individiual branches did not need to be checked. We want to ensure that the query only returns bugs that the user has access to, then ensure that the bug's security is not rechecked.

Curtis Hovey (sinzui) on 2010-02-11
Changed in launchpad-registry:
milestone: 10.02 → series-10.05
Curtis Hovey (sinzui) on 2010-05-14
Changed in launchpad-registry:
assignee: nobody → Curtis Hovey (sinzui)
status: Triaged → In Progress
milestone: series-future → 10.05
Curtis Hovey (sinzui) on 2010-05-19
Changed in launchpad-registry:
status: In Progress → Fix Committed
Curtis Hovey (sinzui) on 2010-05-31
tags: added: qa-ok
removed: qa-needstesting
Curtis Hovey (sinzui) on 2010-06-02
Changed in launchpad-registry:
status: Fix Committed → Fix Released
Robert Collins (lifeless) wrote :

Reopening after discussion with Curtis.

Changed in launchpad-registry:
status: Fix Released → Triaged
tags: removed: qa-ok
Robert Collins (lifeless) wrote :

This is the bulk of the sql time:

time Average time Saving Database id Statement
1 328 6439 19 6420 launchpad-main-slave SELECT BugSubscription.bug, BugSubscription.date_created, BugSubscription.id, BugSubscription.person, BugSubscription.subscribed_by, "_prejoin1".account, "_prejoin1".creation_comment, "_prejoin1".creation_rationale, "_prejoin1".datecreated, "_prejoin1".defaultmembershipperiod, "_prejoin1".defaultrenewalperiod, "_prejoin1".displayname, "_prejoin1".hide_email_addresses, "_prejoin1".homepage_content, "_prejoin1".icon, "_prejoin1".id, "_prejoin1".logo, "_prejoin1".mailing_list_auto_subscribe_policy, "_prejoin1".merged, "_prejoin1".mugshot, "_prejoin1".name, "_prejoin1".personal_standing, "_prejoin1".personal_standing_reason, "_prejoin1".registrant, "_prejoin1".renewal_policy, "_prejoin1".subscriptionpolicy, "_prejoin1".teamdescription, "_prejoin1".teamowner, "_prejoin1".verbose_bugnotifications, "_prejoin1".visibility FROM BugSubscription LEFT JOIN Person AS "_prejoin1" ON BugSubscription.person = "_prejoin1".id WHERE BugSubscription.bug = %s AND (1=1) ORDER BY BugSubscription.id

which is, if I read it right, a lookup for subscribers of a single bug (and this would explain the private bug correlation with the issue)

description: updated
description: updated
description: updated
Robert Collins (lifeless) wrote :

specifics on the repeated lookups:

These three stack fragments resulted in separate queries.

I've still not managed to trigger bugsubscription queries in testing, however, fixing this particular case will be useful itself.

  File "/home/robertc/launchpad/lp-branches/working/lib/canonical/cachedproperty.py", line 220, in __get__
    result = self.fn(inst)
  File "/home/robertc/launchpad/lp-branches/working/lib/lp/registry/browser/milestone.py", line 272, in bugtasks
    return [self._getListingItem(bugtask) for bugtask in self._bugtasks]
  File "/home/robertc/launchpad/lp-branches/working/lib/canonical/cachedproperty.py", line 220, in __get__
    result = self.fn(inst)
  File "/home/robertc/launchpad/lp-branches/working/lib/lp/registry/browser/milestone.py", line 239, in _bugtasks
    [task.bug.id for task in tasks])

  File "/home/robertc/launchpad/lp-branches/working/lib/canonical/cachedproperty.py", line 220, in __get__
    result = self.fn(inst)
  File "/home/robertc/launchpad/lp-branches/working/lib/lp/registry/browser/milestone.py", line 272, in bugtasks
    return [self._getListingItem(bugtask) for bugtask in self._bugtasks]
  File "/home/robertc/launchpad/lp-branches/working/lib/canonical/cachedproperty.py", line 220, in __get__
    result = self.fn(inst)
  File "/home/robertc/launchpad/lp-branches/working/lib/lp/registry/browser/milestone.py", line 239, in _bugtasks
    [task.bug.id for task in tasks])
  File "/home/robertc/launchpad/lp-branches/working/lib/lp/bugs/model/bugtask.py", line 1410, in getBugTasks
    for bug, task in result:

  File "/home/robertc/launchpad/lp-branches/working/lib/canonical/cachedproperty.py", line 220, in __get__
    result = self.fn(inst)
  File "/home/robertc/launchpad/lp-branches/working/lib/lp/registry/browser/milestone.py", line 272, in bugtasks
    return [self._getListingItem(bugtask) for bugtask in self._bugtasks]
  File "/home/robertc/launchpad/lp-branches/working/lib/canonical/cachedproperty.py", line 220, in __get__
    result = self.fn(inst)
  File "/home/robertc/launchpad/lp-branches/working/lib/lp/registry/browser/milestone.py", line 241, in _bugtasks
    for task in tasks:

Robert Collins (lifeless) wrote :

Ok, another dead end: this is a round about way of handling conjoined bugs; I don't know what they are, but - and this is the crucial bit - it does not expand out as we add more and more bugs.

Robert Collins (lifeless) wrote :

Fresh eyes after a break are a wonderful thing.

This loop:
odule canonical.cachedproperty, line 79, in __get__
    result = self.fn(inst)
  Module lp.registry.browser.milestone, line 272, in bugtasks
    return [self._getListingItem(bugtask) for bugtask in self._bugtasks]
  Module canonical.cachedproperty, line 79, in __get__
    result = self.fn(inst)
  Module lp.registry.browser.milestone, line 242, in _bugtasks
    if task.getConjoinedMaster(bugs_and_tasks[task.bug]) is None:

seems to be be triggering
Module canonical.launchpad.webapp.authorization, line 207, in checkPermission
    principal.account)

via

  Module canonical.database.sqlbase, line 733, in block_implicit_flushes_decorator
    return func(*args, **kwargs)
  Module canonical.launchpad.webapp.authorization, line 207, in checkPermission
    principal.account)
  Module canonical.launchpad.security, line 173, in checkAccountAuthenticated
    return self.checkAuthenticated(IPersonRoles(person))
  Module canonical.launchpad.security, line 946, in checkAuthenticated
    return self.obj.bug.userCanView(user.person)
  Module lp.bugs.model.bug, line 1561, in userCanView
    for subscription in self.subscriptions:

Robert Collins (lifeless) wrote :

Managed to get reproduction in a test case. This - https://bugs.edge.launchpad.net/malone/+bug/619039 is the thing showing the problem (but the root issue is not returning enough data from the db to answer that cheaply).

I'm going to look into making bugsearches cache the fact that the user that did the search has access permissions, if they do, and see what that does to the behaviour.

Robert Collins (lifeless) wrote :

Branch with fix for the current visible cause is up; will try to get that pulled onto staging tomorrow for a quick test - I haven't got an appropriate dataset here to know if its sufficient unto the purpose or not.

Changed in launchpad-registry:
status: Triaged → Fix Committed
tags: added: qa-needstesting
Curtis Hovey (sinzui) on 2010-08-26
tags: added: qa-ok
removed: qa-needstesting
Curtis Hovey (sinzui) on 2010-09-15
Changed in launchpad-registry:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers