Received: (at 43850) by debbugs.gnu.org; 22 Oct 2020 11:49:51 +0000 From debbugs-submit-bounces <at> debbugs.gnu.org Thu Oct 22 07:49:51 2020 Received: from localhost ([127.0.0.1]:51612 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>) id 1kVZ6N-0007K2-Jo for submit <at> 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 <othacehe@HIDDEN>) id 1kVZ6M-0007Jq-FK for 43850 <at> 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 <othacehe@HIDDEN>) id 1kVZ6H-0007iL-5e for 43850 <at> 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 <othacehe@HIDDEN>) id 1kVZ6G-0005Q4-LE for 43850 <at> debbugs.gnu.org; Thu, 22 Oct 2020 07:49:44 -0400 From: Mathieu Othacehe <othacehe@HIDDEN> To: 43850 <at> debbugs.gnu.org Subject: Re: bug#43850: cuirass: inconsistent SQL queries execution time. References: <87a6wyhviz.fsf@HIDDEN> <87362gg35r.fsf@HIDDEN> <87362986k7.fsf@HIDDEN> Date: Thu, 22 Oct 2020 13:49:43 +0200 In-Reply-To: <87362986k7.fsf@HIDDEN> (Mathieu Othacehe's message of "Tue, 20 Oct 2020 13:45:12 +0200") Message-ID: <87blguzdig.fsf@HIDDEN> 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: 43850 X-BeenThere: debbugs-submit <at> debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: <debbugs-submit.debbugs.gnu.org> List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe> List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/> List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org> List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help> List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe> Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org> 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
bug-guix@HIDDEN:bug#43850; Package guix.
Full text available.Received: (at 43850) by debbugs.gnu.org; 20 Oct 2020 11:45:22 +0000 From debbugs-submit-bounces <at> debbugs.gnu.org Tue Oct 20 07:45:22 2020 Received: from localhost ([127.0.0.1]:43814 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>) id 1kUq4w-0002vu-8G for submit <at> 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 <othacehe@HIDDEN>) id 1kUq4u-0002ob-Eg for 43850 <at> 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 <othacehe@HIDDEN>) id 1kUq4p-0003xo-4T for 43850 <at> 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 <othacehe@HIDDEN>) id 1kUq4n-0008Q7-Lg for 43850 <at> debbugs.gnu.org; Tue, 20 Oct 2020 07:45:14 -0400 From: Mathieu Othacehe <othacehe@HIDDEN> To: 43850 <at> debbugs.gnu.org Subject: Re: bug#43850: cuirass: inconsistent SQL queries execution time. References: <87a6wyhviz.fsf@HIDDEN> <87362gg35r.fsf@HIDDEN> Date: Tue, 20 Oct 2020 13:45:12 +0200 In-Reply-To: <87362gg35r.fsf@HIDDEN> (Mathieu Othacehe's message of "Wed, 14 Oct 2020 18:53:20 +0200") Message-ID: <87362986k7.fsf@HIDDEN> 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: 43850 X-BeenThere: debbugs-submit <at> debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: <debbugs-submit.debbugs.gnu.org> List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe> List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/> List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org> List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help> List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe> Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org> 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
bug-guix@HIDDEN:bug#43850; Package guix.
Full text available.Received: (at 43850) by debbugs.gnu.org; 14 Oct 2020 16:53:30 +0000 From debbugs-submit-bounces <at> debbugs.gnu.org Wed Oct 14 12:53:30 2020 Received: from localhost ([127.0.0.1]:52906 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>) id 1kSk1p-0000Kf-Qr for submit <at> 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 <othacehe@HIDDEN>) id 1kSk1o-0000KQ-BZ for 43850 <at> 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 <othacehe@HIDDEN>) id 1kSk1j-0007w2-1a for 43850 <at> 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 <othacehe@HIDDEN>) id 1kSk1i-00034V-Li for 43850 <at> debbugs.gnu.org; Wed, 14 Oct 2020 12:53:22 -0400 From: Mathieu Othacehe <othacehe@HIDDEN> To: 43850 <at> debbugs.gnu.org Subject: Re: bug#43850: cuirass: inconsistent SQL queries execution time. References: <87a6wyhviz.fsf@HIDDEN> Date: Wed, 14 Oct 2020 18:53:20 +0200 In-Reply-To: <87a6wyhviz.fsf@HIDDEN> (Mathieu Othacehe's message of "Wed, 07 Oct 2020 18:04:52 +0200") Message-ID: <87362gg35r.fsf@HIDDEN> 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: 43850 X-BeenThere: debbugs-submit <at> debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: <debbugs-submit.debbugs.gnu.org> List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe> List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/> List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org> List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help> List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe> Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org> 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
bug-guix@HIDDEN:bug#43850; Package guix.
Full text available.Received: (at submit) by debbugs.gnu.org; 7 Oct 2020 16:05:06 +0000 From debbugs-submit-bounces <at> debbugs.gnu.org Wed Oct 07 12:05:06 2020 Received: from localhost ([127.0.0.1]:57792 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from <debbugs-submit-bounces <at> debbugs.gnu.org>) id 1kQBwA-0007Vu-G7 for submit <at> 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 <othacehe@HIDDEN>) id 1kQBw6-0007VV-70 for submit <at> 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 <othacehe@HIDDEN>) id 1kQBw3-0007I8-K7 for bug-guix@HIDDEN; 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 <othacehe@HIDDEN>) id 1kQBw2-0001iz-1Z for bug-guix@HIDDEN; 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 <othacehe@HIDDEN>) id 1kQBvy-0005oC-Nj for bug-guix@HIDDEN; Wed, 07 Oct 2020 12:04:54 -0400 From: Mathieu Othacehe <othacehe@HIDDEN> To: bug-guix@HIDDEN Subject: cuirass: inconsistent SQL queries execution time. Date: Wed, 07 Oct 2020 18:04:52 +0200 Message-ID: <87a6wyhviz.fsf@HIDDEN> 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 <at> debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: <debbugs-submit.debbugs.gnu.org> List-Unsubscribe: <https://debbugs.gnu.org/cgi-bin/mailman/options/debbugs-submit>, <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=unsubscribe> List-Archive: <https://debbugs.gnu.org/cgi-bin/mailman/private/debbugs-submit/> List-Post: <mailto:debbugs-submit <at> debbugs.gnu.org> List-Help: <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=help> List-Subscribe: <https://debbugs.gnu.org/cgi-bin/mailman/listinfo/debbugs-submit>, <mailto:debbugs-submit-request <at> debbugs.gnu.org?subject=subscribe> Errors-To: debbugs-submit-bounces <at> debbugs.gnu.org Sender: "Debbugs-submit" <debbugs-submit-bounces <at> debbugs.gnu.org> 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
Mathieu Othacehe <othacehe@HIDDEN>:bug-guix@HIDDEN.
Full text available.bug-guix@HIDDEN:bug#43850; Package guix.
Full text available.
GNU bug tracking system
Copyright (C) 1999 Darren O. Benham,
1997 nCipher Corporation Ltd,
1994-97 Ian Jackson.