diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml index 9d5eb32..5d38c6f 100644 --- a/doc/src/sgml/ref/pgbench.sgml +++ b/doc/src/sgml/ref/pgbench.sgml @@ -793,9 +793,10 @@ pgbench options dbname - Sets variable varname to an integer value calculated + Sets variable varname to a value calculated from expression. The expression may contain integer constants such as 5432, + double constants such as 3.14159, references to variables :variablename, and expressions composed of unary (-) or binary operators (+, -, *, /, @@ -809,7 +810,7 @@ pgbench options dbname Examples: \set ntellers 10 * :scale -\set aid (1021 * :aid) % (100000 * :scale) + 1 +\set aid (1021 * random(1, 100000 * :scale)) % (100000 * :scale) + 1 @@ -829,66 +830,35 @@ pgbench options dbname - By default, or when uniform is specified, all values in the - range are drawn with equal probability. Specifying gaussian - or exponential options modifies this behavior; each - requires a mandatory parameter which determines the precise shape of the - distribution. - + + + + \setrandom n 1 10 or \setrandom n 1 10 uniform + is equivalent to \set n random(1, 10) and uses a uniform + distribution. + + - - For a Gaussian distribution, the interval is mapped onto a standard - normal distribution (the classical bell-shaped Gaussian curve) truncated - at -parameter on the left and +parameter - on the right. - Values in the middle of the interval are more likely to be drawn. - To be precise, if PHI(x) is the cumulative distribution - function of the standard normal distribution, with mean mu - defined as (max + min) / 2.0, with - - f(x) = PHI(2.0 * parameter * (x - mu) / (max - min + 1)) / - (2.0 * PHI(parameter) - 1.0) - - then value i between min and - max inclusive is drawn with probability: - f(i + 0.5) - f(i - 0.5). - Intuitively, the larger parameter, the more - frequently values close to the middle of the interval are drawn, and the - less frequently values close to the min and - max bounds. About 67% of values are drawn from the - middle 1.0 / parameter, that is a relative - 0.5 / parameter around the mean, and 95% in the middle - 2.0 / parameter, that is a relative - 1.0 / parameter around the mean; for instance, if - parameter is 4.0, 67% of values are drawn from the - middle quarter (1.0 / 4.0) of the interval (i.e. from - 3.0 / 8.0 to 5.0 / 8.0) and 95% from - the middle half (2.0 / 4.0) of the interval (second and - third quartiles). The minimum parameter is 2.0 for - performance of the Box-Muller transform. - + + + \setrandom n 1 10 exponential 3.0 is equivalent to + \set n random_exponential(1, 10, 3.0) and uses an + exponential distribution. + + - - For an exponential distribution, parameter - controls the distribution by truncating a quickly-decreasing - exponential distribution at parameter, and then - projecting onto integers between the bounds. - To be precise, with - -f(x) = exp(-parameter * (x - min) / (max - min + 1)) / (1.0 - exp(-parameter)) - - Then value i between min and - max inclusive is drawn with probability: - f(x) - f(x + 1). - Intuitively, the larger parameter, the more - frequently values close to min are accessed, and the - less frequently values close to max are accessed. - The closer to 0 parameter, the flatter (more uniform) - the access distribution. - A crude approximation of the distribution is that the most frequent 1% - values in the range, close to min, are drawn - parameter% of the time. - parameter value must be strictly positive. + + + \setrandom n 1 10 gaussian 2.0 is equivalent to + \set n random_gaussian(1, 10, 2.0), and uses a gaussian + distribution. + + + + + See the documentation of these functions below for further information + about the precise shape of these distributions, depending on the value + of the parameter. @@ -985,7 +955,7 @@ f(x) = exp(-parameter * (x - min) / (max - min + 1)) / (1.0 - exp(-parameter)) abs(a) same as a - integer value + integer or double absolute value abs(-17) 17 @@ -993,8 +963,22 @@ f(x) = exp(-parameter * (x - min) / (max - min + 1)) / (1.0 - exp(-parameter)) debug(a) same asa print to stderr the given argument - debug(5432) - 5432 + debug(5432.1) + 5432.1 + + + double(i) + double + cast to double + double(5432) + 5432.0 + + + int(x) + integer + cast to int + int(5.4 + 3.8) + 9 max(i [, ... ] ) @@ -1010,22 +994,128 @@ f(x) = exp(-parameter * (x - min) / (max - min + 1)) / (1.0 - exp(-parameter)) min(5, 4, 3, 2) 2 + + pi() + double + value of the PI constant + pi() + 3.14159265358979323846 + + + random(lb, ub) + integer + uniformly-distributed random integer in [lb, ub] + random(1, 10) + an integer between 1 and 10 + + + random_exponential(lb, ub, parameter) + integer + exponentially-distributed random integer in [lb, ub], + see below + random_exponential(1, 10, 3.0) + an integer between 1 and 10 + + + random_gaussian(lb, ub, parameter) + integer + gaussian-distributed random integer in [lb, ub], + see below + random_gaussian(1, 10, 2.5) + an integer between 1 and 10 + + + sqrt(x) + double + square root + sqrt(2.0) + 1.414213562 + + + The random function generates values using a uniform + distribution, that is all the values are drawn within the specified + range with equal probability. The random_exponential and + random_gaussian functions require an additional double + parameter which determines the precise shape of the distribution. + + + + + + For an exponential distribution, parameter + controls the distribution by truncating a quickly-decreasing + exponential distribution at parameter, and then + projecting onto integers between the bounds. + To be precise, with + +f(x) = exp(-parameter * (x - min) / (max - min + 1)) / (1 - exp(-parameter)) + + Then value i between min and + max inclusive is drawn with probability: + f(x) - f(x + 1). + + + + Intuitively, the larger the parameter, the more + frequently values close to min are accessed, and the + less frequently values close to max are accessed. + The closer to 0 parameter is, the flatter (more + uniform) the access distribution. + A crude approximation of the distribution is that the most frequent 1% + values in the range, close to min, are drawn + parameter% of the time. + The parameter value must be strictly positive. + + + + + + For a Gaussian distribution, the interval is mapped onto a standard + normal distribution (the classical bell-shaped Gaussian curve) truncated + at -parameter on the left and +parameter + on the right. + Values in the middle of the interval are more likely to be drawn. + To be precise, if PHI(x) is the cumulative distribution + function of the standard normal distribution, with mean mu + defined as (max + min) / 2.0, with + + f(x) = PHI(2.0 * parameter * (x - mu) / (max - min + 1)) / + (2.0 * PHI(parameter) - 1) + + then value i between min and + max inclusive is drawn with probability: + f(i + 0.5) - f(i - 0.5). + Intuitively, the larger the parameter, the more + frequently values close to the middle of the interval are drawn, and the + less frequently values close to the min and + max bounds. About 67% of values are drawn from the + middle 1.0 / parameter, that is a relative + 0.5 / parameter around the mean, and 95% in the middle + 2.0 / parameter, that is a relative + 1.0 / parameter around the mean; for instance, if + parameter is 4.0, 67% of values are drawn from the + middle quarter (1.0 / 4.0) of the interval (i.e. from + 3.0 / 8.0 to 5.0 / 8.0) and 95% from + the middle half (2.0 / 4.0) of the interval (second and third + quartiles). The minimum parameter is 2.0 for performance + of the Box-Muller transform. + + + + As an example, the full definition of the built-in TPC-B-like transaction is: -\set nbranches :scale -\set ntellers 10 * :scale -\set naccounts 100000 * :scale -\setrandom aid 1 :naccounts -\setrandom bid 1 :nbranches -\setrandom tid 1 :ntellers -\setrandom delta -5000 5000 +\set aid random(1, 100000 * :scale) +\set bid random(1, 1 * :scale) +\set tid random(1, 10 * :scale) +\set delta random(-5000, 5000) BEGIN; UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; SELECT abalance FROM pgbench_accounts WHERE aid = :aid; @@ -1184,6 +1274,8 @@ number of clients: 10 number of threads: 1 number of transactions per client: 1000 number of transactions actually processed: 10000/10000 +latency average: 16.052 ms +latency stddev: 8.204 ms tps = 618.764555 (including connections establishing) tps = 622.977698 (excluding connections establishing) SQL script 1: <builtin: TPC-B (sort of)> @@ -1191,13 +1283,10 @@ SQL script 1: <builtin: TPC-B (sort of)> - latency average = 15.844 ms - latency stddev = 2.715 ms - statement latencies in milliseconds: - 0.004386 \set nbranches 1 * :scale - 0.001343 \set ntellers 10 * :scale - 0.001212 \set naccounts 100000 * :scale - 0.001310 \setrandom aid 1 :naccounts - 0.001073 \setrandom bid 1 :nbranches - 0.001005 \setrandom tid 1 :ntellers - 0.001078 \setrandom delta -5000 5000 + 0.002522 \set aid random(1, 100000 * :scale) + 0.005459 \set bid random(1, 1 * :scale) + 0.002348 \set tid random(1, 10 * :scale) + 0.001078 \set delta random(-5000, 5000) 0.326152 BEGIN; 0.603376 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; 0.454643 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y index 93c6173..a098237 100644 --- a/src/bin/pgbench/exprparse.y +++ b/src/bin/pgbench/exprparse.y @@ -18,6 +18,7 @@ PgBenchExpr *expr_parse_result; static PgBenchExprList *make_elist(PgBenchExpr *exp, PgBenchExprList *list); static PgBenchExpr *make_integer_constant(int64 ival); +static PgBenchExpr *make_double_constant(double dval); static PgBenchExpr *make_variable(char *varname); static PgBenchExpr *make_op(const char *operator, PgBenchExpr *lexpr, PgBenchExpr *rexpr); @@ -32,6 +33,7 @@ static PgBenchExpr *make_func(const int fnumber, PgBenchExprList *args); %union { int64 ival; + double dval; char *str; PgBenchExpr *expr; PgBenchExprList *elist; @@ -40,9 +42,10 @@ static PgBenchExpr *make_func(const int fnumber, PgBenchExprList *args); %type elist %type expr %type INTEGER function +%type DOUBLE %type VARIABLE FUNCTION -%token INTEGER VARIABLE FUNCTION +%token INTEGER DOUBLE VARIABLE FUNCTION %token CHAR_ERROR /* never used, will raise a syntax error */ /* Precedence: lowest to highest */ @@ -68,6 +71,7 @@ expr: '(' expr ')' { $$ = $2; } | expr '/' expr { $$ = make_op("/", $1, $3); } | expr '%' expr { $$ = make_op("%", $1, $3); } | INTEGER { $$ = make_integer_constant($1); } + | DOUBLE { $$ = make_double_constant($1); } | VARIABLE { $$ = make_variable($1); } | function '(' elist ')'{ $$ = make_func($1, $3); } ; @@ -89,6 +93,17 @@ make_integer_constant(int64 ival) } static PgBenchExpr * +make_double_constant(double dval) +{ + PgBenchExpr *expr = pg_malloc(sizeof(PgBenchExpr)); + + expr->etype = ENODE_CONSTANT; + expr->u.constant.type = PGBT_DOUBLE; + expr->u.constant.u.dval = dval; + return expr; +} + +static PgBenchExpr * make_variable(char *varname) { PgBenchExpr *expr = pg_malloc(sizeof(PgBenchExpr)); @@ -129,6 +144,13 @@ static struct { "min", -1, PGBENCH_MIN }, { "max", -1, PGBENCH_MAX }, { "debug", 1, PGBENCH_DEBUG }, + { "pi", 0, PGBENCH_PI }, + { "sqrt", 1, PGBENCH_SQRT }, + { "int", 1, PGBENCH_INT }, + { "double", 1, PGBENCH_DOUBLE }, + { "random", 2, PGBENCH_RANDOM }, + { "random_gaussian", 3, PGBENCH_RANDOM_GAUSSIAN }, + { "random_exponential", 3, PGBENCH_RANDOM_EXPONENTIAL }, /* keep as last array element */ { NULL, 0, 0 } }; diff --git a/src/bin/pgbench/exprscan.l b/src/bin/pgbench/exprscan.l index 7e851f0..b56850d 100644 --- a/src/bin/pgbench/exprscan.l +++ b/src/bin/pgbench/exprscan.l @@ -58,6 +58,11 @@ space [ \t\r\f] yylval.ival = strtoint64(yytext); return INTEGER; } +[0-9]+\.[0-9]+ { + yycol += yyleng; + yylval.dval = atof(yytext); + return DOUBLE; + } [a-zA-Z0-9_]+ { yycol += yyleng; yylval.str = pg_strdup(yytext); diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c index be70804..075ca01 100644 --- a/src/bin/pgbench/pgbench.c +++ b/src/bin/pgbench/pgbench.c @@ -1000,6 +1000,36 @@ coerceToInt(PgBenchValue *pval) { if (pval->type == PGBT_INT) return pval->u.ival; + else if (pval->type == PGBT_DOUBLE) + { + double dval = pval->u.dval; + if (dval < INT64_MIN || INT64_MAX < dval) + { + /* + * Stop on double->int overflow. + * + * Other options would be to abort the current transaction or + * the running client, but this would have more impact on the code + * for little benefit. + */ + fprintf(stderr, "double to int overflow for %f\n", dval); + exit(1); + } + return (int64) dval; + } + /* stop on internal error */ + fprintf(stderr, "unexpected value type %d\n", pval->type); + exit(1); + return 0; +} + +static double +coerceToDouble(PgBenchValue *pval) +{ + if (pval->type == PGBT_DOUBLE) + return pval->u.dval; + else if (pval->type == PGBT_INT) + return (double) pval->u.ival; /* stop on internal error */ fprintf(stderr, "unexpected value type %d\n", pval->type); exit(1); @@ -1013,6 +1043,13 @@ setIntValue(PgBenchValue *pv, int64 ival) pv->u.ival = ival; } +static void +setDoubleValue(PgBenchValue *pv, double dval) +{ + pv->type = PGBT_DOUBLE; + pv->u.dval = dval; +} + static bool evalFunc(TState *thread, CState *st, PgBenchFunction func, PgBenchExprLink *args, PgBenchValue *retval) @@ -1037,7 +1074,47 @@ evalFunc(TState *thread, CState *st, if (!evaluateExpr(thread, st, args->next->expr, &rval)) return false; - if (lval.type == PGBT_INT && rval.type == PGBT_INT) + /* overloaded type management, double if some double */ + if (lval.type == PGBT_DOUBLE || rval.type == PGBT_DOUBLE) + { + switch (func) + { + case PGBENCH_ADD: + setDoubleValue(retval, coerceToDouble(&lval) + coerceToDouble(&rval)); + return true; + + case PGBENCH_SUB: + setDoubleValue(retval, coerceToDouble(&lval) - coerceToDouble(&rval)); + return true; + + case PGBENCH_MUL: + setDoubleValue(retval, coerceToDouble(&lval) * coerceToDouble(&rval)); + return true; + + case PGBENCH_DIV: + setDoubleValue(retval, coerceToDouble(&lval) / coerceToDouble(&rval)); + return true; + + case PGBENCH_MOD: /* no overloading for modulo */ + if (coerceToInt(&rval) == 0) + { + fprintf(stderr, "division by zero\n"); + return false; + } + if (coerceToInt(&rval) == -1) + { + setIntValue(retval, 0); + return true; + } + setIntValue(retval, coerceToInt(&lval) % coerceToInt(&rval)); + return true; + + default: + /* cannot get here */ + Assert(0); + } + } + else /* both operands are integers */ { switch (func) { @@ -1076,13 +1153,12 @@ evalFunc(TState *thread, CState *st, Assert(0); } } - else - { - /* cannot get here */ - Assert(0); - } } + case PGBENCH_PI: + setDoubleValue(retval, M_PI); + return true; + case PGBENCH_ABS: { PgBenchValue arg; @@ -1090,7 +1166,12 @@ evalFunc(TState *thread, CState *st, if (!evaluateExpr(thread, st, args->expr, &arg)) return false; - if (arg.type == PGBT_INT) + if (arg.type == PGBT_DOUBLE) + { + double d = coerceToDouble(&arg); + setDoubleValue(retval, d < 0.0 ? -d: d); + } + else if (arg.type == PGBT_INT) { int64 i = coerceToInt(&arg); setIntValue(retval, i < 0? -i: i); @@ -1099,6 +1180,18 @@ evalFunc(TState *thread, CState *st, return true; } + case PGBENCH_SQRT: + { + PgBenchValue arg; + + if (!evaluateExpr(thread, st, args->expr, &arg)) + return false; + + setDoubleValue(retval, sqrt(coerceToDouble(&arg))); + + return true; + } + case PGBENCH_DEBUG: { if (!evaluateExpr(thread, st, args->expr, retval)) @@ -1109,6 +1202,8 @@ evalFunc(TState *thread, CState *st, if (retval->type == PGBT_INT) fprintf(stderr, "int " INT64_FORMAT "\n", retval->u.ival); + else if (retval->type == PGBT_DOUBLE) + fprintf(stderr, "double %f\n", retval->u.dval); else { /* internal error */ @@ -1119,6 +1214,30 @@ evalFunc(TState *thread, CState *st, return true; } + case PGBENCH_DOUBLE: + { + PgBenchValue arg; + + if (!evaluateExpr(thread, st, args->expr, &arg)) + return false; + + setDoubleValue(retval, coerceToDouble(&arg)); + + return true; + } + + case PGBENCH_INT: + { + PgBenchValue arg; + + if (!evaluateExpr(thread, st, args->expr, &arg)) + return false; + + setIntValue(retval, coerceToInt(&arg)); + + return true; + } + case PGBENCH_MIN: case PGBENCH_MAX: { @@ -1150,6 +1269,78 @@ evalFunc(TState *thread, CState *st, return true; } + case PGBENCH_RANDOM: + case PGBENCH_RANDOM_EXPONENTIAL: + case PGBENCH_RANDOM_GAUSSIAN: + { + PgBenchValue varg1, varg2; + int64 arg1, arg2; + + if (!evaluateExpr(thread, st, args->expr, &varg1)) + return false; + + if (!evaluateExpr(thread, st, args->next->expr, &varg2)) + return false; + + arg1 = coerceToInt(&varg1); + arg2 = coerceToInt(&varg2); + + /* check random range */ + if (arg1 > arg2) + { + fprintf(stderr, "empty range given to random\n"); + st->ecnt++; + return false; + } + else if (arg2 - arg1 < 0 || (arg2 - arg1) + 1 < 0) + { + /* prevent int overflows in random functions */ + fprintf(stderr, "random range is too large\n"); + st->ecnt++; + return false; + } + + if (func == PGBENCH_RANDOM) + setIntValue(retval, getrand(thread, arg1, arg2)); + else /* gaussian & exponential */ + { + PgBenchValue param; + double dparam; + + if (!evaluateExpr(thread, st, args->next->next->expr, ¶m)) + return false; + + dparam = coerceToDouble(¶m); + if (func == PGBENCH_RANDOM_GAUSSIAN) + { + if (dparam < MIN_GAUSSIAN_PARAM) + { + fprintf(stderr, + "gaussian parameter must be at least %f " + "(not %f)\n", MIN_GAUSSIAN_PARAM, dparam); + st->ecnt++; + return false; + } + + setIntValue(retval, getGaussianRand(thread, arg1, arg2, dparam)); + } + else /* exponential */ + { + if (dparam <= 0.0) + { + fprintf(stderr, + "exponential parameter must be greater than zero" + " (got %f)\n", dparam); + st->ecnt++; + return false; + } + + setIntValue(retval, getExponentialRand(thread, arg1, arg2, dparam)); + } + } + + return true; + } default: fprintf(stderr, "unexpected function tag: %d\n", func); exit(1); @@ -1183,7 +1374,15 @@ evaluateExpr(TState *thread, CState *st, PgBenchExpr *expr, PgBenchValue *retval return false; } - setIntValue(retval, strtoint64(var)); + /* guess double type (n for "inf", "-inf" and "nan") */ + if (strchr(var, '.') != NULL || strchr(var, 'n') != NULL) + { + double dv; + sscanf(var, "%lf", &dv); + setDoubleValue(retval, dv); + } + else + setIntValue(retval, strtoint64(var)); return true; } @@ -1814,6 +2013,8 @@ top: if (result.type == PGBT_INT) sprintf(res, INT64_FORMAT, coerceToInt(&result)); + else if (result.type == PGBT_DOUBLE) + sprintf(res, "%.18e", coerceToDouble(&result)); else /* cannot happend if evaluateExpr above returned ok */ Assert(0); diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h index 1f7251a..91337fd 100644 --- a/src/bin/pgbench/pgbench.h +++ b/src/bin/pgbench/pgbench.h @@ -16,7 +16,8 @@ */ typedef enum { - PGBT_INT + PGBT_INT, + PGBT_DOUBLE // add other types here } PgBenchValueType; @@ -26,6 +27,7 @@ typedef struct union { int64 ival; + double dval; // add other types here } u; } PgBenchValue; @@ -50,6 +52,13 @@ typedef enum PgBenchFunction PGBENCH_ABS, PGBENCH_MIN, PGBENCH_MAX, + PGBENCH_PI, + PGBENCH_INT, + PGBENCH_DOUBLE, + PGBENCH_SQRT, + PGBENCH_RANDOM, + PGBENCH_RANDOM_GAUSSIAN, + PGBENCH_RANDOM_EXPONENTIAL } PgBenchFunction; typedef struct PgBenchExpr PgBenchExpr;