From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on dcvr.yhbt.net X-Spam-Level: X-Spam-ASN: X-Spam-Status: No, score=-4.0 required=3.0 tests=ALL_TRUSTED,BAYES_00 shortcircuit=no autolearn=ham autolearn_force=no version=3.4.2 Received: from localhost (dcvr.yhbt.net [127.0.0.1]) by dcvr.yhbt.net (Postfix) with ESMTP id EDC7A1F8C6 for ; Thu, 8 Jul 2021 08:25:19 +0000 (UTC) From: Eric Wong To: meta@public-inbox.org Subject: [PATCH] extindex: dedupe: reduce SQLite contention and dirty data Date: Thu, 8 Jul 2021 08:25:19 +0000 Message-Id: <20210708082519.7987-1-e@80x24.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: 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(< ? 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;