pgbench stats per script & other stuff

Oops, as usual I forgot something...

This v2 removes old stats code that was put in comment and simplify the
logic when counting lag times, as they are now taken into account at the
end of the transaction instead of at the beginning.

This patch adds per-script statistics & other improvements to pgbench

Rationale: Josh asked for the per-script stats:-)

Some restructuring is done so that all stats (-l --aggregate-interval
--progress --per-script-stats, latency & lag...) share the same structures
and functions to accumulate data. This limits a lot the growth of pgbench
from this patch (+17 lines).

In passing, remove the distinction between internal and external scripts.
Pgbench just execute scripts, some of them may be internal...

As a side effect, all scripts can be accumulated "pgbench -B -N -S -f ..."
would execute 4 scripts, 3 of which internal (tpc-b, simple-update,
select-only and another externally supplied one).

Also add a weight option to change the probability of choosing some scripts
when several are available.

Hmmm... Not sure that the --per-script-stats option is really useful. The
stats could always be shown when several scripts are executed?

sh> ./pgbench -T 3 -B -N -w 2 -S -w 7 --per-script-stats
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 3 s
number of transactions actually processed: 3192
latency average: 0.940 ms
tps = 1063.756045 (including connections establishing)
tps = 1065.412737 (excluding connections establishing)
SQL script 0: <builtin: TPC-B (sort of)>
- weight is 1
- 297 transactions (tps = 98.977301)
- latency average = 3.001 ms
- latency stddev = 1.320 ms
SQL script 1: <builtin: simple update>
- weight is 2
- 621 transactions (tps = 206.952539)
- latency average = 2.506 ms
- latency stddev = 1.194 ms
SQL script 2: <builtin: select only>
- weight is 7
- 2274 transactions (tps = 757.826205)
- latency average = 0.236 ms
- latency stddev = 0.083 ms

--
Fabien.

On Fri, Jul 17, 2015 at 9:50 AM, Fabien <coelho@cri.ensmp.fr> wrote:

sh> ./pgbench -T 3 -B -N -w 2 -S -w 7 --per-script-stats

That is a truly horrifying abuse of command-line arguments. -1 from
me, or minus more than one if I've got that many chits to burn.

I have been thinking that the way to do this is to push more into the
script file itself, e.g. allow:

\if random() < 0.1
stuff
\else
other stuff
\endif

Maybe that's overkill and there's some way of specifying multiple
scripts on the command line, but IMO what you've got here is not it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Robert,

On Fri, Jul 17, 2015 at 9:50 AM, Fabien <coelho@cri.ensmp.fr> wrote:

sh> ./pgbench -T 3 -B -N -w 2 -S -w 7 --per-script-stats

That is a truly horrifying abuse of command-line arguments. -1 from
me, or minus more than one if I've got that many chits to burn.

Are you against the -w, or against saying that pgbench execute scripts,
whether internal or from files?

The former is obviously a matter of taste and I can remove "-w" if nobody
wants it, too bad because the feature seems useful to me from a testing
point of view, this is a choice between aesthetic and feature. Note that
you do not have to use it if you do not like it.

The later really homogeneise the code internally and allows to factor out
things, to have orthogonal features (internal scripts are treated the same
way as external files, this requires less lines of code because it is
simpler), and does not harm anyone IMO, so it would be sad to let it go.

I have been thinking that the way to do this is to push more into the
script file itself, e.g. allow:

\if random() < 0.1
stuff
\else
other stuff
\endif

Maybe that's overkill and there's some way of specifying multiple
scripts on the command line, but IMO what you've got here is not it.

I think that is overkill, and moreover it is not useful: the point is to
collect statistics *per scripts*, with an "random if" you would not know
which part was executed, so you would loose the whole point of having per
script stats.

