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 sHIxC0XpnF//IwAA0tVLHw (envelope-from ) for ; Sat, 31 Oct 2020 04:34: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 AAT/BkXpnF+ddAAAB5/wlQ (envelope-from ) for ; Sat, 31 Oct 2020 04:34: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 98420940222 for ; Sat, 31 Oct 2020 04:34:12 +0000 (UTC) Received: from localhost ([::1]:50304 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kYiaf-0004km-Um for larch@yhetil.org; Sat, 31 Oct 2020 00:34:09 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:36850) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kYiaY-0004kQ-Nd for bug-guix@gnu.org; Sat, 31 Oct 2020 00:34:02 -0400 Received: from debbugs.gnu.org ([209.51.188.43]:48761) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1kYiaY-0007qh-Ay for bug-guix@gnu.org; Sat, 31 Oct 2020 00:34:02 -0400 Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2) (envelope-from ) id 1kYiaY-00067f-7J for bug-guix@gnu.org; Sat, 31 Oct 2020 00:34:02 -0400 X-Loop: help-debbugs@gnu.org Subject: bug#43850: cuirass: inconsistent SQL queries execution time. Resent-From: Maxim Cournoyer Original-Sender: "Debbugs-submit" Resent-CC: bug-guix@gnu.org Resent-Date: Sat, 31 Oct 2020 04:34:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: followup 43850 X-GNU-PR-Package: guix X-GNU-PR-Keywords: To: Mathieu Othacehe Received: via spool by 43850-submit@debbugs.gnu.org id=B43850.160411880923485 (code B ref 43850); Sat, 31 Oct 2020 04:34:02 +0000 Received: (at 43850) by debbugs.gnu.org; 31 Oct 2020 04:33:29 +0000 Received: from localhost ([127.0.0.1]:60307 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kYiZr-00066Z-B3 for submit@debbugs.gnu.org; Sat, 31 Oct 2020 00:33:29 -0400 Received: from mail-io1-f51.google.com ([209.85.166.51]:45217) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1kYiZo-00066M-AB for 43850@debbugs.gnu.org; Sat, 31 Oct 2020 00:33:17 -0400 Received: by mail-io1-f51.google.com with SMTP id s7so9600417iol.12 for <43850@debbugs.gnu.org>; Fri, 30 Oct 2020 21:33:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:cc:subject:references:date:in-reply-to:message-id :user-agent:mime-version; bh=UMoe0oBWhjGEBQPe12AmOztGxYodtzQJAsu/pZIkvwE=; b=uwUVmpOiWLUzwLFoFEkdu4zrhyPx+9GDvoqNk1IknXCYqo+kSIcYfkcj/VNvdBJPOA D/zxA8rIC02UES+d471HSK17rYO/xRXFzA2wNwk31KyH2a5XVY4a+aU9snFsUPoBQoYr wYou9K7BhJXJy+OfhWUPAbYDenGAfIkiTEdI799TyMsaOesarYK05eKcQJgxCvsuAsAY WTUQNb/q1anXJYhu7WMlEWYYLMdfhL8J0aXdJbIZnGIEXV1VCftgGTLnTKmC2P1gkx6z yZ9Qy2zxyGmeL9i+CYbfXLQXisY3LYRj04xQGlf58aShdoWiEq3UpjXeoYKVDjVMd0He yCfQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:cc:subject:references:date:in-reply-to :message-id:user-agent:mime-version; bh=UMoe0oBWhjGEBQPe12AmOztGxYodtzQJAsu/pZIkvwE=; b=XB2kxo06p5nXox5hNpSw2uCkMaFgVhjFMZHkuteuHAq+CNo2/oHhOiyH6VpDAdGn3g I/cSZcAodwef03lppZUX0k6Jzb9eoyj+An4kR2afnz3hBnFFwUVD4DCuyz7N30bZsjiq zocpAG3NDbnmERlTFZJlyUE+udoCxc4ONPDUrrIU4xH+yD8ok4EnDO+GtSYR59JiVLhm 2SJtEKIgkkIA6pNHsdAgfwl7W35SyrzAagna+ZSLWPwqlnKXOP/xytuhvTFKUbP2VrkJ lSWLldmU3ormMEOP7uxX0H5QmcTvnPe9BhjPqxfEsV6ksZRAhzaOuT00VI64HYELVPXF FPZA== X-Gm-Message-State: AOAM533KF8Wv0jk+rtS2HlK/0ZyEyrk8srQywkuBkNPtMbS0jwWnNBil 5rM4CN19gq3TCDrO8KL12hSdjiH3J5ee4g== X-Google-Smtp-Source: ABdhPJzEEFlaCIjag7o/wuULM8ITZtRF0/4z+js4+uU9GUe1dZK7EGTeGZuskUPpSbV4rtJg4FcRdg== X-Received: by 2002:a6b:bbc6:: with SMTP id l189mr4201337iof.145.1604118790561; Fri, 30 Oct 2020 21:33:10 -0700 (PDT) Received: from hurd (dsl-236-123-160.b2b2c.ca. [207.236.123.160]) by smtp.gmail.com with ESMTPSA id r4sm6072806ilj.43.2020.10.30.21.33.09 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 30 Oct 2020 21:33:09 -0700 (PDT) From: Maxim Cournoyer References: <87a6wyhviz.fsf@gnu.org> <87362gg35r.fsf@gnu.org> <87362986k7.fsf@gnu.org> <87blguzdig.fsf@gnu.org> <87blgo8ufs.fsf@gnu.org> <87d014yq3t.fsf@cbaines.net> <871rhjyd38.fsf@gnu.org> Date: Sat, 31 Oct 2020 00:33:08 -0400 In-Reply-To: <871rhjyd38.fsf@gnu.org> (Mathieu Othacehe's message of "Tue, 27 Oct 2020 15:10:03 +0100") Message-ID: <87wnz7xbej.fsf@gmail.com> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain X-Spam-Score: 0.0 (/) X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list X-Spam-Score: -1.0 (-) X-BeenThere: bug-guix@gnu.org List-Id: Bug reports for GNU Guix List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Cc: 43850@debbugs.gnu.org Errors-To: bug-guix-bounces+larch=yhetil.org@gnu.org Sender: "bug-Guix" X-Scanner: ns3122888.ip-94-23-21.eu Authentication-Results: aspmx1.migadu.com; dkim=fail (headers rsa verify failed) header.d=gmail.com header.s=20161025 header.b=uwUVmpOi; dmarc=fail reason="SPF not aligned (relaxed)" header.from=gmail.com (policy=none); 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: 0.09 X-TUID: oUYhEVEUFtcX Hello, Mathieu Othacehe writes: > Hello Chris, > >> I think Ricardo mentioned that the machine running Cuirass uses an SSD >> for the root filesystem, so moving the database there may help? > > Looks like the database was already on the SSD before my tmpfs > experiment. > > mathieu@berlin ~$ df -h > Filesystem Size Used Avail Use% Mounted on > none 95G 0 95G 0% /dev > /dev/sda1 916G 321G 549G 37% / > /dev/sdb1 37T 34T 2.6T 94% /gnu > tmpfs 95G 8.0K 95G 1% /dev/shm > tmpfs 10G 2.4G 7.7G 24% /var/lib/cuirass_tmpfs > > I don't really get why I/O pressure on /dev/sdb could impact /dev/sda. > > Thanks, > > Mathieu As an aside, running --8<---------------cut here---------------start------------->8--- sudo sqlite3 /var/guix/db/db.sqlite vacuum --8<---------------cut here---------------end--------------->8--- shaved off some 40 Mb from my large database file: -rw-r--r-- 1 root root 468889600 Oct 31 00:16 db.sqlite -rw-r--r-- 1 root root 510648320 Oct 28 23:36 db.sqlite.bak Perhaps we should run 'vacuum' when invoking 'guix gc' or at some other key places (where lots of data gets removed from the DB). There's also the auto_vacuum PRAGMA, which is not enabled currently: --8<---------------cut here---------------start------------->8--- sqlite3 /var/guix/db/db.sqlite 'pragma auto_vacuum' 0 --8<---------------cut here---------------end--------------->8--- But the later doesn't necessarily sound like a good idea: Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse. [0] [0]: https://www.sqlite.org/pragma.html#pragma_auto_vacuum Maxim