Hello, Danny Milosavljevic <dan...@scratchpost.org> skribis:
> On Sat, 10 Feb 2018 12:18:56 +0100 > l...@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’.
