Launchpad itself

Archive:+packages (Archive:+index) timeouts

Bug #711073 reported by Robert Collins
32
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Launchpad itself
Fix Released
Critical
William Grant

Bug Description

 33 SELECT SourcePackagePublishingHistory.ancestor, SourcePackagePublishingHistory.archive, SourcePac ... SourcePackageRelease.version) DESC, SourcePackagePublishingHistory.id DESC LIMIT $INT OFFSET $INT:
     7 https://launchpad.net/%7Echromium-daily/+archive/ppa/+packages (Archive:+packages)
       OOPS-1857C1433, OOPS-1857D1615, OOPS-1857G119, OOPS-1857M1447, OOPS-1857N2219

Seems to be roughly this - note the very high non-sql time. This is consistent across different servers.
SQL time: 9692 ms
Non-sql time: 8244 ms
Total time: 17936 ms
Statement Count: 22

two queries cover the bulk of the SQL time.

1 4682.0 1 SQL-launchpad-main-slave
SELECT SourcePackagePublishingHistory.ancestor, SourcePackagePublishingHistory.archive, SourcePackagePublishingHistory.component, SourcePackagePublishingHistory.datecreated, SourcePackagePublishingHistory.datemadepending, SourcePackagePublishingHistory.datepublished, SourcePackagePublishingHistory.dateremoved, SourcePackagePublishingHistory.datesuperseded, SourcePackagePublishingHistory.distroseries, SourcePackagePublishingHistory.id, SourcePackagePublishingHistory.pocket, SourcePackagePublishingHistory.removal_comment, SourcePackagePublishingHistory.removed_by, SourcePackagePublishingHistory.scheduleddeletiondate, SourcePackagePublishingHistory.section, SourcePackagePublishingHistory.sourcepackagerelease, SourcePackagePublishingHistory.status, SourcePackagePublishingHistory.supersededby, "_prejoin1".user_defined_fields, "_prejoin1".architecturehintlist, "_prejoin1".build_conflicts, "_prejoin1".build_conflicts_indep, "_prejoin1".builddepends, "_prejoin1".builddependsindep, "_prejoin1".changelog, "_prejoin1".changelog_entry, "_prejoin1".component, "_prejoin1".copyright, "_prejoin1".creator, "_prejoin1".dateuploaded, "_prejoin1".dsc, "_prejoin1".dsc_binaries, "_prejoin1".dsc_format, "_prejoin1".dsc_maintainer_rfc822, "_prejoin1".dsc_standards_version, "_prejoin1".dscsigningkey, "_prejoin1".format, "_prejoin1".homepage, "_prejoin1".id, "_prejoin1".maintainer, "_prejoin1".section, "_prejoin1".sourcepackage_recipe_build, "_prejoin1".sourcepackagename, "_prejoin1".upload_archive, "_prejoin1".upload_distroseries, "_prejoin1".urgency, "_prejoin1".VERSION, "_prejoin2".account, "_prejoin2".creation_comment, "_prejoin2".creation_rationale, "_prejoin2".datecreated, "_prejoin2".defaultmembershipperiod, "_prejoin2".defaultrenewalperiod, "_prejoin2".displayname, "_prejoin2".hide_email_addresses, "_prejoin2".homepage_content, "_prejoin2".icon, "_prejoin2".id, "_prejoin2".logo, "_prejoin2".mailing_list_auto_subscribe_policy, "_prejoin2".merged, "_prejoin2".mugshot, "_prejoin2".name, "_prejoin2".personal_standing, "_prejoin2".personal_standing_reason, "_prejoin2".registrant, "_prejoin2".renewal_policy, "_prejoin2".subscriptionpolicy, "_prejoin2".teamdescription, "_prejoin2".teamowner, "_prejoin2".verbose_bugnotifications, "_prejoin2".visibility, "_prejoin3".active, "_prejoin3".algorithm, "_prejoin3".can_encrypt, "_prejoin3".fingerprint, "_prejoin3".id, "_prejoin3".keyid, "_prejoin3".keysize, "_prejoin3".OWNER, "_prejoin4".binarycount, "_prejoin4".changeslist, "_prejoin4".date_created, "_prejoin4".datereleased, "_prejoin4".defer_translation_imports, "_prejoin4".description, "_prejoin4".displayname, "_prejoin4".distribution, "_prejoin4".driver, "_prejoin4".hide_all_translations, "_prejoin4".id, "_prejoin4".language_pack_base, "_prejoin4".language_pack_delta, "_prejoin4".language_pack_full_export_requested, "_prejoin4".language_pack_proposed, "_prejoin4".messagecount, "_prejoin4".name, "_prejoin4".nominatedarchindep, "_prejoin4".OWNER, "_prejoin4".parent_series, "_prejoin4".sourcecount, "_prejoin4".releasestatus, "_prejoin4".summary, "_prejoin4".title, "_prejoin4".VERSION, "_prejoin5".id, "_prejoin5".name
FROM SourcePackageName, SourcePackageRelease, SourcePackagePublishingHistory
LEFT JOIN SourcePackageRelease AS "_prejoin1" ON SourcePackagePublishingHistory.sourcepackagerelease = "_prejoin1".id
LEFT JOIN Person AS "_prejoin2" ON "_prejoin1".creator = "_prejoin2".id
LEFT JOIN GPGKey AS "_prejoin3" ON "_prejoin1".dscsigningkey = "_prejoin3".id
LEFT JOIN DistroSeries AS "_prejoin4" ON SourcePackagePublishingHistory.distroseries = "_prejoin4".id
LEFT JOIN Section AS "_prejoin5" ON SourcePackagePublishingHistory.section = "_prejoin5".id
WHERE SourcePackagePublishingHistory.archive = 7087
  AND SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id
  AND SourcePackageRelease.sourcepackagename = SourcePackageName.id
  AND (1=1)
