Branch:+register-merge / Specification:+linkbranch time out due to substring matching many tables

Bug #793830 reported by Bryce Harrington on 2011-06-07
94
This bug affects 11 people
Affects Status Importance Assigned to Milestone
Launchpad itself
Critical
Steve Kowalik

Bug Description

got this attempting to propose a merge of one of my branches to pitti using the full url to the branch. Tried a couple more times but still got oopses.

Worked correctly when I specified the branch using its short name, "lp:~pitti/postgresql/debian-9.0".

OOPS-1984A11

See also bug 496041 which asks for less hits on substring matches (or better locality/weighting)

The culprit query includes this component:
(SELECT Branch.id
                       FROM Branch
                       WHERE Branch.name LIKE u'%https://code.launchpad.net/~pitti/postgresql/debian-9.0%'
                         OR Branch.url = u'https://code.launchpad.net/~pitti/postgresql/debian-9.0')
                    UNION
                      (SELECT Branch.id
                       FROM Branch,
                            Product
                       WHERE Branch.product = Product.id
                         AND Product.name LIKE u'%https://code.launchpad.net/~pitti/postgresql/debian-9.0%')
                    UNION
                      (SELECT Branch.id
                       FROM Branch,
                            Person AS OWNER
                       WHERE Branch.OWNER = OWNER.id
                         AND OWNER.name LIKE u'%https://code.launchpad.net/~pitti/postgresql/debian-9.0%')
                    UNION
                      (SELECT Branch.id
                       FROM Branch,
                            Distribution,
                            DistroSeries,
                            SourcePackageName
                       WHERE Branch.sourcepackagename = SourcePackageName.id
                         AND Branch.distroseries = DistroSeries.id
                         AND DistroSeries.distribution = Distribution.id
                         AND (SourcePackageName.name LIKE u'%https://code.launchpad.net/~pitti/postgresql/debian-9.0%'
                              OR DistroSeries.name LIKE u'%https://code.launchpad.net/~pitti/postgresql/debian-9.0%'
                              OR Distribution.name LIKE u'%https://code.launchpad.net/~pitti/postgresql/debian-9.0%')

which -hot- has 1 second of table scanning overhead (0.3 in the branch.name check, 0.6 in the person.name check) but may be substantially more given that there are 300K branches and 1.2M person rows.

Related branches

description: updated
tags: added: oops
tags: added: timeout
removed: oops
Changed in launchpad:
status: New → Triaged
importance: Undecided → Critical
summary: - OOPS-1984A11 proposing branch for merge
+ Branch:+register-merge time out
summary: - Branch:+register-merge time out
+ Branch:+register-merge time out due to substring matching many tables
description: updated
tags: added: critical-analysis
Francis J. Lacoste (flacoste) wrote :

This bug is actually a symptom of bug 372591, which is that unless there is an exact match on the unique name (short URL), we are searching all over the place like the above queries look like.

We should probably do something similar to what Curtis' squad did with the person and target picker (which is kind of what bug 372591 covers.

Martin Pool (mbp) wrote :

and more recently OOPS-3671d44169e2cdee4fde888380ed0497

what was strange there is that I started proposing the merge by clicking the 'propose merge' button on the branch page, and then accepted the default target branch, so I don't know why it would be doing a heuristic search. it ought to have a perfect match on both.

description: updated
summary: - Branch:+register-merge time out due to substring matching many tables
+ Branch:+register-merge / Specification:+linkbranch time out due to
+ substring matching many tables
William Grant (wgrant) on 2013-02-11
Changed in launchpad:
assignee: nobody → Steve Kowalik (stevenk)
status: Triaged → In Progress
Launchpad QA Bot (lpqabot) wrote :
tags: added: qa-needstesting
Changed in launchpad:
status: In Progress → Fix Committed
Steve Kowalik (stevenk) on 2013-02-14
tags: added: qa-ok
removed: qa-needstesting
Steve Kowalik (stevenk) on 2013-02-22
Changed in launchpad:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers