user/dev discussion of public-inbox itself
 help / color / mirror / code / Atom feed
From: "Eric Wong (Contractor, The Linux Foundation)" <e@80x24.org>
To: meta@public-inbox.org
Subject: [PATCH 8/8] msgmap: speed up minmax with separate queries
Date: Sat,  7 Apr 2018 03:41:54 +0000	[thread overview]
Message-ID: <20180407034154.2309-9-e@80x24.org> (raw)
In-Reply-To: <20180407034154.2309-1-e@80x24.org>

This significantly improves the performance of the NNTP GROUP
command with 2.7 million messages from over 250ms to 700us.
SQLite is weird about this, but at least there's a way to
optimize it.
---
 lib/PublicInbox/Msgmap.pm | 10 +++++++---
 t/perf-nntpd.t            | 13 +++++++++----
 2 files changed, 16 insertions(+), 7 deletions(-)

diff --git a/lib/PublicInbox/Msgmap.pm b/lib/PublicInbox/Msgmap.pm
index f5f8843..feef8ba 100644
--- a/lib/PublicInbox/Msgmap.pm
+++ b/lib/PublicInbox/Msgmap.pm
@@ -138,10 +138,14 @@ sub num_for {
 sub minmax {
 	my ($self) = @_;
 	my $dbh = $self->{dbh};
-	my $sth = $self->{num_minmax} ||=
-		$dbh->prepare('SELECT MIN(num),MAX(num) FROM msgmap');
+	# breaking MIN and MAX into separate queries speeds up from 250ms
+	# to around 700us with 2.7million messages.
+	my $sth = $dbh->prepare_cached('SELECT MIN(num) FROM msgmap', undef, 1);
 	$sth->execute;
-        $sth->fetchrow_array;
+	my $min = $sth->fetchrow_array;
+	$sth = $dbh->prepare_cached('SELECT MAX(num) FROM msgmap', undef, 1);
+	$sth->execute;
+	($min, $sth->fetchrow_array);
 }
 
 sub mid_prefixes {
diff --git a/t/perf-nntpd.t b/t/perf-nntpd.t
index 4987f98..e502153 100644
--- a/t/perf-nntpd.t
+++ b/t/perf-nntpd.t
@@ -3,7 +3,7 @@
 use strict;
 use warnings;
 use Test::More;
-use Benchmark qw(:all);
+use Benchmark qw(:all :hireswallclock);
 use PublicInbox::Inbox;
 use File::Temp qw/tempdir/;
 use POSIX qw(dup2);
@@ -79,8 +79,13 @@ $s = IO::Socket::INET->new(%opts);
 $s->autoflush(1);
 my $buf = $s->getline;
 is($buf, "201 server ready - post via email\r\n", 'got greeting');
-ok($s->print("GROUP $group\r\n"), 'changed group');
-$buf = $s->getline;
+
+my $t = timeit(10, sub {
+	ok($s->print("GROUP $group\r\n"), 'changed group');
+	$buf = $s->getline;
+});
+diag 'GROUP took: ' . timestr($t);
+
 my ($tot, $min, $max) = ($buf =~ /\A211 (\d+) (\d+) (\d+) /);
 ok($tot && $min && $max, 'got GROUP response');
 my $nr = $max - $min;
@@ -100,7 +105,7 @@ sub read_until_dot ($) {
 	$n;
 }
 
-my $t = timeit(1, sub {
+$t = timeit(1, sub {
 	$s->print("XOVER $spec\r\n");
 	$n = read_until_dot($s);
 });
-- 
EW


      parent reply	other threads:[~2018-04-07  3:41 UTC|newest]

Thread overview: 9+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2018-04-07  3:41 [PATCH 0/8] less code, less memory, more speed Eric Wong (Contractor, The Linux Foundation)
2018-04-07  3:41 ` [PATCH 1/8] psgi: ensure /$INBOX/$MESSAGE_ID/T/ endpoint is chronological Eric Wong (Contractor, The Linux Foundation)
2018-04-07  3:41 ` [PATCH 2/8] over: avoid excessive SELECT Eric Wong (Contractor, The Linux Foundation)
2018-04-07  3:41 ` [PATCH 3/8] over: remove forked subprocess Eric Wong (Contractor, The Linux Foundation)
2018-04-07  3:41 ` [PATCH 4/8] v2writable: reduce barriers Eric Wong (Contractor, The Linux Foundation)
2018-04-07  3:41 ` [PATCH 5/8] index: allow specifying --jobs=0 to disable multiprocess Eric Wong (Contractor, The Linux Foundation)
2018-04-07  3:41 ` [PATCH 6/8] convert: support converting with altid defined Eric Wong (Contractor, The Linux Foundation)
2018-04-07  3:41 ` [PATCH 7/8] store less data in the Xapian document Eric Wong (Contractor, The Linux Foundation)
2018-04-07  3:41 ` Eric Wong (Contractor, The Linux Foundation) [this message]

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=20180407034154.2309-9-e@80x24.org \
    --to=e@80x24.org \
    --cc=meta@public-inbox.org \
    /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
Be sure your reply has a Subject: header at the top and a blank line before the message body.
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).