Print latency percentiles in pgbench

Started by Roman Khapov17 days ago7 messageshackers
Jump to latest
#1Roman Khapov
rkhapov@yandex-team.ru

Hi hackers!

I did a lot of benchmarks in last time, and more are incoming.
While benchmarking with pgbench I keep hitting the same annoyance: the final
report says only "latency average" and "latency stddev",
which tells nothing about the distribution. p95/p99 is usually what you
actually want when evaluating tail latency.

Today the answer is to run with -l and post-process
the log, but logging itself perturbs subsequent transactions (fsync'ed writes
on the hot path), and requires a second step.

What if we add new option, smth like
--latency-percentiles[=p1,p2,...], which extends the final reports with:
latency min = 4.114 ms
latency max = 15.213 ms
latency p50 = 6.097 ms
latency p95 = 12.211 ms
latency p99 = 13.883 ms

Main design points, kept deliberately simple for a first version:
- percentiles are estimated from a fixed-size log-scale histogram
(32 sub-buckets per octave, 4 KB, zero allocations, a few ops per
transaction; no overhead at all when the option is not given). A more
clever estimator (t-digest and friends) can replace it later behind
the same interface, but seemed like overkill to start with;
- the histogram is a separate member of StatsData next to the latency
SimpleStats, so existing stats structs/log files/per-command table and
the default output stay untouched;
- semantics match existing latency stats: successful, non-skipped
transactions only;

Accuracy vs a sorted -l log of the same run: p50 0.535 vs 0.533 ms,
p99 1.152 vs 1.158 ms - within the sub-bucket resolution.

POC-patch attached, any feedback would be appreciated!

--
Best regards,
Roman Khapov

Attachments:

