Launchpad itself

Archive:+packages timeouts

Bug #672371 reported by Robert Collins
28
This bug affects 4 people
Affects Status Importance Assigned to Milestone
Launchpad itself
Fix Released
High
Jelmer Vernooij
Launchpad itself 10.11 "Tenacious Turkey"

Bug Description

OOPS-1774F2318

https://launchpad.net/++oops++/~yavdr/+archive/stable-vdr/+packages?start=0&batch=204
https://launchpad.net/~pythoneers/+archive/py27stack4/+packages

hpad-rev-11888
Revno: 11888
SQL time: 12980 ms
Non-sql time: 2423 ms
Total time: 15403 ms
Statement Count: 226

Query count coming down, but still 10 times what it should be.

1 72 304 4 300 SQL-launchpad-main-slave SELECT SourcePackageName.id, SourcePackageName.name FROM SourcePackageName WHERE SourcePackageName.id = %s LIMIT 1
2 29 156 5 151 SQL-launchpad-main-slave SELECT 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 BuildFarmJob WHERE BuildFarmJob.id = %s LIMIT 1
3 29 59 2 57 SQL-launchpad-main-slave SELECT PackageBuild.archive, PackageBuild.build_farm_job, PackageBuild.dependencies, PackageBuild.id, PackageBuild.pocket, PackageBuild.upload_log FROM PackageBuild WHERE PackageBuild.id = %s LIMIT 1
4 19 131 6 125 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 FROM SourcePackagePublishingHistory WHERE SourcePackagePublishingHistory.id IN (%s) AND SourcePackagePublishingHistory.archive = %s
5 11 2910 264 2646 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)
6 6 1876 312 1564 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) 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)
7 5 43 8 35 SQL-launchpad-main-slave SELECT Person.account, Person.creation_comment, Person.creation_rationale, Person.datecreated, Person.defaultmembershipperiod, Person.defaultrenewalperiod, Person.displayname, Person.hide_email_addresses, Person.homepage_content, Person.icon, Person.id, Person.logo, Person.mailing_list_auto_subscribe_policy, Person.merged, Person.mugshot, Person.name, Person.personal_standing, Person.personal_standing_reason, Person.registrant, Person.renewal_policy, Person.subscriptionpolicy, Person.teamdescription, Person.teamowner, Person.verbose_bugnotifications, Person.visibility FROM Person WHERE Person.id = %s LIMIT 1

See original description

Related branches

lp:~lifeless/launchpad/soyuz
Robert Collins (community): Approve
Diff: 12 lines (+1/-1)
1 file modified
lib/lp/registry/model/distroseries.py (+1/-1)
Revision history for this message
Robert Collins (lifeless) wrote :

We've removed the override timeout for this as it appears much better (though still massively improvable).

Changed in soyuz:
status: New → Triaged
importance: Undecided → High
Julian Edwards (julian-edwards)
tags: added: ppa
Revision history for this message
Barry Warsaw (barry) wrote :

I see it on https://launchpad.net/~pythoneers/+archive/py27stack4/+packages

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

I have it flat in tests; trying to expedite delivery of the fix now.

Changed in soyuz:
assignee: nobody → Robert Collins (lifeless)
milestone: none → 10.11
tags: added: regression
Revision history for this message
Robert Collins (lifeless) wrote :

We've added a 20 second timeout override for this in the interim, but it won't help large ppas.

Revision history for this message
Jelmer Vernooij (jelmer) wrote :

I'm QA'ing this.

Changed in soyuz:
assignee: Robert Collins (lifeless) → Jelmer Vernooij (jelmer)
Revision history for this message
Launchpad QA Bot (lpqabot) wrote : Bug fixed by a commit

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

tags: added: qa-needstesting
Changed in soyuz:
status: Triaged → Fix Committed
Revision history for this message
Philip Muškovac (yofel) wrote :

Fixed for me (https://launchpad.net/~neon/+archive/ppa/+packages) - thanks!

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

The flat query count version shows the problem clearly:

SELECT * FROM ((SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release FROM BinaryPackageBuild, BinaryPackagePublishingHistory, BinaryPackageRelease, 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, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) AND SourcePackagePublishingHistory.archive != PackageBuild.archive AND BinaryPackagePublishingHistory.archive = SourcePackagePublishingHistory.archive AND BinaryPackagePublishingHistory.binarypackagerelease = BinaryPackageRelease.id AND BinaryPackageRelease.build = BinaryPackageBuild.id ORDER BY BinaryPackageBuild.id) UNION (SELECT BinaryPackageBuild.distro_arch_series, BinaryPackageBuild.id, BinaryPackageBuild.package_build, BinaryPackageBuild.source_package_release 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, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) AND SourcePackagePublishingHistory.archive = PackageBuild.archive ORDER BY BinaryPackageBuild.id)) AS "_e" ORDER BY id

is taking 10 seconds (for 50 spph records)

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

62706, 30538, 1130, 62712, 31352, 785, 1168, 62786, 207, 34272, 59608, 24976, 67887, 215, 41229, 24214, 68739, 229, 41832, 98, 24111, 68744, 21367, 266, 53596, 23434, 70213, 313, 52354, 23463, 70938, 776, 23310, 66, 807, 52624, 22399, 72578, 56735, 52355, 26, 59648, 72174, 1113, 55, 21913, 1121, 30246, 76, 1126

seems to be the ids for this

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

arhm. thats sourcepackagename ids.

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

With help from stub & spm we identified the issue - an extra unnecessary sourcepackagename in the from clause of a query; fix is in PQM now - and we should be able to deploy this in the near future.

Revision history for this message
Julian Edwards (julian-edwards) wrote : Re: [Bug 672371] Re: Archive:+packages timeouts

On Friday 12 November 2010 07:02:01 you wrote:
> With help from stub & spm we identified the issue - an extra unnecessary
> sourcepackagename in the from clause of a query; fix is in PQM now - and
> we should be able to deploy this in the near future.

That was the cause of a separate query bust somewhere else. 8.3 was far more
lenient to that type of fault; 8.4 is not.

Revision history for this message
Launchpad QA Bot (lpqabot) wrote : Bug fixed by a commit

Fixed in stable r11914 (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/11914) by a commit, but not testable.

tags: added: qa-untestable
removed: qa-needstesting
Revision history for this message
Curtis Hovey (sinzui) wrote :

The subquery clauses is working with 1000s of sprs for each spn, not 1. I have a revised query that limits the matched sprs to have exactly 1 for each spn.

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

curtis - I think opening a new bug for the getNewerSourceReleases thing specifically may make sense?

tags: added: qa-ok
removed: qa-untestable
Revision history for this message
Robert Collins (lifeless) wrote :

Both these pages are working now - though a bit slowly. Will file a new bug for more tuning.

Changed in soyuz:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Duplicates of this bug

Subscribing...

Other bug subscribers

Remote bug watches

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