Timeout with large milestone with lots of private bugs

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.

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.

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.

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.

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

Reopening after discussion with Curtis.

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)

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:

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.

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:

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.

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.