Date: Mon, 25 Jan 2016 22:22:31 -0300
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Fabien COELHO <coelho@cri.ensmp.fr>
Cc: "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: pgbench stats per script & other stuff
Message-ID: <20160126012231.GA549232@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="BOKacYhQ+x31HxR3"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <alpine.DEB.2.10.1601231501570.21060@sto>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


--BOKacYhQ+x31HxR3
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

Fabien COELHO wrote:

> a) add -b option for cumulating builtins and rework internal script
>    management so that builtin and external scripts are managed the
>    same way.

I tweaked this a bit.  I found a bug in threadRun: it was reading the
commands first, and setting st->use_file later.  This led to the wrong
commands being read.

Some other less interesting changes:

* made chooseScript have the logic to react to single existing script;
no need to inject ternary operators in each caller to check for that
condition.

* Added a debug line every time a script is chosen,
+       if (debug)
+           fprintf(stderr, "client %d executing script \"%s\"\n", st->id,
+                   sql_script[st->use_file].name);
  (I'd have liked to have chooseScript itself do it, but it doesn't
  have the script name handy.  Maybe this indicates that the data
  structures are slightly wrong.)

* Added a separate routine to list available scripts; originally that
was duplicated in "-b list" and when -b got an invalid script name.

* In usage(), I split out the options to select a script instead of
mixing them within "Benchmarking options"; also changed wording of
parenthical comment, no longer carrying the full list of scripts (a
choice which also omitted "-b list" itself):
+          "\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"
+          "  -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"
+          "                           (same as \"-b select-only\")\n"

I couldn't find a better heading to use there, so that'll have to do
unless someone has a better idea.

Some other trivial changes.  Patch attached.  I plan to push this as
soon as I'm able.

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

--BOKacYhQ+x31HxR3
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="pgbench-script-stats-14-a.patch"

diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 541d17b..fdd3331 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -261,6 +261,23 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
     benchmarking arguments:
 
     <variablelist>
+     <varlistentry>
+      <term><option>-b</> <replaceable>scriptname</></term>
+      <term><option>--builtin</> <replaceable>scriptname</></term>
+      <listitem>
+       <para>
+        Add the specified builtin script to the list of executed scripts.
+        Available builtin scripts are: <literal>tpcb-like</>,
+        <literal>simple-update</> and <literal>select-only</>.
+        The provided <replaceable>scriptname</> needs only to be a prefix
+        of the builtin name, hence <literal>simp</> would be enough to select
+        <literal>simple-update</>.
+        With special name <literal>list</>, show the list of builtin scripts
+        and exit immediately.
+       </para>
+      </listitem>
+     </varlistentry>
+
 
      <varlistentry>
       <term><option>-c</option> <replaceable>clients</></term>
@@ -307,14 +324,13 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
      </varlistentry>
 
      <varlistentry>
-      <term><option>-f</option> <replaceable>filename</></term>
-      <term><option>--file=</option><replaceable>filename</></term>
+      <term><option>-f</> <replaceable>filename</></term>
+      <term><option>--file=</><replaceable>filename</></term>
       <listitem>
        <para>
-        Read transaction script from <replaceable>filename</>.
+        Add a transaction script read from <replaceable>filename</> to
+        the list of executed scripts.
         See below for details.
-        <option>-N</option>, <option>-S</option>, and <option>-f</option>
-        are mutually exclusive.
        </para>
       </listitem>
      </varlistentry>
@@ -404,10 +420,8 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
       <term><option>--skip-some-updates</option></term>
       <listitem>
        <para>
-        Do not update <structname>pgbench_tellers</> and
-        <structname>pgbench_branches</>.
-        This will avoid update contention on these tables, but
-        it makes the test case even less like TPC-B.
+        Run builtin simple-update script.
+        Shorthand for <option>-b simple-update</>.
        </para>
       </listitem>
      </varlistentry>
@@ -512,9 +526,9 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
         Report the specified scale factor in <application>pgbench</>'s
         output.  With the built-in tests, this is not necessary; the
         correct scale factor will be detected by counting the number of
