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 91cd1c934a1fa7889d293574d99df9878a53292f..c6cd1bc58d0ae0b5d676b56288680f8dbb5c0eda 100644 (file)
--- 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 fcd7ffe2317ba518d06ff3faf3d8328600d4d1a1..d04cdda6e3dc2eeaff75096fa92c25e0e96ecd0e 100644 (file)
--- 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) {