From: Eric Wong <e@80x24.org>
Date: Mon, 11 Oct 2021 08:06:16 +0000 (+0000)
Subject: sqlite: PRAGMA optimize on close
X-Git-Tag: v1.7.0~192
X-Git-Url: http://www.git.stargrave.org/?p=public-inbox.git;a=commitdiff_plain;h=4c6d7f06985840068340f33eef81dcf7eb8209e0

sqlite: PRAGMA optimize on close

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
---

diff --git a/lib/PublicInbox/LeiMailSync.pm b/lib/PublicInbox/LeiMailSync.pm
index 91cd1c93..c6cd1bc5 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 fcd7ffe2..d04cdda6 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) {