From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Fabien COELHO <coelho@cri.ensmp.fr>
Cc: Michael Paquier <michael.paquier@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: pgbench stats per script & other stuff
Date: Thu, 4 Feb 2016 10:58:06 +0100
Message-ID: <20160204095806.GA220429@alvherre.pgsql> (raw)
In-Reply-To: <alpine.DEB.2.10.1602011704430.13459@sto>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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@10 -f uno.sql@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 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-hackersAttachments:
[text/x-diff] pgbench-script-stats-17-d.patch (9.2K, ../20160204095806.GA220429@alvherre.pgsql/2-pgbench-script-stats-17-d.patch)
download | inline diff:diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgmlindex ade1b53..1b28d55 100644--- a/doc/src/sgml/ref/pgbench.sgml+++ b/doc/src/sgml/ref/pgbench.sgml@@ -262,11 +262,13 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
<variablelist>
<varlistentry>
- <term><option>-b</> <replaceable>scriptname</></term>- <term><option>--builtin</> <replaceable>scriptname</></term>+ <term><option>-b</> <replaceable>scriptname[@weight]</></term>+ <term><option>--builtin</>=<replaceable>scriptname[@weight]</></term>
<listitem>
<para>
Add the specified builtin script to the list of executed scripts.
+ An optional integer weight after <literal>@</> allows to adjust the+ probability of drawing the script. If not specified, it is set to 1.
Available builtin scripts are: <literal>tpcb-like</>,
<literal>simple-update</> and <literal>select-only</>.
With special name <literal>list</>, show the list of builtin scripts
@@ -321,12 +323,14 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
</varlistentry>
<varlistentry>
- <term><option>-f</> <replaceable>filename</></term>- <term><option>--file=</><replaceable>filename</></term>+ <term><option>-f</> <replaceable>filename[@weight]</></term>+ <term><option>--file=</><replaceable>filename[@weight]</></term>
<listitem>
<para>
Add a transaction script read from <replaceable>filename</> to
the list of executed scripts.
+ An optional integer weight after <literal>@</> allows to adjust the+ probability of drawing the test.
See below for details.
</para>
</listitem>
@@ -686,9 +690,13 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
<title>What is the <quote>Transaction</> Actually Performed in <application>pgbench</application>?</title>
<para>
- Pgbench executes test scripts chosen randomly from a specified list.+ <application>pgbench</> executes test scripts chosen randomly+ from a specified list.
They include built-in scripts with <option>-b</> and
user-provided custom scripts with <option>-f</>.
+ Each script may be given a relative weight specified after a+ <literal>@</> so as to change its drawing probability.+ The default weight is <literal>1</>.
</para>
<para>
@@ -1137,7 +1145,7 @@ number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 618.764555 (including connections establishing)
tps = 622.977698 (excluding connections establishing)
-SQL script 1: <builtin: TPC-B (sort of)>+SQL script 1, weight 1: <builtin: TPC-B (sort of)>
- 10000 transactions (100.0% of total, tps = 618.764555)
- latency average = 15.844 ms
- latency stddev = 2.715 ms
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.cindex 7eb6a2d..418f441 100644--- a/src/bin/pgbench/pgbench.c+++ b/src/bin/pgbench/pgbench.c@@ -38,6 +38,7 @@
#include "portability/instr_time.h"
#include <ctype.h>
+#include <limits.h>
#include <math.h>
#include <signal.h>
#include <sys/time.h>
@@ -179,6 +180,8 @@ char *login = NULL;
char *dbName;
const char *progname;
+#define WSEP '@' /* weight separator */+
volatile bool timer_exceeded = false; /* flag from signal handler */
/* variable definitions */
@@ -299,11 +302,14 @@ typedef struct
static struct
{
const char *name;
+ int weight;
Command **commands;
- StatsData stats;+ StatsData stats;
} sql_script[MAX_SCRIPTS]; /* SQL script files */
static int num_scripts; /* number of scripts in sql_script[] */
static int num_commands = 0; /* total number of Command structs */
+static int total_weight = 0;+
static int debug = 0; /* debug flag */
/* Define builtin test scripts */
@@ -389,9 +395,9 @@ usage(void)
" --tablespace=TABLESPACE create tables in the specified tablespace\n"
" --unlogged-tables create tables as unlogged tables\n"
"\nOptions to select what to run:\n"
- " -b, --builtin=NAME add buitin script (use \"-b list\" to display\n"- " available scripts)\n"- " -f, --file=FILENAME add transaction script from FILENAME\n"+ " -b, --builtin=NAME[@W] add builtin script NAME weighted at W (default: 1)\n"+ " (use \"-b list\" to list available scripts)\n"+ " -f, --file=FILENAME[@W] add script FILENAME weighted at W (default: 1)\n"
" -N, --skip-some-updates skip updates of pgbench_tellers and pgbench_branches\n"
" (same as \"-b simple-update\")\n"
" -S, --select-only perform SELECT-only transactions\n"
@@ -1238,10 +1244,20 @@ clientDone(CState *st, bool ok)
static int
chooseScript(TState *thread)
{
+ int i = 0,+ w = 0,+ wc;+
if (num_scripts == 1)
return 0;
- return getrand(thread, 0, num_scripts - 1);+ wc = (int) getrand(thread, 0, total_weight - 1);+ do+ {+ w += sql_script[i++].weight;+ } while (w <= wc);++ return i - 1;
}
/* return false iff client should be disconnected */
@@ -2683,8 +2699,56 @@ findBuiltin(const char *name, char **desc)
exit(1);
}
+/*+ * Determine the weight specification from a script option (-b, -f), if any,+ * and return it as an integer (1 is returned if there's no weight). The+ * script name is returned in *script as a malloc'd string.+ */+static int+parseScriptWeight(const char *option, char **script)+{+ char *sep;+ int weight;++ if ((sep = strrchr(option, WSEP)))+ {+ int namelen = sep - option;+ long wtmp;+ char *badp;++ /* generate the script name */+ *script = pg_malloc(namelen + 1);+ strncpy(*script, option, namelen);+ (*script)[namelen] = '\0';++ /* process digits of the weight spec */+ errno = 0;+ wtmp = strtol(sep + 1, &badp, 10);+ if (errno != 0 || badp == sep + 1 || *badp != '\0')+ {+ fprintf(stderr, "invalid weight specification: %s\n", sep);+ exit(1);+ }+ if (wtmp > INT_MAX || wtmp <= 0)+ {+ fprintf(stderr,+ "weight specification out of range (1 .. %u): " INT64_FORMAT "\n",+ INT_MAX, (int64) wtmp);+ exit(1);+ }+ weight = wtmp;+ }+ else+ {+ *script = pg_strdup(option);+ weight = 1;+ }++ return weight;+}+
static void
-addScript(const char *name, Command **commands)+addScript(const char *name, Command **commands, int weight)
{
if (commands == NULL)
{
@@ -2699,6 +2763,7 @@ addScript(const char *name, Command **commands)
}
sql_script[num_scripts].name = name;
+ sql_script[num_scripts].weight = weight;
sql_script[num_scripts].commands = commands;
initStats(&sql_script[num_scripts].stats, 0.0);
num_scripts++;
@@ -2791,9 +2856,9 @@ printResults(TState *threads, StatsData *total, instr_time total_time,
for (i = 0; i < num_scripts; i++)
{
- printf("SQL script %d: %s\n"+ printf("SQL script %d, weight %d: %s\n"
" - " INT64_FORMAT " transactions (%.1f%% of total, tps = %f)\n",
- i + 1, sql_script[i].name,+ i + 1, sql_script[i].weight, sql_script[i].name,
sql_script[i].stats.cnt,
100.0 * sql_script[i].stats.cnt / total->cnt,
sql_script[i].stats.cnt / time_include);
@@ -2887,6 +2952,7 @@ main(int argc, char **argv)
instr_time conn_total_time;
int64 latency_late = 0;
StatsData stats;
+ int weight;
char *desc;
int i;
@@ -2935,6 +3001,8 @@ main(int argc, char **argv)
while ((c = getopt_long(argc, argv, "ih:nvp:dqb:SNc:j:Crs:t:T:U:lf:D:F:M:P:R:L:", long_options, &optindex)) != -1)
{
+ char *script;+
switch (c)
{
case 'i':
@@ -3066,27 +3134,29 @@ main(int argc, char **argv)
exit(0);
}
+ weight = parseScriptWeight(optarg, &script);
addScript(desc,
- process_builtin(findBuiltin(optarg, &desc), desc));+ process_builtin(findBuiltin(script, &desc), desc), weight);
benchmarking_option_set = true;
internal_script_used = true;
break;
case 'S':
addScript(desc,
process_builtin(findBuiltin("select-only", &desc),
- desc));+ desc), 1);
benchmarking_option_set = true;
internal_script_used = true;
break;
case 'N':
addScript(desc,
process_builtin(findBuiltin("simple-update", &desc),
- desc));+ desc), 1);
benchmarking_option_set = true;
internal_script_used = true;
break;
case 'f':
- addScript(optarg, process_file(optarg));+ weight = parseScriptWeight(optarg, &script);+ addScript(script, process_file(script), weight);
benchmarking_option_set = true;
break;
case 'D':
@@ -3225,11 +3295,18 @@ main(int argc, char **argv)
if (num_scripts == 0 && !is_init_mode)
{
addScript(desc,
- process_builtin(findBuiltin("tpcb-like", &desc), desc));+ process_builtin(findBuiltin("tpcb-like", &desc), desc), 1);
benchmarking_option_set = true;
internal_script_used = true;
}
+ /* compute total_weight */+ for (i = 0; i < num_scripts; i++)+ {+ /* FIXME this can overflow */+ total_weight += sql_script[i].weight;+ }+
/* show per script stats if several scripts are used */
if (num_scripts > 1)
per_script_stats = true;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, coelho@cri.ensmp.fr, michael.paquier@gmail.com
Subject: Re: pgbench stats per script & other stuff
In-Reply-To: <20160204095806.GA220429@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox