user/dev discussion of public-inbox itself
 help / color / mirror / code / Atom feed
Search results ordered by [date|relevance]  view[summary|nested|Atom feed]
thread overview below | download mbox.gz: |
* [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).