BugTask:+editstatus-page timing out in ~4% of requests

I think using this will be better for the heat :
select bug.heat from bug, bugtask where bugtask.bug = bug.id and bugtask.distribution = 1 order by bug.heat desc limit 1;
 heat
 -------
 11062
 (1 row)
 Time: 58.710 ms

Heres a set of times for this by permutation:

MAX/LIMIT 1/3 D/DS/DSNN TIME
MAX 1 D 2000ms
MAX 3 D 150ms
MAX 1 DS 196ms
MAX 3 DS 2ms
LIMIT 3 DSNN 5000ms
LIMIT 3 DSNN 2ms
LIMIT 1 DSNN 200ms
LIMIT 1 DS 2ms
LIMIT 1 D 2ms
LIMIT 3 D 2ms

We have a LIMIT based query where both distro 1 and 3 perform well, for both the distro and distroseries cases.

LIMIT means order by bug.heat desc limit 1; max means max(bug.heat)
D means a query as per bugtarget.py on bugtask.distribution=X
DS means a query on bug, bugtask, distroseries where distoseries=X
DSNN means DS but with a strictly redundant 'AND bugtask.distroseries is not null'

As we know the number of distroseries is generally small I'm going to try an IN clause next.

that variation performed equivalently to the DS/DSNN and is harder to read so not worth it. I'd say switching back to LIMIT and putting in the distroseries not null clauses is probably best for now.

08:34 < deryck> SELECT Bug.heat FROM Bug, Bugtask, DistroSeries
08:34 < deryck> WHERE Bugtask.bug = Bug.id AND
08:34 < deryck> Bugtask.distroseries IS NOT NULL AND
08:34 < deryck> Bugtask.distroseries = DistroSeries.id AND
08:34 < deryck> DistroSeries.distribution = 3 ORDER BY Bug.heat
08:34 < deryck> DESC LIMIT 1;

I had a poke at this a bit more today and I agree that adding the redundant not null clause is the best for now. It generally performs the best and most consistently across distro, distroseries, projects, project groups, etc. However, there are some combinations where I can't find a query to get below 300-400 ms. For now, that's okay, but the more quickly we drive down our timeout threshold, the more quickly this becomes a problem again.

We need to have a way to push this out of the web app. The jobs system broke down for us when used for heat before. Some of that could have been our use of it (firing off too many jobs or a job for every bug touched). But whatever we do, we need a way to defer this calculation only momentarily because of how much heat is used in the web UI now. i.e. we need to push this out of in-app calculation, but it still needs to happen pretty quickly so web pages are up to date.

I'm working on a branch now to make this minor query change, still leaving it in app, to buy us some time.

Cheers,
deryck

Alternatively we could denormalise this; put the max heat for a bug
target on the bug target, and update it when when bugs change; we
would still need to query it when the hottest bug for a target is
retargeted; but that should be pretty rare.

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

The initial cause of timeouts related to bug heat is fixed, according to my attempts to +editstatus on staging on Ubuntu bugtasks.

\o/