* [PATCH 2/6] sqlite: PRAGMA optimize on close
2021-10-11 8:06 5% [PATCH 0/6] extindex: --reindex --fast gets faster Eric Wong
@ 2021-10-11 8:06 7% ` Eric Wong
0 siblings, 0 replies; 2+ results
From: Eric Wong @ 2021-10-11 8:06 UTC (permalink / raw)
To: meta
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) {
^ permalink raw reply related [relevance 7%]
* [PATCH 0/6] extindex: --reindex --fast gets faster
@ 2021-10-11 8:06 5% Eric Wong
2021-10-11 8:06 7% ` [PATCH 2/6] sqlite: PRAGMA optimize on close Eric Wong
0 siblings, 1 reply; 2+ results
From: Eric Wong @ 2021-10-11 8:06 UTC (permalink / raw)
To: meta
-extindex --reindex --fast --all performance is nearly doubled.
There's also a bunch of cleanups for more consistently handling
with various forms of message removal from an extindex.
Eric Wong (6):
extindex: speed up --reindex --fast
sqlite: PRAGMA optimize on close
extindex: rename var: active => active_shards
extindex: share unref logic in more places
extindex: more consistent doc removal
extindex: avoid invalid blobs after unref
lib/PublicInbox/ExtSearchIdx.pm | 303 ++++++++++++++++----------------
lib/PublicInbox/LeiMailSync.pm | 3 +-
lib/PublicInbox/Over.pm | 4 +-
lib/PublicInbox/OverIdx.pm | 54 +-----
lib/PublicInbox/V2Writable.pm | 11 ++
t/extsearch.t | 2 +-
t/over.t | 5 +-
7 files changed, 173 insertions(+), 209 deletions(-)
^ permalink raw reply [relevance 5%]
Results 1-2 of 2 | reverse | options above
-- pct% links below jump to the message on this page, permalinks otherwise --
2021-10-11 8:06 5% [PATCH 0/6] extindex: --reindex --fast gets faster Eric Wong
2021-10-11 8:06 7% ` [PATCH 2/6] sqlite: PRAGMA optimize on close Eric Wong
Code repositories for project(s) associated with this public 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).