ORDER BY SourcePackageName.name, debversion_sort_key(SourcePackageRelease.VERSION) DESC, SourcePackagePublishingHistory.id DESC LIMIT 76
OFFSET 0
2 3380.0 1 SQL-launchpad-main-slave
SELECT SourcePackagePublishingHistory.ancestor,
       SourcePackagePublishingHistory.archive,
       SourcePackagePublishingHistory.component,
       SourcePackagePublishingHistory.datecreated,
       SourcePackagePublishingHistory.datemadepending,
       SourcePackagePublishingHistory.datepublished,
       SourcePackagePublishingHistory.dateremoved,
       SourcePackagePublishingHistory.datesuperseded,
       SourcePackagePublishingHistory.distroseries,
       SourcePackagePublishingHistory.id,
       SourcePackagePublishingHistory.pocket,
       SourcePackagePublishingHistory.removal_comment,
       SourcePackagePublishingHistory.removed_by,
       SourcePackagePublishingHistory.scheduleddeletiondate,
       SourcePackagePublishingHistory.section,
       SourcePackagePublishingHistory.sourcepackagerelease,
       SourcePackagePublishingHistory.status,
       SourcePackagePublishingHistory.supersededby,
       PackageUpload.archive,
       PackageUpload.changesfile,
       PackageUpload.date_created,
       PackageUpload.distroseries,
       PackageUpload.id,
       PackageUpload.pocket,
       PackageUpload.signing_key,
       PackageUpload.status,
       SourcePackageRelease.user_defined_fields,
       SourcePackageRelease.architecturehintlist,
       SourcePackageRelease.build_conflicts,
       SourcePackageRelease.build_conflicts_indep,
       SourcePackageRelease.builddepends,
       SourcePackageRelease.builddependsindep,
       SourcePackageRelease.changelog,
       SourcePackageRelease.changelog_entry,
       SourcePackageRelease.component,
       SourcePackageRelease.copyright,
       SourcePackageRelease.creator,
       SourcePackageRelease.dateuploaded,
       SourcePackageRelease.dsc,
       SourcePackageRelease.dsc_binaries,
       SourcePackageRelease.dsc_format,
       SourcePackageRelease.dsc_maintainer_rfc822,
       SourcePackageRelease.dsc_standards_version,
       SourcePackageRelease.dscsigningkey,
       SourcePackageRelease.format,
       SourcePackageRelease.homepage,
       SourcePackageRelease.id,
       SourcePackageRelease.maintainer,
       SourcePackageRelease.section,
       SourcePackageRelease.sourcepackage_recipe_build,
       SourcePackageRelease.sourcepackagename,
       SourcePackageRelease.upload_archive,
       SourcePackageRelease.upload_distroseries,
       SourcePackageRelease.urgency,
       SourcePackageRelease.VERSION, LibraryFileAlias.content,
                                     LibraryFileAlias.date_created,
                                     LibraryFileAlias.expires,
                                     LibraryFileAlias.filename,
                                     LibraryFileAlias.hits,
                                     LibraryFileAlias.id,
                                     LibraryFileAlias.last_accessed,
                                     LibraryFileAlias.mimetype,
                                     LibraryFileAlias.restricted,
                                     LibraryFileContent.datecreated,
                                     LibraryFileContent.filesize,
                                     LibraryFileContent.id,
                                     LibraryFileContent.md5,
                                     LibraryFileContent.sha1
