From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp1 ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms11 with LMTPS id qJ8fBzGQbF+RawAA0tVLHw (envelope-from ) for ; Thu, 24 Sep 2020 12:25:21 +0000 Received: from aspmx1.migadu.com ([2001:41d0:2:4a6f::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp1 with LMTPS id QATyAjGQbF+fBwAAbx9fmQ (envelope-from ) for ; Thu, 24 Sep 2020 12:25:21 +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 0B450940717 for ; Thu, 24 Sep 2020 12:25:09 +0000 (UTC) Received: from localhost ([::1]:36188 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kLPm8-0001DK-Jq for larch@yhetil.org; Thu, 24 Sep 2020 07:51:00 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:45750) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kLPlC-0000QJ-7h for bug-guix@gnu.org; Thu, 24 Sep 2020 07:50:02 -0400 Received: from debbugs.gnu.org ([209.51.188.43]:55472) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1kLPlB-0007uT-TG for bug-guix@gnu.org; Thu, 24 Sep 2020 07:50:01 -0400 Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2) (envelope-from ) id 1kLPlB-000646-Q7 for bug-guix@gnu.org; Thu, 24 Sep 2020 07:50:01 -0400 X-Loop: help-debbugs@gnu.org Subject: bug#43588: cuirass: Slow SQL queries. Resent-From: Mathieu Othacehe Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Thu, 24 Sep 2020 11:50:01 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: report 43588 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: 43588@debbugs.gnu.org X-Debbugs-Original-To: bug-guix@gnu.org Received: via spool by submit@debbugs.gnu.org id=B.160094816023238 (code B ref -1); Thu, 24 Sep 2020 11:50:01 +0000 Received: (at submit) by debbugs.gnu.org; 24 Sep 2020 11:49:20 +0000 Received: from localhost ([127.0.0.1]:38776 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kLPkW-00062i-AS for submit@debbugs.gnu.org; Thu, 24 Sep 2020 07:49:20 -0400 Received: from lists.gnu.org ([209.51.188.17]:56938) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kLPkR-00062N-4N for submit@debbugs.gnu.org; Thu, 24 Sep 2020 07:49:18 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:45566) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kLPkQ-00009h-C0 for bug-guix@gnu.org; Thu, 24 Sep 2020 07:49:14 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:59531) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kLPkQ-0007rF-2z for bug-guix@gnu.org; Thu, 24 Sep 2020 07:49:14 -0400 Received: from [2a01:e0a:19b:d9a0:b888:25b1:7546:eb44] (port=36550 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kLPkP-0001q8-FX for bug-guix@gnu.org; Thu, 24 Sep 2020 07:49:13 -0400 From: Mathieu Othacehe Date: Thu, 24 Sep 2020 13:49:11 +0200 Message-ID: <878scz75u0.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: fTDqpd9Cn9IP Hello, I just added SQL queries logging to Cuirass. I also adapted the Guix shepherd service accordingly and deployed it on berlin. The results are already interesting: --8<---------------cut here---------------start------------->8--- 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='16009' )) 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 95.38 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='5407' )) 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 65.88 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 --8<---------------cut here---------------end--------------->8--- Those three queries that originate from "db-get-evaluation-summary" procedure take more than 1 minute to run (65.88, 86.23 and 95.38 seconds). Even if concurrent database reading is permitted, the number of database workers is finite and some of the observed infamous "504" errors are the result of database worker starvation. If you want to help me optimize those queries, you can have a look to "/var/log/cuirass-sql.log" and "/var/log/cuirass-web-sql.log" on berlin, or request those files. Thanks, Mathieu -- https://othacehe.org