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 UFCFL30Jcl9PBwAA0tVLHw (envelope-from ) for ; Mon, 28 Sep 2020 16:04:13 +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 qGNLK30Jcl+KegAAB5/wlQ (envelope-from ) for ; Mon, 28 Sep 2020 16:04:13 +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 3005B9404C2 for ; Mon, 28 Sep 2020 16:04:13 +0000 (UTC) Received: from localhost ([::1]:34780 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kMvdK-000223-7x for larch@yhetil.org; Mon, 28 Sep 2020 12:04:10 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:57428) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kMvcE-00016w-VF for bug-guix@gnu.org; Mon, 28 Sep 2020 12:03:03 -0400 Received: from debbugs.gnu.org ([209.51.188.43]:42274) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1kMvcE-0003ho-L2 for bug-guix@gnu.org; Mon, 28 Sep 2020 12:03:02 -0400 Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2) (envelope-from ) id 1kMvcE-0001Q4-Hg for bug-guix@gnu.org; Mon, 28 Sep 2020 12:03:02 -0400 Subject: bug#43588: cuirass: Slow SQL queries. Resent-From: Mathieu Othacehe Original-Sender: "Debbugs-submit" Resent-To: bug-guix@gnu.org Resent-Date: Mon, 28 Sep 2020 16:03:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: cc-closed 43588 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: 43588-done@debbugs.gnu.org Mail-Followup-To: 43588@debbugs.gnu.org, othacehe@gnu.org, othacehe@gnu.org Received: via spool by 43588-done@debbugs.gnu.org id=D43588.16013089565400 (code D ref 43588); Mon, 28 Sep 2020 16:03:02 +0000 Received: (at 43588-done) by debbugs.gnu.org; 28 Sep 2020 16:02:36 +0000 Received: from localhost ([127.0.0.1]:53819 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kMvbo-0001P0-Ab for submit@debbugs.gnu.org; Mon, 28 Sep 2020 12:02:36 -0400 Received: from eggs.gnu.org ([209.51.188.92]:50526) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kMvbl-0001Oi-H9 for 43588-done@debbugs.gnu.org; Mon, 28 Sep 2020 12:02:34 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:46372) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kMvbe-0003cO-GY for 43588-done@debbugs.gnu.org; Mon, 28 Sep 2020 12:02:27 -0400 Received: from [2a01:e0a:19b:d9a0:d549:2a8f:de3b:c9c0] (port=39312 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kMvbc-0005Yy-3i for 43588-done@debbugs.gnu.org; Mon, 28 Sep 2020 12:02:24 -0400 From: Mathieu Othacehe References: <878scz75u0.fsf@gnu.org> Date: Mon, 28 Sep 2020 18:02:21 +0200 In-Reply-To: <878scz75u0.fsf@gnu.org> (Mathieu Othacehe's message of "Thu, 24 Sep 2020 13:49:11 +0200") Message-ID: <87eemldh4i.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: aspmx1.migadu.com; dkim=none; dmarc=pass (policy=none) header.from=gnu.org; spf=pass (aspmx1.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: HjrR16YpAa+E Hello, > SELECT E.id, E.status, E.timestamp, E.checkouttime, E.evaltime, B.total, B.succeeded, B.failed, B.scheduled FROM (SELECT id, status, timestamp, checkouttime, evaltime FROM Evaluations WHERE (id='8255' )) E LEFT JOIN (SELECT rowid, evaluation, SUM(status=0) as succeeded, SUM(status>0) as failed, SUM(status<0) as scheduled, SUM(status>-100) as total FROM Builds GROUP BY evaluation) B ON B.evaluation=E.id ORDER BY E.id ASC 86.23 Turns out those queries are really slow because they are doing full scans of the Builds table. Using "EXPLAIN QUERY PLAN" shows that this is caused by the "GROUP BY" in the "B" sub-query. Running the above query takes around 3 seconds right now. I think that it took 86.23 seconds because the WAL file contained a lot of temporary data, slowing down the whole query. I ran a "PRAGMA wal_checkpoint(FULL)" as those queries were started to slow down and observed an immediate improvement. Anyway, the real problem here is just highlighted by the WAL file usage. Those queries can really go faster. I rewrote them and added a few indexes with 0ffcb80ebbaa2b177f03548035a2ef21ae7ac41d. They now take less than 1ms, which should very much improve the web browsing experience as well as decrease the database worker starvation. Thanks, Mathieu -- https://othacehe.org