Subject: bug#41708: "guix weather" : 504 error

From: Christopher Baines <[email-masked]>
Date: Tue, 09 Jun 2020 22:12:32 +0100

zimoun <[email-masked]> writes:

> By default, "guix weather" returns:
>
> --8<---------------cut here---------------start------------->8---
> 'https://ci.guix.gnu.org/api/queue?nr=1000' returned 504 ("Gateway Time-out")
> --8<---------------cut here---------------end--------------->8---
>
> which is not fatal but annoying.  Especially, it takes time.
>
> As discussed on IRC [1], it seems that it is a bug server side.
>
> In addition, something appears similar with Bayfront, well the 4
> substitutes servers that I know returns:
>
> --8<---------------cut here---------------start------------->8---
> 'https://ci.guix.gnu.org/api/queue?nr=1000' returned 504 ("Gateway Time-out")
> 'https://bayfront.guix.gnu.org/api/queue?nr=1000' returned 504
> ("Gateway Time-out")
> (continuous integration information unavailable)
> 'https://guix.tobias.gr/api/queue?nr=1000' returned 410 ("Gone")
> --8<---------------cut here---------------end--------------->8---

Hey,

So I think I've got a patch [1] to Cuirass to "fix" this.

1: https://lists.gnu.org/archive/html/guix-devel/2020-06/msg00117.html

I expected this was due to the complicated part of the db-get-builds
query, but I think it's actually down to the simple part that fetches
all the outputs for all the builds. Fetching the outputs for a build is
slow, because at the moment, there's no index on the Outputs field, so
looking up the outputs requires reading through the whole table, and the
Outputs table can be quite large.

The performance should improve with the additional query. To see why,
you can use EXPLAIN QUERY PLAN to see what SQLite plans to do when
processing the query:

sqlite> EXPLAIN QUERY PLAN SELECT name, path FROM Outputs WHERE derivation = 'foo';
QUERY PLAN
`--SCAN TABLE Outputs

sqlite> CREATE INDEX Outputs_derivation_index ON Outputs (derivation);

sqlite> EXPLAIN QUERY PLAN SELECT name, path FROM Outputs WHERE derivation = 'foo';
QUERY PLAN
`--SEARCH TABLE Outputs USING INDEX Outputs_derivation_index (derivation=?)

I believe that searching the table using an index is going to be faster
than scanning the table, and testing locally and on bayfront suggests
this will resolve the performance issue.