[PATCH] sql: Add a couple of indexes.

[PATCH] sql: Add a couple of indexes.

[Christopher Baines]

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 0000000..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

Re: [PATCH] sql: Add a couple of indexes.

[Christopher Baines]

[-- Attachment #1: Type: text/plain, Size: 2527 bytes --]


Christopher Baines <mail@cbaines.net> 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 0000000..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

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 962 bytes --]

Re: [PATCH] sql: Add a couple of indexes.

[zimoun]

Hi Chris,

On Sat, 13 Jun 2020 at 17:29, Christopher Baines <mail@cbaines.net> 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]

[-- Attachment #1: Type: text/plain, Size: 1952 bytes --]


zimoun <zimon.toutoune@gmail.com> writes:

> Hi Chris,
>
> On Sat, 13 Jun 2020 at 17:29, Christopher Baines <mail@cbaines.net> 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

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 962 bytes --]

Re: [PATCH] sql: Add a couple of indexes.

[zimoun]

Hi Chris,

On Sun, 14 Jun 2020 at 12:48, Christopher Baines <mail@cbaines.net> 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.

[Christopher Baines]

[-- Attachment #1: Type: text/plain, Size: 1366 bytes --]


zimoun <zimon.toutoune@gmail.com> writes:

> Hi Chris,
>
> On Sun, 14 Jun 2020 at 12:48, Christopher Baines <mail@cbaines.net> 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.

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 962 bytes --]