Hello Thomas,
Thanks for the feedback.
+ the account branch has a 15% probability to be in the same branch
as the teller (unless
I would say "... has a 15% probability of being in the same ...". The
same wording appears further down in the comment.
Fixed.
I see that the parameters you propose match the TPCB 2.0 description[1],
[...]
Nearly:-(
While re-re-re-re-reading the spec, it was 85%, i.e. people mostly go to
their local teller, I managed to get it wrong. Sigh. Fixed. Hopefully.
I've updated the script a little so that it is closer to spec. I've also
changed the script definition so that it still works as expected if
someone changes "nbranches" definition for some reason, even if this
is explicitely discourage by comments.
I wonder if "strict" is the right name here though. "tpcb-like-2" at
least leaves room for someone to propose yet another variant, and still
includes the "-like" disclaimer, which I interpret as a way of making it
clear that this benchmark and results produced by it have no official
TPC audited status.
Hmmm.
The -like suffix is really about the conformance of the script, not the
rest, but that should indeed be clearer. I've expanded the comment and doc
about this with a disclaimers, so that there is no ambiguity about what is
expected to conform, which is only the transaction script.
I have added a comment about the non conformance of the "int" type use for
balances in the initialization phase.
Also, on second thought, I've changed the name to "standard-tpcb", but I'm
unsure whether it is better than "script-tpcb". There is an insentive to
have a different prefix so that "-b t" would not complain of ambiguity
between "tpcb-like*", which would be a regression. This is why I did not
choose the simple "tcp". There may be a "standard-tpcb-2" anyway.
I have added a small test run in the TAP test.
On my TODO list is adding an initialization option to change the balance
type for conformance, by using NUMERIC or integer8.
While thinking about that, an unrelated thought occured to me: adding a
partitioned initialization variant would be nice to test the performance
impact of partitioning easily. I should have thought of that as soon as
partitioning was added. Added to my TODO list as well.
--
Fabien.
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 09af9f1a7d..6ff557b2b3 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -347,7 +347,8 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
An optional integer weight after <literal>@</literal> allows to adjust the
probability of drawing the script. If not specified, it is set to 1.
Available built-in scripts are: <literal>tpcb-like</literal>,
- <literal>simple-update</literal> and <literal>select-only</literal>.
+ <literal>simple-update</literal>, <literal>select-only</literal> and
+ <literal>standard-tpcb</literal>.
Unambiguous prefixes of built-in names are accepted.
With special name <literal>list</literal>, show the list of built-in scripts
and exit immediately.
@@ -869,6 +870,25 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
If you select the <literal>select-only</literal> built-in (also <option>-S</option>),
only the <command>SELECT</command> is issued.
</para>
+
+ <para>
+ If you select the <literal>standard-tpcb</literal> built-in, the SQL commands are similar
+ to <literal>tpcb-like</literal>, but the <literal>delta</literal> amount is 6 digits,
+ the account branch has a 85% probability of being in the same branch as the teller (unless
+ there is only one branch in which case it is always the same), and the final account
+ balance is actually extracted by the script.
+ </para>
+
+ <note>
+ <para>
+ Disclaimer: with <literal>standard-tpcb</literal>, only the transaction script
+ is expected to conform to the "TPC Benchmark(tm) B revision 2.0" specification.
+ Other parts of the benchmark, such as type capabilities, initialization,
+ performance data collection, checks on final values, database configuration,
+ test duration... may or may not conform to the standard depending on the actual
+ run.
+ </para>
+ </note>
</refsect2>
<refsect2>
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 8b84658ccd..4888beb129 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -193,8 +193,12 @@ int64 random_seed = -1;
* end of configurable parameters
*********************************************************************/
-#define nbranches 1 /* Makes little sense to change this. Change
- * -s instead */
+/*
+ * It makes little sense to change "nbranches", use -s instead.
+ *
+ * Nevertheless, "ntellers" and "naccounts" must be divisible by "nbranches".
+ */
+#define nbranches 1
#define ntellers 10
#define naccounts 100000
@@ -566,6 +570,61 @@ static const BuiltinScript builtin_script[] =
"<builtin: select only>",
"\\set aid random(1, " CppAsString2(naccounts) " * :scale)\n"
"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
+ },
+ {
+ /*---
+ * Standard conforming TPC-B benchmark script as defined in
+ * "TPC BENCHMARK (tm) B Standard Specification Revision 2.0"
+ * from 7 June 1994, see http://www.tpc.org/tpcb/spec/tpcb_current.pdf
+ *
+ * The transaction profile is defined Section 1.2.
+ *
+ * Transaction inputs are defined in Section 5.3:
+ *
+ * - The account branch has a 85% probability of being in the same
+ * branch as the teller.
+ * - The delta amount is in [-999999, 999999].
+ *
+ * By application of very explicit Section 1.3.2, the final account
+ * balance is actually extracted by the driver with \gset.
+ *
+ * DISCLAIMER: only the transaction script is expected to conform to the
+ * specification. Other parts (types capabilities, initialization,
+ * performance data collection, database configuration, checks on final
+ * values, ...) may or may not conform to the requirements of the
+ * benchmark depending on the actual benchmark run.
+ */
+ "standard-tpcb",
+ "<builtin: standard TPC-B>",
+ /* choose teller and compute its branch */
+ "\\set tid random(1, " CppAsString2(ntellers) " * :scale)\n"
+ "\\set btid 1 + (:tid - 1) / (" CppAsString2(ntellers) " / " CppAsString2(nbranches) ")\n"
+ /* choose account branch: 85% same as teller unless there is only one branch */
+ "\\if random(0, 99) < 85 or :scale * " CppAsString2(nbranches)" = 1\n"
+ "\\set bid :btid\n"
+ "\\else\n"
+ "\\set bid random(1, " CppAsString2(nbranches) " * :scale - 1)\n"
+ "\\set bid :bid + case when :bid >= :btid then 1 else 0 end\n"
+ "\\endif\n"
+ /* choose account within branch */
+ "\\set lid random(1, " CppAsString2(naccounts) "/" CppAsString2(nbranches) ")\n"
+ "\\set aid (:bid - 1) * " CppAsString2(naccounts) "/" CppAsString2(nbranches) " + :lid\n"
+ "\\set delta random(-999999, 999999)\n"
+ /* it should probably be combined, but that currently breaks -M prepared */
+ "BEGIN;\n"
+ "INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)\n"
+ " VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);\n"
+ "UPDATE pgbench_accounts\n"
+ " SET abalance = abalance + :delta\n"
+ " WHERE aid = :aid\n"
+ " RETURNING abalance\\gset\n"
+ "UPDATE pgbench_tellers\n"
+ " SET tbalance = tbalance + :delta\n"
+ " WHERE tid = :tid;\n"
+ "UPDATE pgbench_branches\n"
+ " SET bbalance = bbalance + :delta\n"
+ " WHERE bid = :bid;\n"
+ "END;\n"
}
};
@@ -3616,6 +3675,11 @@ initCreateTables(PGconn *con)
* would completely break comparability of pgbench results with prior
* versions. Since pgbench has never pretended to be fully TPC-B compliant
* anyway, we stick with the historical behavior.
+ *
+ * Note 2: also according to spec, balances must hold 10 decimal digits
+ * plus sign. The 4-bytes "int" type used below does not conform. Given
+ * the default settings and the random walk (aka Drunkard's Walk) theorem,
+ * some balances may overflow on long runs.
*/
struct ddlinfo
{
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index 3b097a91b2..db236ef5db 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -171,6 +171,22 @@ pgbench(
],
'pgbench select only');
+pgbench(
+ '-t 10 -c 3 -M prepared -b st -r',
+ 0,
+ [
+ qr{builtin: standard TPC-B},
+ qr{clients: 3\b},
+ qr{threads: 1\b},
+ qr{processed: 30/30},
+ qr{mode: prepared},
+ qr{\\if random\(0, 99\) < 85}
+ ],
+ [
+ qr{vacuum}
+ ],
+ 'pgbench standard tpc-b');
+
# check if threads are supported
my $nthreads = 2;
