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
prev 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.
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).