Upcoming work pages appear to time out a lot
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
| Launchpad itself |
Critical
|
William Grant |
Bug Description
My personal upcomingwork page seems to time out a whole lot (but I also have lots of stuff on it).
https:/
For instance: (Error ID: OOPS-3462b9b41acbf8d5afb00ba372201498)
Related branches
- Colin Watson: Approve on 2017-06-06
-
Diff: 62 lines (+33/-7)1 file modifiedlib/lp/registry/model/person.py (+33/-7)
Colin Watson (cjwatson) wrote : | #1 |
Brian Murray (brian-murray) wrote : | #2 |
This also happens to me.
Changed in launchpad: | |
status: | New → Triaged |
importance: | Undecided → Critical |
tags: | added: lp-blueprints oops timeout |
William Grant (wgrant) wrote : | #3 |
The main problem is that the planner can't accurately estimate the number of workitems due to one of the assignee filters being on the wrong side of a join, causing it to end up retrieving all workitems and all specifications and filtering those down. This runs into a growing performance issue with ACL checks for Canonical employees that are in lots of teams.
https:/
tags: | removed: oops |
Changed in launchpad: | |
assignee: | nobody → William Grant (wgrant) |
status: | Triaged → In Progress |
William Grant (wgrant) wrote : | #4 |
EXPLAIN (ANALYZE, BUFFERS) results: https:/
As anticipated, the planner works much better when the CTE optimisation barrier is added, forcing it to use indexes to calculate the set of workitems.
Launchpad QA Bot (lpqabot) wrote : | #5 |
Fixed in stable r18400 <http://
tags: | added: qa-needstesting |
Changed in launchpad: | |
status: | In Progress → Fix Committed |
tags: |
added: qa-ok removed: qa-needstesting |
Changed in launchpad: | |
status: | Fix Committed → Fix Released |
The offending query is:
SELECT DISTINCT SpecificationWo rkItem. assignee, SpecificationWo rkItem. date_created, SpecificationWo rkItem. deleted, SpecificationWo rkItem. id, SpecificationWo rkItem. milestone, SpecificationWo rkItem. sequence, SpecificationWo rkItem. specification, SpecificationWo rkItem. status, SpecificationWo rkItem. title product = Product.id rkItem ON SpecificationWo rkItem. specification = Specification.id SpecificationWo rkItem. milestone, Specification. milestone) = Milestone.id product IS NULL information_ type IN (1,
2) (Specification. access_ grants) &&
(SELECT ARRAY_AGG( TeamParticipati on.team)
FROM TeamParticipation
WHERE TeamParticipati on.person = 1557381), FALSE) ARRAY[( Specification. access_ policy) ]&&
(SELECT ARRAY_AGG( AccessPolicyGra nt.policy)
FROM AccessPolicyGrant
JOIN TeamParticipation ON TeamParticipati on.team = AccessPolicyGra nt.grantee
WHERE TeamParticipati on.person = 1557381), FALSE)) dateexpected <= E'2017-11-15' dateexpected >= E'2017-05-19' rkItem. deleted = FALSE orkItem. assignee IN (1557381) assignee IN (1557381)) rkItem. id;
FROM Specification
LEFT JOIN Product ON Specification.
JOIN SpecificationWo
JOIN Milestone ON COALESCE(
WHERE (Specification.
OR Product.active = TRUE)
AND (Specification.
OR COALESCE(
OR COALESCE(
AND Milestone.
AND Milestone.
AND SpecificationWo
AND (SpecificationW
OR Specification.
ORDER BY SpecificationWo
I can't get good EXPLAIN ANALYZE output from dogfood on this since the database dump is a bit too old (and when I tweak the dates dogfood is an order of magnitude faster, so I don't want to put much faith in the results), so I'll have to wait until a weekday when we can get a sysadmin to try it on production.