Launchpad itself

buildfarmjob schema is inefficient for reporting

Bug #758258 reported by Robert Collins
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Launchpad itself
Fix Released
Critical
William Grant

Bug Description

We do queries like 'what packages failed to build ordered by date'. Bugs like bug 730396 bug 739066 bug 739065 may/are symptoms of this.

Broadly when the following is true:
 - the dataset in a table is larger than we want to evaluate & sort every page load (implies we want an index for sorted results)
then
 - we need to be able to filter the results for any query to a sensible subset on that table

This is because when there is an index matching the sort order, postgresql will strongly prefer to iterate that rather than loading all the results and doing an in-memory sort.

The BuildFarmJob collection:
BuildFarmJob(BFJ)->PackageBuild(PB)->BinaryPackageBuild(BPB)
BuildFarmJob(BFJ)->PackageBuild(PB)->SourcePackageRecipeBuild(SPRB)
BuildFarmJob(BFJ)->TranslationTemplatesBuild(TTB)

fails the 'able to filter' criteria: these three different combinations always appear together:
BPB always has a PB always has a BFJ
DPRB always has a PB always has a BFJ
TTB always has a BFJ

But the filtering (e.g. on branch for TTB) is separated from the ordering (e.g. date in BFJ).

As these three combinations act as a single object consistently, the simplest thing is to fold them all together: roll BFJ+PB into PBP, roll BFJ+PB into SPRB and roll BFJ into TTB.

BFJ is 200MB (2M rows)
PB is 100MB (2M rows)
BPB is 91MB (2M rows)
SPRB is < 1MB (20K rows)
TTB is < 1MB (4300 rows)

SPRB and TTB could be migrated during downtime, but the BPB migration will have to be done online via a garbo migration script.

Related branches

