Re: pgbench stats per script & other stuff

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
Cc: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pgbench stats per script & other stuff
Date: 2016-02-04 09:58:06
Message-ID: 20160204095806.GA220429@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Something is wrong with patch d. I noticed two things,

1. the total_weight stuff can overflow,
2. the chooseScript stuff is broken, or something.

See the output below and notice how the percentages don't add up to 100%
(this exact case is an absurd one, of course, but I noticed totals of
99.5% and others while playing with reasonable numbers, so this is
something that really needs to be fixed.)

Another thing is that the "transaction type" output really deserves some
more work. I think "multiple scripts" really doesn't cut it; we should
have some YAML-like as in the latency reports, which lists all scripts
in use and their weights.

Also, while I have your attention regarding accumulated "technical
debt", please have a look at the "desc" argument used in addScript etc.
It's pretty ridiculous currently. Maybe findBuiltin / process_builtin /
process_file should return a struct containing Command ** and the
"desc" string, rather than passing desc as a separate argument.

I changed the getWeight stuff completely (and renamed it, and added
comments); I wasn't comfortable with the idea of messing with the
optarg, and neither with the idea of continuing to use it without
copying after processing the arguments. I think some platforms don't
like any of those things.

Attached is my version of the patch. While you're messing with it, it'd
be nice if you added comments on top of your recently added functions
such as findBuiltin, process_builtin, chooseScript.

$ ./pgbench -r -j4 -c4 -t1000 -b tpcb-like(at)10 -f uno(dot)sql(at)214748364
starting vacuum...end.
transaction type: multiple scripts
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
number of transactions per client: 1000
number of transactions actually processed: 4000/4000
latency average: 0.000 ms
tps = 23422.357812 (including connections establishing)
tps = 24172.981858 (excluding connections establishing)
SQL script 1, weight 10: <builtin: TPC-B (sort of)>
- 0 transactions (0.0% of total, tps = 0.000000)
- latency average = -nan ms
- latency stddev = -nan ms
- statement latencies in milliseconds:
-nan \set nbranches 1 * :scale
-nan \set ntellers 10 * :scale
-nan \set naccounts 100000 * :scale
-nan \setrandom aid 1 :naccounts
-nan \setrandom bid 1 :nbranches
-nan \setrandom tid 1 :ntellers
-nan \setrandom delta -5000 5000
-nan BEGIN;
-nan UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
-nan SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
-nan UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
-nan UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
-nan INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
-nan END;
SQL script 2, weight 214748364: uno.sql
- 2649 transactions (66.2% of total, tps = 15511.456461)
- latency average = 0.163 ms
- latency stddev = 0.337 ms
- statement latencies in milliseconds:
0.158 select 1;

Álvaro Herrera
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
pgbench-script-stats-17-d.patch text/x-diff 9.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Etsuro Fujita 2016-02-04 09:58:50 Re: postgres_fdw join pushdown (was Re: Custom/Foreign-Join-APIs)
Previous Message Peter Geoghegan 2016-02-04 09:46:15 Re: Using quicksort for every external sort run