-        rows in the <structname>pgbench_branches</> table.  However, when testing
-        custom benchmarks (<option>-f</> option), the scale factor
-        will be reported as 1 unless this option is used.
+        rows in the <structname>pgbench_branches</> table.
+        However, when testing only custom benchmarks (<option>-f</> option),
+        the scale factor will be reported as 1 unless this option is used.
        </para>
       </listitem>
      </varlistentry>
@@ -524,7 +538,8 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
       <term><option>--select-only</option></term>
       <listitem>
        <para>
-        Perform select-only transactions instead of TPC-B-like test.
+        Run built-in select-only script.
+        Shorthand for <option>-b select-only</>.
        </para>
       </listitem>
      </varlistentry>
@@ -674,7 +689,17 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
   <title>What is the <quote>Transaction</> Actually Performed in <application>pgbench</application>?</title>
 
   <para>
-   The default transaction script issues seven commands per transaction:
+   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</>.
+ </para>
+
+  <para>
+   The default builtin transaction script (also invoked with <option>-b tpcb-like</>)
+   issues seven commands per transaction over randomly chosen <literal>aid</>,
+   <literal>tid</>, <literal>bid</> and <literal>balance</>.
+   The scenario is inspired by the TPC-B benchmark, but is not actually TPC-B,
+   hence the name.
   </para>
 
   <orderedlist>
@@ -688,9 +713,15 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
   </orderedlist>
 
   <para>
-   If you specify <option>-N</>, steps 4 and 5 aren't included in the
-   transaction.  If you specify <option>-S</>, only the <command>SELECT</> is
-   issued.
+   If you select the <literal>simple-update</> builtin (also <option>-N</>),
+   steps 4 and 5 aren't included in the transaction.
+   This will avoid update contention on these tables, but
+   it makes the test case even less like TPC-B.
+  </para>
+
+  <para>
+   If you select the <literal>select-only</> builtin (also <option>-S</>),
+   only the <command>SELECT</> is issued.
   </para>
  </refsect2>
 
@@ -702,10 +733,7 @@ pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
    benchmark scenarios by replacing the default transaction script
    (described above) with a transaction script read from a file
    (<option>-f</option> option).  In this case a <quote>transaction</>
-   counts as one execution of a script file.  You can even specify
-   multiple scripts (multiple <option>-f</option> options), in which
-   case a random one of the scripts is chosen each time a client session
-   starts a new transaction.
+   counts as one execution of a script file.
   </para>
 
   <para>
@@ -1112,7 +1140,8 @@ 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)
-statement latencies in milliseconds:
+SQL script 1: &lt;builtin: TPC-B (sort of)&gt;
+ - per command latencies in ms:
         0.004386        \set nbranches 1 * :scale
         0.001343        \set ntellers 10 * :scale
         0.001212        \set naccounts 100000 * :scale
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 9e422c5..c8924d2 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -189,13 +189,12 @@ typedef struct
 	char	   *value;			/* its value */
 } Variable;
 
-#define MAX_FILES		128		/* max number of SQL script files allowed */
+#define MAX_SCRIPTS		128		/* max number of SQL scripts allowed */
 #define SHELL_COMMAND_SIZE	256 /* maximum size allowed for shell command */
 
 /*
- * structures used in custom query mode
+ * Connection state
  */
-
 typedef struct
 {
 	PGconn	   *con;			/* connection handle to DB */
@@ -211,8 +210,8 @@ typedef struct
 	instr_time	txn_begin;		/* used for measuring schedule lag times */
 	instr_time	stmt_begin;		/* used for measuring statement latencies */
 	bool		is_throttled;	/* whether transaction throttling is done */
-	int			use_file;		/* index in sql_files for this client */
-	bool		prepared[MAX_FILES];
+	int			use_file;		/* index in sql_scripts for this client */
+	bool		prepared[MAX_SCRIPTS];	/* whether client prepared the script */
 
 	/* per client collected stats */
 	int			cnt;			/* xacts count */
@@ -296,51 +295,68 @@ typedef struct
 	double		sum2_lag;		/* sum(lag*lag) */
 } AggVals;
 
