| 2011-02-01 03:50:53 |
Robert Collins |
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 |
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
|
|
