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 ENdpNjGWcV+kEgAA0tVLHw (envelope-from ) for ; Mon, 28 Sep 2020 07:52:17 +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 aE5JMjGWcV8pIgAAbx9fmQ (envelope-from ) for ; Mon, 28 Sep 2020 07:52:17 +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 3B89D9402AD for ; Mon, 28 Sep 2020 07:52:17 +0000 (UTC) Received: from localhost ([::1]:59220 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kMnxH-0006bO-CR for larch@yhetil.org; Mon, 28 Sep 2020 03:52:15 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:56852) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kMnx4-0006ZS-61 for bug-guix@gnu.org; Mon, 28 Sep 2020 03:52:02 -0400 Received: from debbugs.gnu.org ([209.51.188.43]:39781) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1kMnx3-0000nP-TH for bug-guix@gnu.org; Mon, 28 Sep 2020 03:52:01 -0400 Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2) (envelope-from ) id 1kMnx3-0003Qj-SX for bug-guix@gnu.org; Mon, 28 Sep 2020 03:52:01 -0400 Subject: bug#43564: cuirass: Contention while registering new builds. Resent-From: Mathieu Othacehe Original-Sender: "Debbugs-submit" Resent-To: bug-guix@gnu.org Resent-Date: Mon, 28 Sep 2020 07:52:01 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: cc-closed 43564 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: 43564-done@debbugs.gnu.org Mail-Followup-To: 43564@debbugs.gnu.org, othacehe@gnu.org, othacehe@gnu.org Received: via spool by 43564-done@debbugs.gnu.org id=D43564.160127950713162 (code D ref 43564); Mon, 28 Sep 2020 07:52:01 +0000 Received: (at 43564-done) by debbugs.gnu.org; 28 Sep 2020 07:51:47 +0000 Received: from localhost ([127.0.0.1]:51326 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kMnwp-0003QD-FQ for submit@debbugs.gnu.org; Mon, 28 Sep 2020 03:51:47 -0400 Received: from eggs.gnu.org ([209.51.188.92]:49966) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kMnwn-0003Q2-Gp for 43564-done@debbugs.gnu.org; Mon, 28 Sep 2020 03:51:45 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]:38494) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kMnwi-0000mL-7E for 43564-done@debbugs.gnu.org; Mon, 28 Sep 2020 03:51:40 -0400 Received: from [2a01:e0a:19b:d9a0:d549:2a8f:de3b:c9c0] (port=33304 helo=cervin) by fencepost.gnu.org with esmtpsa (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.82) (envelope-from ) id 1kMnwh-0001qB-Qa for 43564-done@debbugs.gnu.org; Mon, 28 Sep 2020 03:51:40 -0400 From: Mathieu Othacehe References: <87imc5zrc8.fsf@gnu.org> Date: Mon, 28 Sep 2020 09:51:38 +0200 In-Reply-To: <87imc5zrc8.fsf@gnu.org> (Mathieu Othacehe's message of "Tue, 22 Sep 2020 18:52:39 +0200") Message-ID: <87k0wee3ud.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: K7yYkFy7Ssjv Hello, > As each evaluation has to register around 50k builds, there might be > some sort of "writing" contention on the database, explaining the long > build registration time. Turns out, once an evaluation is over, new builds are registered. This registration tries to insert a new build for each derivation returned by the evaluation phase. If the new build does not add a new output, the insertion query is then rollbacked. This means that there are at least as many insertion queries as new derivations. SQlite allows at most one writer at a time, and even though we are using WAL mode, performing a lot of insertions will reduce the reading performances. When multiple evaluations are performed in parallel, the large number of concurrent insertion queries also causes contention. Having 10 pending evaluations means that we are probably trying to insert around 500.000 records concurrently. This is something that SQLite does not seem to be designed for. To avoid those issues, we need to check first in the "Outputs" table which derivations are already registered. This means that most of the insertion queries will be replaced by reading queries, that are much less expensive and more suitable for Cuirass concurrent implementation. In the best case of one pending evaluation, the registration duration is reduced from 1800 seconds to 320 seconds. I think that the gain is way larger when there are multiple pending evaluations. Pushed a fix as 461e07e14e1c8013343c0a2cb26c0e022e10d5e4. Thanks, Mathieu