-static Command **sql_files[MAX_FILES];	/* SQL script files */
-static int	num_files;			/* number of script files */
+static struct
+{
+	const char *name;
+	Command	 **commands;
+} 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	debug = 0;			/* debug flag */
 
-/* default scenario */
-static char *tpc_b = {
-	"\\set nbranches " CppAsString2(nbranches) " * :scale\n"
-	"\\set ntellers " CppAsString2(ntellers) " * :scale\n"
-	"\\set naccounts " CppAsString2(naccounts) " * :scale\n"
-	"\\setrandom aid 1 :naccounts\n"
-	"\\setrandom bid 1 :nbranches\n"
-	"\\setrandom tid 1 :ntellers\n"
-	"\\setrandom delta -5000 5000\n"
-	"BEGIN;\n"
-	"UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
-	"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
-	"UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;\n"
-	"UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;\n"
-	"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
-	"END;\n"
+/* Define builtin test scripts */
+#define N_BUILTIN 3
+static struct
+{
+	char	   *name;			/* very short name for -b ... */
+	char	   *desc;			/* short description */
+	char	   *commands;		/* actual pgbench script */
+}
+builtin_script[] =
+{
+	{
+		"tpcb-like",
+		"<builtin: TPC-B (sort of)>",
+		"\\set nbranches " CppAsString2(nbranches) " * :scale\n"
+		"\\set ntellers " CppAsString2(ntellers) " * :scale\n"
+		"\\set naccounts " CppAsString2(naccounts) " * :scale\n"
+		"\\setrandom aid 1 :naccounts\n"
+		"\\setrandom bid 1 :nbranches\n"
+		"\\setrandom tid 1 :ntellers\n"
+		"\\setrandom delta -5000 5000\n"
+		"BEGIN;\n"
+		"UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
+		"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
+		"UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;\n"
+		"UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;\n"
+		"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
+		"END;\n"
+	},
+	{
+		"simple-update",
+		"<builtin: simple update>",
+		"\\set nbranches " CppAsString2(nbranches) " * :scale\n"
+		"\\set ntellers " CppAsString2(ntellers) " * :scale\n"
+		"\\set naccounts " CppAsString2(naccounts) " * :scale\n"
+		"\\setrandom aid 1 :naccounts\n"
+		"\\setrandom bid 1 :nbranches\n"
+		"\\setrandom tid 1 :ntellers\n"
+		"\\setrandom delta -5000 5000\n"
+		"BEGIN;\n"
+		"UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
+		"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
+		"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
+		"END;\n"
+	},
+	{
+		"select-only",
+		"<builtin: select only>",
+		"\\set naccounts " CppAsString2(naccounts) " * :scale\n"
+		"\\setrandom aid 1 :naccounts\n"
+		"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
+	}
 };
 
-/* -N case */
-static char *simple_update = {
-	"\\set nbranches " CppAsString2(nbranches) " * :scale\n"
-	"\\set ntellers " CppAsString2(ntellers) " * :scale\n"
-	"\\set naccounts " CppAsString2(naccounts) " * :scale\n"
-	"\\setrandom aid 1 :naccounts\n"
-	"\\setrandom bid 1 :nbranches\n"
-	"\\setrandom tid 1 :ntellers\n"
-	"\\setrandom delta -5000 5000\n"
-	"BEGIN;\n"
-	"UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;\n"
-	"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
-	"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
-	"END;\n"
-};
-
-/* -S case */
-static char *select_only = {
-	"\\set naccounts " CppAsString2(naccounts) " * :scale\n"
-	"\\setrandom aid 1 :naccounts\n"
-	"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
-};
 
 /* Function prototypes */
 static void setalarm(int seconds);
@@ -366,24 +382,29 @@ usage(void)
 	"                           create indexes in the specified tablespace\n"
 	 "  --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"
+		   "  -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"
+		   "                           (same as \"-b select-only\")\n"
 		   "\nBenchmarking options:\n"
 		   "  -c, --client=NUM         number of concurrent database clients (default: 1)\n"
 		   "  -C, --connect            establish new connection for each transaction\n"
 		   "  -D, --define=VARNAME=VALUE\n"
 	  "                           define variable for use by custom script\n"
-		 "  -f, --file=FILENAME      read transaction script from FILENAME\n"
 		   "  -j, --jobs=NUM           number of threads (default: 1)\n"
 		   "  -l, --log                write transaction times to log file\n"
 	"  -L, --latency-limit=NUM  count transactions lasting more than NUM ms as late\n"
 		   "  -M, --protocol=simple|extended|prepared\n"
 		   "                           protocol for submitting queries (default: simple)\n"
 		   "  -n, --no-vacuum          do not run VACUUM before tests\n"
-		   "  -N, --skip-some-updates  skip updates of pgbench_tellers and pgbench_branches\n"
 		   "  -P, --progress=NUM       show thread progress report every NUM seconds\n"
 		   "  -r, --report-latencies   report average latency per command\n"
 		"  -R, --rate=NUM           target rate in transactions per second\n"
 		   "  -s, --scale=NUM          report this scale factor in output\n"
-		   "  -S, --select-only        perform SELECT-only transactions\n"
 		   "  -t, --transactions=NUM   number of transactions each client runs (default: 10)\n"
 		 "  -T, --time=NUM           duration of benchmark test in seconds\n"
 		   "  -v, --vacuum-all         vacuum all four standard tables before tests\n"
@@ -1124,6 +1145,15 @@ agg_vals_init(AggVals *aggs, instr_time start)
 	aggs->start_time = INSTR_TIME_GET_DOUBLE(start);
 }
 