t254490_1
0001-pgbench-add-latency-percentiles-option.patchapplication/octet-stream; name=0001-pgbench-add-latency-percentiles-option.patch; x-unix-mode=0644Download+298-18
#2Hữu Đôn Hoàng
hoanghuudon02hp@gmail.com
In reply to: Roman Khapov (#1)
Re: Print latency percentiles in pgbench

Hi Roman,

POC-patch attached, any feedback would be appreciated!

Thanks for working on this. I've started with the documentation part
of the patch, on master at 30faf13889b.

The doc changes re-indent some existing text without changing it:

- the <varlistentry> and <term> lines of --log-prefix,
- the "when neither --progress, --rate, nor --latency-limit" lines,
- the whole paragraph on retries ("The main report contains the
number of failed transactions...").

The new --latency-percentiles varlistentry is also indented one level
deeper than the entries around it, so --log-prefix now has its
<varlistentry> and <term> at a different depth from its <listitem>.

Because of this, the doc diff is +57/-12, while git diff -w shows
+47/-2. Could you keep the existing indentation and indent the new
entry like its neighbours, so that only the real changes remain?

Regards,
Don Hoang

#3Hữu Đôn Hoàng
hoanghuudon02hp@gmail.com
In reply to: Hữu Đôn Hoàng (#2)
Re: Print latency percentiles in pgbench

Hi Roman,

I've continued reviewing the patch, on master at 30faf13889b.

1. --latency-percentiles=nan passes the range check and then fails
an assertion:

$ pgbench --latency-percentiles=nan -t 10 bench
pgbench: pgbench.c:1517: latencyHistogramPercentile: Assertion
`p > 0.0 && p <= 100.0' failed.

Every comparison with NaN is false, so "percentile <= 0.0 ||
percentile > 100.0" does not reject it. Writing the check as
!(percentile > 0.0 && percentile <= 100.0) would.

2. The patch adds no tests. It would be good to cover the parse
errors (0, 101, abc, ",50", 11 values, nan) in
002_pgbench_no_server.pl and check the new output lines in
001_pgbench_with_server.pl.

3. Because the argument is optional, the list has to be attached
with "=". With a space, the list is taken as the database name:

$ pgbench --latency-percentiles 50,99 -t 10 bench
pgbench: error: too many command-line arguments (first is "bench")

$ pgbench --latency-percentiles 50,99 -t 10
pgbench: error: connection to server ... failed: FATAL: database
"50,99" does not exist

Neither message points at the option, so I think the docs should
say that the value must be given as --latency-percentiles=LIST.

4. The docs say the reported percentile "lies within the histogram
bucket that contains the requested rank", but not how wide a
bucket is. It would help to state the resolution (about 3% of the
value) and the cases where it does not hold:

- A percentile can fall outside [min, max] because of the
interpolation inside a bucket. With a script containing only
"\sleep 2 ms" (500 transactions) I got:

latency min = 2.023 ms
latency p0.1 = 2.016 ms

Clamping the result to the min/max that SimpleStats already
tracks would avoid this.

- Below 32 us an octave is a single bucket, so nothing is
interpolated: for 1000 latencies between 20 and 29 us, p1, p50
and p99 all come out as 16 us.

Regards,
Don Hoang

#4Phil Alger
phil@exkalibr.com
In reply to: Hữu Đôn Hoàng (#3)
Re: Print latency percentiles in pgbench

Hello,

+1 to the NaN rejection, tests, and documenting the '=' form.

One small addition: duplicates like --latency-percentiles=50,50
are accepted; probably fine, but worth confirming that's intentional.

Best,
Phil Alger

--
EDB

On Wednesday, November 4th, 2026 at 08:51, Hữu Đôn Hoàng <hoanghuudon02hp@gmail.com> wrote:

Show quoted text

Hi Roman,

I've continued reviewing the patch, on master at 30faf13889b.

1. --latency-percentiles=nan passes the range check and then fails
an assertion:

$ pgbench --latency-percentiles=nan -t 10 bench
pgbench: pgbench.c:1517: latencyHistogramPercentile: Assertion
`p > 0.0 && p <= 100.0' failed.

Every comparison with NaN is false, so "percentile <= 0.0 ||
percentile > 100.0" does not reject it. Writing the check as
!(percentile > 0.0 && percentile <= 100.0) would.

2. The patch adds no tests. It would be good to cover the parse
errors (0, 101, abc, ",50", 11 values, nan) in
002_pgbench_no_server.pl and check the new output lines in
001_pgbench_with_server.pl.

3. Because the argument is optional, the list has to be attached
with "=". With a space, the list is taken as the database name:

$ pgbench --latency-percentiles 50,99 -t 10 bench
pgbench: error: too many command-line arguments (first is "bench")

$ pgbench --latency-percentiles 50,99 -t 10
pgbench: error: connection to server ... failed: FATAL: database
"50,99" does not exist

Neither message points at the option, so I think the docs should
say that the value must be given as --latency-percentiles=LIST.

4. The docs say the reported percentile "lies within the histogram
bucket that contains the requested rank", but not how wide a
bucket is. It would help to state the resolution (about 3% of the
value) and the cases where it does not hold:

- A percentile can fall outside [min, max] because of the
interpolation inside a bucket. With a script containing only
"\sleep 2 ms" (500 transactions) I got:

latency min = 2.023 ms
latency p0.1 = 2.016 ms

Clamping the result to the min/max that SimpleStats already
tracks would avoid this.

- Below 32 us an octave is a single bucket, so nothing is
interpolated: for 1000 latencies between 20 and 29 us, p1, p50
and p99 all come out as 16 us.

Regards,
Don Hoang

Attachments:

publickey - phil@exkalibr.com - 0x504BCE4B.ascapplication/pgp-keys; filename="publickey - phil@exkalibr.com - 0x504BCE4B.asc"; name="publickey - phil@exkalibr.com - 0x504BCE4B.asc"Download
#5Zakhar Pashkin
kaisenaiko@gmail.com
In reply to: Hữu Đôn Hoàng (#3)
Re: Print latency percentiles in pgbench

Hi Roman, Don, Phil,

Following Don's review, I put together a supplementary parser and test
patch on top of Roman's October 31 version. i also reproduced the reported
NaN assertion failure.

The patch uses the positive range check Don suggested, retaining the
existing strtod() and errno checks. It adds argument-error tests and short
SELECT 1 runs checking the output labels and formatting. Invalid-input
tests require the percentile diagnostic, exit status 1 and empty stdout;
a connection failure cannot satisfy them. The docs now show explicitly
that a list must be attached with "=".

I left repeated values accepted and preserved the supplied order, with
a test covering both. Is that the intended behavior? The patch also moves
the existing duplicate-option check ahead of both forms and rejects an
empty final list element. Thus bare/bare and explicit/bare repetitions
are rejected consistently, and "--latency-percentiles=50," reports an
invalid list. I can separate those choices from the NaN fix if preferred.

Both complete pgbench TAP tests pass (787 checks) with and without
assertions on Linux x86_64 with GCC 13.3, using master at 627c4df3b73a.
The builds used --enable-tap-tests --without-readline --without-zlib
--without-icu; the assertion build also used --enable-cassert. I have
not tested other platforms.

The attachment applies after the original feature patch.

Regards,
Zakhar Pashkin

Attachments:

v2-0001-pgbench-validate-latency-percentile-options.patchapplication/octet-stream; name=v2-0001-pgbench-validate-latency-percentile-options.patchDownload+126-9
#6Dmitry Dolgov
9erthalion6@gmail.com
In reply to: Roman Khapov (#1)
Re: Print latency percentiles in pgbench

On Sat, Oct 31, 2026 at 11:54:32AM +0500, Roman Khapov wrote:
I did a lot of benchmarks in last time, and more are incoming.
While benchmarking with pgbench I keep hitting the same annoyance: the final
report says only "latency average" and "latency stddev",
which tells nothing about the distribution. p95/p99 is usually what you
actually want when evaluating tail latency.

Yep, having pgbench reporting percentiles is definitely a good idea.

What if we add new option, smth like
--latency-percentiles[=p1,p2,...], which extends the final reports with:
latency min = 4.114 ms
latency max = 15.213 ms
latency p50 = 6.097 ms
latency p95 = 12.211 ms
latency p99 = 13.883 ms

Main design points, kept deliberately simple for a first version:
- percentiles are estimated from a fixed-size log-scale histogram
(32 sub-buckets per octave, 4 KB, zero allocations, a few ops per
transaction; no overhead at all when the option is not given). A more
clever estimator (t-digest and friends) can replace it later behind
the same interface, but seemed like overkill to start with;
- the histogram is a separate member of StatsData next to the latency
SimpleStats, so existing stats structs/log files/per-command table and
the default output stay untouched;
- semantics match existing latency stats: successful, non-skipped
transactions only;

I'm less sure about this particular implementation though. From what I
understand buckets and sub-buckets width is log-scale, meaning that the
higher it gets (overall and withing a bucket) the more uncertainly we
get, right? It could be quite annoying at times, like with bpftrace hist
where too large buckets sometimes obscure interesting data variation on
the far side of the range. Even more, I've managed to get a test run
where the p95 was larger than the reported max latency:

latency average = 67397.720 ms
latency stddev = 2167.818 ms
latency min = 63949.915 ms
latency max = 70166.801 ms
latency p95 = 70464.307 ms

I think this can be an argument for more complicated, but more precise
algorithm for percentiles estimation.

On top of that it would be great if the actual algorithm implementation
would not be tightly coupled with the pgbench itself, but rather will be
agnostic to the call site, allowing to use it everywhere.

Aside of already mentioned issues, the interface could be probably
improved:

* It looks weird to me that the same option enables min/max and the
percentiles. While I see where it's coming from, if the option is
called "latency-percentiles", it's reasonable to expect it to show
percentiles, and have a separate option for min/max.

* There are examples of options that could be repeated multiple times,
e.g. the pgbench script to invoke. Maybe it would be easier to do the
same with the percentiles, instead of coming up with a format to
scrabmle everything into a string.

* Pgbench progress also reports latency average, maybe worth extending
it as well.

#7shihao zhong
zhong950419@gmail.com
In reply to: Dmitry Dolgov (#6)
Re: Print latency percentiles in pgbench

I like this feature +1