Re: Cuirass news

[ludo@gnu.org (Ludovic Courtès)]

Hello,

Danny Milosavljevic <dannym@scratchpost.org> skribis:

> On Sat, 10 Feb 2018 12:18:56 +0100
> ludo@gnu.org (Ludovic Courtès) wrote:
>
>> Thinking about it, it wouldn’t matter that HTTP requests are processed
>> sequentially if database queries run really fast.  I’m not sure if we
>> can achieve it.  WDYT?
>
> That depends on how fast.  But it should be possible to optimize the actual
> query (using indices, lookups are O(log N)).  Also, if it's the same
> query as before, it usually will be really fast as most of the pointers
> are still where they were before.
>
> Sqlite3 already automatically created indices for all the primary keys.
>
> There's also https://www.sqlite.org/pragma.html#pragma_optimize if we need it.
>
> We can always try it with serialized database access and use a connection
> pool should it get too slow later.

My point is /latestbuilds and /queue already take several seconds on the
database that we have on berlin, which is quite big.  So we have a
problem already.

I tried this:

--8<---------------cut here---------------start------------->8---
$ sudo sqlite3 /var/run/cuirass/cuirass.db 
Password: 
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
sqlite> select count(*) from builds where status < 0;
636635
sqlite> select count(*) from builds inner join Derivations ON Builds.derivation = Derivations.derivation and Builds.evaluation = Derivations.evaluation
   ...> INNER JOIN Evaluations ON Derivations.evaluation = Evaluations.id
   ...> INNER JOIN Specifications ON Evaluations.specification = Specifications.repo_name;
2156003
sqlite> pragma optimize;
sqlite> 
--8<---------------cut here---------------end--------------->8---

… but that doesn’t seem to have any effect, presumably because sqlite3
already optimized whatever it could.

Thoughts?

Ludo’.