FROM LibraryFileAlias,
     LibraryFileContent,
     PackageUpload,
     PackageUploadSource,
     SourcePackagePublishingHistory,
     SourcePackageRelease
WHERE LibraryFileContent.id = LibraryFileAlias.content
  AND LibraryFileAlias.id = PackageUpload.changesfile
  AND PackageUpload.id = PackageUploadSource.packageupload
  AND PackageUpload.status = %s
  AND PackageUpload.distroseries = SourcePackageRelease.upload_distroseries
  AND PackageUpload.archive = SourcePackageRelease.upload_archive
  AND PackageUploadSource.sourcepackagerelease = SourcePackageRelease.id
  AND SourcePackageRelease.id = SourcePackagePublishingHistory.sourcepackagerelease
  AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
ORDER BY SourcePackagePublishingHistory.id

This terrible thing contains most of the python time - I suspect storm cache updating overhead (because wide queries are hell on the cache - we update every field every time...)
15. 799 4682ms SQL-launchpad-main-slave
SELECT SourcePackagePublishingHistory.ancestor, SourcePackagePublishingHistory.archive, SourcePackagePublishingHistory.component, SourcePackagePublishingHistory.datecreated, SourcePackagePublishingHistory.datemadepending, SourcePackagePublishingHistory.datepublished, SourcePackagePublishingHistory.dateremoved, SourcePackagePublishingHistory.datesuperseded, SourcePackagePublishingHistory.distroseries, SourcePackagePublishingHistory.id, SourcePackagePublishingHistory.pocket, SourcePackagePublishingHistory.removal_comment, SourcePackagePublishingHistory.removed_by, SourcePackagePublishingHistory.scheduleddeletiondate, SourcePackagePublishingHistory.section, SourcePackagePublishingHistory.sourcepackagerelease, SourcePackagePublishingHistory.status, SourcePackagePublishingHistory.supersededby, "_prejoin1".user_defined_fields, "_prejoin1".architecturehintlist, "_prejoin1".build_conflicts, "_prejoin1".build_conflicts_indep, "_prejoin1".builddepends, "_prejoin1".builddependsindep, "_prejoin1".changelog, "_prejoin1".changelog_entry, "_prejoin1".component, "_prejoin1".copyright, "_prejoin1".creator, "_prejoin1".dateuploaded, "_prejoin1".dsc, "_prejoin1".dsc_binaries, "_prejoin1".dsc_format, "_prejoin1".dsc_maintainer_rfc822, "_prejoin1".dsc_standards_version, "_prejoin1".dscsigningkey, "_prejoin1".format, "_prejoin1".homepage, "_prejoin1".id, "_prejoin1".maintainer, "_prejoin1".section, "_prejoin1".sourcepackage_recipe_build, "_prejoin1".sourcepackagename, "_prejoin1".upload_archive, "_prejoin1".upload_distroseries, "_prejoin1".urgency, "_prejoin1".VERSION, "_prejoin2".account, "_prejoin2".creation_comment, "_prejoin2".creation_rationale, "_prejoin2".datecreated, "_prejoin2".defaultmembershipperiod, "_prejoin2".defaultrenewalperiod, "_prejoin2".displayname, "_prejoin2".hide_email_addresses, "_prejoin2".homepage_content, "_prejoin2".icon, "_prejoin2".id, "_prejoin2".logo, "_prejoin2".mailing_list_auto_subscribe_policy, "_prejoin2".merged, "_prejoin2".mugshot, "_prejoin2".name, "_prejoin2".personal_standing, "_prejoin2".personal_standing_reason, "_prejoin2".registrant, "_prejoin2".renewal_policy, "_prejoin2".subscriptionpolicy, "_prejoin2".teamdescription, "_prejoin2".teamowner, "_prejoin2".verbose_bugnotifications, "_prejoin2".visibility, "_prejoin3".active, "_prejoin3".algorithm, "_prejoin3".can_encrypt, "_prejoin3".fingerprint, "_prejoin3".id, "_prejoin3".keyid, "_prejoin3".keysize, "_prejoin3".OWNER, "_prejoin4".binarycount, "_prejoin4".changeslist, "_prejoin4".date_created, "_prejoin4".datereleased, "_prejoin4".defer_translation_imports, "_prejoin4".description, "_prejoin4".displayname, "_prejoin4".distribution, "_prejoin4".driver, "_prejoin4".hide_all_translations, "_prejoin4".id, "_prejoin4".language_pack_base, "_prejoin4".language_pack_delta, "_prejoin4".language_pack_full_export_requested, "_prejoin4".language_pack_proposed, "_prejoin4".messagecount, "_prejoin4".name, "_prejoin4".nominatedarchindep, "_prejoin4".OWNER, "_prejoin4".parent_series, "_prejoin4".sourcecount, "_prejoin4".releasestatus, "_prejoin4".summary, "_prejoin4".title, "_prejoin4".VERSION, "_prejoin5".id, "_prejoin5".name
FROM SourcePackageName, SourcePackageRelease, SourcePackagePublishingHistory
LEFT JOIN SourcePackageRelease AS "_prejoin1" ON SourcePackagePublishingHistory.sourcepackagerelease = "_prejoin1".id
LEFT JOIN Person AS "_prejoin2" ON "_prejoin1".creator = "_prejoin2".id
LEFT JOIN GPGKey AS "_prejoin3" ON "_prejoin1".dscsigningkey = "_prejoin3".id
LEFT JOIN DistroSeries AS "_prejoin4" ON SourcePackagePublishingHistory.distroseries = "_prejoin4".id
LEFT JOIN Section AS "_prejoin5" ON SourcePackagePublishingHistory.section = "_prejoin5".id
WHERE SourcePackagePublishingHistory.archive = 7087
  AND SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id
  AND SourcePackageRelease.sourcepackagename = SourcePackageName.id
  AND (1=1)
