Bug page times out getting subscriptions for logged in users on bugs with many duplicates

[komputes]

Timeouts are happening when logged in on bugs with a large number of duplicates.

Example bugs:
* Bug #507062 -> OOPS-2070DX91
* Bug #507062 -> OOPS-2070DX91, OOPS-2117CJ113
* Bug #835153 -> OOPS-2073EE58

This is basically our usual case of death-by-a-thousand-queries (OOPS-2117CJ113 shows more than 900 queries before timing out). The SQL timeline shows that the list of duplicates is retrieved at statement 122 and the rest are just extra queries triggered by the display of the duplicates. This should be at most one query. We seem to be issuing an extra 7 queries per duplicate.

When retrieving the list of duplicates, we are 2.8s in the request (which isn't great), but the remaining 6 seconds are spent on needless queries while failing to display the list of duplicates.

[Aaron Bentley (abentley)]

This is a timeout. It looks like it's happening when looking up your bug subscriptions.

[komputes (komputes)]

I'm not subscribes to these two bugs AFAIK. Apport recognized existing bugs and opened them in chrome where I am not signed in. I opened them in firefox, where I am signed in and I get an oops every time.

[Daniel van Vugt (vanvugt)]

Confirmed with bug 576648 for me.

[William Grant (wgrant)]

https://lp-oops.canonical.com/oops/?oopsid=OOPS-3c62882fd39e38d4091213cbf0270da5

[Steve Kowalik (stevenk)]

OOPS-d4b24378110b85ccc7ed204eebd50f3f is a modern OOPS.

[Till Kamppeter (till-kamppeter)]

Another one:

https://bugs.launchpad.net/ubuntu/+source/colord/+bug/1027139

Error ID: OOPS-5ff99f1749e9f582da0c5190ccb276e0

[William Grant (wgrant)]

_getDirectAndDuplicateSubscriptions finds subscription details for all the dupes to which the user has a bugsubscription -- even if that bugsubscription is via a team. This manifests itself here because ~ubuntu-crashes-universe is subscribed to probably every single one of the dupes of these massively duplicated bugs.

This basically only affects users who are members of crash-bug-triagers (eg. ubuntu-bug-control and ubuntu-dev), which explains why random users aren't complaining. It can likely be worked around by unsubscribing crash-bug-triagers from the public dupes.

[Launchpad QA Bot (lpqabot)]

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

[Curtis Hovey (sinzui)]

This is okay for release, but I am not convinced it is fixed. If
    https://bugs.launchpad.net/ubuntu/+source/libx11/+bug/507062
loads in production after r16140 is released. then this bug certainly is fixed.

[William Grant (wgrant)]

There's still approximately one query per dupe: the permission check. That should be preloadable.