user/dev discussion of public-inbox itself
 help / color / mirror / code / Atom feed
* [PATCH] extindex: dedupe: reduce SQLite contention and dirty data
@ 2021-07-08  8:25 Eric Wong
  0 siblings, 0 replies; only message in thread
From: Eric Wong @ 2021-07-08  8:25 UTC (permalink / raw)
  To: meta

Complex queries causes SQLite to block readers for longer than
their retry period.  For dedupe, it was also preventing us from
making good use of checkpoints due to the query time.

With many deduplications, checkpoints are necessary to maintain
system health due to having too much data piled up.
---
 lib/PublicInbox/ExtSearchIdx.pm | 14 +++++++-------
 1 file changed, 7 insertions(+), 7 deletions(-)

diff --git a/lib/PublicInbox/ExtSearchIdx.pm b/lib/PublicInbox/ExtSearchIdx.pm
index a421e16b..0e27bba6 100644
--- a/lib/PublicInbox/ExtSearchIdx.pm
+++ b/lib/PublicInbox/ExtSearchIdx.pm
@@ -884,19 +884,20 @@ sub eidx_dedupe ($$) {
 	my $iter;
 	my $min_id = 0;
 	local $sync->{-regen_fmt} = "dedupe %u/".$self->{oidx}->max."\n";
+
+	# note: we could write this query more intelligently,
+	# but that causes lock contention with read-only processes
 dedupe_restart:
 	$iter = $self->{oidx}->dbh->prepare(<<EOS);
-SELECT DISTINCT(mid),id FROM msgid WHERE id IN
-(SELECT id FROM id2num WHERE id > ? GROUP BY num HAVING COUNT(num) > 1)
-ORDER BY id
+SELECT mid,id FROM msgid WHERE id > ? ORDER BY id ASC
 EOS
 	$iter->execute($min_id);
 	while (my ($mid, $id) = $iter->fetchrow_array) {
 		last if $sync->{quit};
 		$self->{current_info} = "dedupe $mid";
 		${$sync->{nr}} = $min_id = $id;
-		my ($n, $prv, @smsg);
-		while (my $x = $self->{oidx}->next_by_mid($mid, \$n, \$prv)) {
+		my ($prv, @smsg);
+		while (my $x = $self->{oidx}->next_by_mid($mid, \$id, \$prv)) {
 			push @smsg, $x;
 		}
 		next if scalar(@smsg) < 2;
@@ -918,8 +919,7 @@ EOS
 		# need to wait on every single one
 		$self->git->async_wait_all;
 
-		# is checkpoint needed? $iter is a very expensive query to restart
-		if (0 && checkpoint_due($sync)) {
+		if (checkpoint_due($sync)) {
 			undef $iter;
 			reindex_checkpoint($self, $sync);
 			goto dedupe_restart;

^ permalink raw reply	[flat|nested] only message in thread

only message in thread, other threads:[~2021-07-08  8:25 UTC | newest]

Thread overview: (only message) (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2021-07-08  8:25 [PATCH] extindex: dedupe: reduce SQLite contention and dirty data Eric Wong

user/dev discussion of public-inbox itself

This inbox may be cloned and mirrored by anyone:

	git clone --mirror https://public-inbox.org/meta
	git clone --mirror http://czquwvybam4bgbro.onion/meta
	git clone --mirror http://hjrcffqmbrq6wope.onion/meta
	git clone --mirror http://ou63pmih66umazou.onion/meta

	# If you have public-inbox 1.1+ installed, you may
	# initialize and index your mirror using the following commands:
	public-inbox-init -V1 meta meta/ https://public-inbox.org/meta \
		meta@public-inbox.org
	public-inbox-index meta

Example config snippet for mirrors.
Newsgroups are available over NNTP:
	nntp://news.public-inbox.org/inbox.comp.mail.public-inbox.meta
	nntp://7fh6tueqddpjyxjmgtdiueylzoqt6pt7hec3pukyptlmohoowvhde4yd.onion/inbox.comp.mail.public-inbox.meta
	nntp://ie5yzdi7fg72h7s4sdcztq5evakq23rdt33mfyfcddc5u3ndnw24ogqd.onion/inbox.comp.mail.public-inbox.meta
	nntp://4uok3hntl7oi7b4uf4rtfwefqeexfzil2w6kgk2jn5z2f764irre7byd.onion/inbox.comp.mail.public-inbox.meta
	nntp://news.gmane.io/gmane.mail.public-inbox.general
 note: .onion URLs require Tor: https://www.torproject.org/

code repositories for project(s) associated with this inbox:

	https://80x24.org/public-inbox.git

AGPL code for this site: git clone https://public-inbox.org/public-inbox.git