Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aRJzL-0000jM-2R for pgsql-hackers@arkaria.postgresql.org; Thu, 04 Feb 2016 13:30:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aRJzK-0003EP-LY for pgsql-hackers@arkaria.postgresql.org; Thu, 04 Feb 2016 13:30:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aRJzJ-0003At-Fe for pgsql-hackers@postgresql.org; Thu, 04 Feb 2016 13:30:21 +0000 Received: from out.smtp-auth.no-ip.com ([8.23.224.60]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aRJzD-00034M-Li for pgsql-hackers@postgresql.org; Thu, 04 Feb 2016 13:30:21 +0000 X-No-IP: alvh.no-ip.org@noip-smtp X-Report-Spam-To: abuse@no-ip.com Received: from alvin.alvh.no-ip.org (unknown [141.134.129.180]) (Authenticated sender: alvh.no-ip.org@noip-smtp) by smtp-auth.no-ip.com (Postfix) with ESMTPA id C9C33402ECF; Thu, 4 Feb 2016 05:30:06 -0800 (PST) Received: by alvin.alvh.no-ip.org (Postfix, from userid 1000) id 1B4AE515; Thu, 4 Feb 2016 10:58:06 +0100 (CET) Date: Thu, 4 Feb 2016 10:58:06 +0100 From: Alvaro Herrera To: Fabien COELHO Cc: Michael Paquier , "pgsql-hackers@postgresql.org" Subject: Re: pgbench stats per script & other stuff Message-ID: <20160204095806.GA220429@alvherre.pgsql> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="FL5UXtIhxfXey3p5" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.23 (2014-03-12) X-Pg-Spam-Score: 0.5 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org --FL5UXtIhxfXey3p5 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit 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: - 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 --FL5UXtIhxfXey3p5 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="pgbench-script-stats-17-d.patch" diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml index ade1b53..1b28d55 100644 --- a/doc/src/sgml/ref/pgbench.sgml +++ b/doc/src/sgml/ref/pgbench.sgml @@ -262,11 +262,13 @@ pgbench options dbname - - + + Add the specified builtin script to the list of executed scripts. + An optional integer weight after @ allows to adjust the + probability of drawing the script. If not specified, it is set to 1. Available builtin scripts are: tpcb-like, simple-update and select-only. With special name list, show the list of builtin scripts @@ -321,12 +323,14 @@ pgbench options dbname - - + + Add a transaction script read from filename to the list of executed scripts. + An optional integer weight after @ allows to adjust the + probability of drawing the test. See below for details. @@ -686,9 +690,13 @@ pgbench options dbname What is the <quote>Transaction</> Actually Performed in <application>pgbench</application>? - Pgbench executes test scripts chosen randomly from a specified list. + pgbench executes test scripts chosen randomly + from a specified list. They include built-in scripts with @@ -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.c index 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 +#include #include #include #include @@ -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; --FL5UXtIhxfXey3p5 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 --FL5UXtIhxfXey3p5--