From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on dcvr.yhbt.net X-Spam-Level: X-Spam-ASN: X-Spam-Status: No, score=-4.0 required=3.0 tests=ALL_TRUSTED,BAYES_00 shortcircuit=no autolearn=ham autolearn_force=no version=3.4.2 Received: from localhost (dcvr.yhbt.net [127.0.0.1]) by dcvr.yhbt.net (Postfix) with ESMTP id 4C0F91F9FC for ; Mon, 11 Oct 2021 08:06:20 +0000 (UTC) From: Eric Wong To: meta@public-inbox.org Subject: [PATCH 2/6] sqlite: PRAGMA optimize on close Date: Mon, 11 Oct 2021 08:06:16 +0000 Message-Id: <20211011080620.27478-3-e@80x24.org> In-Reply-To: <20211011080620.27478-1-e@80x24.org> References: <20211011080620.27478-1-e@80x24.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: 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) {