+static int
+chooseScript(TState *thread)
+{
+	if (num_scripts == 1)
+		return 0;
+
+	return getrand(thread, 0, num_scripts - 1);
+}
+
 /* return false iff client should be disconnected */
 static bool
 doCustom(TState *thread, CState *st, instr_time *conn_time, FILE *logfile, AggVals *agg)
@@ -1144,7 +1174,7 @@ doCustom(TState *thread, CState *st, instr_time *conn_time, FILE *logfile, AggVa
 top:
 	INSTR_TIME_SET_ZERO(now);
 
-	commands = sql_files[st->use_file];
+	commands = sql_script[st->use_file].commands;
 
 	/*
 	 * Handle throttling once per transaction by sleeping.  It is simpler to
@@ -1328,8 +1358,11 @@ top:
 		if (commands[st->state] == NULL)
 		{
 			st->state = 0;
-			st->use_file = (int) getrand(thread, 0, num_files - 1);
-			commands = sql_files[st->use_file];
+			st->use_file = chooseScript(thread);
+			commands = sql_script[st->use_file].commands;
+			if (debug)
+				fprintf(stderr, "client %d executing script \"%s\"\n", st->id,
+						sql_script[st->use_file].name);
 			st->is_throttled = false;
 
 			/*
@@ -2238,7 +2271,6 @@ static Command *
 process_commands(char *buf, const char *source, const int lineno)
 {
 	const char	delim[] = " \f\n\r\t\v";
-
 	Command    *my_commands;
 	int			j;
 	char	   *p,
@@ -2289,7 +2321,7 @@ process_commands(char *buf, const char *source, const int lineno)
 
 		if (pg_strcasecmp(my_commands->argv[0], "setrandom") == 0)
 		{
-			/*
+			/*--------
 			 * parsing:
 			 *   \setrandom variable min max [uniform]
 			 *   \setrandom variable min max (gaussian|exponential) parameter
@@ -2488,7 +2520,11 @@ read_line_from_file(FILE *fd)
 	return NULL;
 }
 
-static int
+/*
+ * Given a file name, read it and return the array of Commands contained
+ * therein.  "-" means to read stdin.
+ */
+static Command **
 process_file(char *filename)
 {
 #define COMMANDS_ALLOC_NUM 128
@@ -2500,12 +2536,6 @@ process_file(char *filename)
 	char	   *buf;
 	int			alloc_num;
 
-	if (num_files >= MAX_FILES)
-	{
-		fprintf(stderr, "at most %d SQL files are allowed\n", MAX_FILES);
-		exit(1);
-	}
-
 	alloc_num = COMMANDS_ALLOC_NUM;
 	my_commands = (Command **) pg_malloc(sizeof(Command *) * alloc_num);
 
@@ -2516,7 +2546,7 @@ process_file(char *filename)
 		fprintf(stderr, "could not open file \"%s\": %s\n",
 				filename, strerror(errno));
 		pg_free(my_commands);
-		return false;
+		return NULL;
 	}
 
 	lineno = 0;
@@ -2548,13 +2578,11 @@ process_file(char *filename)
 
 	my_commands[index] = NULL;
 
-	sql_files[num_files++] = my_commands;
-
-	return true;
+	return my_commands;
 }
 
 static Command **