ORDER BY SourcePackageName.name, debversion_sort_key(SourcePackageRelease.VERSION) DESC, SourcePackagePublishingHistory.id DESC LIMIT 76
OFFSET 0

See original description
Tags: timeout
Robert Collins (lifeless)
description: updated
Revision history for this message
Curtis Hovey (sinzui) wrote : Re: Archive:+packages (+index) timeouts

ArchiveSourcePackageListViewBase used by both +index and +packages is partially at fault. Fixing the view or the base objects/queries will address the common problem for OOPS-1886F765 and OOPS-1857N2219

summary: - Archive:+packages timeouts
+ Archive:+packages (+index) timeouts
Robert Collins (lifeless)
summary: - Archive:+packages (+index) timeouts
+ Archive:+packages (Archive:+index) timeouts
Revision history for this message
William Grant (wgrant) wrote :

Dominating new OOPSes are queries involved in build status summarisation. This is unnecessary for +index, duplicated on +packages (ArchiveSourcePublication prepopulates some stuff for getBuildStatusSummariesForSourceIdsAndArchives, but that method grabs them itself), and getBuildStatusSummariesForSourceIdsAndArchives is pretty terrible on its own.

Changed in launchpad:
assignee: nobody → William Grant (wgrant)
status: Triaged → In Progress
Revision history for this message
William Grant (wgrant) wrote :

They're 99% < 5s and rarely timing out now. I don't think more significant progress can be made before bug #758258 is sorted out after the next DB deploy.

Changed in launchpad:
assignee: William Grant (wgrant) → nobody
status: In Progress → Triaged
William Grant (wgrant)
Changed in launchpad:
assignee: nobody → William Grant (wgrant)
status: Triaged → In Progress
William Grant (wgrant)
Changed in launchpad:
status: In Progress → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Related questions

Remote bug watches

Bug watches keep track of this bug in other bug trackers.