| 2010-12-17 10:17:12 |
Robert Collins |
bug |
|
|
added bug |
| 2010-12-17 10:19:23 |
Robert Collins |
description |
7 https://launchpad.net/%7Echromium-daily/+archive/dev/+index (Archive:+index)
OOPS-1811A1577, OOPS-1811A1578, OOPS-1811E1796, OOPS-1811E1797, OOPS-1811K1590 |
7 https://launchpad.net/%7Echromium-daily/+archive/dev/+index (Archive:+index)
OOPS-1811A1577, OOPS-1811A1578, OOPS-1811E1796, OOPS-1811E1797, OOPS-1811K1590
consistently high sql time. Taking the first one...
Branch: launchpad-rev-12035
Revno: 12035
SQL time: 15976 ms
Non-sql time: 511 ms
Total time: 16487 ms
Statement Count: 49
1 984.0 1 SQL-launchpad-main-slave SELECT * FROM ((SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive != PackageBuild.archive AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id) UNION (SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive = PackageBuild.archive AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id)) AS "_1dd4f" ORDER BY id
2 882.0 1 SQL-launchpad-main-slave SELECT 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".lucilleconfig, "_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 = 14095 AND SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id AND SourcePackageRelease.sourcepackagename = SourcePackageName.id AND SourcePackagePublishingHistory.status IN (2) AND (1=1) ORDER BY datepublished DESC LIMIT 5 OFFSET 0
...
that is, queries taking nearly 1 second, and many of them, but none are detected as duplicates. |
|
| 2010-12-17 10:32:05 |
Robert Collins |
description |
7 https://launchpad.net/%7Echromium-daily/+archive/dev/+index (Archive:+index)
OOPS-1811A1577, OOPS-1811A1578, OOPS-1811E1796, OOPS-1811E1797, OOPS-1811K1590
consistently high sql time. Taking the first one...
Branch: launchpad-rev-12035
Revno: 12035
SQL time: 15976 ms
Non-sql time: 511 ms
Total time: 16487 ms
Statement Count: 49
1 984.0 1 SQL-launchpad-main-slave SELECT * FROM ((SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive != PackageBuild.archive AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id) UNION (SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive = PackageBuild.archive AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id)) AS "_1dd4f" ORDER BY id
2 882.0 1 SQL-launchpad-main-slave SELECT 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".lucilleconfig, "_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 = 14095 AND SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id AND SourcePackageRelease.sourcepackagename = SourcePackageName.id AND SourcePackagePublishingHistory.status IN (2) AND (1=1) ORDER BY datepublished DESC LIMIT 5 OFFSET 0
...
that is, queries taking nearly 1 second, and many of them, but none are detected as duplicates. |
7 https://launchpad.net/%7Echromium-daily/+archive/dev/+index (Archive:+index)
OOPS-1811A1577, OOPS-1811A1578, OOPS-1811E1796, OOPS-1811E1797, OOPS-1811K1590
consistently high sql time. Taking the first one...
Branch: launchpad-rev-12035
Revno: 12035
SQL time: 15976 ms
Non-sql time: 511 ms
Total time: 16487 ms
Statement Count: 49
1 984.0 1 SQL-launchpad-main-slave SELECT * FROM ((SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive != PackageBuild.archive AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id) UNION (SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive = PackageBuild.archive AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id)) AS "_1dd4f" ORDER BY id
2 882.0 1 SQL-launchpad-main-slave SELECT 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".lucilleconfig, "_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 = 14095 AND SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id AND SourcePackageRelease.sourcepackagename = SourcePackageName.id AND SourcePackagePublishingHistory.status IN (2) AND (1=1) ORDER BY datepublished DESC LIMIT 5 OFFSET 0
...
that is, queries taking nearly 1 second, and many of them, but not all are detected as duplicates.
The duplicates are:
1 3 2300 766 1534 SQL-launchpad-main-slave (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackageBuild.id IN (%s, %s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag) EXCEPT (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.pocket = SourcePackagePublishingHistory.pocket AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackagePublishingHistory.datepublished IS NOT NULL AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag)
2 3 959 319 640 SQL-launchpad-main-slave SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized, PackageBuild.archive, PackageBuild.build_farm_job, PackageBuild.dependencies, PackageBuild.id, PackageBuild.pocket, PackageBuild.upload_log, BuildFarmJob.builder, BuildFarmJob.date_created, BuildFarmJob.date_finished, BuildFarmJob.date_first_dispatched, BuildFarmJob.date_started, BuildFarmJob.failure_count, BuildFarmJob.id, BuildFarmJob.job_type, BuildFarmJob.log, BuildFarmJob.processor, BuildFarmJob.status, BuildFarmJob.virtualized FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackageBuild.id IN (%s, %s) AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag
3 2 1031 515 516 SQL-launchpad-main-slave (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s) AND BinaryPackageBuild.id IN (%s, %s, %s, %s, %s, %s, %s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag) EXCEPT (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.pocket = SourcePackagePublishingHistory.pocket AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s) AND BinaryPackagePublishingHistory.datepublished IS NOT NULL AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag)
which would save 2.5 seconds if eliminated. |
|
| 2010-12-17 10:32:47 |
Robert Collins |
description |
7 https://launchpad.net/%7Echromium-daily/+archive/dev/+index (Archive:+index)
OOPS-1811A1577, OOPS-1811A1578, OOPS-1811E1796, OOPS-1811E1797, OOPS-1811K1590
consistently high sql time. Taking the first one...
Branch: launchpad-rev-12035
Revno: 12035
SQL time: 15976 ms
Non-sql time: 511 ms
Total time: 16487 ms
Statement Count: 49
1 984.0 1 SQL-launchpad-main-slave SELECT * FROM ((SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive != PackageBuild.archive AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id) UNION (SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive = PackageBuild.archive AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id)) AS "_1dd4f" ORDER BY id
2 882.0 1 SQL-launchpad-main-slave SELECT 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".lucilleconfig, "_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 = 14095 AND SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id AND SourcePackageRelease.sourcepackagename = SourcePackageName.id AND SourcePackagePublishingHistory.status IN (2) AND (1=1) ORDER BY datepublished DESC LIMIT 5 OFFSET 0
...
that is, queries taking nearly 1 second, and many of them, but not all are detected as duplicates.
The duplicates are:
1 3 2300 766 1534 SQL-launchpad-main-slave (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackageBuild.id IN (%s, %s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag) EXCEPT (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.pocket = SourcePackagePublishingHistory.pocket AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackagePublishingHistory.datepublished IS NOT NULL AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag)
2 3 959 319 640 SQL-launchpad-main-slave SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized, PackageBuild.archive, PackageBuild.build_farm_job, PackageBuild.dependencies, PackageBuild.id, PackageBuild.pocket, PackageBuild.upload_log, BuildFarmJob.builder, BuildFarmJob.date_created, BuildFarmJob.date_finished, BuildFarmJob.date_first_dispatched, BuildFarmJob.date_started, BuildFarmJob.failure_count, BuildFarmJob.id, BuildFarmJob.job_type, BuildFarmJob.log, BuildFarmJob.processor, BuildFarmJob.status, BuildFarmJob.virtualized FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackageBuild.id IN (%s, %s) AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag
3 2 1031 515 516 SQL-launchpad-main-slave (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s) AND BinaryPackageBuild.id IN (%s, %s, %s, %s, %s, %s, %s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag) EXCEPT (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.pocket = SourcePackagePublishingHistory.pocket AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s) AND BinaryPackagePublishingHistory.datepublished IS NOT NULL AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag)
which would save 2.5 seconds if eliminated. |
7 https://launchpad.net/%7Echromium-daily/+archive/dev/+index (Archive:+index)
OOPS-1811A1577, OOPS-1811A1578, OOPS-1811E1796, OOPS-1811E1797, OOPS-1811K1590
consistently high sql time. Taking the first one...
Branch: launchpad-rev-12035
Revno: 12035
SQL time: 15976 ms
Non-sql time: 511 ms
Total time: 16487 ms
Statement Count: 49
1 984.0 1 SQL-launchpad-main-slave SELECT * FROM ((SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive != PackageBuild.archive AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id) UNION (SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive = PackageBuild.archive AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id)) AS "_1dd4f" ORDER BY id
2 882.0 1 SQL-launchpad-main-slave SELECT 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".lucilleconfig, "_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 = 14095 AND SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id AND SourcePackageRelease.sourcepackagename = SourcePackageName.id AND SourcePackagePublishingHistory.status IN (2) AND (1=1) ORDER BY datepublished DESC LIMIT 5 OFFSET 0
...
that is, queries taking nearly 1 second, and few of them are detected as duplicates.
The duplicates are:
1 3 2300 766 1534 SQL-launchpad-main-slave (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackageBuild.id IN (%s, %s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag) EXCEPT (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.pocket = SourcePackagePublishingHistory.pocket AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackagePublishingHistory.datepublished IS NOT NULL AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag)
2 3 959 319 640 SQL-launchpad-main-slave SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized, PackageBuild.archive, PackageBuild.build_farm_job, PackageBuild.dependencies, PackageBuild.id, PackageBuild.pocket, PackageBuild.upload_log, BuildFarmJob.builder, BuildFarmJob.date_created, BuildFarmJob.date_finished, BuildFarmJob.date_first_dispatched, BuildFarmJob.date_started, BuildFarmJob.failure_count, BuildFarmJob.id, BuildFarmJob.job_type, BuildFarmJob.log, BuildFarmJob.processor, BuildFarmJob.status, BuildFarmJob.virtualized FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackageBuild.id IN (%s, %s) AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag
3 2 1031 515 516 SQL-launchpad-main-slave (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s) AND BinaryPackageBuild.id IN (%s, %s, %s, %s, %s, %s, %s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag) EXCEPT (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.pocket = SourcePackagePublishingHistory.pocket AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s) AND BinaryPackagePublishingHistory.datepublished IS NOT NULL AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag)
which would save 2.5 seconds if eliminated.
|
|
| 2010-12-20 06:09:42 |
Robert Collins |
tags |
timeout |
dba timeout |
|
| 2011-01-03 18:43:22 |
Robert Collins |
description |
7 https://launchpad.net/%7Echromium-daily/+archive/dev/+index (Archive:+index)
OOPS-1811A1577, OOPS-1811A1578, OOPS-1811E1796, OOPS-1811E1797, OOPS-1811K1590
consistently high sql time. Taking the first one...
Branch: launchpad-rev-12035
Revno: 12035
SQL time: 15976 ms
Non-sql time: 511 ms
Total time: 16487 ms
Statement Count: 49
1 984.0 1 SQL-launchpad-main-slave SELECT * FROM ((SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive != PackageBuild.archive AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id) UNION (SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive = PackageBuild.archive AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id)) AS "_1dd4f" ORDER BY id
2 882.0 1 SQL-launchpad-main-slave SELECT 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".lucilleconfig, "_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 = 14095 AND SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id AND SourcePackageRelease.sourcepackagename = SourcePackageName.id AND SourcePackagePublishingHistory.status IN (2) AND (1=1) ORDER BY datepublished DESC LIMIT 5 OFFSET 0
...
that is, queries taking nearly 1 second, and few of them are detected as duplicates.
The duplicates are:
1 3 2300 766 1534 SQL-launchpad-main-slave (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackageBuild.id IN (%s, %s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag) EXCEPT (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.pocket = SourcePackagePublishingHistory.pocket AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackagePublishingHistory.datepublished IS NOT NULL AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag)
2 3 959 319 640 SQL-launchpad-main-slave SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized, PackageBuild.archive, PackageBuild.build_farm_job, PackageBuild.dependencies, PackageBuild.id, PackageBuild.pocket, PackageBuild.upload_log, BuildFarmJob.builder, BuildFarmJob.date_created, BuildFarmJob.date_finished, BuildFarmJob.date_first_dispatched, BuildFarmJob.date_started, BuildFarmJob.failure_count, BuildFarmJob.id, BuildFarmJob.job_type, BuildFarmJob.log, BuildFarmJob.processor, BuildFarmJob.status, BuildFarmJob.virtualized FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackageBuild.id IN (%s, %s) AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag
3 2 1031 515 516 SQL-launchpad-main-slave (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s) AND BinaryPackageBuild.id IN (%s, %s, %s, %s, %s, %s, %s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag) EXCEPT (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.pocket = SourcePackagePublishingHistory.pocket AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s) AND BinaryPackagePublishingHistory.datepublished IS NOT NULL AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag)
which would save 2.5 seconds if eliminated.
|
7 https://launchpad.net/%7Echromium-daily/+archive/dev/+index (Archive:+index)
OOPS-1811A1577, OOPS-1811A1578, OOPS-1811E1796, OOPS-1811E1797, OOPS-1811K1590
OOPS-1828D1750 in xorg-edgers shows similar characteristics with rev 12138, though only 8 seconds of SQL time (still exorbitant).
consistently high sql time. Taking the first one...
Branch: launchpad-rev-12035
Revno: 12035
SQL time: 15976 ms
Non-sql time: 511 ms
Total time: 16487 ms
Statement Count: 49
1 984.0 1 SQL-launchpad-main-slave SELECT * FROM ((SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive != PackageBuild.archive AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id) UNION (SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive = PackageBuild.archive AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id)) AS "_1dd4f" ORDER BY id
2 882.0 1 SQL-launchpad-main-slave SELECT 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".lucilleconfig, "_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 = 14095 AND SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id AND SourcePackageRelease.sourcepackagename = SourcePackageName.id AND SourcePackagePublishingHistory.status IN (2) AND (1=1) ORDER BY datepublished DESC LIMIT 5 OFFSET 0
...
that is, queries taking nearly 1 second, and few of them are detected as duplicates.
The duplicates are:
1 3 2300 766 1534 SQL-launchpad-main-slave (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackageBuild.id IN (%s, %s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag) EXCEPT (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.pocket = SourcePackagePublishingHistory.pocket AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackagePublishingHistory.datepublished IS NOT NULL AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag)
2 3 959 319 640 SQL-launchpad-main-slave SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized, PackageBuild.archive, PackageBuild.build_farm_job, PackageBuild.dependencies, PackageBuild.id, PackageBuild.pocket, PackageBuild.upload_log, BuildFarmJob.builder, BuildFarmJob.date_created, BuildFarmJob.date_finished, BuildFarmJob.date_first_dispatched, BuildFarmJob.date_started, BuildFarmJob.failure_count, BuildFarmJob.id, BuildFarmJob.job_type, BuildFarmJob.log, BuildFarmJob.processor, BuildFarmJob.status, BuildFarmJob.virtualized FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackageBuild.id IN (%s, %s) AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag
3 2 1031 515 516 SQL-launchpad-main-slave (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s) AND BinaryPackageBuild.id IN (%s, %s, %s, %s, %s, %s, %s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag) EXCEPT (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.pocket = SourcePackagePublishingHistory.pocket AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s) AND BinaryPackagePublishingHistory.datepublished IS NOT NULL AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag)
which would save 2.5 seconds if eliminated.
|
|
| 2011-01-12 16:42:43 |
Robert Collins |
launchpad: importance |
High |
Critical |
|
| 2011-01-21 22:33:34 |
Julian Edwards |
branch linked |
|
lp:~julian-edwards/launchpad/timeout-bug-691478 |
|
| 2011-01-21 22:34:13 |
Julian Edwards |
launchpad: status |
Triaged |
In Progress |
|
| 2011-01-22 16:31:46 |
Launchpad QA Bot |
launchpad: milestone |
|
11.02 |
|
| 2011-01-22 16:31:46 |
Launchpad QA Bot |
launchpad: assignee |
|
Julian Edwards (julian-edwards) |
|
| 2011-01-22 16:31:48 |
Launchpad QA Bot |
tags |
dba timeout |
dba qa-needstesting timeout |
|
| 2011-01-22 16:31:51 |
Launchpad QA Bot |
launchpad: status |
In Progress |
Fix Committed |
|
| 2011-01-23 14:03:02 |
Julian Edwards |
tags |
dba qa-needstesting timeout |
dba qa-ok timeout |
|
| 2011-01-28 01:41:05 |
William Grant |
launchpad: status |
Fix Committed |
Fix Released |
|
| 2011-01-31 12:32:59 |
Julian Edwards |
launchpad: status |
Fix Released |
Triaged |
|
| 2011-02-01 05:14:51 |
Robert Collins |
branch unlinked |
lp:~julian-edwards/launchpad/timeout-bug-691478 |
|
|
| 2011-02-01 05:15:22 |
Robert Collins |
description |
7 https://launchpad.net/%7Echromium-daily/+archive/dev/+index (Archive:+index)
OOPS-1811A1577, OOPS-1811A1578, OOPS-1811E1796, OOPS-1811E1797, OOPS-1811K1590
OOPS-1828D1750 in xorg-edgers shows similar characteristics with rev 12138, though only 8 seconds of SQL time (still exorbitant).
consistently high sql time. Taking the first one...
Branch: launchpad-rev-12035
Revno: 12035
SQL time: 15976 ms
Non-sql time: 511 ms
Total time: 16487 ms
Statement Count: 49
1 984.0 1 SQL-launchpad-main-slave SELECT * FROM ((SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive != PackageBuild.archive AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id) UNION (SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive = PackageBuild.archive AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY BinaryPackageBuild.id)) AS "_1dd4f" ORDER BY id
2 882.0 1 SQL-launchpad-main-slave SELECT 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".lucilleconfig, "_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 = 14095 AND SourcePackagePublishingHistory.sourcepackagerelease = SourcePackageRelease.id AND SourcePackageRelease.sourcepackagename = SourcePackageName.id AND SourcePackagePublishingHistory.status IN (2) AND (1=1) ORDER BY datepublished DESC LIMIT 5 OFFSET 0
...
that is, queries taking nearly 1 second, and few of them are detected as duplicates.
The duplicates are:
1 3 2300 766 1534 SQL-launchpad-main-slave (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackageBuild.id IN (%s, %s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag) EXCEPT (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.pocket = SourcePackagePublishingHistory.pocket AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackagePublishingHistory.datepublished IS NOT NULL AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag)
2 3 959 319 640 SQL-launchpad-main-slave SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized, PackageBuild.archive, PackageBuild.build_farm_job, PackageBuild.dependencies, PackageBuild.id, PackageBuild.pocket, PackageBuild.upload_log, BuildFarmJob.builder, BuildFarmJob.date_created, BuildFarmJob.date_finished, BuildFarmJob.date_first_dispatched, BuildFarmJob.date_started, BuildFarmJob.failure_count, BuildFarmJob.id, BuildFarmJob.job_type, BuildFarmJob.log, BuildFarmJob.processor, BuildFarmJob.status, BuildFarmJob.virtualized FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s) AND BinaryPackageBuild.id IN (%s, %s) AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag
3 2 1031 515 516 SQL-launchpad-main-slave (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE BinaryPackageBuild.package_build = PackageBuild.id AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s) AND BinaryPackageBuild.id IN (%s, %s, %s, %s, %s, %s, %s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag) EXCEPT (SELECT 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, BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release, DistroArchSeries.architecturetag, DistroArchSeries.distroseries, DistroArchSeries.enabled, DistroArchSeries.id, DistroArchSeries.official, DistroArchSeries.owner, DistroArchSeries.package_count, DistroArchSeries.processorfamily, DistroArchSeries.supports_virtualized FROM BinaryPackageBuild, BinaryPackageName, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release AND BinaryPackageRelease.build = BinaryPackageBuild.id AND BinaryPackageRelease.binarypackagename = BinaryPackageName.id AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries AND BinaryPackagePublishingHistory.distroarchseries = DistroArchSeries.id AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackagePublishingHistory.pocket = SourcePackagePublishingHistory.pocket AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s) AND BinaryPackagePublishingHistory.datepublished IS NOT NULL AND BinaryPackageBuild.package_build = PackageBuild.id AND PackageBuild.build_farm_job = BuildFarmJob.id AND BuildFarmJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag)
which would save 2.5 seconds if eliminated.
|
260 (SELECT SourcePackagePublishingHistory.ancestor, SourcePackagePublishingHistory.archive, SourcePa ... mJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag):
GET: 235 POST: 2 Other: 23 Robots: 15 Local: 74
51 https://launchpad.net/%7Exorg-edgers/+archive/ppa/+index (Archive:+index)
OOPS-1857C1105, OOPS-1857C1261, OOPS-1857C1477, OOPS-1857C1480, OOPS-1857C2230
39 https://launchpad.net/%7Ekubuntu-ppa/+archive/backports/+index (Archive:+index)
OOPS-1857C2339, OOPS-1857C2368, OOPS-1857C2369, OOPS-1857C2962, OOPS-1857C648
33 https://launchpad.net/%7Ezend-framework/+archive/ppa/+index (Archive:+index)
OOPS-1857A403, OOPS-1857A404, OOPS-1857A405, OOPS-1857A406, OOPS-1857C507
|
|
| 2011-02-01 05:15:34 |
Robert Collins |
launchpad: assignee |
Julian Edwards (julian-edwards) |
|
|
| 2011-02-01 05:15:37 |
Robert Collins |
launchpad: milestone |
11.02 |
|
|
| 2011-02-01 06:12:34 |
Robert Collins |
description |
260 (SELECT SourcePackagePublishingHistory.ancestor, SourcePackagePublishingHistory.archive, SourcePa ... mJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag):
GET: 235 POST: 2 Other: 23 Robots: 15 Local: 74
51 https://launchpad.net/%7Exorg-edgers/+archive/ppa/+index (Archive:+index)
OOPS-1857C1105, OOPS-1857C1261, OOPS-1857C1477, OOPS-1857C1480, OOPS-1857C2230
39 https://launchpad.net/%7Ekubuntu-ppa/+archive/backports/+index (Archive:+index)
OOPS-1857C2339, OOPS-1857C2368, OOPS-1857C2369, OOPS-1857C2962, OOPS-1857C648
33 https://launchpad.net/%7Ezend-framework/+archive/ppa/+index (Archive:+index)
OOPS-1857A403, OOPS-1857A404, OOPS-1857A405, OOPS-1857A406, OOPS-1857C507
|
260 (SELECT SourcePackagePublishingHistory.ancestor, SourcePackagePublishingHistory.archive, SourcePa ... mJob.status IN (%s) ORDER BY SourcePackagePublishingHistory.id, DistroArchSeries.architecturetag):
GET: 235 POST: 2 Other: 23 Robots: 15 Local: 74
51 https://launchpad.net/%7Exorg-edgers/+archive/ppa/+index (Archive:+index)
OOPS-1857C1105, OOPS-1857C1261, OOPS-1857C1477, OOPS-1857C1480, OOPS-1857C2230
39 https://launchpad.net/%7Ekubuntu-ppa/+archive/backports/+index (Archive:+index)
OOPS-1857C2339, OOPS-1857C2368, OOPS-1857C2369, OOPS-1857C2962, OOPS-1857C648
33 https://launchpad.net/%7Ezend-framework/+archive/ppa/+index (Archive:+index)
OOPS-1857A403, OOPS-1857A404, OOPS-1857A405, OOPS-1857A406, OOPS-1857C507
Taking OOPS-1857A403
Branch: launchpad-rev-12274
Revno: 12274
SQL time: 13968 ms
Non-sql time: 402 ms
Total time: 14370 ms
Statement Count: 50
The query count is tolerable; single largest query is
1 1309.0 1 SQL-launchpad-main-slave
SELECT *
FROM (
(SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release
FROM BinaryPackageBuild, BinaryPackagePublishingHistory, BinaryPackageRelease, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory
WHERE BinaryPackageBuild.package_build = PackageBuild.id
AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id
AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries
AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release
AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s, %s, %s)
AND SourcePackagePublishingHistory.archive != PackageBuild.archive
AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive
AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id
AND BinaryPackageRelease.build = BinaryPackageBuild.id
AND BinaryPackageBuild.package_build = PackageBuild.id
AND PackageBuild.build_farm_job = BuildFarmJob.id
AND BuildFarmJob.status IN (%s)
ORDER BY BinaryPackageBuild.id)
UNION
(SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release
FROM BinaryPackageBuild, BuildFarmJob, DistroArchSeries, PackageBuild, SourcePackagePublishingHistory
WHERE BinaryPackageBuild.package_build = PackageBuild.id
AND BinaryPackageBuild.distro_arch_series = DistroArchSeries.id
AND SourcePackagePublishingHistory.distroseries = DistroArchSeries.distroseries
AND SourcePackagePublishingHistory.sourcepackagerelease = BinaryPackageBuild.source_package_release
AND SourcePackagePublishingHistory.id IN (%s, %s, %s, %s, %s, %s)
AND SourcePackagePublishingHistory.archive = PackageBuild.archive
AND BinaryPackageBuild.package_build = PackageBuild.id
AND PackageBuild.build_farm_job = BuildFarmJob.id
AND BuildFarmJob.status IN (%s)
ORDER BY BinaryPackageBuild.id)) AS "_294bf"
ORDER BY id
|
|
| 2011-02-01 09:40:26 |
Julian Edwards |
launchpad: assignee |
|
Julian Edwards (julian-edwards) |
|
| 2011-02-15 15:58:42 |
Julian Edwards |
launchpad: status |
Triaged |
Fix Released |
|
