18 SELECT COUNT(*) FROM (SELECT DISTINCT SourcePackageName.id, SourcePackageName.name, coalesce(tota ... $INT) AND archive.purpose = $INT AND section.name != $STRING AND packaging.id IS NULL)) AS "_tmp":
GET: 18 Robots: 9 Local: 9
8 https://launchpad.net/ubuntu/natty/+needs-packaging (DistroSeries:+needs-packaging)
OOPS-1907C1055, OOPS-1907C285, OOPS-1907D513, OOPS-1907E288, OOPS-1907F245
5 https://answers.launchpad.net/ubuntu/karmic/+needs-packaging (DistroSeries:+needs-packaging)
OOPS-1907D739, OOPS-1907E21, OOPS-1907G723, OOPS-1907J2432, OOPS-1907N622
2 https://launchpad.net/ubuntu/maverick/+needs-packaging (DistroSeries:+needs-packaging)
OOPS-1907A193, OOPS-1907L219
[3 other URLs]
Branch: launchpad-rev-12632.1
Revno: 12633
SQL time: 10722 ms
Non-sql time: 361 ms
Total time: 11083 ms
Statement Count: 28
two slow queries
25. 456 6058ms SQL-launchpad-main-slave
SELECT COUNT(*)
FROM
(SELECT DISTINCT SourcePackageName.id, SourcePackageName.name, coalesce(total_bug_heat, 0) + coalesce(po_messages, 0) + CASE
WHEN spr.component = 1 THEN 1000
ELSE 0
END AS score, coalesce(bug_count, 0) AS bug_count, coalesce(total_messages, 0) AS total_messages
FROM SourcePackageName
JOIN SourcePackageRelease spr ON SourcePackageName.id = spr.sourcepackagename
JOIN SourcePackagePublishingHistory spph ON spr.id = spph.sourcepackagerelease
JOIN archive ON spph.archive = Archive.id
JOIN section ON spph.section = section.id
JOIN DistroSeries ON spph.distroseries = DistroSeries.id
LEFT JOIN Packaging ON SourcePackageName.id = Packaging.sourcepackagename
AND Packaging.distroseries = DistroSeries.id
LEFT JOIN DistributionSourcePackage dsp ON dsp.sourcepackagename = spr.sourcepackagename
AND dsp.distribution = DistroSeries.distribution
LEFT JOIN
(SELECT POTemplate.sourcepackagename, POTemplate.distroseries, SUM(POTemplate.messagecount) * 0.5 AS po_messages, SUM(POTemplate.messagecount) AS total_messages
FROM POTemplate
WHERE POTemplate.sourcepackagename IS NOT NULL
AND POTemplate.distroseries = 106
GROUP BY POTemplate.sourcepackagename, POTemplate.distroseries) messages ON SourcePackageName.id = messages.sourcepackagename
AND DistroSeries.id = messages.distroseries
WHERE (DistroSeries.id = 106
AND spph.status IN (1, 2)
AND archive.purpose = 1
AND section.name != 'translations'
AND packaging.id IS NULL)) AS "_tmp"
26. 6516 4491ms SQL-launchpad-main-slave
SELECT DISTINCT SourcePackageName.id, SourcePackageName.name, coalesce(total_bug_heat, 0) + coalesce(po_messages, 0) + CASE
WHEN spr.component = 1 THEN 1000
ELSE 0
END AS score,
coalesce(bug_count, 0) AS bug_count,
coalesce(total_messages, 0) AS total_messages
FROM SourcePackageName
JOIN SourcePackageRelease spr ON SourcePackageName.id = spr.sourcepackagename
JOIN SourcePackagePublishingHistory spph ON spr.id = spph.sourcepackagerelease
JOIN archive ON spph.archive = Archive.id
JOIN section ON spph.section = section.id
JOIN DistroSeries ON spph.distroseries = DistroSeries.id
LEFT JOIN Packaging ON SourcePackageName.id = Packaging.sourcepackagename
AND Packaging.distroseries = DistroSeries.id
LEFT JOIN DistributionSourcePackage dsp ON dsp.sourcepackagename = spr.sourcepackagename
AND dsp.distribution = DistroSeries.distribution
LEFT JOIN
(SELECT POTemplate.sourcepackagename, POTemplate.distroseries, SUM(POTemplate.messagecount) * 0.5 AS po_messages, SUM(POTemplate.messagecount) AS total_messages
FROM POTemplate
WHERE POTemplate.sourcepackagename IS NOT NULL
AND POTemplate.distroseries = 106
GROUP BY POTemplate.sourcepackagename,
POTemplate.distroseries) messages ON SourcePackageName.id = messages.sourcepackagename
AND DistroSeries.id = messages.distroseries
WHERE (DistroSeries.id = 106
AND spph.status IN (1,
2)
AND archive.purpose = 1
AND section.name != 'translations'
AND packaging.id IS NULL)
ORDER BY score DESC, SourcePackageName.name LIMIT 21
OFFSET 20
I suspect this page has definitional problems:
- its reporting needs packaging for frozen distributions (not interesting as they are frozen)
- its reporting more detail than is useful - the number of translation strings and total bug heat isn't particularly helpful AFAICT: if we want to drive this to zero, the fact its non zero should be sufficient
That said, the performance of the page varies wildly: it just rendered: 28 queries/external actions issued in 0.92 seconds, but the refresh before that timed out. - partly explained by the use of memcache, but if the miss-case is slow, we will see repeated timeouts here.
Some possibilities:
- move it to distro rather than distro series (because a /distro/ only has one moving target for packaging into)
- strip out, or denormalise the expensive things to show.