* [PATCH 3/5] index: optimize after all SQLite DB commits
2021-10-12 22:44 6% [PATCH 0/5] fix extindex reindex harder Eric Wong
@ 2021-10-12 22:44 7% ` Eric Wong
0 siblings, 0 replies; 2+ results
From: Eric Wong @ 2021-10-12 22:44 UTC (permalink / raw)
To: meta
This covers v1 inboxes, as well. We also guard the execution
since "PRAGMA optimize" was only introduced in SQLite 3.18.0
(2017-03-30)
---
lib/PublicInbox/LeiMailSync.pm | 2 +-
lib/PublicInbox/OverIdx.pm | 1 +
lib/PublicInbox/SearchIdx.pm | 1 +
lib/PublicInbox/V2Writable.pm | 16 +++++-----------
4 files changed, 8 insertions(+), 12 deletions(-)
diff --git a/lib/PublicInbox/LeiMailSync.pm b/lib/PublicInbox/LeiMailSync.pm
index c6cd1bc58d0a..f7e37ad9ca80 100644
--- a/lib/PublicInbox/LeiMailSync.pm
+++ b/lib/PublicInbox/LeiMailSync.pm
@@ -48,7 +48,7 @@ sub lms_pause {
my ($self) = @_;
$self->{fmap} = {};
my $dbh = delete $self->{dbh};
- $dbh->do('PRAGMA optimize') if $dbh;
+ eval { $dbh->do('PRAGMA optimize') } if $dbh;
}
sub create_tables {
diff --git a/lib/PublicInbox/OverIdx.pm b/lib/PublicInbox/OverIdx.pm
index d6d706f7fed0..9fdb26c0d5c2 100644
--- a/lib/PublicInbox/OverIdx.pm
+++ b/lib/PublicInbox/OverIdx.pm
@@ -434,6 +434,7 @@ sub commit_lazy {
my ($self) = @_;
delete $self->{txn} or return;
$self->{dbh}->commit;
+ eval { $self->{dbh}->do('PRAGMA optimize') };
}
sub begin_lazy {
diff --git a/lib/PublicInbox/SearchIdx.pm b/lib/PublicInbox/SearchIdx.pm
index a2ed94993993..928152ec4df4 100644
--- a/lib/PublicInbox/SearchIdx.pm
+++ b/lib/PublicInbox/SearchIdx.pm
@@ -793,6 +793,7 @@ sub v1_checkpoint ($$;$) {
${$sync->{max}} = $self->{batch_bytes};
$self->{mm}->{dbh}->commit;
+ eval { $self->{mm}->{dbh}->do('PRAGMA optimize') };
my $xdb = $self->{xdb};
if ($newest && $xdb) {
my $cur = $xdb->get_metadata('last_commit');
diff --git a/lib/PublicInbox/V2Writable.pm b/lib/PublicInbox/V2Writable.pm
index efcc1fc21a18..3914383cc9d3 100644
--- a/lib/PublicInbox/V2Writable.pm
+++ b/lib/PublicInbox/V2Writable.pm
@@ -547,11 +547,11 @@ sub checkpoint ($;$) {
}
my $shards = $self->{idx_shards};
if ($shards) {
- my $mm = $self->{mm};
- my $dbh = $mm->{dbh} if $mm;
+ my $dbh = $self->{mm}->{dbh} if $self->{mm};
# SQLite msgmap data is second in importance
$dbh->commit if $dbh;
+ eval { $dbh->do('PRAGMA optimize') };
# SQLite overview is third
$self->{oidx}->commit_lazy;
@@ -620,16 +620,10 @@ 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 $@;
+ if ($self->{oidx} && $self->{oidx}->{dbh} && $err) {
+ eval { $self->{oidx}->rollback_lazy };
+ $err .= "overview rollback: $@\n" if $@;
}
my $shards = delete $self->{idx_shards};
^ permalink raw reply related [relevance 7%]
* [PATCH 0/5] fix extindex reindex harder
@ 2021-10-12 22:44 6% Eric Wong
2021-10-12 22:44 7% ` [PATCH 3/5] index: optimize after all SQLite DB commits Eric Wong
0 siblings, 1 reply; 2+ results
From: Eric Wong @ 2021-10-12 22:44 UTC (permalink / raw)
To: meta
1/5 may affect some users
3/5 quiets things down for users on SQLite <3.18
5/5 is a good usability fix for me
Eric Wong (5):
extindex: flush pending reindex before unref
lei/store: use remove_doc to save some LoC
index: optimize after all SQLite DB commits
doc: relnotes: note some recent improvements
lei up --all: show output for warnings
Documentation/RelNotes/v1.7.0.wip | 14 +++++++++++++-
lib/PublicInbox/ExtSearchIdx.pm | 4 ++++
lib/PublicInbox/LEI.pm | 12 ++++++++----
lib/PublicInbox/LeiMailSync.pm | 2 +-
lib/PublicInbox/LeiStore.pm | 3 +--
lib/PublicInbox/LeiUp.pm | 7 +++++++
lib/PublicInbox/OverIdx.pm | 1 +
lib/PublicInbox/SearchIdx.pm | 1 +
lib/PublicInbox/V2Writable.pm | 16 +++++-----------
9 files changed, 41 insertions(+), 19 deletions(-)
^ permalink raw reply [relevance 6%]
Results 1-2 of 2 | reverse | options above
-- pct% links below jump to the message on this page, permalinks otherwise --
2021-10-12 22:44 6% [PATCH 0/5] fix extindex reindex harder Eric Wong
2021-10-12 22:44 7% ` [PATCH 3/5] index: optimize after all SQLite DB commits 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).