Archive:+subscriptions times out with many subscribers

[Anthony Lenton]

When trying to access the current list of subscribers to a ppa LP consistently times out:

https://launchpad.net/~commercial-ppa-uploaders/+archive/vendetta-online/+subscriptions

This ppa has ~7k subscribers, so that could have to do with it.

OOPS-1908L1513 looks like death by repetition in the template creating links to people.

1 586 3194 5 3189 SQL-launchpad-main-slave
SELECT Person.account,
       Person.creation_comment,
       Person.creation_rationale,
       Person.datecreated,
       Person.defaultmembershipperiod,
       Person.defaultrenewalperiod,
       Person.displayname,
       Person.hide_email_addresses,
       Person.homepage_content,
       Person.icon,
       Person.id,
       Person.logo,
       Person.mailing_list_auto_subscribe_policy,
       Person.merged,
       Person.mugshot,
       Person.name,
       Person.personal_standing,
       Person.personal_standing_reason,
       Person.registrant,
       Person.renewal_policy,
       Person.subscriptionpolicy,
       Person.teamdescription,
       Person.teamowner,
       Person.verbose_bugnotifications,
       Person.visibility
FROM Person
WHERE Person.id = $INTLIMIT $INT
2 584 2154 3 2151 SQL-launchpad-main-slave
SELECT ValidPersonCache.id
FROM ValidPersonCache
WHERE ValidPersonCache.id = $INTLIMIT $INT

[Robert Collins (lifeless)]

7K direct subscribers? m,eeeeeeeeeeep

[Robert Collins (lifeless)]

Can we please get an OOPS code - I can't see the archive so cannot generate one myself.

[Anthony Lenton (elachuni)]

@lifeless: yup, OOPS-1913D817

[Launchpad QA Bot (lpqabot)]

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

[Robert Collins (lifeless)]

08:15 < sinzui> lifeless: I asked achuni to help since he reported the bug. The cold cache does timeout: OOPS-1914QS128 and the second try does too: OOPS-1914QS129

[Robert Collins (lifeless)]

OOPS-1914QS133
OOPS-1914QS134
OOPS-1914QS135
OOPS-1914QS136
OOPS-1914QS137
OOPS-1914QS138
OOPS-1914QS139

[Robert Collins (lifeless)]

smaller archives render ok; this can be deployed, and we'll see the production impact.

[Launchpad QA Bot (lpqabot)]

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

[Robert Collins (lifeless)]

@anthony, could you confirm that this now works (or supply a couple of updated OOPSes if it doesn't). Thanks!

[Anthony Lenton (elachuni)]

Hi lifeless,
Yup, these are from prod:
OOPS-1917K170
OOPS-1917K178
OOPS-1917S143
OOPS-1917R129
OOPS-1917Q151
OOPS-1917K185
OOPS-1917Q152
OOPS-1917O138

[Robert Collins (lifeless)]

Wheeee

Branch: launchpad-rev-12696
Revno: 12696
SQL time: 664 ms
Non-sql time: 24942 ms
Total time: 25606 ms
Statement Count: 29

6 seconds between
26. 159 58ms SQL-launchpad-main-slave
SELECT ArchiveSubscriber.archive,
       ArchiveSubscriber.cancelled_by,
       ArchiveSubscriber.date_cancelled,
       ArchiveSubscriber.date_created,
       ArchiveSubscriber.date_expires,
       ArchiveSubscriber.description,
       ArchiveSubscriber.id,
       ArchiveSubscriber.registrant,
       ArchiveSubscriber.status,
       ArchiveSubscriber.subscriber
FROM ArchiveSubscriber
WHERE ArchiveSubscriber.archive = 21283
  AND ArchiveSubscriber.status = 1
ORDER BY ArchiveSubscriber.date_created DESC

and

27. 6731 574ms SQL-launchpad-main-slave
SELECT Person.account,

and then 16 seconds before it runs up against a valid person cache check and fails spectacularly.

Clearly the SQL is fast enough; possibly canonical_url or some other formatter inefficiency.

[Robert Collins (lifeless)]

Marking fix released - the sql change was successful and any cpu time issue will need fresh data and eyes; I haven't seen this in a while now.

[Anthony Lenton (elachuni)]

Hi lifeless,

This still fails consistently, here are some more oops ids for it:

OOPS-2064AO2
OOPS-2064AW1
OOPS-2064M3
OOPS-2064EB3
OOPS-2064L1
OOPS-2064ED4
OOPS-2064AO3
OOPS-2064AQ4
OOPS-2064C4
OOPS-2064AX1
OOPS-2064DS1
OOPS-2064CP2
OOPS-2064DV1
OOPS-2064DZ2
OOPS-2064K1
OOPS-2064DS3
OOPS-2064EE2
OOPS-2064M4
OOPS-2064CC1
OOPS-2064C5
OOPS-2064L3

Possibly there aren't that many ppas with multi-k subscribers yet, but this is a serious issue for managing commercial ppas where we sometimes need to go in and revoke somebody's subscription (due to a refund, or if the private debline is found on a public forum for instance).

Looking at ppa subscriptions pages it seems that the results there aren't paginated at all, I would imagine having 15k user details on the page would cause these issues. Would it be something that's easy to add?

I believe that ppa is the one with most direct subscribers, but other ppas with multi-k direct subscribers have the same issue:

OOPS-2064DV2 (7.4k subscribers)
OOPS-2064DZ3 (4.8k subscribers)
OOPS-2064E2 (7.5k subscribers)

[Anthony Lenton (elachuni)]

Bumped back to open, let me know if I should file a new bug instead

[Robert Collins (lifeless)]

Always open new bugs please, that fits our qa and workflows -much- better.

[Anthony Lenton (elachuni)]

Filed bug #834303 with this.