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 AK39E8j2zV+iOAAA0tVLHw (envelope-from ) for ; Mon, 07 Dec 2020 09:32:56 +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 CFbJD8j2zV8zeQAAbx9fmQ (envelope-from ) for ; Mon, 07 Dec 2020 09:32:56 +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 154989403EE for ; Mon, 7 Dec 2020 09:32:55 +0000 (UTC) Received: from localhost ([::1]:37930 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kmCt4-0002Q5-QJ for larch@yhetil.org; Mon, 07 Dec 2020 04:32:54 -0500 Received: from eggs.gnu.org ([2001:470:142:3::10]:44448) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1kmCsj-0002PY-AU for guix-devel@gnu.org; Mon, 07 Dec 2020 04:32:34 -0500 Received: from mira.cbaines.net ([2a01:7e00:e000:2f8:fd4d:b5c7:13fb:3d27]:57319) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1kmCsg-0002LQ-IY; Mon, 07 Dec 2020 04:32:32 -0500 Received: from localhost (188.28.112.220.threembb.co.uk [188.28.112.220]) by mira.cbaines.net (Postfix) with ESMTPSA id CEC1527BC00; Mon, 7 Dec 2020 09:32:25 +0000 (GMT) Received: from capella (localhost [127.0.0.1]) by localhost (OpenSMTPD) with ESMTP id a5f8856d; Mon, 7 Dec 2020 09:32:23 +0000 (UTC) References: <87im9fqizr.fsf@cbaines.net> <874kkyvunc.fsf@gnu.org> User-agent: mu4e 1.4.13; emacs 27.1 From: Christopher Baines To: Mathieu Othacehe Subject: Re: Cuirass WAL size issues In-reply-to: <874kkyvunc.fsf@gnu.org> Date: Mon, 07 Dec 2020 09:32:20 +0000 Message-ID: <877dpuq7vf.fsf@cbaines.net> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha512; protocol="application/pgp-signature" Received-SPF: pass client-ip=2a01:7e00:e000:2f8:fd4d:b5c7:13fb:3d27; envelope-from=mail@cbaines.net; helo=mira.cbaines.net 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 autolearn=ham autolearn_force=no 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: , Cc: guix-devel@gnu.org Errors-To: guix-devel-bounces+larch=yhetil.org@gnu.org Sender: "Guix-devel" X-Migadu-Flow: FLOW_IN X-Migadu-Spam-Score: -4.40 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-Migadu-Queue-Id: 154989403EE X-Spam-Score: -4.40 X-Migadu-Scanner: ns3122888.ip-94-23-21.eu X-TUID: 6Js9uCaVBVfb --=-=-= Content-Type: text/plain Mathieu Othacehe writes: > Hello Chris, > >> I believe SQLite checkpoints the WAL file after transactions commit, if >> the WAL is over 1000 pages in size. At least for the Guix Build >> Coordinator though, that didn't seem to be working/happening as the WAL >> file seemed to just grow and grow. > > I have noticed that the WAL file size had a strong impact on Cuirass > performances on berlin[1]. > > My conclusion was that, WAL file is nice when doing a lot of writing, > but it makes reading really slow over time. I did set the checkpoint > value to 16 pages of 32KiB as recommended here[2]. > >> Switching focus to Cuirass, I think it's got similar issues. I've pushed >> a few commits to this branch [1], the important one being [2] which >> rewrites a few procedures to call sqlite-reset when the statement is >> finished with, rather than calling it before starting to read from the >> statement. > > The first two commits look nice. Regarding the third one, it makes sense > to finalize or reset a statement once done with it, but I cannot find > anything in SQLite documentation insisting on that point. > > Have you found some resources elaborating on that? No, that's fustrated me as well. Given sqlite-prepare resets cached statements, I did try removing most of the sqlite-reset calls from the Guix Build Coordinator, but that went very badly so I reverted it shortly after. I was also struggling to find documentation on what to do/not do. I'll neaten up these commits later and push them, hopefully it'll help with the WAL size and/or query performance. Chris --=-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAl/N9qRfFIAAAAAALgAo aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNF ODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2Jh aW5lcy5uZXQACgkQXiijOwuE9XcaIg/6A+58p7YK6WMh9Qdhe3s76Yp0oo57xezk Aa6NDFaeWgGkWhclAwBX3tGH/BpirZUoNkM71jSC2oxTsMqRynohbGdGGeRSj1Ui P6kCkjoe1PewQiYmHXHI1HTHWssZonjZ7TSAoFj1ZYKDkNPdWfOvHmOO4C5yHrSJ O3/7uv+8hKiTikp/NEYc+1SPXDMoCY8Td431toxUEUZc552G+izYYRKcXf148glw RYrb7JmGhp+n0d0jDqNP58RF4oXKMKsyRP9Rqcx/PSB07mf+bJyVkuLEDmcmhM+5 mvqNcf9BI+i9Gbpp9CPiDc0zszNSJ39ES6q09bTvGRmK7d+Cn9ri5XjthGKirhVG +sxeuB8vxAkmsS2p1Jp4lOfKZmAMFN76OGrNlMO3wQI+a/SHjh7rtJJ8iAbQeo6k Jv9dbLZHGHeJm/eJmucAZDysAcZ+2rlWIHvgVpbQUF4m9GKh7mMIemi+MUNQUyUm oOCge2lZorOjoGYFZlxEhG54rOhAR2tK+FOiJfZrpXUuDVgSHmAv9MGgUjNgMS8B 4As1fp7Q4IE3fbnuGkbDnmchfSHeSFlgdlkOu5Rux99hqR5AksuP+isQhZk5Fjsz X5qdvthIwkHqy10JYa5zJi+DOAkOfZGyNxrGwVC0aYxdozevhv5jxOrFnTGqPhQV bgT7ST73g0A= =TRdF -----END PGP SIGNATURE----- --=-=-=--