diff options
author | Eric Wong (Contractor, The Linux Foundation) <e@80x24.org> | 2018-04-07 03:41:54 +0000 |
---|---|---|
committer | Eric Wong (Contractor, The Linux Foundation) <e@80x24.org> | 2018-04-07 03:42:30 +0000 |
commit | 74b92712fa7a21fe504b9908edebcf11bb9dc170 (patch) | |
tree | cf4abdc1b8d8a64151fdcbacd095942fa227ff8e | |
parent | 3348ad4b3b1a0865ee58a902953165ea0f4aa4bd (diff) | |
download | public-inbox-74b92712fa7a21fe504b9908edebcf11bb9dc170.tar.gz |
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.
-rw-r--r-- | lib/PublicInbox/Msgmap.pm | 10 | ||||
-rw-r--r-- | 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 f5f88431..feef8ba7 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 4987f981..e5021532 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); }); |