Speed up hydra-update-gc-roots

[?]

Mar 7, 2012, 2:02 PM

KJQWSRCCQEKF64L4NYYZ7VKAF2YEPYXXTRW6BI464P6Q6KU34TQAC

Dependencies

[2] C5W3WZ3Y * Index the Builds table on drvPath to prevent a full table scan
[3] HPEG2RHV Merge the BuildResultInfo table into the Builds table
[4] 4X6NS66Q * Keep the most recent builds for each job.
[5] ONAQU4BR hydra: handle case where build to keep set to 0
[6] A63IHCMX * Register GC roots properly.
[7] IN272KZW * Automatically keep all builds in the latest successful release in
[8] WZ3AEJ67 * hydra_update_gc_roots.pl registers build outputs that should be kept
[9] AFGT66EI hydra: if a project or jobset is hidden (and thus disabled) do not keep builds
[10] NEWDDAOF * Allow users to change the value of a build's "keep" flag, which
[11] XBU2ODSP * More renaming.
[12] HLYVEJX7 * Keep the 3 most recent builds for every platform for every job.
[13] 6KJXJB7N qualify ordery_by clauses when necessary, remove unnecessary order_by's, reported by Ludo, resulted in errors in sqlite
[14] AEFNBIIW * Merge the GC branch.
[15] E3LZ5H7Q hydra: only use keepnr to determine if builds should be kept
[16] MOX7XJ2E Merge the BuildSchedulingInfo table into the Builds table
[17] 3E6IP3R3 * Add the name of the jobset to ReleaseSetJobs, otherwise we can't
[18] 2T42QGZD * Register builds as GC roots so they don't get deleted.
[19] KNAVU6BH * Do not garbage collect releases.
[*] N22GPKYT * Put info about logs / build products in the DB.
[*] O25D52TA initial support for mercurial
[*] D5QIOJGP * Move everything up one directory.

Change contents

replacement in src/script/hydra-update-gc-roots at line 16

[4.755]→[4.755:774](∅→∅)

