grahamc/hydra2 - Change KJQWSRCCQEKF64L4NYYZ7VKAF2YEPYXXTRW6BI464P6Q6KU34TQAC

Speed up hydra-update-gc-roots

The hydra-update-gc-roots script is taking around 95 minutes on our Hydra instance (though a lot of that is I/O wait). This patch significantly reduces the number of database queries. In particular, the N most recent successful builds for each job in a jobset are now determined in a single query. Also, it removes the calls to readlink().

Created by Eelco Dolstra on March 7, 2012

KJQWSRCCQEKF64L4NYYZ7VKAF2YEPYXXTRW6BI464P6Q6KU34TQAC

Dependencies

In channels

main

Change contents

Replacement in src/script/hydra-update-gc-roots at line 16 [4.472]
B:BD[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.472]
B:BD[4.795] → [5.643:688]
```
    Hydra::Helper::Nix::registerRoot($path);
```
[4.795]
[4.1047]
```
    registerRoot($path);
```
Insertion in src/script/hydra-update-gc-roots at line 21 [4.472]
[4.1072]
[4.1072]

Insertion in src/script/hydra-update-gc-roots at line 23 [4.472]

[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.472]

B:BD[6.177] → [7.0:55]

B:BD[7.55] → [8.0:30]

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

[6.177]

[8.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.472]
B:BD[6.343] → [6.343:381]
```
        registerRoot $build->outpath;
```
[6.343]
[6.381]
```
	addRoot $build->outpath;
```

Replacement in src/script/hydra-update-gc-roots at line 34 [4.472]

B:BD[6.394] → [6.394:474]

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

[6.394]

[6.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.472]

B:BD[4.1148] → [9.3675:3699]

# Go over all projects.

[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.472]

B:BD[4.1149] → [10.0:56]

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

[4.1149]

[9.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.472]

B:BD[11.1380] → [11.1380:1430]

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

[11.1380]

[11.1430]

    foreach my $jobset ($project->jobsets->search({}, { order_by => ["name" ]})) {

Replacement in src/script/hydra-update-gc-roots at line 78 [4.472]

∅:D[12.923] → [11.1770:2007]

B:BD[11.1770] → [11.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";

[12.923]

[6.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.472]

B:BD[6.798] → [11.2008:2574]

B:BD[11.2574] → [3.10952:10999]

∅:D[12.971] → [11.2661:2775]

∅:D[3.10999] → [11.2661:2775]

B:BD[11.2661] → [11.2661:2775]

B:BD[11.2775] → [13.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;
           }
        }

[6.798]

[9.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.472]

B:BD[9.4440] → [14.1795:1924]

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

[9.4440]

[14.1924]

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

Deletion in src/script/hydra-update-gc-roots at line 107 [4.472]

∅:D[6.1266] → [4.1610:1616]

∅:D[9.4685] → [4.1610:1616]

B:BD[4.1610] → [4.1610:1616]

B:BD[4.1616] → [7.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;

Deletion in src/script/hydra-update-gc-roots at line 111 [4.472]
B:BD[4.1620] → [6.1267:1318]
B:BD[6.1318] → [15.2268:2314]
B:BD[15.2314] → [3.11000:11081]
∅:D[3.11081] → [6.1462:1500]
B:BD[6.1462] → [6.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.472]

B:BD[15.2366] → [16.7624:7696]

∅:D[16.7696] → [4.1785:1825]

B:BD[4.1785] → [4.1785:1825]

B:BD[4.1825] → [15.2367:2434]

∅:D[15.2434] → [5.830:910]

B:BD[5.830] → [5.830:910]

∅:D[5.910] → [4.1825:1863]

B:BD[4.1825] → [4.1825:1863]

B:BD[4.1863] → [17.1370:1430]

∅:D[5.949] → [4.1863:1968]

∅:D[17.1430] → [4.1863:1968]

B:BD[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";
    }
}

[15.2366]

[4.1968]

keepBuild $_ foreach $db->resultset('Builds')->search({ finished => 0 }, { columns => [ @columns ] });

Insertion in src/script/hydra-update-gc-roots at line 122 [4.472]
[4.2064]
[4.2064]
```
my $rootsKept = 0;
my $rootsDeleted = 0;
```

Replacement in src/script/hydra-update-gc-roots at line 127 [4.472]

B:BD[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.472]

B:BD[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++;

Insertion in src/script/hydra-update-gc-roots at line 139 [4.472]
[4.2367]
```
print STDERR "kept $rootsKept roots, deleted $rootsDeleted roots\n";
```

Insertion in src/sql/hydra.sql at line 520 [19.1]

[2.54]

[18.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 (----------)

[20.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