-process_builtin(char *tb, const char *source)
+process_builtin(const char *tb, const char *source)
 {
 #define COMMANDS_ALLOC_NUM 128
 
@@ -2608,9 +2636,60 @@ process_builtin(char *tb, const char *source)
 	return my_commands;
 }
 
+static void
+listAvailableScripts(void)
+{
+	int		i;
+
+	fprintf(stderr, "Available builtin scripts:\n");
+	for (i = 0; i < N_BUILTIN; i++)
+		fprintf(stderr, "\t%s\n", builtin_script[i].name);
+	fprintf(stderr, "\n");
+}
+
+static char *
+findBuiltin(const char *name, char **desc)
+{
+	int			len = strlen(name);
+	int			i;
+
+	for (i = 0; i < N_BUILTIN; i++)
+	{
+		if (strncmp(builtin_script[i].name, name, len) == 0)
+		{
+			*desc = builtin_script[i].desc;
+			return builtin_script[i].commands;
+		}
+	}
+
+	fprintf(stderr, "no builtin script found for name \"%s\"\n", name);
+	listAvailableScripts();
+	exit(1);
+}
+
+static void
+addScript(const char *name, Command **commands)
+{
+	if (commands == NULL)
+	{
+		fprintf(stderr, "empty command list for script \"%s\"\n", name);
+		exit(1);
+	}
+
+	if (num_scripts >= MAX_SCRIPTS)
+	{
+		fprintf(stderr, "at most %d SQL scripts are allowed\n", MAX_SCRIPTS);
+		exit(1);
+	}
+
+	sql_script[num_scripts].name = name;
+	sql_script[num_scripts].commands = commands;
+	num_scripts++;
+}
+
 /* print out results */
 static void
