From 86c43f4e22c0771fd0cc6bce2799802c894ee2ec Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Mon, 28 Mar 2016 20:45:57 -0400 Subject: pgbench: Support double constants and functions. The new functions are pi(), random(), random_exponential(), random_gaussian(), and sqrt(). I was worried that this would be slower than before, but, if anything, it actually turns out to be slightly faster, because we now express the built-in pgbench scripts using fewer lines; each \setrandom can be merged into a subsequent \set. Fabien Coelho --- doc/src/sgml/ref/pgbench.sgml | 283 +++++++++++++++++++++++++++--------------- 1 file changed, 185 insertions(+), 98 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml index c6d1454b1e9..4ceddae681b 100644 --- a/doc/src/sgml/ref/pgbench.sgml +++ b/doc/src/sgml/ref/pgbench.sgml @@ -815,9 +815,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, unary operators (+, -) and binary operators (+, -, *, /, @@ -830,7 +831,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 @@ -850,66 +851,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. @@ -990,34 +960,6 @@ f(x) = exp(-parameter * (x - min) / (max - min + 1)) / (1.0 - exp(-parameter)) - - - 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 -BEGIN; -UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; -SELECT abalance FROM pgbench_accounts WHERE aid = :aid; -UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; -UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; -INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); -END; - - - This script allows each iteration of the transaction to reference - different, randomly-chosen rows. (This example also shows why it's - important for each client session to have its own variables — - otherwise they'd not be independently touching different rows.) - - @@ -1046,7 +988,7 @@ END; abs(a) same as a - integer value + integer or double absolute value abs(-17) 17 @@ -1054,8 +996,22 @@ END; debug(a) same as a 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 [, ... ] ) @@ -1071,9 +1027,143 @@ END; 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 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; +UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; +UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; +INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); +END; + + + This script allows each iteration of the transaction to reference + different, randomly-chosen rows. (This example also shows why it's + important for each client session to have its own variables — + otherwise they'd not be independently touching different rows.) + + @@ -1223,13 +1313,10 @@ tps = 618.764555 (including connections establishing) tps = 622.977698 (excluding connections establishing) script statistics: - 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; -- cgit v1.2.3