From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.4.0 (2014-02-07) 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.0 Received: from localhost (dcvr.yhbt.net [127.0.0.1]) by dcvr.yhbt.net (Postfix) with ESMTP id 68AFD1F51C for ; Sat, 7 Apr 2018 03:41:56 +0000 (UTC) From: "Eric Wong (Contractor, The Linux Foundation)" 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 Message-Id: <20180407034154.2309-9-e@80x24.org> In-Reply-To: <20180407034154.2309-1-e@80x24.org> References: <20180407034154.2309-1-e@80x24.org> List-Id: 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