If you have another suggestion about how to provide weights, which does
not rely on ifs nor on options? Maybe a special comment in the script (yuk
from my point of view because the script would carry its weight whereas I
think this should be orthogonal to the script contents, but it would be
better than nothing..).

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Jul 21, 2015 at 10:42 AM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

sh> ./pgbench -T 3 -B -N -w 2 -S -w 7 --per-script-stats

That is a truly horrifying abuse of command-line arguments. -1 from
me, or minus more than one if I've got that many chits to burn.

Are you against the -w, or against saying that pgbench execute scripts,
whether internal or from files?

I'm against the idea that we accept multiple arguments for scripts,
and that a subsequent -w modifies the meaning of the
script-specifiying argument already read. That strikes me as a very
unintuitive interface. I'm not sure exactly what would be better at
the moment, but I think we need something better.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

5~5~5~

That is a truly horrifying abuse of command-line arguments. -1 from
me, or minus more than one if I've got that many chits to burn.

Are you against the -w, or against saying that pgbench execute scripts,
whether internal or from files?

I'm against the idea that we accept multiple arguments for scripts,

Pgbench *currently* already accept multiple "-f ..." options, and this is
a good thing to test realistic loads which may intermix several kind of
transactions, say a lot of readonly and some update or insert, and very
rare deletes...

Now if you do not need it you do not use it, and all is fine. Once you
have several scripts, being able to "weight" them becomes useful for
realism.

and that a subsequent -w modifies the meaning of the script-specifiying
argument already read. That strikes me as a very unintuitive interface.

Ok, I understand this "afterward modification" objection.

What if the -w would be required *before*, and supply a weight for (the
first/maybe all) script(s) specified *afterwards*, so it does not modify
something already provided? I think it would be more intuitive, or at
least less surprising.

I'm not sure exactly what would be better at the moment, but I think we
need something better.

Maybe -f file.sql:weight (yuk from my point of view, but it can be
done easily).

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[...] and that a subsequent -w modifies the meaning of the
script-specifiying argument already read. That strikes me as a very
unintuitive interface.

Ok, I understand this "afterward modification" objection.

What if the -w would be required *before*, and supply a weight for (the
first/maybe all) script(s) specified *afterwards*, so it does not modify
something already provided? I think it would be more intuitive, or at least
less surprising.

Here is a v3 which does that. If there is a better idea, do not hesitate!

sh> ./pgbench -w 9 -f one.sql -f now.sql -T 2 -P 1 --per-script-stats
starting vacuum...end.
progress: 1.0 s, 24536.0 tps, lat 0.039 ms stddev 0.024
progress: 2.0 s, 25963.8 tps, lat 0.038 ms stddev 0.015
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 2 s
number of transactions actually processed: 50501
latency average = 0.039 ms
latency stddev = 0.020 ms
tps = 25249.464772 (including connections establishing)
tps = 25339.454154 (excluding connections establishing)
SQL script 0, weight 9: one.sql
- 45366 transactions (89.8% of total, tps = 22682.070035)
- latency average = 0.038 ms
- latency stddev = 0.016 ms
SQL script 1, weight 1: now.sql
- 5135 transactions (10.2% of total, tps = 2567.394737)
- latency average = 0.044 ms
- latency stddev = 0.041 ms

--
Fabien.

On Tue, Jul 21, 2015 at 12:29 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Pgbench *currently* already accept multiple "-f ..." options, and this is a
good thing to test realistic loads which may intermix several kind of
transactions, say a lot of readonly and some update or insert, and very rare
deletes...

Hmm, I didn't realize that. The code looks a bit inconsistent right
now - e.g. we do support multiple files, but pgbench's options-parsing
loop sets ttype to a value that depends only on the last of -f, -N,
and -S encountered.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 07/21/2015 09:29 AM, Fabien COELHO wrote:

Maybe -f file.sql:weight (yuk from my point of view, but it can be done
easily).

Maybe it's past time for pgbench to have a config file?

Given that we want to define some per-workload options, the config file
would probably need to be YAML or JSON, e.g.:

