From unknown Sun Aug 17 10:19:28 2025 X-Loop: help-debbugs@gnu.org Subject: bug#43850: cuirass: inconsistent SQL queries execution time. Resent-From: Mathieu Othacehe Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Wed, 07 Oct 2020 16:06:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: report 43850 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: 43850@debbugs.gnu.org X-Debbugs-Original-To: bug-guix@gnu.org Received: via spool by submit@debbugs.gnu.org id=B.160208670628891 (code B ref -1); Wed, 07 Oct 2020 16:06:02 +0000 Received: (at submit) by debbugs.gnu.org; 7 Oct 2020 16:05:06 +0000 Received: from localhost ([127.0.0.1]:57792 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kQBwA-0007Vu-G7 for submit@debbugs.gnu.org; Wed, 07 Oct 2020 12:05:06 -0400 Received: from lists.gnu.org ([209.51.188.17]:40864) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kQBw6-0007VV-70 for submit@debbugs.gnu.org; Wed, 07 Oct 2020 12:05:05 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:35432) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kQBw3-0007I8-K7 for bug-guix@gnu.org; Wed, 07 Oct 2020 12:05:01 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:47221) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kQBw2-0001iz-1Z for bug-guix@gnu.org; Wed, 07 Oct 2020 12:04:59 -0400 Received: from [2a01:e0a:19b:d9a0:4192:d6b6:52f:260e] (port=51108 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kQBvy-0005oC-Nj for bug-guix@gnu.org; Wed, 07 Oct 2020 12:04:54 -0400 From: Mathieu Othacehe Date: Wed, 07 Oct 2020 18:04:52 +0200 Message-ID: <87a6wyhviz.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: -2.3 (--) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) Hello, Over the last few weeks I made sure that all Cuirass SQL queries were using indexes. As the "Builds" and "Outputs" tables can be really large, having queries covered by indexes is imperative for consistent queries duration. However, I observed that some queries have inconsistent duration. --8<---------------cut here---------------start------------->8--- 2020-10-07T17:59:09 Database worker unresponsive for 5 seconds (db-get-builds-max). 2020-10-07T17:59:13 GET /build/3183151/details 2020-10-07T17:59:13 GET /eval/92 2020-10-07T17:59:14 builds request took 3.66e-4 seconds 2020-10-07T17:59:14 Database worker unresponsive for 5 seconds (db-get-builds-max). 2020-10-07T17:59:15 GET /eval/13234 2020-10-07T17:59:16 GET /build/3146487/details 2020-10-07T17:59:19 Database worker unresponsive for 5 seconds (db-get-builds-max). --8<---------------cut here---------------end--------------->8--- "db-get-builds-max" query has the following query plan: --8<---------------cut here---------------start------------->8--- QUERY PLAN |--SEARCH TABLE Builds USING INDEX Builds_stoptime_id (stoptime=?) `--SCALAR SUBQUERY 1 `--SEARCH TABLE Builds USING INDEX Builds_evaluation_index (evaluation=?) --8<---------------cut here---------------end--------------->8--- so it should always reasonably fast. The log seem to indicate that the worker running one of those queries is busy for more than 10 seconds here. My understanding of SQLite WAL mode is that writers should not block readers. So I don't get why this query can be intermittently so slow. Thanks, Mathieu -- https://othacehe.org From unknown Sun Aug 17 10:19:28 2025 X-Loop: help-debbugs@gnu.org Subject: bug#43850: cuirass: inconsistent SQL queries execution time. Resent-From: Mathieu Othacehe Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Wed, 14 Oct 2020 16:54:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 43850 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: 43850@debbugs.gnu.org Received: via spool by 43850-submit@debbugs.gnu.org id=B43850.16026944101285 (code B ref 43850); Wed, 14 Oct 2020 16:54:02 +0000 Received: (at 43850) by debbugs.gnu.org; 14 Oct 2020 16:53:30 +0000 Received: from localhost ([127.0.0.1]:52906 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kSk1p-0000Kf-Qr for submit@debbugs.gnu.org; Wed, 14 Oct 2020 12:53:30 -0400 Received: from eggs.gnu.org ([209.51.188.92]:34014) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kSk1o-0000KQ-BZ for 43850@debbugs.gnu.org; Wed, 14 Oct 2020 12:53:28 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:32822) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kSk1j-0007w2-1a for 43850@debbugs.gnu.org; Wed, 14 Oct 2020 12:53:23 -0400 Received: from [2a01:cb18:832e:5f00:1013:8f9a:f686:d929] (port=37156 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kSk1i-00034V-Li for 43850@debbugs.gnu.org; Wed, 14 Oct 2020 12:53:22 -0400 From: Mathieu Othacehe References: <87a6wyhviz.fsf@gnu.org> Date: Wed, 14 Oct 2020 18:53:20 +0200 In-Reply-To: <87a6wyhviz.fsf@gnu.org> (Mathieu Othacehe's message of "Wed, 07 Oct 2020 18:04:52 +0200") Message-ID: <87362gg35r.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: -2.3 (--) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) Hello, I pushed and deployed several patches that: - update metrics in a single transaction - register builds in a single transaction - use a single write database worker, queuing queries and submitting them by batches (in a single transaction). - optimize some SQLite parameters (decrease WAL size, use more RAM caching) this should improve the situation, even if I still observe some inconsistent execution duration. I still have a few improvement ideas such as pre-allocating a large database file to decrease fragmentation and running periodic vacuums. Thanks, Mathieu From unknown Sun Aug 17 10:19:28 2025 X-Loop: help-debbugs@gnu.org Subject: bug#43850: cuirass: inconsistent SQL queries execution time. Resent-From: Mathieu Othacehe Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Tue, 20 Oct 2020 11:46:01 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 43850 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: 43850@debbugs.gnu.org Received: via spool by 43850-submit@debbugs.gnu.org id=B43850.160319432211323 (code B ref 43850); Tue, 20 Oct 2020 11:46:01 +0000 Received: (at 43850) by debbugs.gnu.org; 20 Oct 2020 11:45:22 +0000 Received: from localhost ([127.0.0.1]:43814 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kUq4w-0002vu-8G for submit@debbugs.gnu.org; Tue, 20 Oct 2020 07:45:22 -0400 Received: from eggs.gnu.org ([209.51.188.92]:51582) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kUq4u-0002ob-Eg for 43850@debbugs.gnu.org; Tue, 20 Oct 2020 07:45:20 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:54254) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kUq4p-0003xo-4T for 43850@debbugs.gnu.org; Tue, 20 Oct 2020 07:45:15 -0400 Received: from [2a01:e0a:19b:d9a0:50c1:4a69:300e:4531] (port=53436 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kUq4n-0008Q7-Lg for 43850@debbugs.gnu.org; Tue, 20 Oct 2020 07:45:14 -0400 From: Mathieu Othacehe References: <87a6wyhviz.fsf@gnu.org> <87362gg35r.fsf@gnu.org> Date: Tue, 20 Oct 2020 13:45:12 +0200 In-Reply-To: <87362gg35r.fsf@gnu.org> (Mathieu Othacehe's message of "Wed, 14 Oct 2020 18:53:20 +0200") Message-ID: <87362986k7.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: -2.3 (--) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) Hello, > this should improve the situation, even if I still observe some > inconsistent execution duration. I tried to use the two following pragma: --8<---------------cut here---------------start------------->8--- PRAGMA synchronous = OFF PRAGMA mmap_size = 10737418240 --8<---------------cut here---------------end--------------->8--- hoping that mmaping the Cuirass database and disabling synchronisation would help, but there were still inconsistent duration. I have now copied the database to a tmpfs mounted directory to make sure that those inconsistent duration are only caused by the I/O pressure on berlin. Thanks, Mathieu From unknown Sun Aug 17 10:19:28 2025 X-Loop: help-debbugs@gnu.org Subject: bug#43850: cuirass: inconsistent SQL queries execution time. Resent-From: Mathieu Othacehe Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Thu, 22 Oct 2020 11:50:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 43850 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: 43850@debbugs.gnu.org Received: via spool by 43850-submit@debbugs.gnu.org id=B43850.160336739128157 (code B ref 43850); Thu, 22 Oct 2020 11:50:02 +0000 Received: (at 43850) by debbugs.gnu.org; 22 Oct 2020 11:49:51 +0000 Received: from localhost ([127.0.0.1]:51612 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kVZ6N-0007K2-Jo for submit@debbugs.gnu.org; Thu, 22 Oct 2020 07:49:51 -0400 Received: from eggs.gnu.org ([209.51.188.92]:34308) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kVZ6M-0007Jq-FK for 43850@debbugs.gnu.org; Thu, 22 Oct 2020 07:49:50 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:41771) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kVZ6H-0007iL-5e for 43850@debbugs.gnu.org; Thu, 22 Oct 2020 07:49:45 -0400 Received: from [2a01:e0a:19b:d9a0:50c1:4a69:300e:4531] (port=44898 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kVZ6G-0005Q4-LE for 43850@debbugs.gnu.org; Thu, 22 Oct 2020 07:49:44 -0400 From: Mathieu Othacehe References: <87a6wyhviz.fsf@gnu.org> <87362gg35r.fsf@gnu.org> <87362986k7.fsf@gnu.org> Date: Thu, 22 Oct 2020 13:49:43 +0200 In-Reply-To: <87362986k7.fsf@gnu.org> (Mathieu Othacehe's message of "Tue, 20 Oct 2020 13:45:12 +0200") Message-ID: <87blguzdig.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: -2.3 (--) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) Hello, > I have now copied the database to a tmpfs mounted directory to make sure > that those inconsistent duration are only caused by the I/O pressure on > berlin. This helps a lot. The Cuirass web service has been running smooth since two days, without any inconsistent query times. I'm considering using a tmpfs backed database for good. The problem is that we would need a save/restore mechanism in case Berlin reboots. WDYT? Thanks, Mathieu From unknown Sun Aug 17 10:19:28 2025 X-Loop: help-debbugs@gnu.org Subject: bug#43850: cuirass: inconsistent SQL queries execution time. Resent-From: Ludovic =?UTF-8?Q?Court=C3=A8s?= Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Mon, 26 Oct 2020 23:01:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 43850 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: Mathieu Othacehe Cc: 43850@debbugs.gnu.org Received: via spool by 43850-submit@debbugs.gnu.org id=B43850.16037532043742 (code B ref 43850); Mon, 26 Oct 2020 23:01:02 +0000 Received: (at 43850) by debbugs.gnu.org; 26 Oct 2020 23:00:04 +0000 Received: from localhost ([127.0.0.1]:41962 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kXBTA-0000yH-JC for submit@debbugs.gnu.org; Mon, 26 Oct 2020 19:00:04 -0400 Received: from eggs.gnu.org ([209.51.188.92]:52384) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kXBT9-0000xE-2d for 43850@debbugs.gnu.org; Mon, 26 Oct 2020 19:00:03 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:37224) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kXBT3-00051S-OJ for 43850@debbugs.gnu.org; Mon, 26 Oct 2020 18:59:57 -0400 Received: from [2a01:e0a:1d:7270:af76:b9b:ca24:c465] (port=37664 helo=ribbon) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kXBSy-0003xQ-M1; Mon, 26 Oct 2020 18:59:56 -0400 From: Ludovic =?UTF-8?Q?Court=C3=A8s?= References: <87a6wyhviz.fsf@gnu.org> <87362gg35r.fsf@gnu.org> <87362986k7.fsf@gnu.org> <87blguzdig.fsf@gnu.org> X-URL: http://www.fdn.fr/~lcourtes/ X-Revolutionary-Date: 5 Brumaire an 229 de la =?UTF-8?Q?R=C3=A9volution?= X-PGP-Key-ID: 0x090B11993D9AEBB5 X-PGP-Key: http://www.fdn.fr/~lcourtes/ludovic.asc X-PGP-Fingerprint: 3CE4 6455 8A84 FDC6 9DB4 0CFB 090B 1199 3D9A EBB5 X-OS: x86_64-pc-linux-gnu Date: Mon, 26 Oct 2020 23:59:51 +0100 In-Reply-To: <87blguzdig.fsf@gnu.org> (Mathieu Othacehe's message of "Thu, 22 Oct 2020 13:49:43 +0200") Message-ID: <87blgo8ufs.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Spam-Score: -2.3 (--) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) Hi, Mathieu Othacehe skribis: >> I have now copied the database to a tmpfs mounted directory to make sure >> that those inconsistent duration are only caused by the I/O pressure on >> berlin. > > This helps a lot. The Cuirass web service has been running smooth since > two days, without any inconsistent query times. Interesting. > I'm considering using a tmpfs backed database for good. The problem is > that we would need a save/restore mechanism in case Berlin > reboots. Hmm sounds risky, no? I wonder if we could instead ensure no I/O-intensive workload runs that machine. I=E2=80=99m thinking in particular of the derivations that produce ISO/qcow images that are not offloaded but maybe should. WDYT? Do you think that=E2=80=99d be enough? Or is tmpfs our only hope? Thanks, Ludo=E2=80=99. From unknown Sun Aug 17 10:19:28 2025 X-Loop: help-debbugs@gnu.org Subject: bug#43850: cuirass: inconsistent SQL queries execution time. Resent-From: Christopher Baines Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Tue, 27 Oct 2020 09:29:01 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 43850 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: Ludovic =?UTF-8?Q?Court=C3=A8s?= , Mathieu Othacehe Cc: 43850@debbugs.gnu.org Received: via spool by 43850-submit@debbugs.gnu.org id=B43850.160379094124782 (code B ref 43850); Tue, 27 Oct 2020 09:29:01 +0000 Received: (at 43850) by debbugs.gnu.org; 27 Oct 2020 09:29:01 +0000 Received: from localhost ([127.0.0.1]:42893 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kXLHp-0006Ra-33 for submit@debbugs.gnu.org; Tue, 27 Oct 2020 05:29:01 -0400 Received: from mira.cbaines.net ([212.71.252.8]:39958) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kXLHn-0006RS-3O for 43850@debbugs.gnu.org; Tue, 27 Oct 2020 05:28:59 -0400 Received: from localhost (92.41.179.191.threembb.co.uk [92.41.179.191]) by mira.cbaines.net (Postfix) with ESMTPSA id DBEDC27BBF2; Tue, 27 Oct 2020 09:28:57 +0000 (GMT) Received: from capella (localhost [127.0.0.1]) by localhost (OpenSMTPD) with ESMTP id 031037c7; Tue, 27 Oct 2020 09:28:55 +0000 (UTC) References: <87a6wyhviz.fsf@gnu.org> <87362gg35r.fsf@gnu.org> <87362986k7.fsf@gnu.org> <87blguzdig.fsf@gnu.org> <87blgo8ufs.fsf@gnu.org> User-agent: mu4e 1.4.13; emacs 27.1 From: Christopher Baines In-reply-to: <87blgo8ufs.fsf@gnu.org> Date: Tue, 27 Oct 2020 09:28:54 +0000 Message-ID: <87d014yq3t.fsf@cbaines.net> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha512; protocol="application/pgp-signature" X-Spam-Score: -0.0 (/) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) --=-=-= Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Ludovic Court=C3=A8s writes: > Hi, > > Mathieu Othacehe skribis: > >>> I have now copied the database to a tmpfs mounted directory to make sure >>> that those inconsistent duration are only caused by the I/O pressure on >>> berlin. >> >> This helps a lot. The Cuirass web service has been running smooth since >> two days, without any inconsistent query times. > > Interesting. > >> I'm considering using a tmpfs backed database for good. The problem is >> that we would need a save/restore mechanism in case Berlin >> reboots. > > Hmm sounds risky, no? > > I wonder if we could instead ensure no I/O-intensive workload runs that > machine. I=E2=80=99m thinking in particular of the derivations that prod= uce > ISO/qcow images that are not offloaded but maybe should. > > WDYT? Do you think that=E2=80=99d be enough? Or is tmpfs our only hope? I think Ricardo mentioned that the machine running Cuirass uses an SSD for the root filesystem, so moving the database there may help? --=-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAl+X6FZfFIAAAAAALgAo aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNF ODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2Jh aW5lcy5uZXQACgkQXiijOwuE9XeKAA//W42HvAHLrMfMRdUSWjwc/r7CGqtznUtu FvRaNWC3wuZEF8G3PVxWg6+4Hql7FjoT1m5c79/EOKwiOM2vfPs4djKAHRwdWNZe xCevpKcuLuYV8lqergrmPUgZVaNYAZ1kZm9ysQHwnrreokuVaaRJMOJBkOglnlAG a9mNAHyWR+SXQdPgm0FGDWcaI6k6r9LsqYorKu3xxgwIP1kcPQFWn2b0WpHMMyUu o/VSNvZX+X/P2fpzH7yaPCpjV90iOwvPSbvF/OlwRemVGKt7wHvUdJu8Vyxb3L9y 6S2UXXAqnvOPs9X0yNnAQntYVkqPVu/Fz2kkSDFGIV6Y2xs82aGVGNSijvPf+AIx pRzzVx4XFyvCUMSLm7JHyxIwBAm/Tj/EVeOeIrKaZrCf3mAB2D9VjDej2oTcmcK2 h0p9mP4du6kNb6ee/qfFLB8dxD2z5kYjlUIPLk9thmDvNSfum7Lh29JJewiGul0Z gwN49Ghalc5Ch0otIm3bGBGucsDfCgrKtLUTYjWbOMqv0PZTcKvQj8X/m3yyqcbO pTK1PsbjfA9BVOdaXxDDxJvZH2Vl8gr4EjxI7Gq0bzgxV6C6I4xwKYh3YAP7hBsG PgRQ5LsBXYMWA50NesziZDTjr4A5TqGXfJDGsZdd4YqdJCgIlujDqQs/9QsOVeFh 9dvkk47YyIA= =54te -----END PGP SIGNATURE----- --=-=-=-- From unknown Sun Aug 17 10:19:28 2025 X-Loop: help-debbugs@gnu.org Subject: bug#43850: cuirass: inconsistent SQL queries execution time. Resent-From: Mathieu Othacehe Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Tue, 27 Oct 2020 14:11:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 43850 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: Christopher Baines Cc: 43850@debbugs.gnu.org, Ludovic =?UTF-8?Q?Court=C3=A8s?= Received: via spool by 43850-submit@debbugs.gnu.org id=B43850.160380781313890 (code B ref 43850); Tue, 27 Oct 2020 14:11:02 +0000 Received: (at 43850) by debbugs.gnu.org; 27 Oct 2020 14:10:13 +0000 Received: from localhost ([127.0.0.1]:45349 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kXPfx-0003by-HU for submit@debbugs.gnu.org; Tue, 27 Oct 2020 10:10:13 -0400 Received: from eggs.gnu.org ([209.51.188.92]:40110) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kXPfw-0003bj-LA for 43850@debbugs.gnu.org; Tue, 27 Oct 2020 10:10:12 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:51246) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kXPfq-0006VA-C3; Tue, 27 Oct 2020 10:10:06 -0400 Received: from [2a01:e0a:19b:d9a0:50c1:4a69:300e:4531] (port=38332 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kXPfp-0000H3-96; Tue, 27 Oct 2020 10:10:05 -0400 From: Mathieu Othacehe References: <87a6wyhviz.fsf@gnu.org> <87362gg35r.fsf@gnu.org> <87362986k7.fsf@gnu.org> <87blguzdig.fsf@gnu.org> <87blgo8ufs.fsf@gnu.org> <87d014yq3t.fsf@cbaines.net> Date: Tue, 27 Oct 2020 15:10:03 +0100 In-Reply-To: <87d014yq3t.fsf@cbaines.net> (Christopher Baines's message of "Tue, 27 Oct 2020 09:28:54 +0000") Message-ID: <871rhjyd38.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: -2.3 (--) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) Hello Chris, > I think Ricardo mentioned that the machine running Cuirass uses an SSD > for the root filesystem, so moving the database there may help? Looks like the database was already on the SSD before my tmpfs experiment. --8<---------------cut here---------------start------------->8--- mathieu@berlin ~$ df -h Filesystem Size Used Avail Use% Mounted on none 95G 0 95G 0% /dev /dev/sda1 916G 321G 549G 37% / /dev/sdb1 37T 34T 2.6T 94% /gnu tmpfs 95G 8.0K 95G 1% /dev/shm tmpfs 10G 2.4G 7.7G 24% /var/lib/cuirass_tmpfs --8<---------------cut here---------------end--------------->8--- I don't really get why I/O pressure on /dev/sdb could impact /dev/sda. Thanks, Mathieu From unknown Sun Aug 17 10:19:28 2025 X-Loop: help-debbugs@gnu.org Subject: bug#43850: cuirass: inconsistent SQL queries execution time. Resent-From: Mathieu Othacehe Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Tue, 27 Oct 2020 18:12:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 43850 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: Christopher Baines Cc: 43850@debbugs.gnu.org, Ludovic =?UTF-8?Q?Court=C3=A8s?= Received: via spool by 43850-submit@debbugs.gnu.org id=B43850.160382231513307 (code B ref 43850); Tue, 27 Oct 2020 18:12:02 +0000 Received: (at 43850) by debbugs.gnu.org; 27 Oct 2020 18:11:55 +0000 Received: from localhost ([127.0.0.1]:45754 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kXTRr-0003SY-J0 for submit@debbugs.gnu.org; Tue, 27 Oct 2020 14:11:55 -0400 Received: from eggs.gnu.org ([209.51.188.92]:55096) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kXTRq-0003SM-BL for 43850@debbugs.gnu.org; Tue, 27 Oct 2020 14:11:54 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:55544) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kXTRh-0001Nt-TA; Tue, 27 Oct 2020 14:11:47 -0400 Received: from [2a01:e0a:19b:d9a0:50c1:4a69:300e:4531] (port=40566 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kXTRR-0000iS-PE; Tue, 27 Oct 2020 14:11:38 -0400 From: Mathieu Othacehe References: <87a6wyhviz.fsf@gnu.org> <87362gg35r.fsf@gnu.org> <87362986k7.fsf@gnu.org> <87blguzdig.fsf@gnu.org> <87blgo8ufs.fsf@gnu.org> <87d014yq3t.fsf@cbaines.net> <871rhjyd38.fsf@gnu.org> Date: Tue, 27 Oct 2020 19:11:28 +0100 In-Reply-To: <871rhjyd38.fsf@gnu.org> (Mathieu Othacehe's message of "Tue, 27 Oct 2020 15:10:03 +0100") Message-ID: <87lffrh73j.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: -2.3 (--) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) > I don't really get why I/O pressure on /dev/sdb could impact /dev/sda. Turns out /tmp is mounted on /dev/sda, so all the building and ISO production are first written on /dev/sda before being copied to the store in /dev/sdb. Reducing the build activity of berlin, as Ludo proposed should help then. Thanks, Mathieu From unknown Sun Aug 17 10:19:28 2025 X-Loop: help-debbugs@gnu.org Subject: bug#43850: cuirass: inconsistent SQL queries execution time. Resent-From: Maxim Cournoyer Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Sat, 31 Oct 2020 04:34:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 43850 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: Mathieu Othacehe Cc: 43850@debbugs.gnu.org, Christopher Baines Received: via spool by 43850-submit@debbugs.gnu.org id=B43850.160411880923485 (code B ref 43850); Sat, 31 Oct 2020 04:34:02 +0000 Received: (at 43850) by debbugs.gnu.org; 31 Oct 2020 04:33:29 +0000 Received: from localhost ([127.0.0.1]:60307 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kYiZr-00066Z-B3 for submit@debbugs.gnu.org; Sat, 31 Oct 2020 00:33:29 -0400 Received: from mail-io1-f51.google.com ([209.85.166.51]:45217) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kYiZo-00066M-AB for 43850@debbugs.gnu.org; Sat, 31 Oct 2020 00:33:17 -0400 Received: by mail-io1-f51.google.com with SMTP id s7so9600417iol.12 for <43850@debbugs.gnu.org>; Fri, 30 Oct 2020 21:33:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:cc:subject:references:date:in-reply-to:message-id :user-agent:mime-version; bh=UMoe0oBWhjGEBQPe12AmOztGxYodtzQJAsu/pZIkvwE=; b=uwUVmpOiWLUzwLFoFEkdu4zrhyPx+9GDvoqNk1IknXCYqo+kSIcYfkcj/VNvdBJPOA D/zxA8rIC02UES+d471HSK17rYO/xRXFzA2wNwk31KyH2a5XVY4a+aU9snFsUPoBQoYr wYou9K7BhJXJy+OfhWUPAbYDenGAfIkiTEdI799TyMsaOesarYK05eKcQJgxCvsuAsAY WTUQNb/q1anXJYhu7WMlEWYYLMdfhL8J0aXdJbIZnGIEXV1VCftgGTLnTKmC2P1gkx6z yZ9Qy2zxyGmeL9i+CYbfXLQXisY3LYRj04xQGlf58aShdoWiEq3UpjXeoYKVDjVMd0He yCfQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:cc:subject:references:date:in-reply-to :message-id:user-agent:mime-version; bh=UMoe0oBWhjGEBQPe12AmOztGxYodtzQJAsu/pZIkvwE=; b=XB2kxo06p5nXox5hNpSw2uCkMaFgVhjFMZHkuteuHAq+CNo2/oHhOiyH6VpDAdGn3g I/cSZcAodwef03lppZUX0k6Jzb9eoyj+An4kR2afnz3hBnFFwUVD4DCuyz7N30bZsjiq zocpAG3NDbnmERlTFZJlyUE+udoCxc4ONPDUrrIU4xH+yD8ok4EnDO+GtSYR59JiVLhm 2SJtEKIgkkIA6pNHsdAgfwl7W35SyrzAagna+ZSLWPwqlnKXOP/xytuhvTFKUbP2VrkJ lSWLldmU3ormMEOP7uxX0H5QmcTvnPe9BhjPqxfEsV6ksZRAhzaOuT00VI64HYELVPXF FPZA== X-Gm-Message-State: AOAM533KF8Wv0jk+rtS2HlK/0ZyEyrk8srQywkuBkNPtMbS0jwWnNBil 5rM4CN19gq3TCDrO8KL12hSdjiH3J5ee4g== X-Google-Smtp-Source: ABdhPJzEEFlaCIjag7o/wuULM8ITZtRF0/4z+js4+uU9GUe1dZK7EGTeGZuskUPpSbV4rtJg4FcRdg== X-Received: by 2002:a6b:bbc6:: with SMTP id l189mr4201337iof.145.1604118790561; Fri, 30 Oct 2020 21:33:10 -0700 (PDT) Received: from hurd (dsl-236-123-160.b2b2c.ca. [207.236.123.160]) by smtp.gmail.com with ESMTPSA id r4sm6072806ilj.43.2020.10.30.21.33.09 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 30 Oct 2020 21:33:09 -0700 (PDT) From: Maxim Cournoyer References: <87a6wyhviz.fsf@gnu.org> <87362gg35r.fsf@gnu.org> <87362986k7.fsf@gnu.org> <87blguzdig.fsf@gnu.org> <87blgo8ufs.fsf@gnu.org> <87d014yq3t.fsf@cbaines.net> <871rhjyd38.fsf@gnu.org> Date: Sat, 31 Oct 2020 00:33:08 -0400 In-Reply-To: <871rhjyd38.fsf@gnu.org> (Mathieu Othacehe's message of "Tue, 27 Oct 2020 15:10:03 +0100") Message-ID: <87wnz7xbej.fsf@gmail.com> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: 0.0 (/) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) Hello, Mathieu Othacehe writes: > Hello Chris, > >> I think Ricardo mentioned that the machine running Cuirass uses an SSD >> for the root filesystem, so moving the database there may help? > > Looks like the database was already on the SSD before my tmpfs > experiment. > > mathieu@berlin ~$ df -h > Filesystem Size Used Avail Use% Mounted on > none 95G 0 95G 0% /dev > /dev/sda1 916G 321G 549G 37% / > /dev/sdb1 37T 34T 2.6T 94% /gnu > tmpfs 95G 8.0K 95G 1% /dev/shm > tmpfs 10G 2.4G 7.7G 24% /var/lib/cuirass_tmpfs > > I don't really get why I/O pressure on /dev/sdb could impact /dev/sda. > > Thanks, > > Mathieu As an aside, running --8<---------------cut here---------------start------------->8--- sudo sqlite3 /var/guix/db/db.sqlite vacuum --8<---------------cut here---------------end--------------->8--- shaved off some 40 Mb from my large database file: -rw-r--r-- 1 root root 468889600 Oct 31 00:16 db.sqlite -rw-r--r-- 1 root root 510648320 Oct 28 23:36 db.sqlite.bak Perhaps we should run 'vacuum' when invoking 'guix gc' or at some other key places (where lots of data gets removed from the DB). There's also the auto_vacuum PRAGMA, which is not enabled currently: --8<---------------cut here---------------start------------->8--- sqlite3 /var/guix/db/db.sqlite 'pragma auto_vacuum' 0 --8<---------------cut here---------------end--------------->8--- But the later doesn't necessarily sound like a good idea: Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse. [0] [0]: https://www.sqlite.org/pragma.html#pragma_auto_vacuum Maxim From unknown Sun Aug 17 10:19:28 2025 X-Loop: help-debbugs@gnu.org Subject: bug#43850: cuirass: inconsistent SQL queries execution time. Resent-From: Mathieu Othacehe Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Sun, 01 Nov 2020 18:24:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 43850 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: Maxim Cournoyer Cc: 43850@debbugs.gnu.org, Christopher Baines Received: via spool by 43850-submit@debbugs.gnu.org id=B43850.160425500532219 (code B ref 43850); Sun, 01 Nov 2020 18:24:02 +0000 Received: (at 43850) by debbugs.gnu.org; 1 Nov 2020 18:23:25 +0000 Received: from localhost ([127.0.0.1]:38459 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kZI0j-0008Nb-8l for submit@debbugs.gnu.org; Sun, 01 Nov 2020 13:23:25 -0500 Received: from eggs.gnu.org ([209.51.188.92]:59606) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kZI0h-0008NO-Bj for 43850@debbugs.gnu.org; Sun, 01 Nov 2020 13:23:23 -0500 Received: from fencepost.gnu.org ([2001:470:142:3::e]:58728) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kZI0c-0002Is-4I; Sun, 01 Nov 2020 13:23:18 -0500 Received: from [2a01:e0a:19b:d9a0:1813:def9:2dcb:55f0] (port=39404 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kZI0b-0000y0-Co; Sun, 01 Nov 2020 13:23:17 -0500 From: Mathieu Othacehe References: <87a6wyhviz.fsf@gnu.org> <87362gg35r.fsf@gnu.org> <87362986k7.fsf@gnu.org> <87blguzdig.fsf@gnu.org> <87blgo8ufs.fsf@gnu.org> <87d014yq3t.fsf@cbaines.net> <871rhjyd38.fsf@gnu.org> <87wnz7xbej.fsf@gmail.com> Date: Sun, 01 Nov 2020 19:23:14 +0100 In-Reply-To: <87wnz7xbej.fsf@gmail.com> (Maxim Cournoyer's message of "Sat, 31 Oct 2020 00:33:08 -0400") Message-ID: <87v9epkkbx.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: -2.3 (--) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) Hey Maxim, > Perhaps we should run 'vacuum' when invoking 'guix gc' or at some other > key places (where lots of data gets removed from the DB). There's also > the auto_vacuum PRAGMA, which is not enabled currently: Vacuuming periodically seems important, however, it didn't resulted in noticeable improvements during high I/O pressure when I tested it. I still think that we should vacuum periodically, even though it means that all database writes are postponed during the duration of the vacuuming. Having a separate fiber in charge of that operation could work I guess. Thanks, Mathieu From unknown Sun Aug 17 10:19:28 2025 MIME-Version: 1.0 X-Mailer: MIME-tools 5.505 (Entity 5.505) X-Loop: help-debbugs@gnu.org From: help-debbugs@gnu.org (GNU bug Tracking System) To: Mathieu Othacehe Subject: bug#43850: closed (Re: bug#43850: cuirass: inconsistent SQL queries execution time.) Message-ID: References: <87mtur5rku.fsf@gnu.org> <87a6wyhviz.fsf@gnu.org> X-Gnu-PR-Message: they-closed 43850 X-Gnu-PR-Package: guix Reply-To: 43850@debbugs.gnu.org Date: Thu, 25 Mar 2021 12:47:02 +0000 Content-Type: multipart/mixed; boundary="----------=_1616676422-23651-1" This is a multi-part message in MIME format... ------------=_1616676422-23651-1 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" Your bug report #43850: cuirass: inconsistent SQL queries execution time. which was filed against the guix package, has been closed. The explanation is attached below, along with your original report. If you require more details, please reply to 43850@debbugs.gnu.org. --=20 43850: http://debbugs.gnu.org/cgi/bugreport.cgi?bug=3D43850 GNU Bug Tracking System Contact help-debbugs@gnu.org with problems ------------=_1616676422-23651-1 Content-Type: message/rfc822 Content-Disposition: inline Content-Transfer-Encoding: 7bit Received: (at 43850-done) by debbugs.gnu.org; 25 Mar 2021 12:46:36 +0000 Received: from localhost ([127.0.0.1]:37417 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1lPPNk-00068j-Cz for submit@debbugs.gnu.org; Thu, 25 Mar 2021 08:46:36 -0400 Received: from eggs.gnu.org ([209.51.188.92]:57630) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1lPPNi-00068U-Ax for 43850-done@debbugs.gnu.org; Thu, 25 Mar 2021 08:46:34 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:50071) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1lPPNc-0008CN-4b for 43850-done@debbugs.gnu.org; Thu, 25 Mar 2021 08:46:28 -0400 Received: from [2a01:cb18:832e:5f00:6893:84b0:dbe3:3cb1] (port=44432 helo=mathieu-HP-EliteBook-840-G1) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1lPPNb-0005rH-Hk for 43850-done@debbugs.gnu.org; Thu, 25 Mar 2021 08:46:27 -0400 From: Mathieu Othacehe To: 43850-done@debbugs.gnu.org Subject: Re: bug#43850: cuirass: inconsistent SQL queries execution time. References: <87a6wyhviz.fsf@gnu.org> <87362gg35r.fsf@gnu.org> <87362986k7.fsf@gnu.org> <87blguzdig.fsf@gnu.org> <87blgo8ufs.fsf@gnu.org> <87d014yq3t.fsf@cbaines.net> <871rhjyd38.fsf@gnu.org> <87lffrh73j.fsf@gnu.org> Date: Thu, 25 Mar 2021 13:46:25 +0100 In-Reply-To: <87lffrh73j.fsf@gnu.org> (Mathieu Othacehe's message of "Tue, 27 Oct 2020 19:11:28 +0100") Message-ID: <87mtur5rku.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: -0.7 (/) X-Debbugs-Envelope-To: 43850-done X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.7 (-) Closing as this is not any issue anymore since the switch to PostgreSQL. Mathieu ------------=_1616676422-23651-1 Content-Type: message/rfc822 Content-Disposition: inline Content-Transfer-Encoding: 7bit Received: (at submit) by debbugs.gnu.org; 7 Oct 2020 16:05:06 +0000 Received: from localhost ([127.0.0.1]:57792 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kQBwA-0007Vu-G7 for submit@debbugs.gnu.org; Wed, 07 Oct 2020 12:05:06 -0400 Received: from lists.gnu.org ([209.51.188.17]:40864) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kQBw6-0007VV-70 for submit@debbugs.gnu.org; Wed, 07 Oct 2020 12:05:05 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:35432) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kQBw3-0007I8-K7 for bug-guix@gnu.org; Wed, 07 Oct 2020 12:05:01 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:47221) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kQBw2-0001iz-1Z for bug-guix@gnu.org; Wed, 07 Oct 2020 12:04:59 -0400 Received: from [2a01:e0a:19b:d9a0:4192:d6b6:52f:260e] (port=51108 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kQBvy-0005oC-Nj for bug-guix@gnu.org; Wed, 07 Oct 2020 12:04:54 -0400 From: Mathieu Othacehe To: bug-guix@gnu.org Subject: cuirass: inconsistent SQL queries execution time. Date: Wed, 07 Oct 2020 18:04:52 +0200 Message-ID: <87a6wyhviz.fsf@gnu.org> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: -2.3 (--) X-Debbugs-Envelope-To: submit X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) Hello, Over the last few weeks I made sure that all Cuirass SQL queries were using indexes. As the "Builds" and "Outputs" tables can be really large, having queries covered by indexes is imperative for consistent queries duration. However, I observed that some queries have inconsistent duration. --8<---------------cut here---------------start------------->8--- 2020-10-07T17:59:09 Database worker unresponsive for 5 seconds (db-get-builds-max). 2020-10-07T17:59:13 GET /build/3183151/details 2020-10-07T17:59:13 GET /eval/92 2020-10-07T17:59:14 builds request took 3.66e-4 seconds 2020-10-07T17:59:14 Database worker unresponsive for 5 seconds (db-get-builds-max). 2020-10-07T17:59:15 GET /eval/13234 2020-10-07T17:59:16 GET /build/3146487/details 2020-10-07T17:59:19 Database worker unresponsive for 5 seconds (db-get-builds-max). --8<---------------cut here---------------end--------------->8--- "db-get-builds-max" query has the following query plan: --8<---------------cut here---------------start------------->8--- QUERY PLAN |--SEARCH TABLE Builds USING INDEX Builds_stoptime_id (stoptime=?) `--SCALAR SUBQUERY 1 `--SEARCH TABLE Builds USING INDEX Builds_evaluation_index (evaluation=?) --8<---------------cut here---------------end--------------->8--- so it should always reasonably fast. The log seem to indicate that the worker running one of those queries is busy for more than 10 seconds here. My understanding of SQLite WAL mode is that writers should not block readers. So I don't get why this query can be intermittently so slow. Thanks, Mathieu -- https://othacehe.org ------------=_1616676422-23651-1--