grahamc/hydra - Change MB5INTIQWM5VXEEWFNX46SWVNTORXWZGKZDOYZEORQ5T5ZM7XI2QC

Normalize nixexpr{input,path} from builds to jobsetevals.

Duplicating this data on every record of the builds table cost approximately 4G of duplication.

Note that the database migration included took about 4h45m on an untuned server which uses very slow rotational disks in a RAID5 setup, with not a lot of RAM. I imagine in production it might take an hour or two, but not 4. If this should become a chunked migration, I can do that.

Note: Because of the question about chunked migrations, I have NOT YET tested this migration thoroughly enough for merge.

Created by Graham Christensen on January 22, 2021

MB5INTIQWM5VXEEWFNX46SWVNTORXWZGKZDOYZEORQ5T5ZM7XI2QC

Dependencies

In channels

upstream

main

Change contents

Deletion in src/lib/Hydra/Schema/Builds.pm at line 135 [6.5202]
B:BD[5.8581] → [5.8581:8598]
B:BD[5.8612] → [5.8612:8634]
B:BD[5.8634] → [7.7989:8009]
∅:D[7.8009] → [5.8675:8692]
B:BD[5.8675] → [5.8675:8692]
B:BD[5.8706] → [5.8706:8726]
B:BD[5.8726] → [8.4218:4239]
```
  is_nullable: 1
=head2 nixexprinput
  data_type: 'text'
  is_nullable: 1
=head2 nixexprpath
  data_type: 'text'
```
Deletion in src/lib/Hydra/Schema/Builds.pm at line 239 [6.5202]
∅:D[7.9030] → [9.102:120]
B:BD[10.949] → [9.102:120]
B:BD[9.120] → [7.9031:9076]
∅:D[7.9076] → [9.223:240]
B:BD[9.223] → [9.223:240]
B:BD[9.240] → [8.4614:4659]
```
  "nixexprinput",
  { data_type => "text", is_nullable => 1 },
  "nixexprpath",
  { data_type => "text", is_nullable => 1 },
```

Replacement in src/lib/Hydra/Schema/Builds.pm at line 531 [6.5202]

B:BD[11.3378] → [12.2522:2664]

# Created by DBIx::Class::Schema::Loader v0.07049 @ 2020-05-27 17:40:41
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:RIKKFfcKXFWIUeM8ma++iw

[11.3378]

[13.4481]

# Created by DBIx::Class::Schema::Loader v0.07049 @ 2021-01-22 07:11:57
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Df5N0EByYJqoSUqA0dld/A

Insertion in src/lib/Hydra/Schema/JobsetEvals.pm at line 91 [15.65]

[5.16449]

[14.512]


=head2 nixexprinput
  data_type: 'text'
  is_nullable: 1
=head2 nixexprpath
  data_type: 'text'
  is_nullable: 1

Insertion in src/lib/Hydra/Schema/JobsetEvals.pm at line 145 [15.65]

[7.18063]

[14.632]

  "nixexprinput",
  { data_type => "text", is_nullable => 1 },
  "nixexprpath",
  { data_type => "text", is_nullable => 1 },

Replacement in src/lib/Hydra/Schema/JobsetEvals.pm at line 232 [15.65]

B:BD[7.18354] → [4.238:380]

# Created by DBIx::Class::Schema::Loader v0.07049 @ 2021-01-21 11:13:38
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:zDBtAFc4HiFUcL/TpkuCcg

[7.18354]

[16.1674]

# Created by DBIx::Class::Schema::Loader v0.07049 @ 2021-01-22 07:11:57
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:hdu+0WWo2363dVvImMKxdA

Replacement in src/lib/Hydra/Schema/Jobsets.pm at line 378 [17.1960]

B:BD[7.22628] → [12.2808:2950]

# Created by DBIx::Class::Schema::Loader v0.07049 @ 2020-05-27 17:40:41
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:aDW78MCelU/ma953aTcHvA

[7.22628]

[18.1062]

# Created by DBIx::Class::Schema::Loader v0.07049 @ 2021-01-22 07:11:57
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:6P1qlC5oVSPRSgRBp6nmrw

Replacement in src/lib/Hydra/Schema/Projects.pm at line 261 [17.3266]

B:BD[19.7308] → [12.2951:3093]

# Created by DBIx::Class::Schema::Loader v0.07049 @ 2020-05-27 17:40:41
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:iBGJjFWiI9Wy9zwT7xGOEA

[19.7308]

[20.171]

# Created by DBIx::Class::Schema::Loader v0.07049 @ 2021-01-22 07:11:57
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Ff5gJejFu+02b0lInobOoQ

