From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp0 ([2001:41d0:2:bcc0::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms11 with LMTPS id 8OLYIVLqfV8pBwAA0tVLHw (envelope-from ) for ; Wed, 07 Oct 2020 16:18:26 +0000 Received: from aspmx2.migadu.com ([2001:41d0:2:bcc0::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp0 with LMTPS id cI7KHVLqfV+eYwAA1q6Kng (envelope-from ) for ; Wed, 07 Oct 2020 16:18:26 +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 aspmx2.migadu.com (Postfix) with ESMTPS id CED556806A1 for ; Wed, 7 Oct 2020 16:18:17 +0000 (UTC) Received: from localhost ([::1]:54126 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kQC8s-0004LZ-TV for larch@yhetil.org; Wed, 07 Oct 2020 12:18:15 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:35638) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kQBx4-00086u-SR for bug-guix@gnu.org; Wed, 07 Oct 2020 12:06:02 -0400 Received: from debbugs.gnu.org ([209.51.188.43]:46249) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1kQBx4-0002P5-H9 for bug-guix@gnu.org; Wed, 07 Oct 2020 12:06:02 -0400 Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2) (envelope-from ) id 1kQBx4-0007XN-AT for bug-guix@gnu.org; Wed, 07 Oct 2020 12:06:02 -0400 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 X-Spam-Score: -3.3 (---) X-BeenThere: bug-guix@gnu.org List-Id: Bug reports for GNU Guix List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: bug-guix-bounces+larch=yhetil.org@gnu.org Sender: "bug-Guix" X-Scanner: scn0 Authentication-Results: aspmx2.migadu.com; dkim=none; dmarc=pass (policy=none) header.from=gnu.org; spf=pass (aspmx2.migadu.com: domain of bug-guix-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=bug-guix-bounces@gnu.org X-Spam-Score: -1.51 X-TUID: arrZ/xQKx2aM 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