From 87dca6d8d5988c5eb54019cca342450b0b7dd6b7 Mon Sep 17 00:00:00 2001 From: "Eric Wong (Contractor, The Linux Foundation)" Date: Mon, 2 Apr 2018 00:04:55 +0000 Subject: www: rework query responses to avoid COUNT in SQLite In many cases, we do not care about the total number of messages. It's a rather expensive operation in SQLite (Xapian only provides an estimate). For LKML, this brings top-level /$INBOX/ loading time from ~375ms to around 60ms on my system. Days ago, this operation was taking 800-900ms(!) for me before introducing the SQLite overview DB. --- lib/PublicInbox/Feed.pm | 11 +++++------ lib/PublicInbox/Mbox.pm | 12 +++++------- lib/PublicInbox/Over.pm | 8 ++++++-- lib/PublicInbox/Search.pm | 15 +++++++++++---- lib/PublicInbox/View.pm | 17 ++++++----------- 5 files changed, 33 insertions(+), 30 deletions(-) (limited to 'lib/PublicInbox') diff --git a/lib/PublicInbox/Feed.pm b/lib/PublicInbox/Feed.pm index 2f59f8c2..ff20d7fa 100644 --- a/lib/PublicInbox/Feed.pm +++ b/lib/PublicInbox/Feed.pm @@ -27,14 +27,13 @@ sub generate { sub generate_thread_atom { my ($ctx) = @_; my $mid = $ctx->{mid}; - my $res = $ctx->{srch}->get_thread($mid); - return _no_thread() unless $res->{total}; + my $msgs = $ctx->{srch}->get_thread($mid); + return _no_thread() unless @$msgs; my $ibx = $ctx->{-inbox}; my $html_url = $ibx->base_url($ctx->{env}); $html_url .= PublicInbox::Hval->new_msgid($mid)->{href}; $ctx->{-html_url} = $html_url; - my $msgs = $res->{msgs}; PublicInbox::WwwAtomStream->response($ctx, 200, sub { while (my $smsg = shift @$msgs) { $ibx->smsg_mime($smsg) and return $smsg; @@ -114,10 +113,10 @@ sub recent_msgs { my $o = $qp ? $qp->{o} : 0; $o += 0; $o = 0 if $o < 0; - my $res = $ibx->recent({ limit => $max, offset => $o }); + my $msgs = $ibx->recent({ limit => $max, offset => $o }); my $next = $o + $max; - $ctx->{next_page} = "o=$next" if $res->{total} >= $next; - return $res->{msgs}; + $ctx->{next_page} = "o=$next" if scalar(@$msgs) == $max; + return $msgs; } my $hex = '[a-f0-9]'; diff --git a/lib/PublicInbox/Mbox.pm b/lib/PublicInbox/Mbox.pm index 1b68f027..05de6be1 100644 --- a/lib/PublicInbox/Mbox.pm +++ b/lib/PublicInbox/Mbox.pm @@ -217,12 +217,12 @@ sub set_filename ($$) { sub getline { my ($self) = @_; my $ctx = $self->{ctx} or return; - my $res; my $ibx = $ctx->{-inbox}; my $gz = $self->{gz}; + my $msgs = $self->{msgs}; do { # work on existing result set - while (defined(my $smsg = shift @{$self->{msgs}})) { + while (defined(my $smsg = shift @$msgs)) { my $msg = eval { $ibx->msg_by_smsg($smsg) } or next; $msg = Email::Simple->new($msg); $gz->write(PublicInbox::Mbox::msg_str($ctx, $msg, @@ -247,11 +247,9 @@ sub getline { } # refill result set - $res = $self->{cb}->($self->{opts}); - $self->{msgs} = $res->{msgs}; - $res = scalar @{$self->{msgs}}; - $self->{opts}->{offset} += $res; - } while ($res); + $msgs = $self->{msgs} = $self->{cb}->($self->{opts}); + $self->{opts}->{offset} += scalar @$msgs; + } while (@$msgs); $gz->close; delete $self->{ctx}; ${delete $self->{buf}}; diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm index cf7a8849..c74072a2 100644 --- a/lib/PublicInbox/Over.pm +++ b/lib/PublicInbox/Over.pm @@ -88,10 +88,12 @@ LIMIT 1 SELECT * $cond ORDER BY ts ASC + return $msgs unless wantarray; + my $nr = $dbh->selectrow_array(<<"", undef, $tid, $sid); SELECT COUNT(num) $cond - { total => $nr, msgs => $msgs }; + ($nr, $msgs); } sub recent { @@ -100,10 +102,12 @@ sub recent { SELECT * FROM over WHERE num > 0 ORDER BY ts DESC + return $msgs unless wantarray; + my $nr = $self->{dbh}->selectrow_array(<<''); SELECT COUNT(num) FROM over WHERE num > 0 - { total => $nr, msgs => $msgs }; + ($nr, $msgs); } sub get_art { diff --git a/lib/PublicInbox/Search.pm b/lib/PublicInbox/Search.pm index 91251246..84c0a22f 100644 --- a/lib/PublicInbox/Search.pm +++ b/lib/PublicInbox/Search.pm @@ -185,10 +185,16 @@ sub get_thread { sub retry_reopen { my ($self, $cb) = @_; - my $ret; for my $i (1..10) { - eval { $ret = $cb->() }; - return $ret unless $@; + if (wantarray) { + my @ret; + eval { @ret = $cb->() }; + return @ret unless $@; + } else { + my $ret; + eval { $ret = $cb->() }; + return $ret unless $@; + } # Exception: The revision being read has been discarded - # you should call Xapian::Database::reopen() if (ref($@) eq 'Search::Xapian::DatabaseModifiedError') { @@ -226,8 +232,9 @@ sub _enquire_once { my @msgs = map { PublicInbox::SearchMsg->load_doc($_->get_document); } $mset->items; + return \@msgs unless wantarray; - { total => $mset->get_matches_estimated, msgs => \@msgs } + ($mset->get_matches_estimated, \@msgs) } # read-write diff --git a/lib/PublicInbox/View.pm b/lib/PublicInbox/View.pm index 8ac405f2..cad90a79 100644 --- a/lib/PublicInbox/View.pm +++ b/lib/PublicInbox/View.pm @@ -408,9 +408,7 @@ sub thread_html { my ($ctx) = @_; my $mid = $ctx->{mid}; my $srch = $ctx->{srch}; - my $sres = $srch->get_thread($mid); - my $msgs = $sres->{msgs}; - my $nr = $sres->{total}; + my ($nr, $msgs) = $srch->get_thread($mid); return missing_thread($ctx) if $nr == 0; my $skel = '
';
 	$skel .= $nr == 1 ? 'only message in thread' : 'end of thread';