pgbench --config=workload1.pgb

workload1.pgb
-------------

database: bench
port: 5432
host: localhost
user: josh
clients : 16
threads : 4
response-times : on
stats-level: script
script1:
file: script1.bench
weight: 3
script2:
file: script2.bench
weight: 1

the above would execute a pgbench with 16 clients, 4 threads, "script1"
three times as often as script2, and report stats at the script (rather
than SQL statement) level.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Robert,

Pgbench *currently* already accept multiple "-f ..." options, and this is a
good thing to test realistic loads which may intermix several kind of
transactions, say a lot of readonly and some update or insert, and very rare
deletes...

Hmm, I didn't realize that. The code looks a bit inconsistent right
now - e.g. we do support multiple files, but pgbench's options-parsing
loop sets ttype to a value that depends only on the last of -f, -N,
and -S encountered.

Indeed. However as with current pgbench <nothing>/-N/-S and -f are
mutually exclusive it is ok to have ttype set as it is.

With the patch pgbench just executes scripts and the options are not
mutually exclusive: some scripts are internal and others are not, but they
are treated the same beyond initialization, which helps removing some code
including the "ttype" variable you mention. The name of the script is kept
in an SQLScript struct along with its commands, weight and stats.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello Josh,

Maybe -f file.sql:weight (yuk from my point of view, but it can be done
easily).

Maybe it's past time for pgbench to have a config file?

That is an idea. For "simple" usage, for backward compatibility and for
people like me who like them, ISTM that options are fine too:-)

Also this may mean adding a dependency to some YAML library, configure
issues (I'm not sure whether pg currently uses YAML, and JSON is quite
verbose), maybe conditionals around the feature to compile without the
dependency, more documentation...

I'm not sure all that is desirable just for weighting scripts.

Given that we want to define some per-workload options, the config file
would probably need to be YAML or JSON, e.g.:

[...]

script1:
file: script1.bench
weight: 3
script2:
file: script2.bench
weight: 1

the above would execute a pgbench with 16 clients, 4 threads, "script1"
three times as often as script2, and report stats at the script (rather
than SQL statement) level.

Yep. Probably numbering within field names should be avoided, so a list of
records that could look like:

scripts:
- file: foo1.sql
weight: 9
- file: foo2.sql
- internal: tpc-b
weight: 2

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 07/21/2015 10:25 PM, Fabien COELHO wrote:

Hello Josh,

Maybe -f file.sql:weight (yuk from my point of view, but it can be done
easily).

Maybe it's past time for pgbench to have a config file?

That is an idea. For "simple" usage, for backward compatibility and for
people like me who like them, ISTM that options are fine too:-)

Also this may mean adding a dependency to some YAML library, configure
issues (I'm not sure whether pg currently uses YAML, and JSON is quite
verbose), maybe conditionals around the feature to compile without the
dependency, more documentation...

I'm not sure all that is desirable just for weighting scripts.

Maybe not.

If so, I would vote for:

-f script1.bench:3 -f script2.bench:1

over:

-f script1.bench -w 3 -f script2.bench -w 1

Making command-line options order-dependant breaks a lot of system call
libraries in various languages, as well as being easy to mess up.

Given that we want to define some per-workload options, the config file
would probably need to be YAML or JSON, e.g.:

[...]

script1:
file: script1.bench
weight: 3
script2:
file: script2.bench
weight: 1

the above would execute a pgbench with 16 clients, 4 threads, "script1"
three times as often as script2, and report stats at the script (rather
than SQL statement) level.

Yep. Probably numbering within field names should be avoided, so a list
of records that could look like:

Oh, you misunderstand. "script1" and "script2" are meant to be
user-supplied names which then get reported in things like response time
output. They're labels. Better example:

deposit:
file: deposit.bench
weight: 3
withdrawal:
file: withdrawal.bench
weight: 3
reporting:
file: summary_report.bench
weigh: 1

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

