user/dev discussion of public-inbox itself
 help / color / mirror / code / Atom feed
From: Eric Wong <e@80x24.org>
To: meta@public-inbox.org
Subject: [PATCH 1/6] extindex: speed up --reindex --fast
Date: Mon, 11 Oct 2021 08:06:15 +0000	[thread overview]
Message-ID: <20211011080620.27478-2-e@80x24.org> (raw)
In-Reply-To: <20211011080620.27478-1-e@80x24.org>

This required some tweaking of xref3 indices in over.sqlite3,
but the end result is it brings no-op "--reindex --fast --all"
checks down to roughly 20 minutes (from 30-40 minutes) on
lore/all.

This is faster because a bunch of small SQLite queries are still
slower en-mass than a bunch of perlops.  Despite the lack of IPC
overhead, crossing .so boundaries and repeating lookups over
btrees is still slower than doing the same with Perl hash tables.
---
 lib/PublicInbox/ExtSearchIdx.pm | 171 ++++++++++++++++----------------
 lib/PublicInbox/Over.pm         |   4 +-
 lib/PublicInbox/OverIdx.pm      |  10 +-
 3 files changed, 94 insertions(+), 91 deletions(-)

diff --git a/lib/PublicInbox/ExtSearchIdx.pm b/lib/PublicInbox/ExtSearchIdx.pm
index d589d2c00f1a..8da98ba44a9a 100644
--- a/lib/PublicInbox/ExtSearchIdx.pm
+++ b/lib/PublicInbox/ExtSearchIdx.pm
@@ -807,10 +807,53 @@ sub reindex_unseen ($$$$) {
 	$self->git->cat_async($xsmsg->{blob}, \&_reindex_unseen, $req);
 }
 