lp:~wgrant/launchpad/flatten-sprb
lp:~wgrant/launchpad/flatten-bfj-0-db
Stuart Bishop (community): Approve (db)
Diff: 94 lines (+79/-0)
2 files modified
database/schema/patch-2209-41-0.sql (+78/-0)
database/schema/security.cfg (+1/-0)
lp:~wgrant/launchpad/flatten-bfj-1-populate
Steve Kowalik (community): Approve (code)
Diff: 682 lines (+248/-72)
10 files modified
lib/lp/buildmaster/interfaces/packagebuild.py (+2/-10)
lib/lp/buildmaster/model/buildfarmjob.py (+16/-9)
lib/lp/buildmaster/model/packagebuild.py (+6/-15)
lib/lp/buildmaster/tests/test_packagebuild.py (+4/-3)
lib/lp/code/model/sourcepackagerecipebuild.py (+67/-10)
lib/lp/soyuz/model/binarypackagebuild.py (+84/-14)
lib/lp/soyuz/scripts/gina/handlers.py (+3/-3)
lib/lp/soyuz/tests/test_binarypackagebuild.py (+20/-0)
lib/lp/soyuz/tests/test_hasbuildrecords.py (+7/-3)
lib/lp/translations/model/translationtemplatesbuild.py (+39/-5)
lp:~wgrant/launchpad/flatten-bfj-2-garbo
Steve Kowalik (community): Approve (code)
Diff: 543 lines (+380/-1)
5 files modified
database/schema/security.cfg (+4/-0)
lib/lp/scripts/garbo.py (+227/-0)
lib/lp/scripts/tests/test_garbo.py (+136/-1)
lib/lp/soyuz/interfaces/binarypackagebuild.py (+3/-0)
lib/lp/soyuz/model/binarypackagebuild.py (+10/-0)
lp:~wgrant/launchpad/flatten-bfj-3-query
Steve Kowalik (community): Approve (code)
Diff: 1290 lines (+261/-339)
20 files modified
database/sampledata/current-dev.sql (+48/-48)
database/sampledata/current.sql (+48/-48)
lib/lp/buildmaster/interfaces/buildfarmjob.py (+2/-0)
lib/lp/buildmaster/interfaces/packagebuild.py (+0/-4)
lib/lp/buildmaster/model/buildfarmjob.py (+11/-17)
lib/lp/buildmaster/model/packagebuild.py (+4/-8)
lib/lp/code/model/sourcepackagerecipe.py (+44/-47)
lib/lp/code/model/sourcepackagerecipebuild.py (+5/-7)
lib/lp/registry/model/sourcepackage.py (+7/-7)
lib/lp/soyuz/doc/sourcepackagerelease.txt (+1/-3)
lib/lp/soyuz/interfaces/binarypackagebuild.py (+0/-4)
lib/lp/soyuz/model/archive.py (+27/-47)
lib/lp/soyuz/model/binarypackagebuild.py (+39/-46)
lib/lp/soyuz/model/buildpackagejob.py (+7/-13)
lib/lp/soyuz/model/distributionsourcepackagerelease.py (+1/-5)
lib/lp/soyuz/model/publishing.py (+5/-10)
lib/lp/soyuz/model/sourcepackagerelease.py (+6/-14)
lib/lp/soyuz/tests/test_archive.py (+3/-6)
lib/lp/translations/interfaces/translationtemplatesbuild.py (+1/-5)
lib/lp/translations/model/translationtemplatesbuild.py (+2/-0)
lp:~wgrant/launchpad/flatten-bfj-3.5-more-query
Steve Kowalik (community): Approve (code)
Diff: 817 lines (+126/-256)
18 files modified
lib/lp/buildmaster/configure.zcml (+0/-6)
lib/lp/buildmaster/interfaces/buildfarmjob.py (+9/-0)
lib/lp/buildmaster/interfaces/packagebuild.py (+0/-17)
lib/lp/buildmaster/model/buildfarmjob.py (+35/-18)
lib/lp/buildmaster/model/packagebuild.py (+0/-43)
lib/lp/buildmaster/tests/test_buildfarmjob.py (+27/-2)
lib/lp/buildmaster/tests/test_packagebuild.py (+0/-42)
lib/lp/code/model/sourcepackagerecipebuild.py (+9/-21)
lib/lp/soyuz/browser/tests/test_queue.py (+1/-1)
lib/lp/soyuz/interfaces/publishing.py (+2/-5)
lib/lp/soyuz/model/archive.py (+4/-4)
lib/lp/soyuz/model/binarypackagebuild.py (+17/-44)
lib/lp/soyuz/model/publishing.py (+4/-11)
lib/lp/soyuz/model/queue.py (+0/-1)
lib/lp/soyuz/scripts/retrydepwait.py (+9/-15)
lib/lp/soyuz/tests/test_hasbuildrecords.py (+3/-14)
lib/lp/soyuz/tests/test_packageupload.py (+1/-1)
lib/lp/translations/model/translationtemplatesbuild.py (+5/-11)
lp:~wgrant/launchpad/flatten-bfj-4-app-eliminate
Steve Kowalik (community): Approve (code)
Diff: 365 lines (+32/-91)
9 files modified
lib/lp/buildmaster/model/buildfarmjob.py (+9/-31)
lib/lp/buildmaster/model/packagebuild.py (+3/-4)
lib/lp/buildmaster/tests/test_buildfarmjob.py (+0/-7)
lib/lp/buildmaster/tests/test_manager.py (+0/-1)
lib/lp/buildmaster/tests/test_packagebuild.py (+1/-2)
lib/lp/code/model/sourcepackagerecipebuild.py (+8/-19)
lib/lp/code/model/tests/test_sourcepackagerecipebuild.py (+2/-8)
lib/lp/soyuz/model/binarypackagebuild.py (+8/-18)
lib/lp/translations/model/translationtemplatesbuild.py (+1/-1)
lp:~wgrant/launchpad/flatten-bfj-2.5-destroy-garbo
William Grant: Approve (code)
Diff: 543 lines (+1/-380)
5 files modified
database/schema/security.cfg (+0/-4)
lib/lp/scripts/garbo.py (+0/-227)
lib/lp/scripts/tests/test_garbo.py (+1/-136)
lib/lp/soyuz/interfaces/binarypackagebuild.py (+0/-3)
lib/lp/soyuz/model/binarypackagebuild.py (+0/-10)
lp:~wgrant/launchpad/flatten-bfj-0.5-lfa-indices
William Grant: Approve (code)
Diff: 27 lines (+23/-0)
1 file modified
database/schema/patch-2209-41-1.sql (+23/-0)
lp:~wgrant/launchpad/flatten-bfj-0.75-ttb-defaults
Stuart Bishop (community): Approve (db)
Diff: 16 lines (+12/-0)
1 file modified
database/schema/patch-2209-41-2.sql (+12/-0)
lp:~wgrant/launchpad/flatten-bfj-5-app-cleanup
Steve Kowalik (community): Approve (code)
Diff: 1229 lines (+186/-489)
17 files modified
lib/lp/buildmaster/configure.zcml (+0/-13)
lib/lp/buildmaster/interfaces/packagebuild.py (+1/-30)
lib/lp/buildmaster/model/buildfarmjob.py (+7/-48)
lib/lp/buildmaster/model/packagebuild.py (+5/-101)
lib/lp/buildmaster/tests/test_manager.py (+1/-1)
lib/lp/buildmaster/tests/test_packagebuild.py (+2/-73)
lib/lp/code/model/sourcepackagerecipe.py (+15/-15)
lib/lp/code/model/sourcepackagerecipebuild.py (+46/-50)
lib/lp/soyuz/doc/sourcepackagerelease.txt (+1/-1)
lib/lp/soyuz/model/archive.py (+9/-13)
lib/lp/soyuz/model/binarypackagebuild.py (+72/-88)
lib/lp/soyuz/model/distributionsourcepackagerelease.py (+1/-2)
lib/lp/soyuz/model/publishing.py (+4/-7)
lib/lp/soyuz/model/queue.py (+0/-1)
lib/lp/soyuz/scripts/retrydepwait.py (+1/-1)
lib/lp/soyuz/tests/test_binarypackagebuild.py (+0/-20)
lib/lp/translations/model/translationtemplatesbuild.py (+21/-25)
lp:~wgrant/launchpad/flatten-bfj-3.75-indices
William Grant: Approve (db)
Diff: 62 lines (+58/-0)
1 file modified
database/schema/patch-2209-41-3.sql (+58/-0)
lp:~wgrant/launchpad/flatten-bfj-6-denorm-more-queries
Steve Kowalik (community): Approve (code)
Diff: 0 lines
lp:~wgrant/launchpad/flatten-bfj-7-db-cleanup
Stuart Bishop (community): Approve (db)
Diff: 526 lines (+156/-192)
5 files modified
database/sampledata/current-dev.sql (+48/-82)
database/sampledata/current.sql (+48/-82)
database/schema/comments.sql (+0/-16)
database/schema/patch-2209-41-5.sql (+60/-0)
database/schema/security.cfg (+0/-12)
lp:~wgrant/launchpad/flatten-bfj-6.5-db-more-indices
William Grant: Approve (db)
Diff: 58 lines (+54/-0)
1 file modified
database/schema/patch-2209-41-4.sql (+54/-0)
Curtis Hovey (sinzui)
Changed in launchpad:
assignee: nobody → Launchpad Green Squad (launchpad-green-squad)
William Grant (wgrant)
Changed in launchpad:
assignee: Launchpad Green Squad (launchpad-green-squad) → William Grant (wgrant)
status: Triaged → In Progress
Curtis Hovey (sinzui)
Changed in launchpad:
assignee: William Grant (wgrant) → nobody
Revision history for this message
William Grant (wgrant) wrote :