-printResults(int ttype, int64 normal_xacts, int nclients,
+printResults(int64 normal_xacts, int nclients,
 			 TState *threads, int nthreads,
 			 instr_time total_time, instr_time conn_total_time,
 			 int64 total_latencies, int64 total_sqlats,
@@ -2620,23 +2699,14 @@ printResults(int ttype, int64 normal_xacts, int nclients,
 	double		time_include,
 				tps_include,
 				tps_exclude;
-	char	   *s;
 
 	time_include = INSTR_TIME_GET_DOUBLE(total_time);
 	tps_include = normal_xacts / time_include;
 	tps_exclude = normal_xacts / (time_include -
 						(INSTR_TIME_GET_DOUBLE(conn_total_time) / nclients));
 
-	if (ttype == 0)
-		s = "TPC-B (sort of)";
-	else if (ttype == 2)
-		s = "Update only pgbench_accounts";
-	else if (ttype == 1)
-		s = "SELECT only";
-	else
-		s = "Custom query";
-
-	printf("transaction type: %s\n", s);
+	printf("transaction type: %s\n",
+		   num_scripts == 1 ? sql_script[0].name : "multiple scripts");
 	printf("scaling factor: %d\n", scale);
 	printf("query mode: %s\n", QUERYMODE[querymode]);
 	printf("number of clients: %d\n", nclients);
@@ -2705,16 +2775,14 @@ printResults(int ttype, int64 normal_xacts, int nclients,
 	{
 		int			i;
 
-		for (i = 0; i < num_files; i++)
+		for (i = 0; i < num_scripts; i++)
 		{
 			Command   **commands;
 
-			if (num_files > 1)
-				printf("statement latencies in milliseconds, file %d:\n", i + 1);
-			else
-				printf("statement latencies in milliseconds:\n");
+			printf("SQL script %d: %s\n", i + 1, sql_script[i].name);
+			printf(" - per command latencies in ms:\n");
 
-			for (commands = sql_files[i]; *commands != NULL; commands++)
+			for (commands = sql_script[i].commands; *commands != NULL; commands++)
 			{
 				Command    *command = *commands;
 				int			cnum = command->command_num;
@@ -2752,6 +2820,7 @@ main(int argc, char **argv)
 {
 	static struct option long_options[] = {
 		/* systematic long/short named options */
+		{"tpc-b", no_argument, NULL, 'b'},
 		{"client", required_argument, NULL, 'c'},
 		{"connect", no_argument, NULL, 'C'},
 		{"debug", no_argument, NULL, 'd'},
@@ -2794,14 +2863,12 @@ main(int argc, char **argv)
 	int			is_init_mode = 0;		/* initialize mode? */
 	int			is_no_vacuum = 0;		/* no vacuum at all before testing? */
 	int			do_vacuum_accounts = 0; /* do vacuum accounts before testing? */
-	int			ttype = 0;		/* transaction type. 0: TPC-B, 1: SELECT only,
-								 * 2: skip update of branches and tellers */
 	int			optindex;
-	char	   *filename = NULL;
 	bool		scale_given = false;
 
 	bool		benchmarking_option_set = false;
 	bool		initialization_option_set = false;
+	bool		internal_script_used = false;
 
 	CState	   *state;			/* status of clients */
 	TState	   *threads;		/* array of thread */
@@ -2816,6 +2883,7 @@ main(int argc, char **argv)
 	int64		throttle_lag_max = 0;
 	int64		throttle_latency_skipped = 0;
 	int64		latency_late = 0;
+	char	   *desc;
 
 	int			i;
 	int			nclients_dealt;
@@ -2861,7 +2929,7 @@ main(int argc, char **argv)
 	state = (CState *) pg_malloc(sizeof(CState));
 	memset(state, 0, sizeof(CState));
 
-	while ((c = getopt_long(argc, argv, "ih:nvp:dqSNc:j:Crs:t:T:U:lf:D:F:M:P:R:L:", long_options, &optindex)) != -1)
+	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)
 	{
 		switch (c)
 		{
@@ -2883,14 +2951,6 @@ main(int argc, char **argv)
 			case 'd':
 				debug++;
 				break;
-			case 'S':
-				ttype = 1;
-				benchmarking_option_set = true;
-				break;
-			case 'N':
-				ttype = 2;
-				benchmarking_option_set = true;
-				break;
 			case 'c':
 				benchmarking_option_set = true;
 				nclients = atoi(optarg);
@@ -2993,12 +3053,36 @@ main(int argc, char **argv)
 				initialization_option_set = true;
 				use_quiet = true;
 				break;
-			case 'f':
+
+			case 'b':
+				if (strcmp(optarg, "list") == 0)
+				{
+					listAvailableScripts();
+					exit(0);
+				}
+
+				addScript(desc,
+						  process_builtin(findBuiltin(optarg, &desc), desc));
+				benchmarking_option_set = true;
+				internal_script_used = true;
+				break;
+			case 'S':
+				addScript(desc,
+						  process_builtin(findBuiltin("select-only", &desc),
+										 desc));
+				benchmarking_option_set = true;
+				internal_script_used = true;
+				break;
+			case 'N':
+				addScript(desc,
+						  process_builtin(findBuiltin("simple-update", &desc),
+										 desc));
+				benchmarking_option_set = true;
+				internal_script_used = true;
+				break;
+			case 'f':
+				addScript(optarg, process_file(optarg));
 				benchmarking_option_set = true;
-				ttype = 3;
-				filename = pg_strdup(optarg);
-				if (process_file(filename) == false || *sql_files[num_files - 1] == NULL)
-					exit(1);
 				break;
 			case 'D':
 				{
@@ -3029,9 +3113,9 @@ main(int argc, char **argv)
 				break;
 			case 'M':
 				benchmarking_option_set = true;
-				if (num_files > 0)
+				if (num_scripts > 0)
 				{
-					fprintf(stderr, "query mode (-M) should be specified before any transaction scripts (-f)\n");
+					fprintf(stderr, "query mode (-M) should be specified before any transaction scripts (-f or -b)\n");
 					exit(1);
 				}
 				for (querymode = 0; querymode < NUM_QUERYMODE; querymode++)
@@ -3132,6 +3216,15 @@ main(int argc, char **argv)
 		}
 	}
 
+	/* set default script if none */
+	if (num_scripts == 0 && !is_init_mode)
+	{
+		addScript(desc,
+				  process_builtin(findBuiltin("tpcb-like", &desc), desc));
+		benchmarking_option_set = true;
+		internal_script_used = true;
+	}
+
 	/*
 	 * Don't need more threads than there are clients.  (This is not merely an
 	 * optimization; throttle_delay is calculated incorrectly below if some
@@ -3260,7 +3353,7 @@ main(int argc, char **argv)
 		exit(1);
 	}
 
-	if (ttype != 3)
+	if (internal_script_used)
 	{
 		/*
 		 * get the scaling factor that should be same as count(*) from
@@ -3344,31 +3437,6 @@ main(int argc, char **argv)
 	INSTR_TIME_SET_CURRENT(start_time);
 	srandom((unsigned int) INSTR_TIME_GET_MICROSEC(start_time));
 
-	/* process builtin SQL scripts */
-	switch (ttype)
-	{
-		case 0:
-			sql_files[0] = process_builtin(tpc_b,
-										   "<builtin: TPC-B (sort of)>");
-			num_files = 1;
-			break;
-
-		case 1:
-			sql_files[0] = process_builtin(select_only,
-										   "<builtin: select only>");
-			num_files = 1;
-			break;
-
-		case 2:
-			sql_files[0] = process_builtin(simple_update,
-										   "<builtin: simple update>");
-			num_files = 1;
-			break;
-
-		default:
-			break;
-	}
-
 	/* set up thread data structures */
 	threads = (TState *) pg_malloc(sizeof(TState) * nthreads);
 	nclients_dealt = 0;
@@ -3499,7 +3567,7 @@ main(int argc, char **argv)
 	 */
 	INSTR_TIME_SET_CURRENT(total_time);
 	INSTR_TIME_SUBTRACT(total_time, start_time);
-	printResults(ttype, total_xacts, nclients, threads, nthreads,
+	printResults(total_xacts, nclients, threads, nthreads,
 				 total_time, conn_total_time, total_latencies, total_sqlats,
 				 throttle_lag, throttle_lag_max, throttle_latency_skipped,
 				 latency_late);
@@ -3583,10 +3651,14 @@ threadRun(void *arg)
 	for (i = 0; i < nstate; i++)
 	{
 		CState	   *st = &state[i];
-		Command   **commands = sql_files[st->use_file];
 		int			prev_ecnt = st->ecnt;
+		Command   **commands;
 
-		st->use_file = getrand(thread, 0, num_files - 1);
+		st->use_file = chooseScript(thread);
+		commands = sql_script[st->use_file].commands;
+		if (debug)
+			fprintf(stderr, "client %d executing script \"%s\"\n", st->id,
+					sql_script[st->use_file].name);
 		if (!doCustom(thread, st, &thread->conn_time, logfile, &aggs))
 			remains--;			/* I've aborted */
 
@@ -3614,7 +3686,7 @@ threadRun(void *arg)
 		for (i = 0; i < nstate; i++)
 		{
 			CState	   *st = &state[i];
-			Command   **commands = sql_files[st->use_file];
+			Command   **commands = sql_script[st->use_file].commands;
 			int			sock;
 
 			if (st->con == NULL)
@@ -3720,7 +3792,7 @@ threadRun(void *arg)
 		for (i = 0; i < nstate; i++)
 		{
 			CState	   *st = &state[i];
-			Command   **commands = sql_files[st->use_file];
+			Command   **commands = sql_script[st->use_file].commands;
 			int			prev_ecnt = st->ecnt;
 
 			if (st->con && (FD_ISSET(PQsocket(st->con), &input_mask)

--BOKacYhQ+x31HxR3
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


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

--BOKacYhQ+x31HxR3--