* [PATCH 0/6] extindex: --reindex --fast gets faster
@ 2021-10-11 8:06 7% Eric Wong
2021-10-11 8:06 4% ` [PATCH 1/6] extindex: speed up --reindex --fast 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 7%]
* [PATCH 1/6] extindex: speed up --reindex --fast
2021-10-11 8:06 7% [PATCH 0/6] extindex: --reindex --fast gets faster Eric Wong
@ 2021-10-11 8:06 4% ` Eric Wong
0 siblings, 0 replies; 2+ results
From: Eric Wong @ 2021-10-11 8:06 UTC (permalink / raw)
To: meta
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 (
^ permalink raw reply related [relevance 4%]
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 7% [PATCH 0/6] extindex: --reindex --fast gets faster Eric Wong
2021-10-11 8:06 4% ` [PATCH 1/6] extindex: speed up --reindex --fast 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).