-sub _reindex_check_unseen ($$$) {
+sub _unref_stale ($$$$$) {
+	my ($sync, $docid, $ibx, $xnum, $oidbin) = @_;
+	my $del = $sync->{self}->{oidx}->dbh->prepare_cached(<<'');
+DELETE FROM xref3 WHERE ibx_id = ? AND xnum = ? AND oidbin = ?
+
+	$del->bind_param(1, $ibx->{-ibx_id});
+	$del->bind_param(2, $xnum);
+	$del->bind_param(3, $oidbin, SQL_BLOB);
+	$del->execute;
+	my $xr3 = $sync->{self}->{oidx}->get_xref3($docid, 1);
+	my $idx = $sync->{self}->idx_shard($docid);
+	if (scalar(@$xr3) == 0) { # all gone
+		$sync->{self}->{oidx}->delete_by_num($docid);
+		$sync->{self}->{oidx}->eidxq_del($docid);
+		$idx->ipc_do('xdb_remove', $docid);
+	} else { # enqueue for reindex of remaining messages
+		$idx->ipc_do('remove_eidx_info', $docid, $ibx->eidx_key);
+		$sync->{self}->{oidx}->eidxq_add($docid); # yes, add
+	}
+}
+
+sub _unref_stale_range ($$$) {
+	my ($sync, $ibx, $lt_or_gt) = @_;
+	my $r;
+	my $lim = 10000;
+	do {
+		$r = $sync->{self}->{oidx}->dbh->selectall_arrayref(
+			<<EOS, undef, $ibx->{-ibx_id});
+SELECT docid,xnum,oidbin FROM xref3
+WHERE ibx_id = ? AND xnum $lt_or_gt LIMIT $lim
+EOS
+		return if $sync->{quit};
+		for (@$r) { # hopefully rare, not worth optimizing:
+			my ($docid, $xnum, $oidbin) = @$_;
+			my $hex = unpack('H*', $oidbin);
+			warn("# $xnum:$hex (#$docid): stale\n");
+			_unref_stale($sync, $docid, $ibx, $xnum, $oidbin);
+		}
+	} while (scalar(@$r) == $lim);
+	1;
+}
+
+sub _reindex_check_ibx ($$$) {
 	my ($self, $sync, $ibx) = @_;
 	my $ibx_id = $ibx->{-ibx_id};
-	my $slice = 1000;
+	my $slice = 10000;
+	my $opt = { limit => $slice };
 	my ($beg, $end) = (1, $slice);
 	my $err = sync_inbox($self, $sync, $ibx) and return;
 	my $max = $ibx->over->max;
@@ -820,11 +863,12 @@ sub _reindex_check_unseen ($$$) {
 	my $msgs;
 	my $pr = $sync->{-opt}->{-progress};
 	my $ekey = $ibx->eidx_key;
-	local $sync->{-regen_fmt} =
-			"$ekey checking unseen %u/".$ibx->over->max."\n";
+	local $sync->{-regen_fmt} = "$ekey checking %u/$max\n";
 	${$sync->{nr}} = 0;
 	my $fast = $sync->{-opt}->{fast};
-	while (scalar(@{$msgs = $ibx->over->query_xover($beg, $end)})) {
+	my $dsu; # _unref_stale_range (< $lo) called
+	my ($lo, $hi);
+	while (scalar(@{$msgs = $ibx->over->query_xover($beg, $end, $opt)})) {
 		${$sync->{nr}} = $beg;
 		$beg = $msgs->[-1]->{num} + 1;
 		$end = $beg + $slice;
@@ -832,92 +876,48 @@ sub _reindex_check_unseen ($$$) {
 		if (checkpoint_due($sync)) {
 			reindex_checkpoint($self, $sync); # release lock
 		}
-
-		my $inx3 = $self->{oidx}->dbh->prepare_cached(<<'', undef, 1);
-SELECT DISTINCT(docid) FROM xref3 WHERE
-ibx_id = ? AND xnum = ? AND oidbin = ?
-
+		($lo, $hi) = ($msgs->[0]->{num}, $msgs->[-1]->{num});
+		$dsu //= _unref_stale_range($sync, $ibx, "< $lo");
+		my $x3a = $self->{oidx}->dbh->selectall_arrayref(
+			<<"", undef, $ibx_id, $lo, $hi);
+SELECT xnum,oidbin,docid FROM xref3 WHERE
+ibx_id = ? AND xnum >= ? AND xnum <= ?
+
+		my %x3m;
+		for (@$x3a) {
+			my $k = pack('J', $_->[0]) . $_->[1];
+			push @{$x3m{$k}}, $_->[2];
+		}
+		undef $x3a;
 		for my $xsmsg (@$msgs) {
-			my $oidbin = pack('H*', $xsmsg->{blob});
-			$inx3->bind_param(1, $ibx_id);
-			$inx3->bind_param(2, $xsmsg->{num});
-			$inx3->bind_param(3, $oidbin, SQL_BLOB);
-			$inx3->execute;
-			my $docids = $inx3->fetchall_arrayref;
-			# index messages which were totally missed
-			# the first time around ASAP:
-			if (scalar(@$docids) == 0) {
+			my $k = pack('JH*', $xsmsg->{num}, $xsmsg->{blob});
+			my $docids = delete($x3m{$k});
+			if (!defined($docids)) {
 				reindex_unseen($self, $sync, $ibx, $xsmsg);
-			} elsif (!$fast) { # already seen, reindex later
-				for my $r (@$docids) {
-					$self->{oidx}->eidxq_add($r->[0]);
+			} elsif (!$fast) {
+				for my $num (@$docids) {
+					$self->{oidx}->eidxq_add($num);
 				}
+				return if $sync->{quit};
 			}
-			last if $sync->{quit};
-		}
-		last if $sync->{quit};
-	}
-}
-
-sub _reindex_check_stale ($$$) {
-	my ($self, $sync, $ibx) = @_;
-	my $min = 0;
-	my $pr = $sync->{-opt}->{-progress};
-	my $fetching;
-	my $ekey = $ibx->eidx_key;
-	local $sync->{-regen_fmt} =
-			"$ekey checking stale/missing %u/".$ibx->over->max."\n";
-	${$sync->{nr}} = 0;
-	do {
-		if (checkpoint_due($sync)) {
-			reindex_checkpoint($self, $sync); # release lock
 		}
-		# now, check if there's stale xrefs
-		my $iter = $self->{oidx}->dbh->prepare_cached(<<'', undef, 1);
-SELECT docid,xnum,oidbin FROM xref3 WHERE ibx_id = ? AND docid > ?
-ORDER BY docid,xnum ASC LIMIT 10000
-
-		$iter->execute($ibx->{-ibx_id}, $min);
-		$fetching = undef;
-
-		while (my ($docid, $xnum, $oidbin) = $iter->fetchrow_array) {
-			return if $sync->{quit};
-			${$sync->{nr}} = $xnum;
-
-			$fetching = $min = $docid;
-			my $smsg = $ibx->over->get_art($xnum);
-			my $err;
-			if (!$smsg) {
-				$err = 'stale';
-			} elsif (pack('H*', $smsg->{blob}) ne $oidbin) {
-				$err = "mismatch (!= $smsg->{blob})";
-			} else {
-				next; # likely, all good
-			}
-			# current_info already has eidx_key
-			my $oidhex = unpack('H*', $oidbin);
-			warn "$xnum:$oidhex (#$docid): $err\n";
-			my $del = $self->{oidx}->dbh->prepare_cached(<<'');
-DELETE FROM xref3 WHERE ibx_id = ? AND xnum = ? AND oidbin = ?
-
-			$del->bind_param(1, $ibx->{-ibx_id});
-			$del->bind_param(2, $xnum);
-			$del->bind_param(3, $oidbin, SQL_BLOB);
-			$del->execute;
-
-			# get_xref3 over-fetches, but this is a rare path:
-			my $xr3 = $self->{oidx}->get_xref3($docid, 1);
-			my $idx = $self->idx_shard($docid);
-			if (scalar(@$xr3) == 0) { # all gone
-				$self->{oidx}->delete_by_num($docid);
-				$self->{oidx}->eidxq_del($docid);
-				$idx->ipc_do('xdb_remove', $docid);
-			} else { # enqueue for reindex of remaining messages
-				$idx->ipc_do('remove_eidx_info', $docid, $ekey);
-				$self->{oidx}->eidxq_add($docid); # yes, add
+		return if $sync->{quit};
+		next unless scalar keys %x3m;
+
+		# eliminate stale/mismatched entries
+		my %mismatch = map { $_->{num} => $_->{blob} } @$msgs;
+		while (my ($k, $docids) = each %x3m) {
+			my ($xnum, $hex) = unpack('JH*', $k);
+			my $bin = pack('H*', $hex);
+			my $exp = $mismatch{$xnum};
+			my $m = defined($exp) ? "mismatch (!= $exp)" : 'stale';
+			warn("# $xnum:$hex (#@$docids): $m\n");
+			for my $i (@$docids) {
+				_unref_stale($sync, $i, $ibx, $xnum, $bin);
 			}
 		}
-	} while (defined $fetching);
+	}
+	_unref_stale_range($sync, $ibx, "> $hi") if defined($hi);
 }
 
 sub _reindex_inbox ($$$) {
@@ -927,8 +927,7 @@ sub _reindex_inbox ($$$) {
 	if (defined(my $err = _ibx_index_reject($ibx))) {
 		warn "W: cannot reindex $ekey ($err)\n";
 	} else {
-		_reindex_check_unseen($self, $sync, $ibx);
-		_reindex_check_stale($self, $sync, $ibx) unless $sync->{quit};
+		_reindex_check_ibx($self, $sync, $ibx);
 	}
 	delete @$ibx{qw(over mm search git)}; # won't need these for a bit
 }
diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm
index 98de82c048c8..30ad949dd027 100644
--- a/lib/PublicInbox/Over.pm
+++ b/lib/PublicInbox/Over.pm
@@ -108,8 +108,8 @@ sub do_get {
 }
 
 sub query_xover {
-	my ($self, $beg, $end) = @_;
-	do_get($self, <<'', {}, $beg, $end);
+	my ($self, $beg, $end, $opt) = @_;
+	do_get($self, <<'', $opt, $beg, $end);
 SELECT num,ts,ds,ddd FROM over WHERE num >= ? AND num <= ?
 ORDER BY num ASC
 
diff --git a/lib/PublicInbox/OverIdx.pm b/lib/PublicInbox/OverIdx.pm
index 985abbf4e693..46f7a066275a 100644
--- a/lib/PublicInbox/OverIdx.pm
+++ b/lib/PublicInbox/OverIdx.pm
@@ -543,9 +543,13 @@ CREATE TABLE IF NOT EXISTS xref3 (
 	$dbh->do('CREATE INDEX IF NOT EXISTS idx_docid ON xref3 (docid)');
 
 	# performance critical, this is not UNIQUE since we may need to
-	# tolerate some old bugs from indexing mirrors
-	$dbh->do('CREATE INDEX IF NOT EXISTS idx_nntp ON '.
-		'xref3 (oidbin,xnum,ibx_id)');
+	# tolerate some old bugs from indexing mirrors.  n.b. we used
+	# to index oidbin here, but leaving it out speeds up reindexing
+	# and "XHDR Xref <$MSGID>" isn't any slower w/o oidbin
+	$dbh->do('CREATE INDEX IF NOT EXISTS idx_reindex ON '.
+		'xref3 (xnum,ibx_id)');
+
+	$dbh->do('CREATE INDEX IF NOT EXISTS idx_oidbin ON xref3 (oidbin)');
 
 		$dbh->do(<<'');
 CREATE TABLE IF NOT EXISTS eidx_meta (

  reply	other threads:[~2021-10-11  8:06 UTC|newest]

Thread overview: 7+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2021-10-11  8:06 [PATCH 0/6] extindex: --reindex --fast gets faster Eric Wong
2021-10-11  8:06 ` Eric Wong [this message]
2021-10-11  8:06 ` [PATCH 2/6] sqlite: PRAGMA optimize on close Eric Wong
2021-10-11  8:06 ` [PATCH 3/6] extindex: rename var: active => active_shards Eric Wong
2021-10-11  8:06 ` [PATCH 4/6] extindex: share unref logic in more places Eric Wong
2021-10-11  8:06 ` [PATCH 5/6] extindex: more consistent doc removal Eric Wong
2021-10-11  8:06 ` [PATCH 6/6] extindex: avoid invalid blobs after unref Eric Wong

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

  List information: https://public-inbox.org/README

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=20211011080620.27478-2-e@80x24.org \
    --to=e@80x24.org \
    --cc=meta@public-inbox.org \
    --subject='Re: [PATCH 1/6] extindex: speed up --reindex --fast' \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

Code repositories for project(s) associated with this 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).