Re: few ideas for pgbench - Mailing list pgsql-hackers

Hello,

>>> When I run pgbench, I usually work with more releases together, so the
>>> server version is important info.
>>
>> Ok. Yes.

Here is a putative patch for this simple part.

>>> What about CSV? Any run can produce one row.
>>
>> Yep, CSV is simple and nice. It depends on what information you would
>> like. For instance for progress report (-P 1) or logs/sampling (-l) would
>> be relevant candidates for CSV. Not so much for the final report, though.
>
> I think so there can be almost all information. We have to ensure
> consistency of columns.
>
> The basic usage can be
>
> for ....
> do
>  pg_bench ... >> logfile
> done
>
> and log file can looks like
>
> start time, rowno, serverver, clientver, connections, scale, readonly,
> jobs, tps, latency, ...
>
> The header row can be optional

Hmmm. It is less clear how to do that with minimal code impact on the
code, as some options which change the report structure, eg when using
multiple scripts (-b/-f) or having detailed per-op informations (-r), as
show below:

  sh> pgbench -P 1 -T 10 -M prepared -c 2 -b se@9 -b si -r
  starting vacuum...end.
  progress: 1.0 s, 10666.9 tps, lat 0.186 ms stddev 0.454
  progress: 2.0 s, 9928.0 tps, lat 0.201 ms stddev 0.466
  progress: 3.0 s, 10314.8 tps, lat 0.193 ms stddev 0.469
  progress: 4.0 s, 10042.7 tps, lat 0.198 ms stddev 0.466
  progress: 5.0 s, 11084.3 tps, lat 0.180 ms stddev 0.408
  progress: 6.0 s, 9804.1 tps, lat 0.203 ms stddev 0.474
  progress: 7.0 s, 10271.5 tps, lat 0.194 ms stddev 0.463
  progress: 8.0 s, 10511.5 tps, lat 0.190 ms stddev 0.424
  progress: 9.0 s, 10005.7 tps, lat 0.199 ms stddev 0.501
  progress: 10.0 s, 10512.4 tps, lat 0.190 ms stddev 0.428
  pgbench (PostgreSQL) 14.0
  server version: 13.2
  transaction type: multiple scripts
  scaling factor: 1
  query mode: prepared
  number of clients: 2
  number of threads: 1
  duration: 10 s
  number of transactions actually processed: 103144
  latency average = 0.193 ms
  latency stddev = 0.455 ms
  initial connection time = 5.043 ms
  tps = 10319.361549 (without initial connection time)
  SQL script 1: <builtin: select only>
  - weight: 9 (targets 90.0% of total)
  - 92654 transactions (89.8% of total, tps = 9269.856947)
  - latency average = 0.052 ms
  - latency stddev = 0.018 ms
  - statement latencies in milliseconds:
          0.000  \set aid random(1, 100000 * :scale)
          0.052  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
  SQL script 2: <builtin: simple update>
  - weight: 1 (targets 10.0% of total)
  - 10490 transactions (10.2% of total, tps = 1049.504602)
  - latency average = 1.436 ms
  - latency stddev = 0.562 ms
  - statement latencies in milliseconds:
          0.001  \set aid random(1, 100000 * :scale)
          0.000  \set bid random(1, 1 * :scale)
          0.000  \set tid random(1, 10 * :scale)
          0.000  \set delta random(-5000, 5000)
          0.027  BEGIN;
          0.065  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
          0.045  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
          0.048  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
          1.249  END

The nature of columns would change depending on options, eg "initial
connection time" does not make sense under -C, so that under a loop around
pgbench scenario rows would not necessarily be consistent…

Also, I'm not sure whether such a report can/should include all inputs
options.

Finally it is unclear how to add such a feature with minimal impact on the
source code.

What I usually do is to put each pgbench run output in a separate file and
write a small shell/perl/python script to process these, possibly
generating CSV on the way.

--
Fabien.