From mboxrd@z Thu Jan 1 00:00:00 1970 From: ludo@gnu.org (Ludovic =?utf-8?Q?Court=C3=A8s?=) Subject: Re: Cuirass news Date: Wed, 14 Feb 2018 14:43:56 +0100 Message-ID: <87zi4bsn37.fsf@gnu.org> References: <877es6x5xj.fsf@gnu.org> <87lggmjjgo.fsf@gmail.com> <87k1w6jjak.fsf@gmail.com> <87h8raxeym.fsf@gnu.org> <20180126153005.259a75e8@scratchpost.org> <87zi4z1eb0.fsf@gnu.org> <20180127181852.42f0bcbf@scratchpost.org> <87fu6bwqix.fsf@gnu.org> <20180208172905.19e9e789@scratchpost.org> <871shvt94p.fsf@gnu.org> <20180209000526.5b9ea8e7@scratchpost.org> <87wozm4i12.fsf@gnu.org> <20180209122931.5a47e63e@scratchpost.org> <877ermyuj2.fsf@gnu.org> <20180209180608.79fb856b@scratchpost.org> <874lmpw0rj.fsf@gnu.org> <20180213101236.5c74e577@scratchpost.org> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:44667) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1elxLs-0007GJ-Sx for guix-devel@gnu.org; Wed, 14 Feb 2018 08:44:02 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1elxLr-0003a1-Nf for guix-devel@gnu.org; Wed, 14 Feb 2018 08:44:00 -0500 Received: from hera.aquilenet.fr ([2a0c:e300::1]:55646) by eggs.gnu.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.71) (envelope-from ) id 1elxLr-0003V2-Gb for guix-devel@gnu.org; Wed, 14 Feb 2018 08:43:59 -0500 In-Reply-To: <20180213101236.5c74e577@scratchpost.org> (Danny Milosavljevic's message of "Tue, 13 Feb 2018 10:12:36 +0100") List-Id: "Development of GNU Guix and the GNU System distribution." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: guix-devel-bounces+gcggd-guix-devel=m.gmane.org@gnu.org Sender: "Guix-devel" To: Danny Milosavljevic Cc: guix-devel Hello, Danny Milosavljevic skribis: > On Sat, 10 Feb 2018 12:18:56 +0100 > ludo@gnu.org (Ludovic Court=C3=A8s) wrote: > >> Thinking about it, it wouldn=E2=80=99t matter that HTTP requests are pro= cessed >> sequentially if database queries run really fast. I=E2=80=99m not sure = if we >> can achieve it. WDYT? > > That depends on how fast. But it should be possible to optimize the actu= al > 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 nee= d 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=20 Password:=20 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.deriva= tion =3D Derivations.derivation and Builds.evaluation =3D Derivations.evalu= ation ...> INNER JOIN Evaluations ON Derivations.evaluation =3D Evaluations.id ...> INNER JOIN Specifications ON Evaluations.specification =3D Specific= ations.repo_name; 2156003 sqlite> pragma optimize; sqlite>=20 --8<---------------cut here---------------end--------------->8--- =E2=80=A6 but that doesn=E2=80=99t seem to have any effect, presumably beca= use sqlite3 already optimized whatever it could. Thoughts? Ludo=E2=80=99.