sub registerRoot {

[4.755]

[4.774]

sub addRoot {
replacement in src/script/hydra-update-gc-roots at line 18

[4.795]→[4.643:688](∅→∅)

Hydra::Helper::Nix::registerRoot($path);

[4.795]

[4.1047]

registerRoot($path);
edit in src/script/hydra-update-gc-roots at line 21

[4.1072]

[4.1072]
edit in src/script/hydra-update-gc-roots at line 23

[4.1073]

[4.1073]

my @columns = ( "id", "project", "jobset", "job", "system", "finished", "outpath", "drvpath", "timestamp" );
replacement in src/script/hydra-update-gc-roots at line 27

[4.177]→[4.0:55](∅→∅),[4.55]→[4.0:30](∅→∅)

print STDERR " keeping build ", $build->id, " (",
$build->system, "; ",

[4.177]

[4.30]

print STDERR " keeping ", ($build->finished ? "" : "scheduled "), "build ", $build->id, " (",
$build->get_column('project'), ":", $build->get_column('jobset'), ":", $build->get_column('job'), "; ",
$build->system, "; ",
replacement in src/script/hydra-update-gc-roots at line 32

[4.343]→[4.343:381](∅→∅)

registerRoot $build->outpath;

[4.343]

[4.381]

addRoot $build->outpath;
replacement in src/script/hydra-update-gc-roots at line 34

[4.394]→[4.394:474](∅→∅)

print STDERR "warning: output ", $build->outpath, " has disappeared\n";

[4.394]

[4.474]

print STDERR " warning: output ", $build->outpath, " has disappeared\n" if $build->finished;
}
if (!$build->finished) {
if (isValidPath($build->drvpath)) {
addRoot $build->drvpath;
} else {
print STDERR " warning: derivation ", $build->drvpath, " has disappeared\n";
}
replacement in src/script/hydra-update-gc-roots at line 46

[4.1148]→[4.3675:3699](∅→∅)

# Go over all projects.

[4.1148]

[4.1148]

# Keep every build in every release of every project.
print STDERR "*** looking for release members\n";
keepBuild $_ foreach $db->resultset('Builds')->search_literal(
"exists (select 1 from releasemembers where build = me.id)", { order_by => ["project", "jobset", "job", "id"] });

# Keep all builds that have been marked as "keep".
print STDERR "*** looking for kept builds\n";
my @buildsToKeep = $db->resultset('Builds')->search(
{ finished => 1, keep => 1 }, { order_by => ["project", "jobset", "job", "id"], columns => [ @columns ] });
keepBuild $_ foreach @buildsToKeep;
replacement in src/script/hydra-update-gc-roots at line 58

[4.1149]→[4.0:56](∅→∅)

foreach my $project ($db->resultset('Projects')->all) {

[4.1149]

[4.3700]

# Go over all projects.
foreach my $project ($db->resultset('Projects')->search({}, { order_by => ["name"] })) {
replacement in src/script/hydra-update-gc-roots at line 63

[4.1380]→[4.1380:1430](∅→∅)

foreach my $jobset ($project->jobsets->all) {

[4.1380]

[4.1430]

foreach my $jobset ($project->jobsets->search({}, { order_by => ["name" ]})) {
replacement in src/script/hydra-update-gc-roots at line 78

[4.923]→[4.1770:2007](∅→∅),[4.1770]→[4.1770:2007](∅→∅)

# Go over all jobs in this jobset.
foreach my $job ($jobset->jobs->all) {
print STDERR "*** looking for builds to keep in job ",
$project->name, ":", $job->jobset->name, ":", $job->name, "\n";

[4.923]

[4.797]

print STDERR "*** looking for the $keepnr most recent successful builds of each job in jobset ",
$project->name, ":", $jobset->name, "\n";
replacement in src/script/hydra-update-gc-roots at line 81

[4.798]→[4.2008:2574](∅→∅),[4.2574]→[3.10952:10999](∅→∅),[4.971]→[4.2661:2775](∅→∅),[3.10999]→[4.2661:2775](∅→∅),[4.2661]→[4.2661:2775](∅→∅),[4.2775]→[4.504:527](∅→∅)

# Keep the N most recent successful builds for each job
# and platform.
# !!! Take time into account? E.g. don't delete builds
# that are younger than N days.
my @systems = $job->builds->search({ }, { select => ["system"], distinct => 1 })->all;
foreach my $system (@systems) {
my @recentBuilds = $job->builds->search(
{ finished => 1
, buildStatus => 0 # == success
, system => $system->system
},
{ order_by => 'me.id DESC'
, rows => $keepnr
});
keepBuild $_ foreach @recentBuilds;
}
}

[4.798]

[4.3896]

keepBuild $_ foreach $jobset->builds->search(
{ 'me.id' => { 'in' => \
[ "select b2.id from Builds b2 join " .
" (select distinct job, system, coalesce( " .
" (select id from builds where project = b.project and jobset = b.jobset and job = b.job and system = b.system and finished = 1 and buildStatus = 0 order by id desc offset ? limit 1)" .
" , 0) nth from builds b where project = ? and jobset = ? and isCurrent = 1) x " .
" on b2.project = ? and b2.jobset = ? and b2.job = x.job and b2.system = x.system and (id >= x.nth) where finished = 1 and buildStatus = 0"
, [ '', $keepnr - 1 ], [ '', $project->name ], [ '', $jobset->name ], [ '', $project->name ], [ '', $jobset->name ] ] }
},
{ order_by => ["job", "system", "id"], columns => [ @columns ] });
replacement in src/script/hydra-update-gc-roots at line 103

[4.4440]→[4.1795:1924](∅→∅)

print STDERR "keeping latest successful view result ", $latest->id, " (", $latest->get_column('releasename'), ")\n";

[4.4440]

[4.1924]

print STDERR " keeping latest successful view result ", $latest->id, " (", $latest->get_column('releasename'), ")\n";
edit in src/script/hydra-update-gc-roots at line 107

[4.1266]→[4.1610:1616](∅→∅),[4.4685]→[4.1610:1616](∅→∅),[4.1610]→[4.1610:1616](∅→∅),[4.1616]→[4.56:407](∅→∅)

}

# Keep every build in every release in this project.
print STDERR "*** keeping releases in project ", $project->name, "\n"
if scalar $project->releases > 0;
foreach my $release ($project->releases->all) {
print STDERR "keeping release ", $release->name, "\n";
keepBuild $_->build foreach $release->releasemembers;
edit in src/script/hydra-update-gc-roots at line 111

[4.1620]→[4.1267:1318](∅→∅),[4.1318]→[4.2268:2314](∅→∅),[4.2314]→[3.11000:11081](∅→∅),[3.11081]→[4.1462:1500](∅→∅),[4.1462]→[4.1462:1500](∅→∅)

# Keep all builds that have been marked as "keep".
print STDERR "*** looking for kept builds\n";
my @buildsToKeep = $db->resultset('Builds')->search({finished => 1, keep => 1});
keepBuild $_ foreach @buildsToKeep;
replacement in src/script/hydra-update-gc-roots at line 113

[4.2366]→[4.7624:7696](∅→∅),[4.7696]→[4.1785:1825](∅→∅),[4.1785]→[4.1785:1825](∅→∅),[4.1825]→[4.2367:2434](∅→∅),[4.2434]→[4.830:910](∅→∅),[4.830]→[4.830:910](∅→∅),[4.910]→[4.1825:1863](∅→∅),[4.1825]→[4.1825:1863](∅→∅),[4.1863]→[4.1370:1430](∅→∅),[4.949]→[4.1863:1968](∅→∅),[4.1430]→[4.1863:1968](∅→∅),[4.1863]→[4.1863:1968](∅→∅)

foreach my $build ($db->resultset('Builds')->search({finished => 0})) {
if (isValidPath($build->drvpath)) {
print STDERR "keeping scheduled build ", $build->id, " (",
strftime("%Y-%m-%d %H:%M:%S", localtime($build->timestamp)), ")\n";
registerRoot $build->drvpath;
registerRoot $build->outpath if -e $build->outpath;
} else {
print STDERR "warning: derivation ", $build->drvpath, " has disappeared\n";
}
}

[4.2366]

[4.1968]

keepBuild $_ foreach $db->resultset('Builds')->search({ finished => 0 }, { columns => [ @columns ] });
edit in src/script/hydra-update-gc-roots at line 122

[4.2064]

[4.2064]

my $rootsKept = 0;
my $rootsDeleted = 0;
replacement in src/script/hydra-update-gc-roots at line 127

[4.2098]→[4.2098:2187](∅→∅)

next if !-l "$gcRootsDir/$link";
my $path = readlink "$gcRootsDir/$link" or die;

[4.2098]

[4.2187]

next if $link eq "." || $link eq "..";
my $path = "/nix/store/$link";
replacement in src/script/hydra-update-gc-roots at line 131

[4.2267]→[4.2267:2344](∅→∅)

unlink "$gcRootsDir/$link" or die "cannot remove $gcRootsDir/$link";

[4.2267]

[4.2344]

$rootsDeleted++;
unlink "$gcRootsDir/$link" or warn "cannot remove $gcRootsDir/$link";
} else {
$rootsKept++;
edit in src/script/hydra-update-gc-roots at line 139

[4.2367]

print STDERR "kept $rootsKept roots, deleted $rootsDeleted roots\n";
edit in src/sql/hydra.sql at line 520

[2.54]

[22.2946]

create index IndexBuildsOnKeep on Builds(keep); -- used by hydra-update-gc-roots
create index IndexMostRecentSuccessfulBuilds on Builds(project, jobset, job, system, finished, buildStatus, id desc); -- used by hydra-update-gc-roots
file addition: upgrade-3.sql (----------)

[23.3004]

create index IndexBuildsOnKeep on Builds(keep); -- used by hydra-update-gc-roots
create index IndexMostRecentSuccessfulBuilds on Builds(project, jobset, job, system, finished, buildStatus, id desc); -- used by hydra-update-gc-roots