@@ -649,8 +647,7 @@ sub thread_skel {
 	my ($dst, $ctx, $hdr, $tpfx) = @_;
 	my $srch = $ctx->{srch};
 	my $mid = mids($hdr)->[0];
-	my $sres = $srch->get_thread($mid);
-	my $nr = $sres->{total};
+	my ($nr, $msgs) = $srch->get_thread($mid);
 	my $expand = qq(expand[flat) .
 	                qq(|nested]  ) .
 			qq(mbox.gz  ) .
@@ -680,12 +677,11 @@ sub thread_skel {
 	$ctx->{prev_attr} = '';
 	$ctx->{prev_level} = 0;
 	$ctx->{dst} = $dst;
-	$sres = $sres->{msgs};
 
 	# reduce hash lookups in skel_dump
 	my $ibx = $ctx->{-inbox};
 	$ctx->{-obfs_ibx} = $ibx->{obfuscate} ? $ibx : undef;
-	walk_thread(thread_results($ctx, $sres), $ctx, *skel_dump);
+	walk_thread(thread_results($ctx, $msgs), $ctx, *skel_dump);
 
 	$ctx->{parent_msg} = $parent;
 }
@@ -1066,11 +1062,10 @@ sub index_topics {
 
 	$ctx->{order} = [];
 	my $srch = $ctx->{srch};
-	my $sres = $ctx->{-inbox}->recent({offset => $off, limit => 200 });
-	$sres = $sres->{msgs};
-	my $nr = scalar @$sres;
+	my $msgs = $ctx->{-inbox}->recent({offset => $off, limit => 200 });
+	my $nr = scalar @$msgs;
 	if ($nr) {
-		walk_thread(thread_results($ctx, $sres), $ctx, *acc_topic);
+		walk_thread(thread_results($ctx, $msgs), $ctx, *acc_topic);
 	}
 	$ctx->{-next_o} = $off + $nr;
 	$ctx->{-cur_o} = $off;
-- 
cgit v1.2.3-24-ge0c7