Re: [PATCH] sql: Add a couple of indexes.
zimoun writes: > Hi Chris, > > On Sun, 14 Jun 2020 at 12:48, Christopher Baines wrote: > >>> --8<---cut here---start->8--- >>> 65.7% substitutes available (9,425 out of 14,354) >>> at least 57,816.3 MiB of nars (compressed) >>> 97,343.9 MiB on disk (uncompressed) >>> 0.010 seconds per request (138.1 seconds in total) >>> 103.0 requests per second >>> >>> 0.0% (0 out of 4,929) of the missing items are queued >>> at least 1,000 queued builds >>> x86_64-linux: 1000 (100.0%) >>> build rate: 9.20 builds per hour >>> x86_64-linux: 9.20 builds per hour >>> --8<---cut here---end--->8--- >>> >>> Hum? Almost 1/3 of substitutes are not available and in the same time >>> not queued. Is it expected? Does it mean that 1/3 of the builds are >>> failing? >> >> Probably not, most likely canceled or a dependency failed to build. > > Well, it depends on how you define what is a build. ;-) Because if one > package's dependency does not build, the package cannot build either. Yeah. Given this is about Cuirass, I was sort of using the Cuirass build states, which does distinguish a build that was attempted, and failed, from one which couldn't be attempted because an input was lacking. signature.asc Description: PGP signature
Re: [PATCH] sql: Add a couple of indexes.
Hi Chris, On Sun, 14 Jun 2020 at 12:48, Christopher Baines wrote: >> --8<---cut here---start->8--- >> 65.7% substitutes available (9,425 out of 14,354) >> at least 57,816.3 MiB of nars (compressed) >> 97,343.9 MiB on disk (uncompressed) >> 0.010 seconds per request (138.1 seconds in total) >> 103.0 requests per second >> >> 0.0% (0 out of 4,929) of the missing items are queued >> at least 1,000 queued builds >> x86_64-linux: 1000 (100.0%) >> build rate: 9.20 builds per hour >> x86_64-linux: 9.20 builds per hour >> --8<---cut here---end--->8--- >> >> Hum? Almost 1/3 of substitutes are not available and in the same time >> not queued. Is it expected? Does it mean that 1/3 of the builds are >> failing? > > Probably not, most likely canceled or a dependency failed to build. Well, it depends on how you define what is a build. ;-) Because if one package's dependency does not build, the package cannot build either. I agree that these 1/3 non-available substitutes can be because only one essential package fails. We discussed at Guix Days something like "guix weather --release"; which could help to report what maintainers/developers consider as essential. However, I feel that something is lacking to identify which package is failing and what is its impact. Something like: the build fails then a warning is raised if "guix refresh -l" is too high. Cheers, simon
Re: [PATCH] sql: Add a couple of indexes.
zimoun writes: > Hi Chris, > > On Sat, 13 Jun 2020 at 17:29, Christopher Baines wrote: > >> I've pushed a rebased version of this patch now. I've also recofigured >> bayfront to apply these changes. Checking with guix weather, I think it >> might be helping. > > Nice! > > What about reconfigure ci.guix.gnu.org and close bug#41708? > > http://issues.guix.gnu.org/issue/41708 Indeed, hopefully that can happen soon. >> → guix weather --substitute-urls=https://bayfront.guix.gnu.org > > [...] > >> 13.8% (897 out of 6,519) of the missing items are queued >> at least 1,000 queued builds >> x86_64-linux: 1000 (100.0%) >> build rate: 9.34 builds per hour >> x86_64-linux: 9.34 builds per hour > > I think it is the first I see these results instead of the 504. :-) > With "guix e782756", it is: > > --8<---cut here---start->8--- > 65.7% substitutes available (9,425 out of 14,354) > at least 57,816.3 MiB of nars (compressed) > 97,343.9 MiB on disk (uncompressed) > 0.010 seconds per request (138.1 seconds in total) > 103.0 requests per second > > 0.0% (0 out of 4,929) of the missing items are queued > at least 1,000 queued builds > x86_64-linux: 1000 (100.0%) > build rate: 9.20 builds per hour > x86_64-linux: 9.20 builds per hour > --8<---cut here---end--->8--- > > Hum? Almost 1/3 of substitutes are not available and in the same time > not queued. Is it expected? Does it mean that 1/3 of the builds are > failing? Probably not, most likely canceled or a dependency failed to build. I looked at a recent revision using the Guix Data Service, and it's unclear [1]. Lots of builds are unknown, and I'm not sure why that would be. 1: http://data.guix.gnu.org/revision/d291b454bd402c88eed1464a1dfa058a2f663874/builds signature.asc Description: PGP signature
Re: [PATCH] sql: Add a couple of indexes.
Hi Chris, On Sat, 13 Jun 2020 at 17:29, Christopher Baines wrote: > I've pushed a rebased version of this patch now. I've also recofigured > bayfront to apply these changes. Checking with guix weather, I think it > might be helping. Nice! What about reconfigure ci.guix.gnu.org and close bug#41708? http://issues.guix.gnu.org/issue/41708 > → guix weather --substitute-urls=https://bayfront.guix.gnu.org [...] > 13.8% (897 out of 6,519) of the missing items are queued > at least 1,000 queued builds > x86_64-linux: 1000 (100.0%) > build rate: 9.34 builds per hour > x86_64-linux: 9.34 builds per hour I think it is the first I see these results instead of the 504. :-) With "guix e782756", it is: --8<---cut here---start->8--- 65.7% substitutes available (9,425 out of 14,354) at least 57,816.3 MiB of nars (compressed) 97,343.9 MiB on disk (uncompressed) 0.010 seconds per request (138.1 seconds in total) 103.0 requests per second 0.0% (0 out of 4,929) of the missing items are queued at least 1,000 queued builds x86_64-linux: 1000 (100.0%) build rate: 9.20 builds per hour x86_64-linux: 9.20 builds per hour --8<---cut here---end--->8--- Hum? Almost 1/3 of substitutes are not available and in the same time not queued. Is it expected? Does it mean that 1/3 of the builds are failing? All the best, simon
Re: [PATCH] sql: Add a couple of indexes.
Christopher Baines writes: > Remove the Builds_index from the schema, as it would have been removed in > error by the upgrade-6 upgrade. Add a specific index on the Builds status > field, as this helps with db-get-builds queries, and add an index on the > Outputs derivation field, as this helps with the db-get-outputs part of > db-get-builds. > > * src/sql/upgrade-7.sql: New file. > * src/schema.sql: Update accordingly. > --- > src/schema.sql| 3 ++- > src/sql/upgrade-7.sql | 7 +++ > 2 files changed, 9 insertions(+), 1 deletion(-) > create mode 100644 src/sql/upgrade-7.sql > > diff --git a/src/schema.sql b/src/schema.sql > index 1104551..60d250a 100644 > --- a/src/schema.sql > +++ b/src/schema.sql > @@ -74,7 +74,8 @@ CREATE TABLE Events ( > > -- Create indexes to speed up common queries, in particular those > -- corresponding to /api/latestbuilds and /api/queue HTTP requests. > -CREATE INDEX Builds_index ON Builds(job_name, system, status ASC, timestamp > ASC, derivation, evaluation, stoptime DESC); > +CREATE INDEX Builds_status_index ON Builds (status); > +CREATE INDEX Outputs_derivation_index ON Outputs (derivation); > CREATE INDEX Inputs_index ON Inputs(specification, name, branch); > > COMMIT; > diff --git a/src/sql/upgrade-7.sql b/src/sql/upgrade-7.sql > new file mode 100644 > index 000..1be3470 > --- /dev/null > +++ b/src/sql/upgrade-7.sql > @@ -0,0 +1,7 @@ > +BEGIN TRANSACTION; > + > +CREATE INDEX Builds_status_index ON Builds (status); > + > +CREATE INDEX Outputs_derivation_index ON Outputs (derivation); > + > +COMMIT; I've pushed a rebased version of this patch now. I've also recofigured bayfront to apply these changes. Checking with guix weather, I think it might be helping. → guix weather --substitute-urls=https://bayfront.guix.gnu.org computing 13,852 package derivations for x86_64-linux... looking for 14,400 store items on https://bayfront.guix.gnu.org... updating substitutes from 'https://bayfront.guix.gnu.org'... 100.0% https://bayfront.guix.gnu.org 54.7% substitutes available (7,881 out of 14,400) at least 37,690.9 MiB of nars (compressed) 71,872.0 MiB on disk (uncompressed) 0.048 seconds per request (697.1 seconds in total) 20.7 requests per second 13.8% (897 out of 6,519) of the missing items are queued at least 1,000 queued builds x86_64-linux: 1000 (100.0%) build rate: 9.34 builds per hour x86_64-linux: 9.34 builds per hour signature.asc Description: PGP signature
[PATCH] sql: Add a couple of indexes.
Remove the Builds_index from the schema, as it would have been removed in error by the upgrade-6 upgrade. Add a specific index on the Builds status field, as this helps with db-get-builds queries, and add an index on the Outputs derivation field, as this helps with the db-get-outputs part of db-get-builds. * src/sql/upgrade-7.sql: New file. * src/schema.sql: Update accordingly. --- src/schema.sql| 3 ++- src/sql/upgrade-7.sql | 7 +++ 2 files changed, 9 insertions(+), 1 deletion(-) create mode 100644 src/sql/upgrade-7.sql diff --git a/src/schema.sql b/src/schema.sql index 1104551..60d250a 100644 --- a/src/schema.sql +++ b/src/schema.sql @@ -74,7 +74,8 @@ CREATE TABLE Events ( -- Create indexes to speed up common queries, in particular those -- corresponding to /api/latestbuilds and /api/queue HTTP requests. -CREATE INDEX Builds_index ON Builds(job_name, system, status ASC, timestamp ASC, derivation, evaluation, stoptime DESC); +CREATE INDEX Builds_status_index ON Builds (status); +CREATE INDEX Outputs_derivation_index ON Outputs (derivation); CREATE INDEX Inputs_index ON Inputs(specification, name, branch); COMMIT; diff --git a/src/sql/upgrade-7.sql b/src/sql/upgrade-7.sql new file mode 100644 index 000..1be3470 --- /dev/null +++ b/src/sql/upgrade-7.sql @@ -0,0 +1,7 @@ +BEGIN TRANSACTION; + +CREATE INDEX Builds_status_index ON Builds (status); + +CREATE INDEX Outputs_derivation_index ON Outputs (derivation); + +COMMIT; -- 2.26.2
