From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp2 ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms11 with LMTPS id OP3fA/L+5F7wUQAA0tVLHw (envelope-from ) for ; Sat, 13 Jun 2020 16:29:38 +0000 Received: from aspmx1.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp2 with LMTPS id sKtAO/H+5F4ZKgAAB5/wlQ (envelope-from ) for ; Sat, 13 Jun 2020 16:29:37 +0000 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id A6A589404E2 for ; Sat, 13 Jun 2020 16:29:37 +0000 (UTC) Received: from localhost ([::1]:53686 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1jk92G-0006Rx-LG for larch@yhetil.org; Sat, 13 Jun 2020 12:29:36 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:52232) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1jk926-0006Qd-AY for guix-devel@gnu.org; Sat, 13 Jun 2020 12:29:26 -0400 Received: from mira.cbaines.net ([2a01:7e00:e000:2f8:fd4d:b5c7:13fb:3d27]:53467) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1jk924-00072i-Gz for guix-devel@gnu.org; Sat, 13 Jun 2020 12:29:26 -0400 Received: from localhost (unknown [46.237.175.96]) by mira.cbaines.net (Postfix) with ESMTPSA id EB33027BBE1 for ; Sat, 13 Jun 2020 17:29:22 +0100 (BST) Received: from localhost (localhost [local]) by localhost (OpenSMTPD) with ESMTPA id f098e6bd for ; Sat, 13 Jun 2020 16:29:20 +0000 (UTC) References: <20200609205342.613-1-mail@cbaines.net> User-agent: mu4e 1.2.0; emacs 26.3 From: Christopher Baines To: guix-devel@gnu.org Subject: Re: [PATCH] sql: Add a couple of indexes. In-reply-to: <20200609205342.613-1-mail@cbaines.net> Date: Sat, 13 Jun 2020 17:29:18 +0100 Message-ID: <87mu577wv5.fsf@cbaines.net> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha512; protocol="application/pgp-signature" Received-SPF: pass client-ip=2a01:7e00:e000:2f8:fd4d:b5c7:13fb:3d27; envelope-from=mail@cbaines.net; helo=mira.cbaines.net X-detected-operating-system: by eggs.gnu.org: First seen = 2020/06/13 12:29:23 X-ACL-Warn: Detected OS = ??? X-Spam_score_int: -18 X-Spam_score: -1.9 X-Spam_bar: - X-Spam_report: (-1.9 / 5.0 requ) BAYES_00=-1.9, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001, UNPARSEABLE_RELAY=0.001 autolearn=_AUTOLEARN X-Spam_action: no action X-BeenThere: guix-devel@gnu.org X-Mailman-Version: 2.1.23 Precedence: list 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+larch=yhetil.org@gnu.org Sender: "Guix-devel" X-Scanner: scn0 Authentication-Results: aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of guix-devel-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=guix-devel-bounces@gnu.org X-Spam-Score: -3.11 X-TUID: GnRhe4S5Kydu --=-=-= Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Christopher Baines 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, timest= amp 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. =E2=86=92 guix weather --substitute-urls=3Dhttps://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 --=-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQKTBAEBCgB9FiEEPonu50WOcg2XVOCyXiijOwuE9XcFAl7k/t5fFIAAAAAALgAo aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNF ODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcACgkQXiijOwuE 9XdbgRAAto/8Q4Cmv0daAGeMlaALA/JYp+JyT7eyXyewYPXmeBNREJjHP//xUmHZ VanYGKz04yT2S4WKS++3HU/YsgbnYrRHp21s4Lu3cpvmiWUxEdZLnOU0iNOLcpB1 HM8I6ie+CaQCmQ41l1/vGW2vJ/x3V3ACMXBCjCBViFQajAwUNEoNIGS05704c34R BpqrNSZsF8TbSvneKkzu00U+Lt9G5P2gI9jm/aTFpwXzOf064KTOiGbwViVQdivn GPYQgO/TDBDyeH+p8YGKwCT1GpMabmMMg9DtqOAXXmaTrsMZlKLW4Pb4uNUSwwsp CpGXWxJQGANAVIm54va+fzKefqW+s9kyhl9kwZO9RkDlxZIO8SZiQlHYrjseDtwx QaEhVhMXutTEn5S5werFUiNdISVfbM/K7WOX/+2bIldqCQhwCiXkBbhBj36JwPVV 4xvc9G92rPqT0qM/aUrSjsoLqBd0iZQV2Lg5KekB1uOzpRBlYc650YE6ekj3QVnG oYe5nZRIPqVBIr2DcMTwktTDx73ndnHZzMo19iKTTxwBFdPrGvmby3dszxUcSgzl kMRplWPoyXSiAJRrMmVgsDhNNUq3GnLXZojKIqDX+Shx9o+77BIilHWsI9PokQHW einoDr9tr4x6U063bdNT6zPiuVA9LCtHntuc4bOXIR0Ze/9Dnpw= =47qm -----END PGP SIGNATURE----- --=-=-=--