If so, I would vote for:
-f script1.bench:3 -f script2.bench:1
over:
-f script1.bench -w 3 -f script2.bench -w 1

Ok, I'll take that into consideration. Any other opinion out there? The
current v3 version is:

-w 3 -f script1.bench -w 1 -f script2.bench

With provision to generate errors if a -w is set but not used,
in two case.

- in the middle ... -w 4 <no script option...> -w 1 ...
- in the end ... -w 1 <no script option...>

I can provide -f x:weight easilly, but this mean that there will be no way
to associate weight for internal scripts. Not orthogonal, not very
elegant, but no big deal.

Oh, you misunderstand. "script1" and "script2" are meant to be
user-supplied names which then get reported in things like response time
output. They're labels.

Ok, that is much better. This means that labels should not choose names
which may interact with other commands, so maybe a list would have been
nice as well. Anyway, I do not think it is the way to go just for this
feature.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2015-07-22 10:54:14 -0700, Josh Berkus wrote:

Making command-line options order-dependant breaks a lot of system call
libraries in various languages, as well as being easy to mess up.

What?

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Fabien COELHO wrote:

[...] and that a subsequent -w modifies the meaning of the
script-specifiying argument already read. That strikes me as a very
unintuitive interface.

Ok, I understand this "afterward modification" objection.

What if the -w would be required *before*, and supply a weight for (the
first/maybe all) script(s) specified *afterwards*, so it does not modify
something already provided? I think it would be more intuitive, or at
least less surprising.

Here is a v3 which does that. If there is a better idea, do not hesitate!

This seems a moderately reasonable interface to me. There are other
programs that behave in that way, and once you get used to the idea, it
makes sense.

I think for complete consistency we would have to require that -w is
specified for all scripts or none of them. I am not sure if this means
that it's okay to have later scripts use a weight specified for a
previous one (i.e. it's only an error to fail to specify a weight for
options before the first -w), or each -f must have always its own -w
explicitely. In other words,
pg_bench -w2 -f script1.sql -f script2.sql
either script2 has weight 2, or it's an error, depending on what we
decide; but
pg_bench -f script1.sql -w 2 -fscript2.sql
is always an error.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jul 22, 2015 at 1:54 PM, Josh Berkus <josh@agliodbs.com> wrote:

If so, I would vote for:

-f script1.bench:3 -f script2.bench:1

over:

-f script1.bench -w 3 -f script2.bench -w 1

Making command-line options order-dependant breaks a lot of system call
libraries in various languages, as well as being easy to mess up.

Yes, I think that's a good idea. I don't know whether : is the right
separator; I kind of line @. But that's bikeshedding.

As Fabien mentions further downthread, it would be nice to set weights
for the built-ins. I'd actually like to introduce a new pgbench
option that selects a builtin script by name, so that we can have more
than three of them without running out of option names (or going
insane). So suppose we introduce pgbench -b BUILTIN_NAME, where
BUILTIN_NAME is initially one of these:

classic
classic-simple-update
classic-select-only

Then you can do pgbench -b classic@1 -b classic-select-only@9 or
similar to get 10% write, 90% read.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Yes, I think that's a good idea. I don't know whether : is the right
separator; I kind of line @. But that's bikeshedding.

Possible ASCII contenders should avoid shell and filename interaction,
which exclude * ? ! & / < > [ ] . - $ and so on: those that seem to
remain are @ , = : % # +. I like "%" because this is about sharing,
although this is not a percentage.

I'd actually like to introduce a new pgbench option that selects a
builtin script by name, so that we can have more than three of them
without running out of option names (or going insane). So suppose we
introduce pgbench -b BUILTIN_NAME, where BUILTIN_NAME is initially one
of these:
classic, classic-simple-update, classic-select-only

Then you can do pgbench -b classic@1 -b classic-select-only@9 or
similar to get 10% write, 90% read.

