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 IO91Klq9y14UOwAA0tVLHw (envelope-from ) for ; Mon, 25 May 2020 12:43:06 +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 KAhZJlq9y14+egAAB5/wlQ (envelope-from ) for ; Mon, 25 May 2020 12:43:06 +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 3AA81940144 for ; Mon, 25 May 2020 12:43:06 +0000 (UTC) Received: from localhost ([::1]:45846 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1jdCRd-0000v9-3k for larch@yhetil.org; Mon, 25 May 2020 08:43:05 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:38316) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1jdCRW-0000uz-8w for guix-devel@gnu.org; Mon, 25 May 2020 08:42:58 -0400 Received: from mira.cbaines.net ([212.71.252.8]:54930) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1jdCRV-0005cv-BG for guix-devel@gnu.org; Mon, 25 May 2020 08:42:57 -0400 Received: from localhost (unknown [46.237.172.222]) by mira.cbaines.net (Postfix) with ESMTPSA id 9C30027BBE1; Mon, 25 May 2020 13:42:55 +0100 (BST) Received: from localhost (localhost [local]) by localhost (OpenSMTPD) with ESMTPA id 468eee70; Mon, 25 May 2020 12:42:53 +0000 (UTC) References: <20200525101518.17526-1-mail@cbaines.net> <20200525124849.35071b94@scratchpost.org> User-agent: mu4e 1.2.0; emacs 26.3 From: Christopher Baines To: Danny Milosavljevic Subject: Re: [PATCH] cuirass: Perform some database "optimization" at startup. In-reply-to: <20200525124849.35071b94@scratchpost.org> Date: Mon, 25 May 2020 13:42:51 +0100 Message-ID: <87ftbokwxg.fsf@cbaines.net> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha512; protocol="application/pgp-signature" Received-SPF: pass client-ip=212.71.252.8; envelope-from=mail@cbaines.net; helo=mira.cbaines.net X-detected-operating-system: by eggs.gnu.org: First seen = 2020/05/25 08:42:55 X-ACL-Warn: Detected OS = Linux 2.2.x-3.x [generic] 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: , Cc: guix-devel@gnu.org 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: -0.61 X-TUID: ne0JMVXnlrrY --=-=-= Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Danny Milosavljevic writes: > the docs at https://www.sqlite.org/pragma.html#pragma_optimize suggest to= run > "PRAGMA optimize" at the end of the connection, or periodically--not at t= he > beginning. > > That makes sense since it has to be able to see which queries are emitted > in order to know what to optimize. > > Also, docs say: > >> The query planner used sqlite_stat1-style statistics for one or more ind= exes >> of the table at some point during the lifetime of the current connection. > > That probably means one would have had to run ANALYZE at some point in th= e past. Thanks Danny, this is interesting. From=20my reading of the docs, I think the only thing the optimize pragma is going to do is run ANALYZE on some tables. There's something about the current connection referenced in "Determination Of When To Run Analyze", but it's not the only thing that triggers this. There is some stuff mentioned about recent queries, but it seems to be prefixed with "(Not yet implemented)". I'm not sure where this would fit in the Cuirass code when connections are closed, as I'm not sure where the connections are closed! In terms of running this regularly, I'm up for trying to work that in, maybe it could happen after new data has been added, or something like that. Although I don't have any evidence to support this, I'm hoping running the optimize pragma at startup will help in some cases, like when migrations add new indexes, as I think the docs say SQLite will analyze a table if an index hasn't been analyzed yet. > Replaying the WAL sounds like a good idea at the beginning, though. Most > journalling filesystems do that too. Cool :) --=-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQKTBAEBCgB9FiEEPonu50WOcg2XVOCyXiijOwuE9XcFAl7LvUtfFIAAAAAALgAo aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNF ODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcACgkQXiijOwuE 9XdF/Q/+O0UWHdXmovudlGWna6yqGItqbtWW6tnS848XMhNKa18KJm4mOfA/BaUY AtCdmkw36qKqf90oUpXOV/JVrHFYzOdCsjBqu32xwcEmgrdkREwgb3q6MEepXhIV PtJNxtS8QK6Nqkx38LJv6HKfAudPybqERr1Yk3XTWm8ByaPGRQtiI1CpA7F8miHA /7AIOEc+/xfN1qE9Mfg9GD8i/WakxjrdTo3fMWS09DdfLHRklxeWHhwtmhVd731s XJE71XBIyW+lOpf4c843kYmGEVBcATyvrmFUnurD7qMynYEjLUSBvmrZGF1+kuuu b4o4rA234J5iifJ+1zi4bI0J0DjM3mMEZf7Wi0b+o595NMwYfmOP0Z8zD2eKJzSQ Jv3vGR/zKnfO1svAjHOdPO3aIfPq/m43EuR2YS7FwHL1w7r8MJ9QYd+GpW5oVJ/9 DRMRw8UbLCsHd3FCwkVFcQYsUmeYZlppQa955j9dInAVkmNQTemi+NBC7pl44J20 ul8nOFhdTb4AGEw5OLUz3gki0p5N2XxC6DEtD+HzRaxRxmhTfpzuX8+F8VU0gSe7 qtnaeCnKuO4i1Mr+sCvA8GcFRvqc6dOZRFnNnVlWwJHr3EyOnE3U67axI7RrPvxY MKpsOUqDhff1QZdrxemB3C5BmtwVU26g9DZbUJB9f5HkktMai7s= =H7Vl -----END PGP SIGNATURE----- --=-=-=--