user/dev discussion of public-inbox itself
 help / color / mirror / code / Atom feed
From: Eric Wong <e@80x24.org>
To: meta@public-inbox.org
Subject: [PATCH 2/6] sqlite: PRAGMA optimize on close
Date: Mon, 11 Oct 2021 08:06:16 +0000	[thread overview]
Message-ID: <20211011080620.27478-3-e@80x24.org> (raw)
In-Reply-To: <20211011080620.27478-1-e@80x24.org>

As recommended by SQLite documentation[1]:

  To achieve the best long-term query performance without the need
  to do a detailed engineering analysis of the application schema
  and SQL, it is recommended that applications run "PRAGMA optimize"
  (with no arguments) just before closing each database connection.

Hopefully that works for our use cases and can make things
faster for us.

[1] https://www.sqlite.org/pragma.html#pragma_optimize
---
 lib/PublicInbox/LeiMailSync.pm |  3 ++-
 lib/PublicInbox/V2Writable.pm  | 11 +++++++++++
 2 files changed, 13 insertions(+), 1 deletion(-)

diff --git a/lib/PublicInbox/LeiMailSync.pm b/lib/PublicInbox/LeiMailSync.pm
index 91cd1c934a1f..c6cd1bc58d0a 100644
--- a/lib/PublicInbox/LeiMailSync.pm
+++ b/lib/PublicInbox/LeiMailSync.pm
@@ -47,7 +47,8 @@ sub lms_write_prepare { ($_[0]->{dbh} //= dbh_new($_[0], 1)); $_[0] }
 sub lms_pause {
 	my ($self) = @_;
 	$self->{fmap} = {};
-	delete $self->{dbh};
+	my $dbh = delete $self->{dbh};
+	$dbh->do('PRAGMA optimize') if $dbh;
 }
 
 sub create_tables {
diff --git a/lib/PublicInbox/V2Writable.pm b/lib/PublicInbox/V2Writable.pm
index fcd7ffe2317b..d04cdda6e3dc 100644
--- a/lib/PublicInbox/V2Writable.pm
+++ b/lib/PublicInbox/V2Writable.pm
@@ -622,7 +622,18 @@ sub done {
 		my $m = $err ? 'rollback' : 'commit';
 		eval { $mm->{dbh}->$m };
 		$err .= "msgmap $m: $@\n" if $@;
+		eval { $mm->{dbh}->do('PRAGMA optimize') };
+		$err .= "msgmap optimize: $@\n" if $@;
 	}
+	if ($self->{oidx} && $self->{oidx}->{dbh}) {
+		if ($err) {
+			eval { $self->{oidx}->rollback_lazy };
+			$err .= "overview rollback: $@\n" if $@;
+		}
+		eval { $self->{oidx}->{dbh}->do('PRAGMA optimize') };
+		$err .= "overview optimize: $@\n" if $@;
+	}
+
 	my $shards = delete $self->{idx_shards};
 	if ($shards) {
 		for (@$shards) {

  parent reply	other threads:[~2021-10-11  8:06 UTC|newest]

Thread overview: 7+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2021-10-11  8:06 [PATCH 0/6] extindex: --reindex --fast gets faster Eric Wong
2021-10-11  8:06 ` [PATCH 1/6] extindex: speed up --reindex --fast Eric Wong
2021-10-11  8:06 ` Eric Wong [this message]
2021-10-11  8:06 ` [PATCH 3/6] extindex: rename var: active => active_shards Eric Wong
2021-10-11  8:06 ` [PATCH 4/6] extindex: share unref logic in more places Eric Wong
2021-10-11  8:06 ` [PATCH 5/6] extindex: more consistent doc removal Eric Wong
2021-10-11  8:06 ` [PATCH 6/6] extindex: avoid invalid blobs after unref Eric Wong

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

  List information: https://public-inbox.org/README

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=20211011080620.27478-3-e@80x24.org \
    --to=e@80x24.org \
    --cc=meta@public-inbox.org \
    --subject='Re: [PATCH 2/6] sqlite: PRAGMA optimize on close' \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

Code repositories for project(s) associated with this inbox:

	https://80x24.org/public-inbox.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).