I like this idea, as -b/-f would be symmetric. Prepending classic to the
names does not look necessary. I would suggest "tpcb-like",
"simple-update" & "select-only", or even maybe any prefix. If the bench
scripts could be read from some pg directory instead of being actually
inlined, even more code could be dropped from pgbench.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jul 23, 2015 at 12:15 PM, Fabien COELHO <coelho@cri.ensmp.fr> wrote:

Yes, I think that's a good idea. I don't know whether : is the right
separator; I kind of line @. But that's bikeshedding.

Possible ASCII contenders should avoid shell and filename interaction, which
exclude * ? ! & / < > [ ] . - $ and so on: those that seem to remain are @ ,
= : % # +. I like "%" because this is about sharing, although this is not a
percentage.

I liked @ because it makes sense to read it as the word "at".

I'd actually like to introduce a new pgbench option that selects a builtin
script by name, so that we can have more than three of them without running
out of option names (or going insane). So suppose we introduce pgbench -b
BUILTIN_NAME, where BUILTIN_NAME is initially one of these:
classic, classic-simple-update, classic-select-only

Then you can do pgbench -b classic@1 -b classic-select-only@9 or
similar to get 10% write, 90% read.

I like this idea, as -b/-f would be symmetric. Prepending classic to the
names does not look necessary. I would suggest "tpcb-like", "simple-update"
& "select-only", or even maybe any prefix. If the bench scripts could be
read from some pg directory instead of being actually inlined, even more
code could be dropped from pgbench.

I think including classic would be a very good idea. We might want to
add a TPC-C like workload in the future, or any number of other
things. Naming things in a good way from the outset can only make
that easier.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I liked @ because it makes sense to read it as the word "at".

Yep, why not.

Prepending classic to the names does not look necessary. I would
suggest "tpcb-like", "simple-update" & "select-only", or even maybe any
prefix. If the bench scripts could be read from some pg directory
instead of being actually inlined, even more code could be dropped from
pgbench.

I think including classic would be a very good idea.

Hmm. This is the command line, you have to type them! With a prefix-based
approach this suggests that the builtin names must start differently so as
to be easily selected.

We might want to add a TPC-C like workload in the future, or any number
of other things. Naming things in a good way from the outset can only
make that easier.

Here is a v4 which:

- removes -w stuff

- enhance -f with @weight

- adds -b/--builtin name@weight, based on prefix

builtin names are: tpcb-like, simple-update & select-only,
which matches their more or less historical names
(although I wasn't sure of "tpcb-sort-of", so I put "tpcb-like")

- removes -B (now can be accessed with -b tpcb-like)

Pgbench builtin scripts are still inlined in the code, not in a separate
directory, which might be an option to simplify the code and allow easy
extensions.

I still think that the "--per-script-stats" option is useless and per
script stats should always be on as soon as several scripts are running.

Even more, I think that stats (maybe no per-command stat though) should
always be collected. The point of pgbench is to collect data, and the
basic end-of-run tps summary is very terse and does not reflect much
of what happened during the run.

Also, maybe per-command detailed stats should use the same common struct
to hold data as all other stats. I did not change it because it is
maintained in a different part of the code.

--
Fabien.

Also, maybe per-command detailed stats should use the same common struct
to hold data as all other stats. I did not change it because it is
maintained in a different part of the code.

I played just once with the --report-latencies option and was astonished
that meta commands showed negative latencies...

This v5 also fixes this bug (on meta commands there is a goto loop in
doCustom, but as now was not reset the stmt_begin ended up being after
now, hence accumulating increasing negative times) and in passing uses the
same stats structure as the rest, which result in removing some more code.
The "report-latencies" option is made to imply per script stats, which
simplifies the final output code, and if you want per-command per-script
stats, probably providing the per-script stats, i.e. the sum of the
commands, make sense.

--
Fabien.