Replacement in src/root/build.tt at line 123 [6.7350]

B:BD[21.226] → [22.0:48]

      [% IF build.nixexprinput || eval.flake %]

[21.226]

[21.260]

      [% IF eval.nixexprinput || eval.flake %]

Replacement in src/root/build.tt at line 349 [6.7350]
∅:D[23.192] → [24.7702:7736]
∅:D[25.609] → [24.7702:7736]
B:BD[24.7702] → [24.7702:7736]
```
      [% IF build.nixexprinput %]
```
[25.609]
[24.7736]
```
      [% IF eval.nixexprinput %]
```

Replacement in src/root/build.tt at line 352 [6.7350]

B:BD[24.7784] → [24.7784:7910]

          <td>file <tt>[% HTML.escape(build.nixexprpath) %]</tt> in input <tt>[% HTML.escape(build.nixexprinput) %]</tt></td>

[24.7784]

[24.7910]

          <td>file <tt>[% HTML.escape(eval.nixexprpath) %]</tt> in input <tt>[% HTML.escape(eval.nixexprinput) %]</tt></td>

Replacement in src/root/reproduce.tt at line 172 [26.527]
B:BD[26.2953] → [26.2953:2996]
```
[% IF input.name == build.nixexprinput +%]
```
[26.2953]
[26.2996]
```
[% IF input.name == eval.nixexprinput +%]
```

Replacement in src/root/reproduce.tt at line 200 [26.527]

B:BD[26.3499] → [2.178:255]

args+=("$nixExprInputDir/[% build.nixexprpath %]" -A '[% build.job.name %]')

[26.3499]

[2.255]

args+=("$nixExprInputDir/[% eval.nixexprpath %]" -A '[% build.job.name %]')

Deletion in src/script/hydra-eval-jobset at line 463 [28.1319]

B:BD[27.16199] → [27.16199:16301]

            , nixexprinput => $jobset->nixexprinput
            , nixexprpath => $jobset->nixexprpath

Insertion in src/script/hydra-eval-jobset at line 725 [28.1319]

[29.993]

[30.6334]

            , nixexprinput => $jobset->nixexprinput
            , nixexprpath => $jobset->nixexprpath

Deletion in src/sql/hydra.sql at line 165 [28.1]

B:BD[9.594] → [9.594:664]

B:BD[9.664] → [31.0:69]

B:BD[31.69] → [32.0:96]

B:BD[32.96] → [3.539:539]

∅:D[32.96] → [9.743:791]

∅:D[3.539] → [9.743:791]

∅:D[33.1269] → [9.743:791]

B:BD[9.743] → [9.743:791]

B:BD[11.9348] → [11.9348:9349]

    -- Copy of the nixExprInput/nixExprPath fields of the jobset that
    -- instantiated this build.  Needed if we want to reproduce this
    -- build.  FIXME: this should be stored in JobsetEvals, storing it
    -- here is denormal.
    nixExprInput  text,
    nixExprPath   text,

Insertion in src/sql/hydra.sql at line 462 [28.1]

[29.1048]

[30.7667]

    nixExprInput  text, -- name of the jobsetInput containing the Nix or Guix expression
    nixExprPath   text, -- relative path of the Nix or Guix expression

File addition: upgrade-71.sql (----------)

[34.3004]

ALTER TABLE JobsetEvals
    ADD COLUMN nixExprInput text,
    ADD COLUMN nixExprPath text;
-- This migration took 4.5 hours on a server
-- with 5400RPM drives, against a copy of hydra's
-- production dataset. It might take a significantly
-- less amount of time there, and not justify a
-- batched migration.
UPDATE jobsetevals
SET (nixexprinput, nixexprpath) = (
    SELECT builds.nixexprinput, builds.nixexprpath
    FROM builds
    LEFT JOIN jobsetevalmembers
      ON jobsetevalmembers.build = builds.id
    WHERE jobsetevalmembers.eval = jobsetevals.id
    LIMIT 1
)
WHERE jobsetevals.id in (
  SELECT jobsetevalsprime.id
  FROM jobsetevals as jobsetevalsprime
  WHERE jobsetevalsprime.nixexprinput IS NULL
  -- AND jobsetevalsprime.id > ? --------- These are in case of a batched migration
  ORDER BY jobsetevalsprime.id ASC  --  /
  -- LIMIT ?  --  ----------------------
);
ALTER TABLE builds
    DROP COLUMN nixexprinput,
    DROP COLUMN nixexprpath;