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 GPunJnGby15vBwAA0tVLHw (envelope-from ) for ; Mon, 25 May 2020 10:18:25 +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 gOJmInGby15TRwAAbx9fmQ (envelope-from ) for ; Mon, 25 May 2020 10:18:25 +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 6488B940144 for ; Mon, 25 May 2020 10:18:25 +0000 (UTC) Received: from localhost ([::1]:41876 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1jdABc-0006sD-Dl for larch@yhetil.org; Mon, 25 May 2020 06:18:24 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:50662) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1jdA8j-0003kw-Cw for guix-devel@gnu.org; Mon, 25 May 2020 06:15:25 -0400 Received: from mira.cbaines.net ([2a01:7e00:e000:2f8:fd4d:b5c7:13fb:3d27]:53605) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1jdA8g-0005UK-6i for guix-devel@gnu.org; Mon, 25 May 2020 06:15:25 -0400 Received: from localhost (unknown [46.237.172.222]) by mira.cbaines.net (Postfix) with ESMTPSA id 6DEA727BBE1 for ; Mon, 25 May 2020 11:15:20 +0100 (BST) Received: from localhost (localhost [local]) by localhost (OpenSMTPD) with ESMTPA id a87f9ae0 for ; Mon, 25 May 2020 10:15:18 +0000 (UTC) From: Christopher Baines To: guix-devel@gnu.org Subject: [PATCH] cuirass: Perform some database "optimization" at startup. Date: Mon, 25 May 2020 11:15:18 +0100 Message-Id: <20200525101518.17526-1-mail@cbaines.net> X-Mailer: git-send-email 2.26.2 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Received-SPF: pass client-ip=2a01:7e00:e000:2f8:fd4d:b5c7:13fb:3d27; envelope-from=mail@cbaines.net; helo=mira.cbaines.net X-detected-operating-system: by eggs.gnu.org: First seen = 2020/05/25 04:25:02 X-ACL-Warn: Detected OS = ??? X-Spam_score_int: -18 X-Spam_score: -1.9 X-Spam_bar: - X-Spam_report: (-1.9 / 5.0 requ) BAYES_00=-1.9, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001, UNPARSEABLE_RELAY=0.001, URIBL_BLOCKED=0.001 autolearn=_AUTOLEARN X-Spam_action: no action X-BeenThere: guix-devel@gnu.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: "Development of GNU Guix and the GNU System distribution." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: guix-devel-bounces+larch=yhetil.org@gnu.org Sender: "Guix-devel" X-Scanner: scn0 Authentication-Results: aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of guix-devel-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=guix-devel-bounces@gnu.org X-Spam-Score: 3.99 X-TUID: mGRccREFVUdX Add a "optimize" step that occurs when starting up the main Curiass process. Currently this does two things, but could be extended to do more. The "PRAGMA optimize;" command prompts SQLite to ANALYZE tables where that might help. The "PRAGMA wal_checkpoint(TRUNCATE);" command has SQLite process any unprocessed changes from the WAL file, then truncate it to 0 bytes. I've got no data to suggest this helps with performance, but I'm hoping that going from a large WAL file to a small one occasionally might be useful. * src/cuirass/database.scm (db-optimize): New procedure. * bin/cuirass.in (main): Run it. --- bin/cuirass.in | 4 ++++ src/cuirass/database.scm | 8 ++++++++ 2 files changed, 12 insertions(+) diff --git a/bin/cuirass.in b/bin/cuirass.in index fbc7c3c..7a2d5ae 100644 --- a/bin/cuirass.in +++ b/bin/cuirass.in @@ -124,6 +124,10 @@ exec ${GUILE:-@GUILE@} --no-auto-compile -e main -s "$0" "$@" (min (current-processor-count) 4)))) (prepare-git) + (unless (option-ref opts 'web #f) + (log-message "performing database optimizations") + (db-optimize)) + (log-message "running Fibers on ~a kernel threads" threads) (run-fibers (lambda () diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm index f80585e..e81ead0 100644 --- a/src/cuirass/database.scm +++ b/src/cuirass/database.scm @@ -38,6 +38,7 @@ db-init db-open db-close + db-optimize db-add-specification db-remove-specification db-get-specifications @@ -277,6 +278,13 @@ database object." "Close database object DB." (sqlite-close db)) +(define* (db-optimize #:optional (db-file (%package-database))) + "Open the database and perform optimizations." + (let ((db (db-open db-file))) + (sqlite-exec db "PRAGMA optimize;") + (sqlite-exec db "PRAGMA wal_checkpoint(TRUNCATE);") + (db-close db))) + (define (last-insert-rowid db) (vector-ref (car (sqlite-exec db "SELECT last_insert_rowid();")) 0)) -- 2.26.2