[PATCH 8/8] msgmap: speed up minmax with separate queries

[Eric Wong (Contractor, The Linux Foundation)]

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

--
unsubscribe: meta+unsubscr...@public-inbox.org
archive: https://public-inbox.org/meta/