timeout on code.launchpad.net/~ubuntu-branches
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Launchpad itself |
Fix Released
|
Critical
|
William Grant |
Bug Description
OOPS-5f256222ff8c758238414f93587ad1eb
This is exceeding the 5s default cap on page timeouts. It is spending >5s in SQL, with these primary culprits:
1978.0 1 SQL-main-slave
SELECT BranchMergeProp
FROM Branch
JOIN BranchMergeProposal ON Branch.id = BranchMergeProp
AND Branch.OWNER = $INT
JOIN Branch AS target ON target.id = BranchMergeProp
WHERE (Branch.
OR COALESCE(
(SELECT ARRAY_AGG(
FROM TeamParticipation
WHERE TeamParticipati
OR Branch.
(SELECT AccessPolicyGra
FROM AccessPolicyGrant
JOIN TeamParticipation ON TeamParticipati
WHERE TeamParticipati
AND (target.
OR COALESCE(
(SELECT ARRAY_AGG(
FROM TeamParticipation
WHERE TeamParticipati
OR target.
(SELECT AccessPolicyGra
FROM AccessPolicyGrant
JOIN TeamParticipation ON TeamParticipati
WHERE TeamParticipati
AND BranchMergeProp
ORDER BY BranchMergeProp
^- This appears to be trying to see if there are any merge proposals for the branches being shown.
There are only 100 branch_ids in the "BranchMergePro
1217.0 2 SQL-main-slave
SELECT Branch.
Branch.id,
Branch.name,
FROM Branch
WHERE Branch.OWNER = $INT
AND Branch.
AND (Branch.
OR COALESCE(
(SELECT ARRAY_AGG(
FROM TeamParticipation
WHERE TeamParticipati
OR Branch.
(SELECT AccessPolicyGra
FROM AccessPolicyGrant
JOIN TeamParticipation ON TeamParticipati
WHERE TeamParticipati
ORDER BY Branch.
OFFSET $INT
^- One key issue of this request, is that it is being requested 2 times. (The average is 1217s, but both times are close to that.)
Looking at the actual queries, the content is, indeed, identical, so we should be caching the results of the first query, and not issuing a second one.
Note that it does appear to be a data-dependent issue, as https:/
I wonder if there is an index that could be put for the ORDER BY so that it can return the LIMIT + OFFSET without having to read all 100k+ branches for ~ubuntu-branches.
It could be that this is a regression because of the changes for privacy.
Related branches
- Curtis Hovey (community): Approve (code)
-
Diff: 32 lines (+6/-3)1 file modifiedlib/lp/code/model/branch.py (+6/-3)
description: | updated |
description: | updated |
tags: | removed: oops |
Changed in launchpad: | |
assignee: | nobody → William Grant (wgrant) |
status: | Triaged → In Progress |
tags: |
added: qa-ok removed: qa-needstesting |
On staging, for the first query, it returns 0 rows, but still takes 1200ms to execute. It would seem we should be filtering in a different order. EXPLAIN ANALYZE gives: 34..95124. 52 rows=71 width=785) (actual time=1555. 036..1555. 036 rows=0 loops=1) osal.date_ created, branchmergeprop osal.id on_person_ idx on teamparticipation (cost=0.00..59.34 rows=51 width=4) (never executed)
Index Cond: (person = 10583) on_person_ idx on teamparticipation (cost=0.00..59.34 rows=51 width=4) (actual time=0.029..0.283 rows=120 loops=1)
Index Cond: (person = 10583) 54..95003. 19 rows=71 width=785) (actual time=1555. 024..1555. 024 rows=0 loops=1) posal.target_ branch = target.id) 77..46503. 17 rows=72 width=785) (actual time=0.296..0.296 rows=0 loops=1) posal.source_ branch = branch.id) 91..377. 49 rows=110 width=785) (actual time=0.293..0.293 rows=0 loops=1)
Recheck Cond: (source_branch = ANY ('{642702, 594960, 642701, 642699, 642698, 642697, 626259, 627638, 612893, 608781, 642661, 605468, 591171, 642609, 642611, 642610, 642607, 642606, 642605, 642601, 642600, 642599, 567823, 642598, 642597, 642596, 642595, 642594, 642593, 642591, 642590, 642589, 642588, 642587, 642586, 642585, 642584, 615804, 642583, 618044, 618397, 642580, 642579, 614466, 294897, 631531, 597979, 145586, 626091, 641863, 631530, 184187, 379944, 350369, 205826, 642550, 642547, 612682, 110462, 305784, 612846, 412005, 598022, 642525, 614325, 642287, 597972, 611905, 120004, 642506, 542770, 456310, 363093, 613279, 637525, 614713, 455168, 637532, 602879, 607321, 597781, 610894, 631529, 615237, 605684, 615280, 598021, 105569, 130730, 605092, 614295, 615277, 615274, 621997, 291172, 642394, 642392, 642391, 599195, 352385} '::integer[ ]))
-> Bitmap Index Scan on branchmergeprop osal__source_ branch_ _idx (cost=0.00..178.88 rows=110 width=0) (actual time=0.289..0.289 rows=0 loops=1)
Index Cond: (source_branch = ANY ('{642702, 594960, 642701, 642699, 642698, 642697, 626259, 627638, 612893, 608781, 642661, 605468, 591171, 642609, 642611, 642610, 642607, 642606, 642605, 642601, 642600, 642599, 567823, 642598, 642597, 642596, 642595, 642594, 642593, 642591, 642590, 642589, 642588, 642587, 642586, 642585, 642584, 615804, 642583, 618044, 618397, 642580, 642579, 614466, 294897, 631531, 597979, 145586, 626091, 641863, 631530, 184187, 379944, 350369, 205826, 642550, 642547, 612682, 110462, 305784, 612846, 412005, 598022, 642525, 614325, 642287, 597972, 611905, 120004, 642506, 542770, 456310, 363093, 613279, 637525, 614713, 455168, 637532, 602879, 607321, 597781, 610894, 631529, 615237, 605684, 615280, 598021, 105569, 130730, 605092, 614295, 615277, 615274, 621997, 291172, 642394, 642392, 642391, 599195, 352385} '...
Sort (cost=95124.
Sort Key: branchmergeprop
Sort Method: quicksort Memory: 25kB
InitPlan 1 (returns $0)
-> Aggregate (cost=59.47..59.48 rows=1 width=4) (never executed)
-> Index Scan using teamparticipati
InitPlan 3 (returns $2)
-> Aggregate (cost=59.47..59.48 rows=1 width=4) (actual time=0.360..0.361 rows=1 loops=1)
-> Index Scan using teamparticipati
-> Hash Join (cost=91145.
Hash Cond: (branchmergepro
-> Hash Join (cost=44836.
Hash Cond: (branchmergepro
-> Bitmap Heap Scan on branchmergeproposal (cost=178.