I've landed the migration that can be deployed before the next DB downtime. Before that downtime we should add new columns (part of the patches in the attached branches), with triggers and garbo jobs to populate them. Then the remaining bits of these two branches should land, migrating to the new flat tables.

William Grant (wgrant)
Changed in launchpad:
status: In Progress → Triaged
Francis J. Lacoste (flacoste)
tags: added: analysis critical
tags: added: critical-analysus
removed: analysis critical
tags: added: critical-analysis
removed: critical-analysus
William Grant (wgrant)
Changed in launchpad:
assignee: nobody → William Grant (wgrant)
status: Triaged → In Progress
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

r12306 in db-stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/db-stable/revision/12306) is part of this bug's fix.

tags: added: qa-needstesting
Revision history for this message
William Grant (wgrant) wrote :

2013-02-03 06:52:15,268 INFO 2209-41-0 applied just now in 0.0 seconds

tags: added: qa-ok
removed: qa-needstesting
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

r16466 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16466) is part of this bug's fix.

tags: added: qa-needstesting
removed: qa-ok
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

r16467 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16467) is part of this bug's fix.

William Grant (wgrant)
tags: added: qa-ok
removed: qa-needstesting
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

r16469 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16469) is part of this bug's fix.

tags: added: qa-needstesting
removed: qa-ok
William Grant (wgrant)
tags: added: qa-ok
removed: qa-needstesting
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

r16471 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16471) is part of this bug's fix.

tags: added: qa-needstesting
removed: qa-ok
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

r16472 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16472) is part of this bug's fix.

Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

r16473 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16473) is part of this bug's fix.

William Grant (wgrant)
tags: added: qa-ok
removed: qa-needstesting
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

r16477 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16477) is part of this bug's fix.

tags: added: qa-needstesting
removed: qa-ok
William Grant (wgrant)
tags: added: qa-ok
removed: qa-needstesting
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

r16479 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16479) is part of this bug's fix.

tags: added: qa-needstesting
removed: qa-ok
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

r16480 in stable (http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/16480) is part of this bug's fix.

Changed in launchpad:
status: In Progress → Fix Committed
Revision history for this message
Launchpad QA Bot (lpqabot) wrote :

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

William Grant (wgrant)
tags: added: qa-ok
removed: qa-needstesting
Steve Kowalik (stevenk)
Changed in launchpad:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.