Archive:+subscriptions times out with many subscribers

Bug #741092 reported by Anthony Lenton
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Launchpad itself
Fix Released
Critical
Unassigned

Bug Description

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

Tags: qa-ok timeout

Related branches

Curtis Hovey (sinzui)
summary: - PPA subscriptions page times out consistently
+ Archive:+subscriptions times out with many subscribers
tags: added: timeout
Changed in launchpad:
status: New → Triaged
importance: Undecided → Critical
Revision history for this message
Robert Collins (lifeless) wrote :

7K direct subscribers? m,eeeeeeeeeeep

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

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

Curtis Hovey (sinzui)
description: updated
Revision history for this message
Anthony Lenton (elachuni) wrote :

@lifeless: yup, OOPS-1913D817

Revision history for this message
Launchpad QA Bot (lpqabot) wrote :
Changed in launchpad:
assignee: nobody → Robert Collins (lifeless)
milestone: none → 11.04
tags: added: qa-needstesting
Changed in launchpad:
status: Triaged → Fix Committed
Revision history for this message
Robert Collins (lifeless) wrote :

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

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

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

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

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

tags: added: qa-ok
removed: qa-needstesting
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :
tags: added: qa-needstesting
removed: qa-ok
tags: added: qa-ok
removed: qa-needstesting
William Grant (wgrant)
Changed in launchpad:
status: Fix Committed → Fix Released
Revision history for this message
Robert Collins (lifeless) wrote :

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

Revision history for this message
Anthony Lenton (elachuni) wrote :

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

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

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.

Changed in launchpad:
status: Fix Released → Triaged
milestone: 11.04 → none
assignee: Robert Collins (lifeless) → nobody
Revision history for this message
Robert Collins (lifeless) wrote :

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.

Changed in launchpad:
status: Triaged → Fix Released
Revision history for this message
Anthony Lenton (elachuni) wrote :

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)

Changed in launchpad:
status: Fix Released → Confirmed
Revision history for this message
Anthony Lenton (elachuni) wrote :

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

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

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

Changed in launchpad:
status: Confirmed → Fix Released
Revision history for this message
Anthony Lenton (elachuni) wrote :

Filed bug #834303 with this.

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

Other bug subscribers