Debian Codegraph @

doc/src/sgml/ref/pgbench.sgml

raw
1 <!--
2 doc/src/sgml/ref/pgbench.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="pgbench">
7 <indexterm zone="pgbench">
8 <primary>pgbench</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle><application>pgbench</application></refentrytitle>
13 <manvolnum>1</manvolnum>
14 <refmiscinfo>Application</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>pgbench</refname>
19 <refpurpose>run a benchmark test on <productname>PostgreSQL</productname></refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <cmdsynopsis>
24 <command>pgbench</command>
25 <arg choice="plain"><option>-i</option></arg>
26 <arg rep="repeat"><replaceable>option</replaceable></arg>
27 <arg choice="opt"><replaceable>dbname</replaceable></arg>
28 </cmdsynopsis>
29 <cmdsynopsis>
30 <command>pgbench</command>
31 <arg rep="repeat"><replaceable>option</replaceable></arg>
32 <arg choice="opt"><replaceable>dbname</replaceable></arg>
33 </cmdsynopsis>
34 </refsynopsisdiv>
35
36 <refsect1>
37 <title>Description</title>
38 <para>
39 <application>pgbench</application> is a simple program for running benchmark
40 tests on <productname>PostgreSQL</productname>. It runs the same sequence of SQL
41 commands over and over, possibly in multiple concurrent database sessions,
42 and then calculates the average transaction rate (transactions per second).
43 By default, <application>pgbench</application> tests a scenario that is
44 loosely based on TPC-B, involving five <command>SELECT</command>,
45 <command>UPDATE</command>, and <command>INSERT</command> commands per transaction.
46 However, it is easy to test other cases by writing your own transaction
47 script files.
48 </para>
49
50 <para>
51 Typical output from <application>pgbench</application> looks like:
52
53 <screen>
54 transaction type: &lt;builtin: TPC-B (sort of)&gt;
55 scaling factor: 10
56 query mode: simple
57 number of clients: 10
58 number of threads: 1
59 maximum number of tries: 1
60 number of transactions per client: 1000
61 number of transactions actually processed: 10000/10000
62 number of failed transactions: 0 (0.000%)
63 latency average = 11.013 ms
64 latency stddev = 7.351 ms
65 initial connection time = 45.758 ms
66 tps = 896.967014 (without initial connection time)
67 </screen>
68
69 The first seven lines report some of the most important parameter
70 settings.
71 The sixth line reports the maximum number of tries for transactions with
72 serialization or deadlock errors (see <xref linkend="failures-and-retries"/>
73 for more information).
74 The eighth line reports the number of transactions completed
75 and intended (the latter being just the product of number of clients
76 and number of transactions per client); these will be equal unless the run
77 failed before completion or some SQL command(s) failed. (In
78 <option>-T</option> mode, only the actual number of transactions is printed.)
79 The next line reports the number of failed transactions due to
80 serialization or deadlock errors (see <xref linkend="failures-and-retries"/>
81 for more information).
82 The last line reports the number of transactions per second.
83 </para>
84
85 <para>
86 The default TPC-B-like transaction test requires specific tables to be
87 set up beforehand. <application>pgbench</application> should be invoked with
88 the <option>-i</option> (initialize) option to create and populate these
89 tables. (When you are testing a custom script, you don't need this
90 step, but will instead need to do whatever setup your test needs.)
91 Initialization looks like:
92
93 <programlisting>
94 pgbench -i <optional> <replaceable>other-options</replaceable> </optional> <replaceable>dbname</replaceable>
95 </programlisting>
96
97 where <replaceable>dbname</replaceable> is the name of the already-created
98 database to test in. (You may also need <option>-h</option>,
99 <option>-p</option>, and/or <option>-U</option> options to specify how to
100 connect to the database server.)
101 </para>
102
103 <caution>
104 <para>
105 <literal>pgbench -i</literal> creates four tables <structname>pgbench_accounts</structname>,
106 <structname>pgbench_branches</structname>, <structname>pgbench_history</structname>, and
107 <structname>pgbench_tellers</structname>,
108 destroying any existing tables of these names.
109 Be very careful to use another database if you have tables having these
110 names!
111 </para>
112 </caution>
113
114 <para>
115 At the default <quote>scale factor</quote> of 1, the tables initially
116 contain this many rows:
117 <screen>
118 table # of rows
119 ---------------------------------
120 pgbench_branches 1
121 pgbench_tellers 10
122 pgbench_accounts 100000
123 pgbench_history 0
124 </screen>
125 You can (and, for most purposes, probably should) increase the number
126 of rows by using the <option>-s</option> (scale factor) option. The
127 <option>-F</option> (fillfactor) option might also be used at this point.
128 </para>
129
130 <para>
131 Once you have done the necessary setup, you can run your benchmark
132 with a command that doesn't include <option>-i</option>, that is
133
134 <programlisting>
135 pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>dbname</replaceable>
136 </programlisting>
137
138 In nearly all cases, you'll need some options to make a useful test.
139 The most important options are <option>-c</option> (number of clients),
140 <option>-t</option> (number of transactions), <option>-T</option> (time limit),
141 and <option>-f</option> (specify a custom script file).
142 See below for a full list.
143 </para>
144 </refsect1>
145
146 <refsect1>
147 <title>Options</title>
148
149 <para>
150 The following is divided into three subsections. Different options are
151 used during database initialization and while running benchmarks, but some
152 options are useful in both cases.
153 </para>
154
155 <refsect2 id="pgbench-init-options">
156 <title>Initialization Options</title>
157
158 <para>
159 <application>pgbench</application> accepts the following command-line
160 initialization arguments:
161
162 <variablelist>
163
164 <varlistentry id="pgbench-option-dbname">
165 <term><option><optional>-d</optional> <replaceable class="parameter">dbname</replaceable></option></term>
166 <term><option><optional>--dbname=</optional><replaceable class="parameter">dbname</replaceable></option></term>
167 <listitem>
168 <para>
169 Specifies the name of the database to test in. If this is
170 not specified, the environment variable
171 <envar>PGDATABASE</envar> is used. If that is not set, the
172 user name specified for the connection is used.
173 </para>
174 </listitem>
175 </varlistentry>
176
177 <varlistentry id="pgbench-option-initialize">
178 <term><option>-i</option></term>
179 <term><option>--initialize</option></term>
180 <listitem>
181 <para>
182 Required to invoke initialization mode.
183 </para>
184 </listitem>
185 </varlistentry>
186
187 <varlistentry id="pgbench-option-init-steps">
188 <term><option>-I <replaceable>init_steps</replaceable></option></term>
189 <term><option>--init-steps=<replaceable>init_steps</replaceable></option></term>
190 <listitem>
191 <para>
192 Perform just a selected set of the normal initialization steps.
193 <replaceable>init_steps</replaceable> specifies the
194 initialization steps to be performed, using one character per step.
195 Each step is invoked in the specified order.
196 The default is <literal>dtgvp</literal>.
197 The available steps are:
198
199 <variablelist>
200 <varlistentry id="pgbench-option-init-steps-d">
201 <term><literal>d</literal> (Drop)</term>
202 <listitem>
203 <para>
204 Drop any existing <application>pgbench</application> tables.
205 </para>
206 </listitem>
207 </varlistentry>
208 <varlistentry id="pgbench-option-init-steps-t">
209 <term><literal>t</literal> (create Tables)</term>
210 <listitem>
211 <para>
212 Create the tables used by the
213 standard <application>pgbench</application> scenario, namely
214 <structname>pgbench_accounts</structname>,
215 <structname>pgbench_branches</structname>,
216 <structname>pgbench_history</structname>, and
217 <structname>pgbench_tellers</structname>.
218 </para>
219 </listitem>
220 </varlistentry>
221 <varlistentry id="pgbench-option-init-steps-g">
222 <term><literal>g</literal> or <literal>G</literal> (Generate data, client-side or server-side)</term>
223 <listitem>
224 <para>
225 Generate data and load it into the standard tables,
226 replacing any data already present.
227 </para>
228 <para>
229 With <literal>g</literal> (client-side data generation),
230 data is generated in <command>pgbench</command> client and then
231 sent to the server. This uses the client/server bandwidth
232 extensively through a <command>COPY</command>.
233 <command>pgbench</command> uses the <option>FREEZE</option> option
234 to load data into ordinary (non-partition) tables with version 14
235 or later of <productname>PostgreSQL</productname> to speed up
236 subsequent <command>VACUUM</command>.
237 Using <literal>g</literal> causes logging to
238 print one message every 100,000 rows while generating data for all
239 tables.
240 </para>
241 <para>
242 With <literal>G</literal> (server-side data generation),
243 only small queries are sent from the <command>pgbench</command>
244 client and then data is actually generated in the server.
245 No significant bandwidth is required for this variant, but
246 the server will do more work.
247 Using <literal>G</literal> causes logging not to print any progress
248 message while generating data.
249 </para>
250 <para>
251 The default initialization behavior uses client-side data
252 generation (equivalent to <literal>g</literal>).
253 </para>
254 </listitem>
255 </varlistentry>
256 <varlistentry id="pgbench-option-init-steps-v">
257 <term><literal>v</literal> (Vacuum)</term>
258 <listitem>
259 <para>
260 Invoke <command>VACUUM</command> on the standard tables.
261 </para>
262 </listitem>
263 </varlistentry>
264 <varlistentry id="pgbench-option-init-steps-p">
265 <term><literal>p</literal> (create Primary keys)</term>
266 <listitem>
267 <para>
268 Create primary key indexes on the standard tables.
269 </para>
270 </listitem>
271 </varlistentry>
272 <varlistentry id="pgbench-option-init-steps-f">
273 <term><literal>f</literal> (create Foreign keys)</term>
274 <listitem>
275 <para>
276 Create foreign key constraints between the standard tables.
277 (Note that this step is not performed by default.)
278 </para>
279 </listitem>
280 </varlistentry>
281 </variablelist></para>
282 </listitem>
283 </varlistentry>
284
285 <varlistentry id="pgbench-option-fillfactor">
286 <term><option>-F</option> <replaceable>fillfactor</replaceable></term>
287 <term><option>--fillfactor=</option><replaceable>fillfactor</replaceable></term>
288 <listitem>
289 <para>
290 Create the <structname>pgbench_accounts</structname>,
291 <structname>pgbench_tellers</structname> and
292 <structname>pgbench_branches</structname> tables with the given fillfactor.
293 Default is 100.
294 </para>
295 </listitem>
296 </varlistentry>
297
298 <varlistentry id="pgbench-option-no-vacuum-init">
299 <term><option>-n</option></term>
300 <term><option>--no-vacuum</option></term>
301 <listitem>
302 <para>
303 Perform no vacuuming during initialization.
304 (This option suppresses the <literal>v</literal> initialization step,
305 even if it was specified in <option>-I</option>.)
306 </para>
307 </listitem>
308 </varlistentry>
309
310 <varlistentry id="pgbench-option-quiet">
311 <term><option>-q</option></term>
312 <term><option>--quiet</option></term>
313 <listitem>
314 <para>
315 Switch logging to quiet mode, producing only one progress message per 5
316 seconds. The default logging prints one message each 100,000 rows, which
317 often outputs many lines per second (especially on good hardware).
318 </para>
319 <para>
320 This setting has no effect if <literal>G</literal> is specified
321 in <option>-I</option>.
322 </para>
323 </listitem>
324 </varlistentry>
325
326 <varlistentry id="pgbench-option-scale-init">
327 <term><option>-s</option> <replaceable>scale_factor</replaceable></term>
328 <term><option>--scale=</option><replaceable>scale_factor</replaceable></term>
329 <listitem>
330 <para>
331 Multiply the number of rows generated by the scale factor.
332 For example, <literal>-s 100</literal> will create 10,000,000 rows
333 in the <structname>pgbench_accounts</structname> table. Default is 1.
334 When the scale is 20,000 or larger, the columns used to
335 hold account identifiers (<structfield>aid</structfield> columns)
336 will switch to using larger integers (<type>bigint</type>),
337 in order to be big enough to hold the range of account
338 identifiers.
339 </para>
340 </listitem>
341 </varlistentry>
342
343 <varlistentry id="pgbench-option-foreign-keys">
344 <term><option>--foreign-keys</option></term>
345 <listitem>
346 <para>
347 Create foreign key constraints between the standard tables.
348 (This option adds the <literal>f</literal> step to the initialization
349 step sequence, if it is not already present.)
350 </para>
351 </listitem>
352 </varlistentry>
353
354 <varlistentry id="pgbench-option-index-tablespace">
355 <term><option>--index-tablespace=<replaceable>index_tablespace</replaceable></option></term>
356 <listitem>
357 <para>
358 Create indexes in the specified tablespace, rather than the default
359 tablespace.
360 </para>
361 </listitem>
362 </varlistentry>
363
364 <varlistentry id="pgbench-option-partition-method">
365 <term><option>--partition-method=<replaceable>NAME</replaceable></option></term>
366 <listitem>
367 <para>
368 Create a partitioned <literal>pgbench_accounts</literal> table with
369 <replaceable>NAME</replaceable> method.
370 Expected values are <literal>range</literal> or <literal>hash</literal>.
371 This option requires that <option>--partitions</option> is set to non-zero.
372 If unspecified, default is <literal>range</literal>.
373 </para>
374 </listitem>
375 </varlistentry>
376
377 <varlistentry id="pgbench-option-partitions">
378 <term><option>--partitions=<replaceable>NUM</replaceable></option></term>
379 <listitem>
380 <para>
381 Create a partitioned <literal>pgbench_accounts</literal> table with
382 <replaceable>NUM</replaceable> partitions of nearly equal size for
383 the scaled number of accounts.
384 Default is <literal>0</literal>, meaning no partitioning.
385 </para>
386 </listitem>
387 </varlistentry>
388
389 <varlistentry id="pgbench-option-tablespace">
390 <term><option>--tablespace=<replaceable>tablespace</replaceable></option></term>
391 <listitem>
392 <para>
393 Create tables in the specified tablespace, rather than the default
394 tablespace.
395 </para>
396 </listitem>
397 </varlistentry>
398
399 <varlistentry id="pgbench-option-unlogged-tables">
400 <term><option>--unlogged-tables</option></term>
401 <listitem>
402 <para>
403 Create all tables as unlogged tables, rather than permanent tables.
404 </para>
405 </listitem>
406 </varlistentry>
407
408 </variablelist>
409 </para>
410
411 </refsect2>
412
413 <refsect2 id="pgbench-run-options">
414 <title>Benchmarking Options</title>
415
416 <para>
417 <application>pgbench</application> accepts the following command-line
418 benchmarking arguments:
419
420 <variablelist>
421 <varlistentry id="pgbench-option-builtin">
422 <term><option>-b</option> <replaceable>scriptname[@weight]</replaceable></term>
423 <term><option>--builtin</option>=<replaceable>scriptname[@weight]</replaceable></term>
424 <listitem>
425 <para>
426 Add the specified built-in script to the list of scripts to be executed.
427 Available built-in scripts are: <literal>tpcb-like</literal>,
428 <literal>simple-update</literal> and <literal>select-only</literal>.
429 Unambiguous prefixes of built-in names are accepted.
430 With the special name <literal>list</literal>, show the list of built-in scripts
431 and exit immediately.
432 </para>
433 <para>
434 Optionally, write an integer weight after <literal>@</literal> to
435 adjust the probability of selecting this script versus other ones.
436 The default weight is 1.
437 See below for details.
438 </para>
439 </listitem>
440 </varlistentry>
441
442 <varlistentry id="pgbench-option-client">
443 <term><option>-c</option> <replaceable>clients</replaceable></term>
444 <term><option>--client=</option><replaceable>clients</replaceable></term>
445 <listitem>
446 <para>
447 Number of clients simulated, that is, number of concurrent database
448 sessions. Default is 1.
449 </para>
450 </listitem>
451 </varlistentry>
452
453 <varlistentry id="pgbench-option-connect">
454 <term><option>-C</option></term>
455 <term><option>--connect</option></term>
456 <listitem>
457 <para>
458 Establish a new connection for each transaction, rather than
459 doing it just once per client session.
460 This is useful to measure the connection overhead.
461 </para>
462 </listitem>
463 </varlistentry>
464
465 <varlistentry id="pgbench-option-define">
466 <term><option>-D</option> <replaceable>varname</replaceable><literal>=</literal><replaceable>value</replaceable></term>
467 <term><option>--define=</option><replaceable>varname</replaceable><literal>=</literal><replaceable>value</replaceable></term>
468 <listitem>
469 <para>
470 Define a variable for use by a custom script (see below).
471 Multiple <option>-D</option> options are allowed.
472 </para>
473 </listitem>
474 </varlistentry>
475
476 <varlistentry id="pgbench-option-file">
477 <term><option>-f</option> <replaceable>filename[@weight]</replaceable></term>
478 <term><option>--file=</option><replaceable>filename[@weight]</replaceable></term>
479 <listitem>
480 <para>
481 Add a transaction script read from <replaceable>filename</replaceable>
482 to the list of scripts to be executed.
483 </para>
484 <para>
485 Optionally, write an integer weight after <literal>@</literal> to
486 adjust the probability of selecting this script versus other ones.
487 The default weight is 1.
488 (To use a script file name that includes an <literal>@</literal>
489 character, append a weight so that there is no ambiguity, for
490 example <literal>filen@me@1</literal>.)
491 See below for details.
492 </para>
493 </listitem>
494 </varlistentry>
495
496 <varlistentry id="pgbench-option-jobs">
497 <term><option>-j</option> <replaceable>threads</replaceable></term>
498 <term><option>--jobs=</option><replaceable>threads</replaceable></term>
499 <listitem>
500 <para>
501 Number of worker threads within <application>pgbench</application>.
502 Using more than one thread can be helpful on multi-CPU machines.
503 Clients are distributed as evenly as possible among available threads.
504 Default is 1.
505 </para>
506 </listitem>
507 </varlistentry>
508
509 <varlistentry id="pgbench-option-log">
510 <term><option>-l</option></term>
511 <term><option>--log</option></term>
512 <listitem>
513 <para>
514 Write information about each transaction to a log file.
515 See below for details.
516 </para>
517 </listitem>
518 </varlistentry>
519
520 <varlistentry id="pgbench-option-latency-limit">
521 <term><option>-L</option> <replaceable>limit</replaceable></term>
522 <term><option>--latency-limit=</option><replaceable>limit</replaceable></term>
523 <listitem>
524 <para>
525 Transactions that last more than <replaceable>limit</replaceable> milliseconds
526 are counted and reported separately, as <firstterm>late</firstterm>.
527 </para>
528 <para>
529 When throttling is used (<option>--rate=...</option>), transactions that
530 lag behind schedule by more than <replaceable>limit</replaceable> ms, and thus
531 have no hope of meeting the latency limit, are not sent to the server
532 at all. They are counted and reported separately as
533 <firstterm>skipped</firstterm>.
534 </para>
535 <para>
536 When the <option>--max-tries</option> option is used, a transaction
537 which fails due to a serialization anomaly or from a deadlock will not
538 be retried if the total time of all its tries is greater than
539 <replaceable>limit</replaceable> ms. To limit only the time of tries
540 and not their number, use <literal>--max-tries=0</literal>. By
541 default, the option <option>--max-tries</option> is set to 1 and
542 transactions with serialization/deadlock errors are not retried. See
543 <xref linkend="failures-and-retries"/> for more information about
544 retrying such transactions.
545 </para>
546 </listitem>
547 </varlistentry>
548
549 <varlistentry id="pgbench-option-protocol">
550 <term><option>-M</option> <replaceable>querymode</replaceable></term>
551 <term><option>--protocol=</option><replaceable>querymode</replaceable></term>
552 <listitem>
553 <para>
554 Protocol to use for submitting queries to the server:
555 <itemizedlist>
556 <listitem>
557 <para><literal>simple</literal>: use simple query protocol.</para>
558 </listitem>
559 <listitem>
560 <para><literal>extended</literal>: use extended query protocol.</para>
561 </listitem>
562 <listitem>
563 <para><literal>prepared</literal>: use extended query protocol with prepared statements.</para>
564 </listitem>
565 </itemizedlist>
566
567 In the <literal>prepared</literal> mode, <application>pgbench</application>
568 reuses the parse analysis result starting from the second query
569 iteration, so <application>pgbench</application> runs faster
570 than in other modes.
571 </para>
572 <para>
573 The default is simple query protocol. (See <xref linkend="protocol"/>
574 for more information.)
575 </para>
576 </listitem>
577 </varlistentry>
578
579 <varlistentry id="pgbench-option-no-vacuum-run">
580 <term><option>-n</option></term>
581 <term><option>--no-vacuum</option></term>
582 <listitem>
583 <para>
584 Perform no vacuuming before running the test.
585 This option is <emphasis>necessary</emphasis>
586 if you are running a custom test scenario that does not include
587 the standard tables <structname>pgbench_accounts</structname>,
588 <structname>pgbench_branches</structname>, <structname>pgbench_history</structname>, and
589 <structname>pgbench_tellers</structname>.
590 </para>
591 </listitem>
592 </varlistentry>
593
594 <varlistentry id="pgbench-option-skip-some-updates">
595 <term><option>-N</option></term>
596 <term><option>--skip-some-updates</option></term>
597 <listitem>
598 <para>
599 Run built-in simple-update script.
600 Shorthand for <option>-b simple-update</option>.
601 </para>
602 </listitem>
603 </varlistentry>
604
605 <varlistentry id="pgbench-option-progress">
606 <term><option>-P</option> <replaceable>sec</replaceable></term>
607 <term><option>--progress=</option><replaceable>sec</replaceable></term>
608 <listitem>
609 <para>
610 Show progress report every <replaceable>sec</replaceable> seconds. The report
611 includes the time since the beginning of the run, the TPS since the
612 last report, and the transaction latency average, standard deviation,
613 and the number of failed transactions since the last report. Under
614 throttling (<option>-R</option>), the latency is computed with respect
615 to the transaction scheduled start time, not the actual transaction
616 beginning time, thus it also includes the average schedule lag time.
617 When <option>--max-tries</option> is used to enable transaction retries
618 after serialization/deadlock errors, the report includes the number of
619 retried transactions and the sum of all retries.
620 </para>
621 </listitem>
622 </varlistentry>
623
624 <varlistentry id="pgbench-option-report-latencies">
625 <term><option>-r</option></term>
626 <term><option>--report-per-command</option></term>
627 <listitem>
628 <para>
629 Report the following statistics for each command after the benchmark
630 finishes: the average per-statement latency (execution time from the
631 perspective of the client), the number of failures, and the number of
632 retries after serialization or deadlock errors in this command. The
633 report displays retry statistics only if the
634 <option>--max-tries</option> option is not equal to 1.
635 </para>
636 </listitem>
637 </varlistentry>
638
639 <varlistentry id="pgbench-option-rate">
640 <term><option>-R</option> <replaceable>rate</replaceable></term>
641 <term><option>--rate=</option><replaceable>rate</replaceable></term>
642 <listitem>
643 <para>
644 Execute transactions targeting the specified rate instead of running
645 as fast as possible (the default). The rate is given in transactions
646 per second. If the targeted rate is above the maximum possible rate,
647 the rate limit won't impact the results.
648 </para>
649 <para>
650 The rate is targeted by starting transactions along a
651 Poisson-distributed schedule time line. The expected start time
652 schedule moves forward based on when the client first started, not
653 when the previous transaction ended. That approach means that when
654 transactions go past their original scheduled end time, it is
655 possible for later ones to catch up again.
656 </para>
657 <para>
658 When throttling is active, the transaction latency reported at the
659 end of the run is calculated from the scheduled start times, so it
660 includes the time each transaction had to wait for the previous
661 transaction to finish. The wait time is called the schedule lag time,
662 and its average and maximum are also reported separately. The
663 transaction latency with respect to the actual transaction start time,
664 i.e., the time spent executing the transaction in the database, can be
665 computed by subtracting the schedule lag time from the reported
666 latency.
667 </para>
668
669 <para>
670 If <option>--latency-limit</option> is used together with <option>--rate</option>,
671 a transaction can lag behind so much that it is already over the
672 latency limit when the previous transaction ends, because the latency
673 is calculated from the scheduled start time. Such transactions are
674 not sent to the server, but are skipped altogether and counted
675 separately.
676 </para>
677
678 <para>
679 A high schedule lag time is an indication that the system cannot
680 process transactions at the specified rate, with the chosen number of
681 clients and threads. When the average transaction execution time is
682 longer than the scheduled interval between each transaction, each
683 successive transaction will fall further behind, and the schedule lag
684 time will keep increasing the longer the test run is. When that
685 happens, you will have to reduce the specified transaction rate.
686 </para>
687 </listitem>
688 </varlistentry>
689
690 <varlistentry id="pgbench-option-scale-run">
691 <term><option>-s</option> <replaceable>scale_factor</replaceable></term>
692 <term><option>--scale=</option><replaceable>scale_factor</replaceable></term>
693 <listitem>
694 <para>
695 Report the specified scale factor in <application>pgbench</application>'s
696 output. With the built-in tests, this is not necessary; the
697 correct scale factor will be detected by counting the number of
698 rows in the <structname>pgbench_branches</structname> table.
699 However, when testing only custom benchmarks (<option>-f</option> option),
700 the scale factor will be reported as 1 unless this option is used.
701 </para>
702 </listitem>
703 </varlistentry>
704
705 <varlistentry id="pgbench-option-select-only">
706 <term><option>-S</option></term>
707 <term><option>--select-only</option></term>
708 <listitem>
709 <para>
710 Run built-in select-only script.
711 Shorthand for <option>-b select-only</option>.
712 </para>
713 </listitem>
714 </varlistentry>
715
716 <varlistentry id="pgbench-option-transactions">
717 <term><option>-t</option> <replaceable>transactions</replaceable></term>
718 <term><option>--transactions=</option><replaceable>transactions</replaceable></term>
719 <listitem>
720 <para>
721 Number of transactions each client runs. Default is 10.
722 </para>
723 </listitem>
724 </varlistentry>
725
726 <varlistentry id="pgbench-option-time">
727 <term><option>-T</option> <replaceable>seconds</replaceable></term>
728 <term><option>--time=</option><replaceable>seconds</replaceable></term>
729 <listitem>
730 <para>
731 Run the test for this many seconds, rather than a fixed number of
732 transactions per client. <option>-t</option> and
733 <option>-T</option> are mutually exclusive.
734 </para>
735 </listitem>
736 </varlistentry>
737
738 <varlistentry id="pgbench-option-vacuum-all">
739 <term><option>-v</option></term>
740 <term><option>--vacuum-all</option></term>
741 <listitem>
742 <para>
743 Vacuum all four standard tables before running the test.
744 With neither <option>-n</option> nor <option>-v</option>, <application>pgbench</application> will vacuum the
745 <structname>pgbench_tellers</structname> and <structname>pgbench_branches</structname>
746 tables, and will truncate <structname>pgbench_history</structname>.
747 </para>
748 </listitem>
749 </varlistentry>
750
751 <varlistentry id="pgbench-option-aggregate-interval">
752 <term><option>--aggregate-interval=<replaceable>seconds</replaceable></option></term>
753 <listitem>
754 <para>
755 Length of aggregation interval (in seconds). May be used only
756 with <option>-l</option> option. With this option, the log contains
757 per-interval summary data, as described below.
758 </para>
759 </listitem>
760 </varlistentry>
761
762 <varlistentry id="pgbench-option-exit-on-abort">
763 <term><option>--exit-on-abort</option></term>
764 <listitem>
765 <para>
766 Exit immediately when any client is aborted due to some error. Without
767 this option, even when a client is aborted, other clients could
768 continue their run as specified by <option>-t</option>
769 or <option>-T</option> option, and <application>pgbench</application>
770 will print an incomplete results in this case.
771 </para>
772 <para>
773 Note that serialization failures or deadlock failures do not abort the
774 client, so they are not affected by this option.
775 See <xref linkend="failures-and-retries"/> for more information.
776 </para>
777 </listitem>
778 </varlistentry>
779
780 <varlistentry id="pgbench-option-failures-detailed">
781 <term><option>--failures-detailed</option></term>
782 <listitem>
783 <para>
784 Report failures in per-transaction and aggregation logs, as well as in
785 the main and per-script reports, grouped by the following types:
786 <itemizedlist>
787 <listitem>
788 <para>serialization failures;</para>
789 </listitem>
790 <listitem>
791 <para>deadlock failures;</para>
792 </listitem>
793 </itemizedlist>
794 See <xref linkend="failures-and-retries"/> for more information.
795 </para>
796 </listitem>
797 </varlistentry>
798
799 <varlistentry id="pgbench-option-log-prefix">
800 <term><option>--log-prefix=<replaceable>prefix</replaceable></option></term>
801 <listitem>
802 <para>
803 Set the filename prefix for the log files created by
804 <option>--log</option>. The default is <literal>pgbench_log</literal>.
805 </para>
806 </listitem>
807 </varlistentry>
808
809 <varlistentry id="pgbench-option-max-tries">
810 <term><option>--max-tries=<replaceable>number_of_tries</replaceable></option></term>
811 <listitem>
812 <para>
813 Enable retries for transactions with serialization/deadlock errors and
814 set the maximum number of these tries. This option can be combined with
815 the <option>--latency-limit</option> option which limits the total time
816 of all transaction tries; moreover, you cannot use an unlimited number
817 of tries (<literal>--max-tries=0</literal>) without
818 <option>--latency-limit</option> or <option>--time</option>.
819 The default value is 1 and transactions with serialization/deadlock
820 errors are not retried. See <xref linkend="failures-and-retries"/>
821 for more information about retrying such transactions.
822 </para>
823 </listitem>
824 </varlistentry>
825
826 <varlistentry id="pgbench-option-progress-timestamp">
827 <term><option>--progress-timestamp</option></term>
828 <listitem>
829 <para>
830 When showing progress (option <option>-P</option>), use a timestamp
831 (Unix epoch) instead of the number of seconds since the
832 beginning of the run. The unit is in seconds, with millisecond
833 precision after the dot.
834 This helps compare logs generated by various tools.
835 </para>
836 </listitem>
837 </varlistentry>
838
839 <varlistentry id="pgbench-option-random-seed">
840 <term><option>--random-seed=</option><replaceable>seed</replaceable></term>
841 <listitem>
842 <para>
843 Set random generator seed. Seeds the system random number generator,
844 which then produces a sequence of initial generator states, one for
845 each thread.
846 Values for <replaceable>seed</replaceable> may be:
847 <literal>time</literal> (the default, the seed is based on the current time),
848 <literal>rand</literal> (use a strong random source, failing if none
849 is available), or an unsigned decimal integer value.
850 The random generator is invoked explicitly from a pgbench script
851 (<literal>random...</literal> functions) or implicitly (for instance option
852 <option>--rate</option> uses it to schedule transactions).
853 When explicitly set, the value used for seeding is shown on the terminal.
854 Any value allowed for <replaceable>seed</replaceable> may also be
855 provided through the environment variable
856 <literal>PGBENCH_RANDOM_SEED</literal>.
857 To ensure that the provided seed impacts all possible uses, put this option
858 first or use the environment variable.
859 </para>
860 <para>
861 Setting the seed explicitly allows to reproduce a <command>pgbench</command>
862 run exactly, as far as random numbers are concerned.
863 As the random state is managed per thread, this means the exact same
864 <command>pgbench</command> run for an identical invocation if there is one
865 client per thread and there are no external or data dependencies.
866 From a statistical viewpoint reproducing runs exactly is a bad idea because
867 it can hide the performance variability or improve performance unduly,
868 e.g., by hitting the same pages as a previous run.
869 However, it may also be of great help for debugging, for instance
870 re-running a tricky case which leads to an error.
871 Use wisely.
872 </para>
873 </listitem>
874 </varlistentry>
875
876 <varlistentry id="pgbench-option-sampling-rate">
877 <term><option>--sampling-rate=<replaceable>rate</replaceable></option></term>
878 <listitem>
879 <para>
880 Sampling rate, used when writing data into the log, to reduce the
881 amount of log generated. If this option is given, only the specified
882 fraction of transactions are logged. 1.0 means all transactions will
883 be logged, 0.05 means only 5% of the transactions will be logged.
884 </para>
885 <para>
886 Remember to take the sampling rate into account when processing the
887 log file. For example, when computing TPS values, you need to multiply
888 the numbers accordingly (e.g., with 0.01 sample rate, you'll only get
889 1/100 of the actual TPS).
890 </para>
891 </listitem>
892 </varlistentry>
893
894 <varlistentry id="pgbench-option-show-script">
895 <term><option>--show-script=</option><replaceable>scriptname</replaceable></term>
896 <listitem>
897 <para>
898 Show the actual code of builtin script <replaceable>scriptname</replaceable>
899 on stderr, and exit immediately.
900 </para>
901 </listitem>
902 </varlistentry>
903
904 <varlistentry id="pgbench-option-verbose-errors">
905 <term><option>--verbose-errors</option></term>
906 <listitem>
907 <para>
908 Print messages about all errors and failures (errors without retrying)
909 including which limit for retries was exceeded and how far it was
910 exceeded for the serialization/deadlock failures. (Note that in this
911 case the output can be significantly increased.)
912 See <xref linkend="failures-and-retries"/> for more information.
913 </para>
914 </listitem>
915 </varlistentry>
916
917 </variablelist>
918 </para>
919
920 </refsect2>
921
922 <refsect2 id="pgbench-common-options">
923 <title>Common Options</title>
924
925 <para>
926 <application>pgbench</application> also accepts the following common command-line
927 arguments for connection parameters and other common settings:
928
929 <variablelist>
930 <varlistentry id="pgbench-option-debug">
931 <term><option>--debug</option></term>
932 <listitem>
933 <para>
934 Print debugging output.
935 </para>
936 </listitem>
937 </varlistentry>
938
939 <varlistentry id="pgbench-option-host">
940 <term><option>-h</option> <replaceable>hostname</replaceable></term>
941 <term><option>--host=</option><replaceable>hostname</replaceable></term>
942 <listitem>
943 <para>
944 The database server's host name
945 </para>
946 </listitem>
947 </varlistentry>
948
949 <varlistentry id="pgbench-option-port">
950 <term><option>-p</option> <replaceable>port</replaceable></term>
951 <term><option>--port=</option><replaceable>port</replaceable></term>
952 <listitem>
953 <para>
954 The database server's port number
955 </para>
956 </listitem>
957 </varlistentry>
958
959 <varlistentry id="pgbench-option-username">
960 <term><option>-U</option> <replaceable>login</replaceable></term>
961 <term><option>--username=</option><replaceable>login</replaceable></term>
962 <listitem>
963 <para>
964 The user name to connect as
965 </para>
966 </listitem>
967 </varlistentry>
968
969 <varlistentry id="pgbench-option-version">
970 <term><option>-V</option></term>
971 <term><option>--version</option></term>
972 <listitem>
973 <para>
974 Print the <application>pgbench</application> version and exit.
975 </para>
976 </listitem>
977 </varlistentry>
978
979 <varlistentry id="pgbench-option-help">
980 <term><option>-?</option></term>
981 <term><option>--help</option></term>
982 <listitem>
983 <para>
984 Show help about <application>pgbench</application> command line
985 arguments, and exit.
986 </para>
987 </listitem>
988 </varlistentry>
989 </variablelist>
990 </para>
991
992 </refsect2>
993 </refsect1>
994
995 <refsect1>
996 <title>Exit Status</title>
997
998 <para>
999 A successful run will exit with status 0. Exit status 1 indicates static
1000 problems such as invalid command-line options or internal errors which
1001 are supposed to never occur. Early errors that occur when starting
1002 benchmark such as initial connection failures also exit with status 1.
1003 Errors during the run such as database errors or problems in the script
1004 will result in exit status 2. In the latter case,
1005 <application>pgbench</application> will print partial results if
1006 <option>--exit-on-abort</option> option is not specified.
1007 </para>
1008 </refsect1>
1009
1010 <refsect1>
1011 <title>Environment</title>
1012
1013 <variablelist>
1014 <varlistentry id="pgbench-environment-pgdatabase">
1015 <term><envar>PGDATABASE</envar></term>
1016 <term><envar>PGHOST</envar></term>
1017 <term><envar>PGPORT</envar></term>
1018 <term><envar>PGUSER</envar></term>
1019
1020 <listitem>
1021 <para>
1022 Default connection parameters.
1023 </para>
1024 </listitem>
1025 </varlistentry>
1026 </variablelist>
1027
1028 <para>
1029 This utility, like most other <productname>PostgreSQL</productname> utilities,
1030 uses the environment variables supported by <application>libpq</application>
1031 (see <xref linkend="libpq-envars"/>).
1032 </para>
1033
1034 <para>
1035 The environment variable <envar>PG_COLOR</envar> specifies whether to use
1036 color in diagnostic messages. Possible values are
1037 <literal>always</literal>, <literal>auto</literal> and
1038 <literal>never</literal>.
1039 </para>
1040 </refsect1>
1041
1042 <refsect1>
1043 <title>Notes</title>
1044
1045 <refsect2 id="transactions-and-scripts" xreflabel="What Is the &quot;Transaction&quot; Actually Performed in pgbench?">
1046 <title>What Is the <quote>Transaction</quote> Actually Performed in <application>pgbench</application>?</title>
1047
1048 <para>
1049 <application>pgbench</application> executes test scripts chosen randomly
1050 from a specified list.
1051 The scripts may include built-in scripts specified with <option>-b</option>
1052 and user-provided scripts specified with <option>-f</option>.
1053 Each script may be given a relative weight specified after an
1054 <literal>@</literal> so as to change its selection probability.
1055 The default weight is <literal>1</literal>.
1056 Scripts with a weight of <literal>0</literal> are ignored.
1057 </para>
1058
1059 <para>
1060 The default built-in transaction script (also invoked with <option>-b tpcb-like</option>)
1061 issues seven commands per transaction over randomly chosen <literal>aid</literal>,
1062 <literal>tid</literal>, <literal>bid</literal> and <literal>delta</literal>.
1063 The scenario is inspired by the TPC-B benchmark, but is not actually TPC-B,
1064 hence the name.
1065 </para>
1066
1067 <orderedlist>
1068 <listitem><para><literal>BEGIN;</literal></para></listitem>
1069 <listitem><para><literal>UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;</literal></para></listitem>
1070 <listitem><para><literal>SELECT abalance FROM pgbench_accounts WHERE aid = :aid;</literal></para></listitem>
1071 <listitem><para><literal>UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;</literal></para></listitem>
1072 <listitem><para><literal>UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;</literal></para></listitem>
1073 <listitem><para><literal>INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);</literal></para></listitem>
1074 <listitem><para><literal>END;</literal></para></listitem>
1075 </orderedlist>
1076
1077 <para>
1078 If you select the <literal>simple-update</literal> built-in (also <option>-N</option>),
1079 steps 4 and 5 aren't included in the transaction.
1080 This will avoid update contention on these tables, but
1081 it makes the test case even less like TPC-B.
1082 </para>
1083
1084 <para>
1085 If you select the <literal>select-only</literal> built-in (also <option>-S</option>),
1086 only the <command>SELECT</command> is issued.
1087 </para>
1088 </refsect2>
1089
1090 <refsect2>
1091 <title>Custom Scripts</title>
1092
1093 <para>
1094 <application>pgbench</application> has support for running custom
1095 benchmark scenarios by replacing the default transaction script
1096 (described above) with a transaction script read from a file
1097 (<option>-f</option> option). In this case a <quote>transaction</quote>
1098 counts as one execution of a script file.
1099 </para>
1100
1101 <para>
1102 A script file contains one or more SQL commands terminated by
1103 semicolons. Empty lines and lines beginning with
1104 <literal>--</literal> are ignored. Script files can also contain
1105 <quote>meta commands</quote>, which are interpreted by <application>pgbench</application>
1106 itself, as described below.
1107 </para>
1108
1109 <note>
1110 <para>
1111 Before <productname>PostgreSQL</productname> 9.6, SQL commands in script files
1112 were terminated by newlines, and so they could not be continued across
1113 lines. Now a semicolon is <emphasis>required</emphasis> to separate consecutive
1114 SQL commands (though an SQL command does not need one if it is followed
1115 by a meta command). If you need to create a script file that works with
1116 both old and new versions of <application>pgbench</application>, be sure to write
1117 each SQL command on a single line ending with a semicolon.
1118 </para>
1119 <para>
1120 It is assumed that <application>pgbench</application> scripts do not contain
1121 incomplete blocks of SQL
1122 transactions. If at runtime the client reaches the end of the script without
1123 completing the last transaction block, it will be aborted.
1124 </para>
1125 </note>
1126
1127 <para>
1128 There is a simple variable-substitution facility for script files.
1129 Variable names must consist of letters (including non-Latin letters),
1130 digits, and underscores, with the first character not being a digit.
1131 Variables can be set by the command-line <option>-D</option> option,
1132 explained above, or by the meta commands explained below.
1133 In addition to any variables preset by <option>-D</option> command-line options,
1134 there are a few variables that are preset automatically, listed in
1135 <xref linkend="pgbench-automatic-variables"/>. A value specified for these
1136 variables using <option>-D</option> takes precedence over the automatic presets.
1137 Once set, a variable's
1138 value can be inserted into an SQL command by writing
1139 <literal>:</literal><replaceable>variablename</replaceable>. When running more than
1140 one client session, each session has its own set of variables.
1141 <application>pgbench</application> supports up to 255 variable uses in one
1142 statement.
1143 </para>
1144
1145 <table id="pgbench-automatic-variables">
1146 <title>pgbench Automatic Variables</title>
1147 <tgroup cols="2">
1148 <colspec colname="col1" colwidth="1*"/>
1149 <colspec colname="col2" colwidth="2*"/>
1150 <thead>
1151 <row>
1152 <entry>Variable</entry>
1153 <entry>Description</entry>
1154 </row>
1155 </thead>
1156
1157 <tbody>
1158 <row>
1159 <entry> <literal>client_id</literal> </entry>
1160 <entry>unique number identifying the client session (starts from zero)</entry>
1161 </row>
1162
1163 <row>
1164 <entry> <literal>default_seed</literal> </entry>
1165 <entry>seed used in hash and pseudorandom permutation functions by default</entry>
1166 </row>
1167
1168 <row>
1169 <entry> <literal>random_seed</literal> </entry>
1170 <entry>random generator seed (unless overwritten with <option>-D</option>)</entry>
1171 </row>
1172
1173 <row>
1174 <entry> <literal>scale</literal> </entry>
1175 <entry>current scale factor</entry>
1176 </row>
1177 </tbody>
1178 </tgroup>
1179 </table>
1180
1181 <para>
1182 Script file meta commands begin with a backslash (<literal>\</literal>) and
1183 normally extend to the end of the line, although they can be continued
1184 to additional lines by writing backslash-return.
1185 Arguments to a meta command are separated by white space.
1186 These meta commands are supported:
1187 </para>
1188
1189 <variablelist>
1190 <varlistentry id="pgbench-metacommand-gset">
1191 <term>
1192 <literal>\gset [<replaceable>prefix</replaceable>]</literal>
1193 <literal>\aset [<replaceable>prefix</replaceable>]</literal>
1194 </term>
1195
1196 <listitem>
1197 <para>
1198 These commands may be used to end SQL queries, taking the place of the
1199 terminating semicolon (<literal>;</literal>).
1200 </para>
1201
1202 <para>
1203 When the <literal>\gset</literal> command is used, the preceding SQL query is
1204 expected to return one row, the columns of which are stored into variables
1205 named after column names, and prefixed with <replaceable>prefix</replaceable>
1206 if provided.
1207 </para>
1208
1209 <para>
1210 When the <literal>\aset</literal> command is used, all combined SQL queries
1211 (separated by <literal>\;</literal>) have their columns stored into variables
1212 named after column names, and prefixed with <replaceable>prefix</replaceable>
1213 if provided. If a query returns no row, no assignment is made and the variable
1214 can be tested for existence to detect this. If a query returns more than one
1215 row, the last value is kept.
1216 </para>
1217
1218 <para>
1219 <literal>\gset</literal> and <literal>\aset</literal> cannot be used in
1220 pipeline mode, since the query results are not yet available by the time
1221 the commands would need them.
1222 </para>
1223
1224 <para>
1225 The following example puts the final account balance from the first query
1226 into variable <replaceable>abalance</replaceable>, and fills variables
1227 <replaceable>p_two</replaceable> and <replaceable>p_three</replaceable>
1228 with integers from the third query.
1229 The result of the second query is discarded.
1230 The result of the two last combined queries are stored in variables
1231 <replaceable>four</replaceable> and <replaceable>five</replaceable>.
1232 <programlisting>
1233 UPDATE pgbench_accounts
1234 SET abalance = abalance + :delta
1235 WHERE aid = :aid
1236 RETURNING abalance \gset
1237 -- compound of two queries
1238 SELECT 1 \;
1239 SELECT 2 AS two, 3 AS three \gset p_
1240 SELECT 4 AS four \; SELECT 5 AS five \aset
1241 </programlisting></para>
1242 </listitem>
1243 </varlistentry>
1244
1245 <varlistentry id="pgbench-metacommand-if-else">
1246 <term><literal>\if</literal> <replaceable class="parameter">expression</replaceable></term>
1247 <term><literal>\elif</literal> <replaceable class="parameter">expression</replaceable></term>
1248 <term><literal>\else</literal></term>
1249 <term><literal>\endif</literal></term>
1250 <listitem>
1251 <para>
1252 This group of commands implements nestable conditional blocks,
1253 similarly to <literal>psql</literal>'s <xref linkend="psql-metacommand-if"/>.
1254 Conditional expressions are identical to those with <literal>\set</literal>,
1255 with non-zero values interpreted as true.
1256 </para>
1257 </listitem>
1258 </varlistentry>
1259
1260 <varlistentry id="pgbench-metacommand-set">
1261 <term>
1262 <literal>\set <replaceable>varname</replaceable> <replaceable>expression</replaceable></literal>
1263 </term>
1264
1265 <listitem>
1266 <para>
1267 Sets variable <replaceable>varname</replaceable> to a value calculated
1268 from <replaceable>expression</replaceable>.
1269 The expression may contain the <literal>NULL</literal> constant,
1270 Boolean constants <literal>TRUE</literal> and <literal>FALSE</literal>,
1271 integer constants such as <literal>5432</literal>,
1272 double constants such as <literal>3.14159</literal>,
1273 references to variables <literal>:</literal><replaceable>variablename</replaceable>,
1274 <link linkend="pgbench-builtin-operators">operators</link>
1275 with their usual SQL precedence and associativity,
1276 <link linkend="pgbench-builtin-functions">function calls</link>,
1277 SQL <link linkend="functions-case"><token>CASE</token> generic conditional
1278 expressions</link> and parentheses.
1279 </para>
1280
1281 <para>
1282 Functions and most operators return <literal>NULL</literal> on
1283 <literal>NULL</literal> input.
1284 </para>
1285
1286 <para>
1287 For conditional purposes, non zero numerical values are
1288 <literal>TRUE</literal>, zero numerical values and <literal>NULL</literal>
1289 are <literal>FALSE</literal>.
1290 </para>
1291
1292 <para>
1293 Too large or small integer and double constants, as well as
1294 integer arithmetic operators (<literal>+</literal>,
1295 <literal>-</literal>, <literal>*</literal> and <literal>/</literal>)
1296 raise errors on overflows.
1297 </para>
1298
1299 <para>
1300 When no final <token>ELSE</token> clause is provided to a
1301 <token>CASE</token>, the default value is <literal>NULL</literal>.
1302 </para>
1303
1304 <para>
1305 Examples:
1306 <programlisting>
1307 \set ntellers 10 * :scale
1308 \set aid (1021 * random(1, 100000 * :scale)) % \
1309 (100000 * :scale) + 1
1310 \set divx CASE WHEN :x &lt;&gt; 0 THEN :y/:x ELSE NULL END
1311 </programlisting></para>
1312 </listitem>
1313 </varlistentry>
1314
1315 <varlistentry id="pgbench-metacommand-sleep">
1316 <term>
1317 <literal>\sleep <replaceable>number</replaceable> [ us | ms | s ]</literal>
1318 </term>
1319
1320 <listitem>
1321 <para>
1322 Causes script execution to sleep for the specified duration in
1323 microseconds (<literal>us</literal>), milliseconds (<literal>ms</literal>) or seconds
1324 (<literal>s</literal>). If the unit is omitted then seconds are the default.
1325 <replaceable>number</replaceable> can be either an integer constant or a
1326 <literal>:</literal><replaceable>variablename</replaceable> reference to a variable
1327 having an integer value.
1328 </para>
1329
1330 <para>
1331 Example:
1332 <programlisting>
1333 \sleep 10 ms
1334 </programlisting></para>
1335 </listitem>
1336 </varlistentry>
1337
1338 <varlistentry id="pgbench-metacommand-setshell">
1339 <term>
1340 <literal>\setshell <replaceable>varname</replaceable> <replaceable>command</replaceable> [ <replaceable>argument</replaceable> ... ]</literal>
1341 </term>
1342
1343 <listitem>
1344 <para>
1345 Sets variable <replaceable>varname</replaceable> to the result of the shell command
1346 <replaceable>command</replaceable> with the given <replaceable>argument</replaceable>(s).
1347 The command must return an integer value through its standard output.
1348 </para>
1349
1350 <para>
1351 <replaceable>command</replaceable> and each <replaceable>argument</replaceable> can be either
1352 a text constant or a <literal>:</literal><replaceable>variablename</replaceable> reference
1353 to a variable. If you want to use an <replaceable>argument</replaceable> starting
1354 with a colon, write an additional colon at the beginning of
1355 <replaceable>argument</replaceable>.
1356 </para>
1357
1358 <para>
1359 Example:
1360 <programlisting>
1361 \setshell variable_to_be_assigned command literal_argument :variable ::literal_starting_with_colon
1362 </programlisting></para>
1363 </listitem>
1364 </varlistentry>
1365
1366 <varlistentry id="pgbench-metacommand-shell">
1367 <term>
1368 <literal>\shell <replaceable>command</replaceable> [ <replaceable>argument</replaceable> ... ]</literal>
1369 </term>
1370
1371 <listitem>
1372 <para>
1373 Same as <literal>\setshell</literal>, but the result of the command
1374 is discarded.
1375 </para>
1376
1377 <para>
1378 Example:
1379 <programlisting>
1380 \shell command literal_argument :variable ::literal_starting_with_colon
1381 </programlisting></para>
1382 </listitem>
1383 </varlistentry>
1384
1385 <varlistentry id="pgbench-metacommand-pipeline">
1386 <term><literal>\startpipeline</literal></term>
1387 <term><literal>\syncpipeline</literal></term>
1388 <term><literal>\endpipeline</literal></term>
1389
1390 <listitem>
1391 <para>
1392 This group of commands implements pipelining of SQL statements.
1393 A pipeline must begin with a <command>\startpipeline</command>
1394 and end with an <command>\endpipeline</command>. In between there
1395 may be any number of <command>\syncpipeline</command> commands,
1396 which sends a <link linkend="protocol-flow-ext-query">sync message</link>
1397 without ending the ongoing pipeline and flushing the send buffer.
1398 In pipeline mode, statements are sent to the server without waiting
1399 for the results of previous statements. See
1400 <xref linkend="libpq-pipeline-mode"/> for more details.
1401 Pipeline mode requires the use of extended query protocol.
1402 </para>
1403 </listitem>
1404 </varlistentry>
1405
1406 </variablelist>
1407 </refsect2>
1408
1409 <refsect2 id="pgbench-builtin-operators">
1410 <title>Built-in Operators</title>
1411
1412 <para>
1413 The arithmetic, bitwise, comparison and logical operators listed in
1414 <xref linkend="pgbench-operators"/> are built into <application>pgbench</application>
1415 and may be used in expressions appearing in
1416 <link linkend="pgbench-metacommand-set"><literal>\set</literal></link>.
1417 The operators are listed in increasing precedence order.
1418 Except as noted, operators taking two numeric inputs will produce
1419 a double value if either input is double, otherwise they produce
1420 an integer result.
1421 </para>
1422
1423 <table id="pgbench-operators">
1424 <title>pgbench Operators</title>
1425 <tgroup cols="1">
1426 <thead>
1427 <row>
1428 <entry role="func_table_entry"><para role="func_signature">
1429 Operator
1430 </para>
1431 <para>
1432 Description
1433 </para>
1434 <para>
1435 Example(s)
1436 </para></entry>
1437 </row>
1438 </thead>
1439
1440 <tbody>
1441 <row>
1442 <entry role="func_table_entry"><para role="func_signature">
1443 <replaceable>boolean</replaceable> <literal>OR</literal> <replaceable>boolean</replaceable>
1444 <returnvalue><replaceable>boolean</replaceable></returnvalue>
1445 </para>
1446 <para>
1447 Logical OR
1448 </para>
1449 <para>
1450 <literal>5 or 0</literal>
1451 <returnvalue>TRUE</returnvalue>
1452 </para></entry>
1453 </row>
1454
1455 <row>
1456 <entry role="func_table_entry"><para role="func_signature">
1457 <replaceable>boolean</replaceable> <literal>AND</literal> <replaceable>boolean</replaceable>
1458 <returnvalue><replaceable>boolean</replaceable></returnvalue>
1459 </para>
1460 <para>
1461 Logical AND
1462 </para>
1463 <para>
1464 <literal>3 and 0</literal>
1465 <returnvalue>FALSE</returnvalue>
1466 </para></entry>
1467 </row>
1468
1469 <row>
1470 <entry role="func_table_entry"><para role="func_signature">
1471 <literal>NOT</literal> <replaceable>boolean</replaceable>
1472 <returnvalue><replaceable>boolean</replaceable></returnvalue>
1473 </para>
1474 <para>
1475 Logical NOT
1476 </para>
1477 <para>
1478 <literal>not false</literal>
1479 <returnvalue>TRUE</returnvalue>
1480 </para></entry>
1481 </row>
1482
1483 <row>
1484 <entry role="func_table_entry"><para role="func_signature">
1485 <replaceable>boolean</replaceable> <literal>IS [NOT] (NULL|TRUE|FALSE)</literal>
1486 <returnvalue><replaceable>boolean</replaceable></returnvalue>
1487 </para>
1488 <para>
1489 Boolean value tests
1490 </para>
1491 <para>
1492 <literal>1 is null</literal>
1493 <returnvalue>FALSE</returnvalue>
1494 </para></entry>
1495 </row>
1496
1497 <row>
1498 <entry role="func_table_entry"><para role="func_signature">
1499 <replaceable>value</replaceable> <literal>ISNULL|NOTNULL</literal>
1500 <returnvalue><replaceable>boolean</replaceable></returnvalue>
1501 </para>
1502 <para>
1503 Nullness tests
1504 </para>
1505 <para>
1506 <literal>1 notnull</literal>
1507 <returnvalue>TRUE</returnvalue>
1508 </para></entry>
1509 </row>
1510
1511 <row>
1512 <entry role="func_table_entry"><para role="func_signature">
1513 <replaceable>number</replaceable> <literal>=</literal> <replaceable>number</replaceable>
1514 <returnvalue><replaceable>boolean</replaceable></returnvalue>
1515 </para>
1516 <para>
1517 Equal
1518 </para>
1519 <para>
1520 <literal>5 = 4</literal>
1521 <returnvalue>FALSE</returnvalue>
1522 </para></entry>
1523 </row>
1524
1525 <row>
1526 <entry role="func_table_entry"><para role="func_signature">
1527 <replaceable>number</replaceable> <literal>&lt;&gt;</literal> <replaceable>number</replaceable>
1528 <returnvalue><replaceable>boolean</replaceable></returnvalue>
1529 </para>
1530 <para>
1531 Not equal
1532 </para>
1533 <para>
1534 <literal>5 &lt;&gt; 4</literal>
1535 <returnvalue>TRUE</returnvalue>
1536 </para></entry>
1537 </row>
1538
1539 <row>
1540 <entry role="func_table_entry"><para role="func_signature">
1541 <replaceable>number</replaceable> <literal>!=</literal> <replaceable>number</replaceable>
1542 <returnvalue><replaceable>boolean</replaceable></returnvalue>
1543 </para>
1544 <para>
1545 Not equal
1546 </para>
1547 <para>
1548 <literal>5 != 5</literal>
1549 <returnvalue>FALSE</returnvalue>
1550 </para></entry>
1551 </row>
1552
1553 <row>
1554 <entry role="func_table_entry"><para role="func_signature">
1555 <replaceable>number</replaceable> <literal>&lt;</literal> <replaceable>number</replaceable>
1556 <returnvalue><replaceable>boolean</replaceable></returnvalue>
1557 </para>
1558 <para>
1559 Less than
1560 </para>
1561 <para>
1562 <literal>5 &lt; 4</literal>
1563 <returnvalue>FALSE</returnvalue>
1564 </para></entry>
1565 </row>
1566
1567 <row>
1568 <entry role="func_table_entry"><para role="func_signature">
1569 <replaceable>number</replaceable> <literal>&lt;=</literal> <replaceable>number</replaceable>
1570 <returnvalue><replaceable>boolean</replaceable></returnvalue>
1571 </para>
1572 <para>
1573 Less than or equal to
1574 </para>
1575 <para>
1576 <literal>5 &lt;= 4</literal>
1577 <returnvalue>FALSE</returnvalue>
1578 </para></entry>
1579 </row>
1580
1581 <row>
1582 <entry role="func_table_entry"><para role="func_signature">
1583 <replaceable>number</replaceable> <literal>&gt;</literal> <replaceable>number</replaceable>
1584 <returnvalue><replaceable>boolean</replaceable></returnvalue>
1585 </para>
1586 <para>
1587 Greater than
1588 </para>
1589 <para>
1590 <literal>5 &gt; 4</literal>
1591 <returnvalue>TRUE</returnvalue>
1592 </para></entry>
1593 </row>
1594
1595 <row>
1596 <entry role="func_table_entry"><para role="func_signature">
1597 <replaceable>number</replaceable> <literal>&gt;=</literal> <replaceable>number</replaceable>
1598 <returnvalue><replaceable>boolean</replaceable></returnvalue>
1599 </para>
1600 <para>
1601 Greater than or equal to
1602 </para>
1603 <para>
1604 <literal>5 &gt;= 4</literal>
1605 <returnvalue>TRUE</returnvalue>
1606 </para></entry>
1607 </row>
1608
1609 <row>
1610 <entry role="func_table_entry"><para role="func_signature">
1611 <replaceable>integer</replaceable> <literal>|</literal> <replaceable>integer</replaceable>
1612 <returnvalue><replaceable>integer</replaceable></returnvalue>
1613 </para>
1614 <para>
1615 Bitwise OR
1616 </para>
1617 <para>
1618 <literal>1 | 2</literal>
1619 <returnvalue>3</returnvalue>
1620 </para></entry>
1621 </row>
1622
1623 <row>
1624 <entry role="func_table_entry"><para role="func_signature">
1625 <replaceable>integer</replaceable> <literal>#</literal> <replaceable>integer</replaceable>
1626 <returnvalue><replaceable>integer</replaceable></returnvalue>
1627 </para>
1628 <para>
1629 Bitwise XOR
1630 </para>
1631 <para>
1632 <literal>1 # 3</literal>
1633 <returnvalue>2</returnvalue>
1634 </para></entry>
1635 </row>
1636
1637 <row>
1638 <entry role="func_table_entry"><para role="func_signature">
1639 <replaceable>integer</replaceable> <literal>&amp;</literal> <replaceable>integer</replaceable>
1640 <returnvalue><replaceable>integer</replaceable></returnvalue>
1641 </para>
1642 <para>
1643 Bitwise AND
1644 </para>
1645 <para>
1646 <literal>1 &amp; 3</literal>
1647 <returnvalue>1</returnvalue>
1648 </para></entry>
1649 </row>
1650
1651 <row>
1652 <entry role="func_table_entry"><para role="func_signature">
1653 <literal>~</literal> <replaceable>integer</replaceable>
1654 <returnvalue><replaceable>integer</replaceable></returnvalue>
1655 </para>
1656 <para>
1657 Bitwise NOT
1658 </para>
1659 <para>
1660 <literal>~ 1</literal>
1661 <returnvalue>-2</returnvalue>
1662 </para></entry>
1663 </row>
1664
1665 <row>
1666 <entry role="func_table_entry"><para role="func_signature">
1667 <replaceable>integer</replaceable> <literal>&lt;&lt;</literal> <replaceable>integer</replaceable>
1668 <returnvalue><replaceable>integer</replaceable></returnvalue>
1669 </para>
1670 <para>
1671 Bitwise shift left
1672 </para>
1673 <para>
1674 <literal>1 &lt;&lt; 2</literal>
1675 <returnvalue>4</returnvalue>
1676 </para></entry>
1677 </row>
1678
1679 <row>
1680 <entry role="func_table_entry"><para role="func_signature">
1681 <replaceable>integer</replaceable> <literal>&gt;&gt;</literal> <replaceable>integer</replaceable>
1682 <returnvalue><replaceable>integer</replaceable></returnvalue>
1683 </para>
1684 <para>
1685 Bitwise shift right
1686 </para>
1687 <para>
1688 <literal>8 &gt;&gt; 2</literal>
1689 <returnvalue>2</returnvalue>
1690 </para></entry>
1691 </row>
1692
1693 <row>
1694 <entry role="func_table_entry"><para role="func_signature">
1695 <replaceable>number</replaceable> <literal>+</literal> <replaceable>number</replaceable>
1696 <returnvalue><replaceable>number</replaceable></returnvalue>
1697 </para>
1698 <para>
1699 Addition
1700 </para>
1701 <para>
1702 <literal>5 + 4</literal>
1703 <returnvalue>9</returnvalue>
1704 </para></entry>
1705 </row>
1706
1707 <row>
1708 <entry role="func_table_entry"><para role="func_signature">
1709 <replaceable>number</replaceable> <literal>-</literal> <replaceable>number</replaceable>
1710 <returnvalue><replaceable>number</replaceable></returnvalue>
1711 </para>
1712 <para>
1713 Subtraction
1714 </para>
1715 <para>
1716 <literal>3 - 2.0</literal>
1717 <returnvalue>1.0</returnvalue>
1718 </para></entry>
1719 </row>
1720
1721 <row>
1722 <entry role="func_table_entry"><para role="func_signature">
1723 <replaceable>number</replaceable> <literal>*</literal> <replaceable>number</replaceable>
1724 <returnvalue><replaceable>number</replaceable></returnvalue>
1725 </para>
1726 <para>
1727 Multiplication
1728 </para>
1729 <para>
1730 <literal>5 * 4</literal>
1731 <returnvalue>20</returnvalue>
1732 </para></entry>
1733 </row>
1734
1735 <row>
1736 <entry role="func_table_entry"><para role="func_signature">
1737 <replaceable>number</replaceable> <literal>/</literal> <replaceable>number</replaceable>
1738 <returnvalue><replaceable>number</replaceable></returnvalue>
1739 </para>
1740 <para>
1741 Division (truncates the result towards zero if both inputs are integers)
1742 </para>
1743 <para>
1744 <literal>5 / 3</literal>
1745 <returnvalue>1</returnvalue>
1746 </para></entry>
1747 </row>
1748
1749 <row>
1750 <entry role="func_table_entry"><para role="func_signature">
1751 <replaceable>integer</replaceable> <literal>%</literal> <replaceable>integer</replaceable>
1752 <returnvalue><replaceable>integer</replaceable></returnvalue>
1753 </para>
1754 <para>
1755 Modulo (remainder)
1756 </para>
1757 <para>
1758 <literal>3 % 2</literal>
1759 <returnvalue>1</returnvalue>
1760 </para></entry>
1761 </row>
1762
1763 <row>
1764 <entry role="func_table_entry"><para role="func_signature">
1765 <literal>-</literal> <replaceable>number</replaceable>
1766 <returnvalue><replaceable>number</replaceable></returnvalue>
1767 </para>
1768 <para>
1769 Negation
1770 </para>
1771 <para>
1772 <literal>- 2.0</literal>
1773 <returnvalue>-2.0</returnvalue>
1774 </para></entry>
1775 </row>
1776 </tbody>
1777 </tgroup>
1778 </table>
1779 </refsect2>
1780
1781 <refsect2 id="pgbench-builtin-functions">
1782 <title>Built-In Functions</title>
1783
1784 <para>
1785 The functions listed in <xref linkend="pgbench-functions"/> are built
1786 into <application>pgbench</application> and may be used in expressions appearing in
1787 <link linkend="pgbench-metacommand-set"><literal>\set</literal></link>.
1788 </para>
1789
1790 <!-- list pgbench functions in alphabetical order -->
1791 <table id="pgbench-functions">
1792 <title>pgbench Functions</title>
1793 <tgroup cols="1">
1794 <thead>
1795 <row>
1796 <entry role="func_table_entry"><para role="func_signature">
1797 Function
1798 </para>
1799 <para>
1800 Description
1801 </para>
1802 <para>
1803 Example(s)
1804 </para></entry>
1805 </row>
1806 </thead>
1807
1808 <tbody>
1809 <row>
1810 <entry role="func_table_entry"><para role="func_signature">
1811 <function>abs</function> ( <replaceable>number</replaceable> )
1812 <returnvalue></returnvalue> same type as input
1813 </para>
1814 <para>
1815 Absolute value
1816 </para>
1817 <para>
1818 <literal>abs(-17)</literal>
1819 <returnvalue>17</returnvalue>
1820 </para></entry>
1821 </row>
1822
1823 <row>
1824 <entry role="func_table_entry"><para role="func_signature">
1825 <function>debug</function> ( <replaceable>number</replaceable> )
1826 <returnvalue></returnvalue> same type as input
1827 </para>
1828 <para>
1829 Prints the argument to <systemitem>stderr</systemitem>,
1830 and returns the argument.
1831 </para>
1832 <para>
1833 <literal>debug(5432.1)</literal>
1834 <returnvalue>5432.1</returnvalue>
1835 </para></entry>
1836 </row>
1837
1838 <row>
1839 <entry role="func_table_entry"><para role="func_signature">
1840 <function>double</function> ( <replaceable>number</replaceable> )
1841 <returnvalue>double</returnvalue>
1842 </para>
1843 <para>
1844 Casts to double.
1845 </para>
1846 <para>
1847 <literal>double(5432)</literal>
1848 <returnvalue>5432.0</returnvalue>
1849 </para></entry>
1850 </row>
1851
1852 <row>
1853 <entry role="func_table_entry"><para role="func_signature">
1854 <function>exp</function> ( <replaceable>number</replaceable> )
1855 <returnvalue>double</returnvalue>
1856 </para>
1857 <para>
1858 Exponential (<literal>e</literal> raised to the given power)
1859 </para>
1860 <para>
1861 <literal>exp(1.0)</literal>
1862 <returnvalue>2.718281828459045</returnvalue>
1863 </para></entry>
1864 </row>
1865
1866 <row>
1867 <entry role="func_table_entry"><para role="func_signature">
1868 <function>greatest</function> ( <replaceable>number</replaceable> <optional>, <literal>...</literal> </optional> )
1869 <returnvalue></returnvalue> <type>double</type> if any argument is double, else <type>integer</type>
1870 </para>
1871 <para>
1872 Selects the largest value among the arguments.
1873 </para>
1874 <para>
1875 <literal>greatest(5, 4, 3, 2)</literal>
1876 <returnvalue>5</returnvalue>
1877 </para></entry>
1878 </row>
1879
1880 <row>
1881 <entry role="func_table_entry"><para role="func_signature">
1882 <function>hash</function> ( <parameter>value</parameter> <optional>, <parameter>seed</parameter> </optional> )
1883 <returnvalue>integer</returnvalue>
1884 </para>
1885 <para>
1886 This is an alias for <function>hash_murmur2</function>.
1887 </para>
1888 <para>
1889 <literal>hash(10, 5432)</literal>
1890 <returnvalue>-5817877081768721676</returnvalue>
1891 </para></entry>
1892 </row>
1893
1894 <row>
1895 <entry role="func_table_entry"><para role="func_signature">
1896 <function>hash_fnv1a</function> ( <parameter>value</parameter> <optional>, <parameter>seed</parameter> </optional> )
1897 <returnvalue>integer</returnvalue>
1898 </para>
1899 <para>
1900 Computes <ulink url="https://en.wikipedia.org/wiki/Fowler%E2%80%93Noll%E2%80%93Vo_hash_function">FNV-1a hash</ulink>.
1901 </para>
1902 <para>
1903 <literal>hash_fnv1a(10, 5432)</literal>
1904 <returnvalue>-7793829335365542153</returnvalue>
1905 </para></entry>
1906 </row>
1907
1908 <row>
1909 <entry role="func_table_entry"><para role="func_signature">
1910 <function>hash_murmur2</function> ( <parameter>value</parameter> <optional>, <parameter>seed</parameter> </optional> )
1911 <returnvalue>integer</returnvalue>
1912 </para>
1913 <para>
1914 Computes <ulink url="https://en.wikipedia.org/wiki/MurmurHash">MurmurHash2 hash</ulink>.
1915 </para>
1916 <para>
1917 <literal>hash_murmur2(10, 5432)</literal>
1918 <returnvalue>-5817877081768721676</returnvalue>
1919 </para></entry>
1920 </row>
1921
1922 <row>
1923 <entry role="func_table_entry"><para role="func_signature">
1924 <function>int</function> ( <replaceable>number</replaceable> )
1925 <returnvalue>integer</returnvalue>
1926 </para>
1927 <para>
1928 Casts to integer.
1929 </para>
1930 <para>
1931 <literal>int(5.4 + 3.8)</literal>
1932 <returnvalue>9</returnvalue>
1933 </para></entry>
1934 </row>
1935
1936 <row>
1937 <entry role="func_table_entry"><para role="func_signature">
1938 <function>least</function> ( <replaceable>number</replaceable> <optional>, <literal>...</literal> </optional> )
1939 <returnvalue></returnvalue> <type>double</type> if any argument is double, else <type>integer</type>
1940 </para>
1941 <para>
1942 Selects the smallest value among the arguments.
1943 </para>
1944 <para>
1945 <literal>least(5, 4, 3, 2.1)</literal>
1946 <returnvalue>2.1</returnvalue>
1947 </para></entry>
1948 </row>
1949
1950 <row>
1951 <entry role="func_table_entry"><para role="func_signature">
1952 <function>ln</function> ( <replaceable>number</replaceable> )
1953 <returnvalue>double</returnvalue>
1954 </para>
1955 <para>
1956 Natural logarithm
1957 </para>
1958 <para>
1959 <literal>ln(2.718281828459045)</literal>
1960 <returnvalue>1.0</returnvalue>
1961 </para></entry>
1962 </row>
1963
1964 <row>
1965 <entry role="func_table_entry"><para role="func_signature">
1966 <function>mod</function> ( <replaceable>integer</replaceable>, <replaceable>integer</replaceable> )
1967 <returnvalue>integer</returnvalue>
1968 </para>
1969 <para>
1970 Modulo (remainder)
1971 </para>
1972 <para>
1973 <literal>mod(54, 32)</literal>
1974 <returnvalue>22</returnvalue>
1975 </para></entry>
1976 </row>
1977
1978 <row>
1979 <entry role="func_table_entry"><para role="func_signature">
1980 <function>permute</function> ( <parameter>i</parameter>, <parameter>size</parameter> [, <parameter>seed</parameter> ] )
1981 <returnvalue>integer</returnvalue>
1982 </para>
1983 <para>
1984 Permuted value of <parameter>i</parameter>, in the range
1985 <literal>[0, size)</literal>. This is the new position of
1986 <parameter>i</parameter> (modulo <parameter>size</parameter>) in a
1987 pseudorandom permutation of the integers <literal>0...size-1</literal>,
1988 parameterized by <parameter>seed</parameter>, see below.
1989 </para>
1990 <para>
1991 <literal>permute(0, 4)</literal>
1992 <returnvalue>an integer between 0 and 3</returnvalue>
1993 </para></entry>
1994 </row>
1995
1996 <row>
1997 <entry role="func_table_entry"><para role="func_signature">
1998 <function>pi</function> ()
1999 <returnvalue>double</returnvalue>
2000 </para>
2001 <para>
2002 Approximate value of <phrase role="symbol_font">&pi;</phrase>
2003 </para>
2004 <para>
2005 <literal>pi()</literal>
2006 <returnvalue>3.14159265358979323846</returnvalue>
2007 </para></entry>
2008 </row>
2009
2010 <row>
2011 <entry role="func_table_entry"><para role="func_signature">
2012 <function>pow</function> ( <parameter>x</parameter>, <parameter>y</parameter> )
2013 <returnvalue>double</returnvalue>
2014 </para>
2015 <para role="func_signature">
2016 <function>power</function> ( <parameter>x</parameter>, <parameter>y</parameter> )
2017 <returnvalue>double</returnvalue>
2018 </para>
2019 <para>
2020 <parameter>x</parameter> raised to the power of <parameter>y</parameter>
2021 </para>
2022 <para>
2023 <literal>pow(2.0, 10)</literal>
2024 <returnvalue>1024.0</returnvalue>
2025 </para></entry>
2026 </row>
2027
2028 <row>
2029 <entry role="func_table_entry"><para role="func_signature">
2030 <function>random</function> ( <parameter>lb</parameter>, <parameter>ub</parameter> )
2031 <returnvalue>integer</returnvalue>
2032 </para>
2033 <para>
2034 Computes a uniformly-distributed random integer in <literal>[lb,
2035 ub]</literal>.
2036 </para>
2037 <para>
2038 <literal>random(1, 10)</literal>
2039 <returnvalue>an integer between 1 and 10</returnvalue>
2040 </para></entry>
2041 </row>
2042
2043 <row>
2044 <entry role="func_table_entry"><para role="func_signature">
2045 <function>random_exponential</function> ( <parameter>lb</parameter>, <parameter>ub</parameter>, <parameter>parameter</parameter> )
2046 <returnvalue>integer</returnvalue>
2047 </para>
2048 <para>
2049 Computes an exponentially-distributed random integer in <literal>[lb,
2050 ub]</literal>, see below.
2051 </para>
2052 <para>
2053 <literal>random_exponential(1, 10, 3.0)</literal>
2054 <returnvalue>an integer between 1 and 10</returnvalue>
2055 </para></entry>
2056 </row>
2057
2058 <row>
2059 <entry role="func_table_entry"><para role="func_signature">
2060 <function>random_gaussian</function> ( <parameter>lb</parameter>, <parameter>ub</parameter>, <parameter>parameter</parameter> )
2061 <returnvalue>integer</returnvalue>
2062 </para>
2063 <para>
2064 Computes a Gaussian-distributed random integer in <literal>[lb,
2065 ub]</literal>, see below.
2066 </para>
2067 <para>
2068 <literal>random_gaussian(1, 10, 2.5)</literal>
2069 <returnvalue>an integer between 1 and 10</returnvalue>
2070 </para></entry>
2071 </row>
2072
2073 <row>
2074 <entry role="func_table_entry"><para role="func_signature">
2075 <function>random_zipfian</function> ( <parameter>lb</parameter>, <parameter>ub</parameter>, <parameter>parameter</parameter> )
2076 <returnvalue>integer</returnvalue>
2077 </para>
2078 <para>
2079 Computes a Zipfian-distributed random integer in <literal>[lb,
2080 ub]</literal>, see below.
2081 </para>
2082 <para>
2083 <literal>random_zipfian(1, 10, 1.5)</literal>
2084 <returnvalue>an integer between 1 and 10</returnvalue>
2085 </para></entry>
2086 </row>
2087
2088 <row>
2089 <entry role="func_table_entry"><para role="func_signature">
2090 <function>sqrt</function> ( <replaceable>number</replaceable> )
2091 <returnvalue>double</returnvalue>
2092 </para>
2093 <para>
2094 Square root
2095 </para>
2096 <para>
2097 <literal>sqrt(2.0)</literal>
2098 <returnvalue>1.414213562</returnvalue>
2099 </para></entry>
2100 </row>
2101 </tbody>
2102 </tgroup>
2103 </table>
2104
2105 <para>
2106 The <literal>random</literal> function generates values using a uniform
2107 distribution, that is all the values are drawn within the specified
2108 range with equal probability. The <literal>random_exponential</literal>,
2109 <literal>random_gaussian</literal> and <literal>random_zipfian</literal>
2110 functions require an additional double parameter which determines the precise
2111 shape of the distribution.
2112 </para>
2113
2114 <itemizedlist>
2115 <listitem>
2116 <para>
2117 For an exponential distribution, <replaceable>parameter</replaceable>
2118 controls the distribution by truncating a quickly-decreasing
2119 exponential distribution at <replaceable>parameter</replaceable>, and then
2120 projecting onto integers between the bounds.
2121 To be precise, with
2122 <literallayout>
2123 f(x) = exp(-parameter * (x - min) / (max - min + 1)) / (1 - exp(-parameter))
2124 </literallayout>
2125 Then value <replaceable>i</replaceable> between <replaceable>min</replaceable> and
2126 <replaceable>max</replaceable> inclusive is drawn with probability:
2127 <literal>f(i) - f(i + 1)</literal>.
2128 </para>
2129
2130 <para>
2131 Intuitively, the larger the <replaceable>parameter</replaceable>, the more
2132 frequently values close to <replaceable>min</replaceable> are accessed, and the
2133 less frequently values close to <replaceable>max</replaceable> are accessed.
2134 The closer to 0 <replaceable>parameter</replaceable> is, the flatter (more
2135 uniform) the access distribution.
2136 A crude approximation of the distribution is that the most frequent 1%
2137 values in the range, close to <replaceable>min</replaceable>, are drawn
2138 <replaceable>parameter</replaceable>% of the time.
2139 The <replaceable>parameter</replaceable> value must be strictly positive.
2140 </para>
2141 </listitem>
2142
2143 <listitem>
2144 <para>
2145 For a Gaussian distribution, the interval is mapped onto a standard
2146 normal distribution (the classical bell-shaped Gaussian curve) truncated
2147 at <literal>-parameter</literal> on the left and <literal>+parameter</literal>
2148 on the right.
2149 Values in the middle of the interval are more likely to be drawn.
2150 To be precise, if <literal>PHI(x)</literal> is the cumulative distribution
2151 function of the standard normal distribution, with mean <literal>mu</literal>
2152 defined as <literal>(max + min) / 2.0</literal>, with
2153 <literallayout>
2154 f(x) = PHI(2.0 * parameter * (x - mu) / (max - min + 1)) /
2155 (2.0 * PHI(parameter) - 1)
2156 </literallayout>
2157 then value <replaceable>i</replaceable> between <replaceable>min</replaceable> and
2158 <replaceable>max</replaceable> inclusive is drawn with probability:
2159 <literal>f(i + 0.5) - f(i - 0.5)</literal>.
2160 Intuitively, the larger the <replaceable>parameter</replaceable>, the more
2161 frequently values close to the middle of the interval are drawn, and the
2162 less frequently values close to the <replaceable>min</replaceable> and
2163 <replaceable>max</replaceable> bounds. About 67% of values are drawn from the
2164 middle <literal>1.0 / parameter</literal>, that is a relative
2165 <literal>0.5 / parameter</literal> around the mean, and 95% in the middle
2166 <literal>2.0 / parameter</literal>, that is a relative
2167 <literal>1.0 / parameter</literal> around the mean; for instance, if
2168 <replaceable>parameter</replaceable> is 4.0, 67% of values are drawn from the
2169 middle quarter (1.0 / 4.0) of the interval (i.e., from
2170 <literal>3.0 / 8.0</literal> to <literal>5.0 / 8.0</literal>) and 95% from
2171 the middle half (<literal>2.0 / 4.0</literal>) of the interval (second and third
2172 quartiles). The minimum allowed <replaceable>parameter</replaceable>
2173 value is 2.0.
2174 </para>
2175 </listitem>
2176 <listitem>
2177 <para>
2178 <literal>random_zipfian</literal> generates a bounded Zipfian
2179 distribution.
2180 <replaceable>parameter</replaceable> defines how skewed the distribution
2181 is. The larger the <replaceable>parameter</replaceable>, the more
2182 frequently values closer to the beginning of the interval are drawn.
2183 The distribution is such that, assuming the range starts from 1,
2184 the ratio of the probability of drawing <replaceable>k</replaceable>
2185 versus drawing <replaceable>k+1</replaceable> is
2186 <literal>((<replaceable>k</replaceable>+1)/<replaceable>k</replaceable>)**<replaceable>parameter</replaceable></literal>.
2187 For example, <literal>random_zipfian(1, ..., 2.5)</literal> produces
2188 the value <literal>1</literal> about <literal>(2/1)**2.5 =
2189 5.66</literal> times more frequently than <literal>2</literal>, which
2190 itself is produced <literal>(3/2)**2.5 = 2.76</literal> times more
2191 frequently than <literal>3</literal>, and so on.
2192 </para>
2193 <para>
2194 <application>pgbench</application>'s implementation is based on
2195 "Non-Uniform Random Variate Generation", Luc Devroye, p. 550-551,
2196 Springer 1986. Due to limitations of that algorithm,
2197 the <replaceable>parameter</replaceable> value is restricted to
2198 the range [1.001, 1000].
2199 </para>
2200 </listitem>
2201 </itemizedlist>
2202
2203 <note>
2204 <para>
2205 When designing a benchmark which selects rows non-uniformly, be aware
2206 that the rows chosen may be correlated with other data such as IDs from
2207 a sequence or the physical row ordering, which may skew performance
2208 measurements.
2209 </para>
2210 <para>
2211 To avoid this, you may wish to use the <function>permute</function>
2212 function, or some other additional step with similar effect, to shuffle
2213 the selected rows and remove such correlations.
2214 </para>
2215 </note>
2216
2217 <para>
2218 Hash functions <literal>hash</literal>, <literal>hash_murmur2</literal> and
2219 <literal>hash_fnv1a</literal> accept an input value and an optional seed parameter.
2220 In case the seed isn't provided the value of <literal>:default_seed</literal>
2221 is used, which is initialized randomly unless set by the command-line
2222 <literal>-D</literal> option.
2223 </para>
2224
2225 <para>
2226 <literal>permute</literal> accepts an input value, a size, and an optional
2227 seed parameter. It generates a pseudorandom permutation of integers in
2228 the range <literal>[0, size)</literal>, and returns the index of the input
2229 value in the permuted values. The permutation chosen is parameterized by
2230 the seed, which defaults to <literal>:default_seed</literal>, if not
2231 specified. Unlike the hash functions, <literal>permute</literal> ensures
2232 that there are no collisions or holes in the output values. Input values
2233 outside the interval are interpreted modulo the size. The function raises
2234 an error if the size is not positive. <function>permute</function> can be
2235 used to scatter the distribution of non-uniform random functions such as
2236 <literal>random_zipfian</literal> or <literal>random_exponential</literal>
2237 so that values drawn more often are not trivially correlated. For
2238 instance, the following <application>pgbench</application> script
2239 simulates a possible real world workload typical for social media and
2240 blogging platforms where a few accounts generate excessive load:
2241
2242 <programlisting>
2243 \set size 1000000
2244 \set r random_zipfian(1, :size, 1.07)
2245 \set k 1 + permute(:r, :size)
2246 </programlisting>
2247
2248 In some cases several distinct distributions are needed which don't correlate
2249 with each other and this is when the optional seed parameter comes in handy:
2250
2251 <programlisting>
2252 \set k1 1 + permute(:r, :size, :default_seed + 123)
2253 \set k2 1 + permute(:r, :size, :default_seed + 321)
2254 </programlisting>
2255
2256 A similar behavior can also be approximated with <function>hash</function>:
2257
2258 <programlisting>
2259 \set size 1000000
2260 \set r random_zipfian(1, 100 * :size, 1.07)
2261 \set k 1 + abs(hash(:r)) % :size
2262 </programlisting>
2263
2264 However, since <function>hash</function> generates collisions, some values
2265 will not be reachable and others will be more frequent than expected from
2266 the original distribution.
2267 </para>
2268
2269 <para>
2270 As an example, the full definition of the built-in TPC-B-like
2271 transaction is:
2272
2273 <programlisting>
2274 \set aid random(1, 100000 * :scale)
2275 \set bid random(1, 1 * :scale)
2276 \set tid random(1, 10 * :scale)
2277 \set delta random(-5000, 5000)
2278 BEGIN;
2279 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
2280 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
2281 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
2282 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
2283 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
2284 END;
2285 </programlisting>
2286
2287 This script allows each iteration of the transaction to reference
2288 different, randomly-chosen rows. (This example also shows why it's
2289 important for each client session to have its own variables &mdash;
2290 otherwise they'd not be independently touching different rows.)
2291 </para>
2292
2293 </refsect2>
2294
2295 <refsect2>
2296 <title>Per-Transaction Logging</title>
2297
2298 <para>
2299 With the <option>-l</option> option (but without
2300 the <option>--aggregate-interval</option> option),
2301 <application>pgbench</application> writes information about each transaction
2302 to a log file. The log file will be named
2303 <filename><replaceable>prefix</replaceable>.<replaceable>nnn</replaceable></filename>,
2304 where <replaceable>prefix</replaceable> defaults to <literal>pgbench_log</literal>, and
2305 <replaceable>nnn</replaceable> is the PID of the
2306 <application>pgbench</application> process.
2307 The prefix can be changed by using the <option>--log-prefix</option> option.
2308 If the <option>-j</option> option is 2 or higher, so that there are multiple
2309 worker threads, each will have its own log file. The first worker will
2310 use the same name for its log file as in the standard single worker case.
2311 The additional log files for the other workers will be named
2312 <filename><replaceable>prefix</replaceable>.<replaceable>nnn</replaceable>.<replaceable>mmm</replaceable></filename>,
2313 where <replaceable>mmm</replaceable> is a sequential number for each worker starting
2314 with 1.
2315 </para>
2316
2317 <para>
2318 Each line in a log file describes one transaction.
2319 It contains the following space-separated fields:
2320
2321 <variablelist>
2322 <varlistentry>
2323 <term><replaceable>client_id</replaceable></term>
2324 <listitem>
2325 <para>
2326 identifies the client session that ran the transaction
2327 </para>
2328 </listitem>
2329 </varlistentry>
2330
2331 <varlistentry>
2332 <term><replaceable>transaction_no</replaceable></term>
2333 <listitem>
2334 <para>
2335 counts how many transactions have been run by that session
2336 </para>
2337 </listitem>
2338 </varlistentry>
2339
2340 <varlistentry>
2341 <term><replaceable>time</replaceable></term>
2342 <listitem>
2343 <para>
2344 transaction's elapsed time, in microseconds
2345 </para>
2346 </listitem>
2347 </varlistentry>
2348
2349 <varlistentry>
2350 <term><replaceable>script_no</replaceable></term>
2351 <listitem>
2352 <para>
2353 identifies the script file that was used for the transaction
2354 (useful when multiple scripts are specified
2355 with <option>-f</option> or <option>-b</option>)
2356 </para>
2357 </listitem>
2358 </varlistentry>
2359
2360 <varlistentry>
2361 <term><replaceable>time_epoch</replaceable></term>
2362 <listitem>
2363 <para>
2364 transaction's completion time, as a Unix-epoch time stamp
2365 </para>
2366 </listitem>
2367 </varlistentry>
2368
2369 <varlistentry>
2370 <term><replaceable>time_us</replaceable></term>
2371 <listitem>
2372 <para>
2373 fractional-second part of transaction's completion time, in
2374 microseconds
2375 </para>
2376 </listitem>
2377 </varlistentry>
2378
2379 <varlistentry>
2380 <term><replaceable>schedule_lag</replaceable></term>
2381 <listitem>
2382 <para>
2383 transaction start delay, that is the difference between the
2384 transaction's scheduled start time and the time it actually
2385 started, in microseconds
2386 (present only if <option>--rate</option> is specified)
2387 </para>
2388 </listitem>
2389 </varlistentry>
2390
2391 <varlistentry>
2392 <term><replaceable>retries</replaceable></term>
2393 <listitem>
2394 <para>
2395 count of retries after serialization or deadlock errors during the
2396 transaction
2397 (present only if <option>--max-tries</option> is not equal to one)
2398 </para>
2399 </listitem>
2400 </varlistentry>
2401 </variablelist>
2402 </para>
2403
2404 <para>
2405 When both <option>--rate</option> and <option>--latency-limit</option> are used,
2406 the <replaceable>time</replaceable> for a skipped transaction will be reported as
2407 <literal>skipped</literal>.
2408 If the transaction ends with a failure, its <replaceable>time</replaceable>
2409 will be reported as <literal>failed</literal>. If you use the
2410 <option>--failures-detailed</option> option, the
2411 <replaceable>time</replaceable> of the failed transaction will be reported as
2412 <literal>serialization</literal> or
2413 <literal>deadlock</literal> depending on the type of failure (see
2414 <xref linkend="failures-and-retries"/> for more information).
2415 </para>
2416
2417 <para>
2418 Here is a snippet of a log file generated in a single-client run:
2419 <screen>
2420 0 199 2241 0 1175850568 995598
2421 0 200 2465 0 1175850568 998079
2422 0 201 2513 0 1175850569 608
2423 0 202 2038 0 1175850569 2663
2424 </screen>
2425
2426 Another example with <literal>--rate=100</literal>
2427 and <literal>--latency-limit=5</literal> (note the additional
2428 <replaceable>schedule_lag</replaceable> column):
2429 <screen>
2430 0 81 4621 0 1412881037 912698 3005
2431 0 82 6173 0 1412881037 914578 4304
2432 0 83 skipped 0 1412881037 914578 5217
2433 0 83 skipped 0 1412881037 914578 5099
2434 0 83 4722 0 1412881037 916203 3108
2435 0 84 4142 0 1412881037 918023 2333
2436 0 85 2465 0 1412881037 919759 740
2437 </screen>
2438 In this example, transaction 82 was late, because its latency (6.173 ms) was
2439 over the 5 ms limit. The next two transactions were skipped, because they
2440 were already late before they were even started.
2441 </para>
2442
2443 <para>
2444 The following example shows a snippet of a log file with failures and
2445 retries, with the maximum number of tries set to 10 (note the additional
2446 <replaceable>retries</replaceable> column):
2447 <screen>
2448 3 0 47423 0 1499414498 34501 3
2449 3 1 8333 0 1499414498 42848 0
2450 3 2 8358 0 1499414498 51219 0
2451 4 0 72345 0 1499414498 59433 6
2452 1 3 41718 0 1499414498 67879 4
2453 1 4 8416 0 1499414498 76311 0
2454 3 3 33235 0 1499414498 84469 3
2455 0 0 failed 0 1499414498 84905 9
2456 2 0 failed 0 1499414498 86248 9
2457 3 4 8307 0 1499414498 92788 0
2458 </screen>
2459 </para>
2460
2461 <para>
2462 If the <option>--failures-detailed</option> option is used, the type of
2463 failure is reported in the <replaceable>time</replaceable> like this:
2464 <screen>
2465 3 0 47423 0 1499414498 34501 3
2466 3 1 8333 0 1499414498 42848 0
2467 3 2 8358 0 1499414498 51219 0
2468 4 0 72345 0 1499414498 59433 6
2469 1 3 41718 0 1499414498 67879 4
2470 1 4 8416 0 1499414498 76311 0
2471 3 3 33235 0 1499414498 84469 3
2472 0 0 serialization 0 1499414498 84905 9
2473 2 0 serialization 0 1499414498 86248 9
2474 3 4 8307 0 1499414498 92788 0
2475 </screen>
2476 </para>
2477
2478 <para>
2479 When running a long test on hardware that can handle a lot of transactions,
2480 the log files can become very large. The <option>--sampling-rate</option> option
2481 can be used to log only a random sample of transactions.
2482 </para>
2483 </refsect2>
2484
2485 <refsect2>
2486 <title>Aggregated Logging</title>
2487
2488 <para>
2489 With the <option>--aggregate-interval</option> option, a different
2490 format is used for the log files. Each log line describes one
2491 aggregation interval. It contains the following space-separated
2492 fields:
2493
2494 <variablelist>
2495 <varlistentry>
2496 <term><replaceable>interval_start</replaceable></term>
2497 <listitem>
2498 <para>
2499 start time of the interval, as a Unix-epoch time stamp
2500 </para>
2501 </listitem>
2502 </varlistentry>
2503
2504 <varlistentry>
2505 <term><replaceable>num_transactions</replaceable></term>
2506 <listitem>
2507 <para>
2508 number of transactions within the interval
2509 </para>
2510 </listitem>
2511 </varlistentry>
2512
2513 <varlistentry>
2514 <term><replaceable>sum_latency</replaceable></term>
2515 <listitem>
2516 <para>
2517 sum of transaction latencies
2518 </para>
2519 </listitem>
2520 </varlistentry>
2521
2522 <varlistentry>
2523 <term><replaceable>sum_latency_2</replaceable></term>
2524 <listitem>
2525 <para>
2526 sum of squares of transaction latencies
2527 </para>
2528 </listitem>
2529 </varlistentry>
2530
2531 <varlistentry>
2532 <term><replaceable>min_latency</replaceable></term>
2533 <listitem>
2534 <para>
2535 minimum transaction latency
2536 </para>
2537 </listitem>
2538 </varlistentry>
2539
2540 <varlistentry>
2541 <term><replaceable>max_latency</replaceable></term>
2542 <listitem>
2543 <para>
2544 maximum transaction latency
2545 </para>
2546 </listitem>
2547 </varlistentry>
2548
2549 <varlistentry>
2550 <term><replaceable>sum_lag</replaceable></term>
2551 <listitem>
2552 <para>
2553 sum of transaction start delays
2554 (zero unless <option>--rate</option> is specified)
2555 </para>
2556 </listitem>
2557 </varlistentry>
2558
2559 <varlistentry>
2560 <term><replaceable>sum_lag_2</replaceable></term>
2561 <listitem>
2562 <para>
2563 sum of squares of transaction start delays
2564 (zero unless <option>--rate</option> is specified)
2565 </para>
2566 </listitem>
2567 </varlistentry>
2568
2569 <varlistentry>
2570 <term><replaceable>min_lag</replaceable></term>
2571 <listitem>
2572 <para>
2573 minimum transaction start delay
2574 (zero unless <option>--rate</option> is specified)
2575 </para>
2576 </listitem>
2577 </varlistentry>
2578
2579 <varlistentry>
2580 <term><replaceable>max_lag</replaceable></term>
2581 <listitem>
2582 <para>
2583 maximum transaction start delay
2584 (zero unless <option>--rate</option> is specified)
2585 </para>
2586 </listitem>
2587 </varlistentry>
2588
2589 <varlistentry>
2590 <term><replaceable>skipped</replaceable></term>
2591 <listitem>
2592 <para>
2593 number of transactions skipped because they would have started too late
2594 (zero unless <option>--rate</option>
2595 and <option>--latency-limit</option> are specified)
2596 </para>
2597 </listitem>
2598 </varlistentry>
2599
2600 <varlistentry>
2601 <term><replaceable>retried</replaceable></term>
2602 <listitem>
2603 <para>
2604 number of retried transactions
2605 (zero unless <option>--max-tries</option> is not equal to one)
2606 </para>
2607 </listitem>
2608 </varlistentry>
2609
2610 <varlistentry>
2611 <term><replaceable>retries</replaceable></term>
2612 <listitem>
2613 <para>
2614 number of retries after serialization or deadlock errors
2615 (zero unless <option>--max-tries</option> is not equal to one)
2616 </para>
2617 </listitem>
2618 </varlistentry>
2619
2620 <varlistentry>
2621 <term><replaceable>serialization_failures</replaceable></term>
2622 <listitem>
2623 <para>
2624 number of transactions that got a serialization error and were not
2625 retried afterwards
2626 (zero unless <option>--failures-detailed</option> is specified)
2627 </para>
2628 </listitem>
2629 </varlistentry>
2630
2631 <varlistentry>
2632 <term><replaceable>deadlock_failures</replaceable></term>
2633 <listitem>
2634 <para>
2635 number of transactions that got a deadlock error and were not
2636 retried afterwards
2637 (zero unless <option>--failures-detailed</option> is specified)
2638 </para>
2639 </listitem>
2640 </varlistentry>
2641 </variablelist>
2642 </para>
2643
2644 <para>
2645 Here is some example output generated with this option:
2646 <screen>
2647 <userinput>pgbench --aggregate-interval=10 --time=20 --client=10 --log --rate=1000 --latency-limit=10 --failures-detailed --max-tries=10 test</userinput>
2648
2649 1650260552 5178 26171317 177284491527 1136 44462 2647617 7321113867 0 9866 64 7564 28340 4148 0
2650 1650260562 4808 25573984 220121792172 1171 62083 3037380 9666800914 0 9998 598 7392 26621 4527 0
2651 </screen>
2652 </para>
2653
2654 <para>
2655 Notice that while the plain (unaggregated) log format shows which script
2656 was used for each transaction, the aggregated format does not. Therefore if
2657 you need per-script data, you need to aggregate the data on your own.
2658 </para>
2659
2660 </refsect2>
2661
2662 <refsect2>
2663 <title>Per-Statement Report</title>
2664
2665 <para>
2666 With the <option>-r</option> option, <application>pgbench</application>
2667 collects the following statistics for each statement:
2668 <itemizedlist>
2669 <listitem>
2670 <para>
2671 <literal>latency</literal> &mdash; elapsed transaction time for each
2672 statement. <application>pgbench</application> reports an average value
2673 of all successful runs of the statement.
2674 </para>
2675 </listitem>
2676 <listitem>
2677 <para>
2678 The number of failures in this statement. See
2679 <xref linkend="failures-and-retries"/> for more information.
2680 </para>
2681 </listitem>
2682 <listitem>
2683 <para>
2684 The number of retries after a serialization or a deadlock error in this
2685 statement. See <xref linkend="failures-and-retries"/> for more information.
2686 </para>
2687 </listitem>
2688 </itemizedlist>
2689 </para>
2690
2691 <para>
2692 The report displays retry statistics only if the <option>--max-tries</option>
2693 option is not equal to 1.
2694 </para>
2695
2696 <para>
2697 All values are computed for each statement executed by every client and are
2698 reported after the benchmark has finished.
2699 </para>
2700
2701 <para>
2702 For the default script, the output will look similar to this:
2703 <screen>
2704 starting vacuum...end.
2705 transaction type: &lt;builtin: TPC-B (sort of)&gt;
2706 scaling factor: 1
2707 query mode: simple
2708 number of clients: 10
2709 number of threads: 1
2710 maximum number of tries: 1
2711 number of transactions per client: 1000
2712 number of transactions actually processed: 10000/10000
2713 number of failed transactions: 0 (0.000%)
2714 number of transactions above the 50.0 ms latency limit: 1311/10000 (13.110 %)
2715 latency average = 28.488 ms
2716 latency stddev = 21.009 ms
2717 initial connection time = 69.068 ms
2718 tps = 346.224794 (without initial connection time)
2719 statement latencies in milliseconds and failures:
2720 0.012 0 \set aid random(1, 100000 * :scale)
2721 0.002 0 \set bid random(1, 1 * :scale)
2722 0.002 0 \set tid random(1, 10 * :scale)
2723 0.002 0 \set delta random(-5000, 5000)
2724 0.319 0 BEGIN;
2725 0.834 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
2726 0.641 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
2727 11.126 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
2728 12.961 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
2729 0.634 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
2730 1.957 0 END;
2731 </screen>
2732
2733 Another example of output for the default script using serializable default
2734 transaction isolation level (<command>PGOPTIONS='-c
2735 default_transaction_isolation=serializable' pgbench ...</command>):
2736 <screen>
2737 starting vacuum...end.
2738 transaction type: &lt;builtin: TPC-B (sort of)&gt;
2739 scaling factor: 1
2740 query mode: simple
2741 number of clients: 10
2742 number of threads: 1
2743 maximum number of tries: 10
2744 number of transactions per client: 1000
2745 number of transactions actually processed: 6317/10000
2746 number of failed transactions: 3683 (36.830%)
2747 number of transactions retried: 7667 (76.670%)
2748 total number of retries: 45339
2749 number of transactions above the 50.0 ms latency limit: 106/6317 (1.678 %)
2750 latency average = 17.016 ms
2751 latency stddev = 13.283 ms
2752 initial connection time = 45.017 ms
2753 tps = 186.792667 (without initial connection time)
2754 statement latencies in milliseconds, failures and retries:
2755 0.006 0 0 \set aid random(1, 100000 * :scale)
2756 0.001 0 0 \set bid random(1, 1 * :scale)
2757 0.001 0 0 \set tid random(1, 10 * :scale)
2758 0.001 0 0 \set delta random(-5000, 5000)
2759 0.385 0 0 BEGIN;
2760 0.773 0 1 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
2761 0.624 0 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
2762 1.098 320 3762 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
2763 0.582 3363 41576 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
2764 0.465 0 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
2765 1.933 0 0 END;
2766 </screen></para>
2767
2768 <para>
2769 If multiple script files are specified, all statistics are reported
2770 separately for each script file.
2771 </para>
2772
2773 <para>
2774 Note that collecting the additional timing information needed for
2775 per-statement latency computation adds some overhead. This will slow
2776 average execution speed and lower the computed TPS. The amount
2777 of slowdown varies significantly depending on platform and hardware.
2778 Comparing average TPS values with and without latency reporting enabled
2779 is a good way to measure if the timing overhead is significant.
2780 </para>
2781 </refsect2>
2782
2783 <refsect2 id="failures-and-retries" xreflabel="Failures and Serialization/Deadlock Retries">
2784 <title>Failures and Serialization/Deadlock Retries</title>
2785
2786 <para>
2787 When executing <application>pgbench</application>, there are three main types
2788 of errors:
2789 <itemizedlist>
2790 <listitem>
2791 <para>
2792 Errors of the main program. They are the most serious and always result
2793 in an immediate exit from <application>pgbench</application> with the
2794 corresponding error message. They include:
2795 <itemizedlist>
2796 <listitem>
2797 <para>
2798 errors at the beginning of <application>pgbench</application>
2799 (e.g. an invalid option value);
2800 </para>
2801 </listitem>
2802 <listitem>
2803 <para>
2804 errors in the initialization mode (e.g. the query to create
2805 tables for built-in scripts fails);
2806 </para>
2807 </listitem>
2808 <listitem>
2809 <para>
2810 errors before starting threads (e.g. could not connect to the
2811 database server, syntax error in the meta command, thread
2812 creation failure);
2813 </para>
2814 </listitem>
2815 <listitem>
2816 <para>
2817 internal <application>pgbench</application> errors (which are
2818 supposed to never occur...).
2819 </para>
2820 </listitem>
2821 </itemizedlist></para>
2822 </listitem>
2823 <listitem>
2824 <para>
2825 Errors when the thread manages its clients (e.g. the client could not
2826 start a connection to the database server / the socket for connecting
2827 the client to the database server has become invalid). In such cases
2828 all clients of this thread stop while other threads continue to work.
2829 However, <option>--exit-on-abort</option> is specified, all of the
2830 threads stop immediately in this case.
2831 </para>
2832 </listitem>
2833 <listitem>
2834 <para>
2835 Direct client errors. They lead to immediate exit from
2836 <application>pgbench</application> with the corresponding error message
2837 in the case of an internal <application>pgbench</application>
2838 error (which are supposed to never occur...) or when
2839 <option>--exit-on-abort</option> is specified. Otherwise in the worst
2840 case they only lead to the abortion of the failed client while other
2841 clients continue their run (but some client errors are handled without
2842 an abortion of the client and reported separately, see below). Later in
2843 this section it is assumed that the discussed errors are only the
2844 direct client errors and they are not internal
2845 <application>pgbench</application> errors.
2846 </para>
2847 </listitem>
2848 </itemizedlist>
2849 </para>
2850
2851 <para>
2852 A client's run is aborted in case of a serious error; for example, the
2853 connection with the database server was lost or the end of script was reached
2854 without completing the last transaction. In addition, if execution of an SQL
2855 or meta command fails for reasons other than serialization or deadlock errors,
2856 the client is aborted. Otherwise, if an SQL command fails with serialization or
2857 deadlock errors, the client is not aborted. In such cases, the current
2858 transaction is rolled back, which also includes setting the client variables
2859 as they were before the run of this transaction (it is assumed that one
2860 transaction script contains only one transaction; see
2861 <xref linkend="transactions-and-scripts"/> for more information).
2862 Transactions with serialization or deadlock errors are repeated after
2863 rollbacks until they complete successfully or reach the maximum
2864 number of tries (specified by the <option>--max-tries</option> option) / the maximum
2865 time of retries (specified by the <option>--latency-limit</option> option) / the end
2866 of benchmark (specified by the <option>--time</option> option). If
2867 the last trial run fails, this transaction will be reported as failed but
2868 the client is not aborted and continues to work.
2869 </para>
2870
2871 <note>
2872 <para>
2873 Without specifying the <option>--max-tries</option> option, a transaction will
2874 never be retried after a serialization or deadlock error because its default
2875 value is 1. Use an unlimited number of tries (<literal>--max-tries=0</literal>)
2876 and the <option>--latency-limit</option> option to limit only the maximum time
2877 of tries. You can also use the <option>--time</option> option to limit the
2878 benchmark duration under an unlimited number of tries.
2879 </para>
2880 <para>
2881 Be careful when repeating scripts that contain multiple transactions: the
2882 script is always retried completely, so successful transactions can be
2883 performed several times.
2884 </para>
2885 <para>
2886 Be careful when repeating transactions with shell commands. Unlike the
2887 results of SQL commands, the results of shell commands are not rolled back,
2888 except for the variable value of the <command>\setshell</command> command.
2889 </para>
2890 </note>
2891
2892 <para>
2893 The latency of a successful transaction includes the entire time of
2894 transaction execution with rollbacks and retries. The latency is measured
2895 only for successful transactions and commands but not for failed transactions
2896 or commands.
2897 </para>
2898
2899 <para>
2900 The main report contains the number of failed transactions. If the
2901 <option>--max-tries</option> option is not equal to 1, the main report also
2902 contains statistics related to retries: the total number of retried
2903 transactions and total number of retries. The per-script report inherits all
2904 these fields from the main report. The per-statement report displays retry
2905 statistics only if the <option>--max-tries</option> option is not equal to 1.
2906 </para>
2907
2908 <para>
2909 If you want to group failures by basic types in per-transaction and
2910 aggregation logs, as well as in the main and per-script reports, use the
2911 <option>--failures-detailed</option> option. If you also want to distinguish
2912 all errors and failures (errors without retrying) by type including which
2913 limit for retries was exceeded and how much it was exceeded by for the
2914 serialization/deadlock failures, use the <option>--verbose-errors</option>
2915 option.
2916 </para>
2917 </refsect2>
2918
2919 <refsect2>
2920 <title>Table Access Methods</title>
2921
2922 <para>
2923 You may specify the <link linkend="tableam">Table Access Method</link>
2924 for the pgbench tables. The environment variable <envar>PGOPTIONS</envar>
2925 specifies database configuration options that are passed to PostgreSQL via
2926 the command line (See <xref linkend="config-setting-shell"/>).
2927 For example, a hypothetical default Table Access Method for the tables that
2928 pgbench creates called <literal>wuzza</literal> can be specified with:
2929 <programlisting>
2930 PGOPTIONS='-c default_table_access_method=wuzza'
2931 </programlisting>
2932 </para>
2933 </refsect2>
2934
2935 <refsect2>
2936 <title>Good Practices</title>
2937
2938 <para>
2939 It is very easy to use <application>pgbench</application> to produce completely
2940 meaningless numbers. Here are some guidelines to help you get useful
2941 results.
2942 </para>
2943
2944 <para>
2945 In the first place, <emphasis>never</emphasis> believe any test that runs
2946 for only a few seconds. Use the <option>-t</option> or <option>-T</option> option
2947 to make the run last at least a few minutes, so as to average out noise.
2948 In some cases you could need hours to get numbers that are reproducible.
2949 It's a good idea to try the test run a few times, to find out if your
2950 numbers are reproducible or not.
2951 </para>
2952
2953 <para>
2954 For the default TPC-B-like test scenario, the initialization scale factor
2955 (<option>-s</option>) should be at least as large as the largest number of
2956 clients you intend to test (<option>-c</option>); else you'll mostly be
2957 measuring update contention. There are only <option>-s</option> rows in
2958 the <structname>pgbench_branches</structname> table, and every transaction wants to
2959 update one of them, so <option>-c</option> values in excess of <option>-s</option>
2960 will undoubtedly result in lots of transactions blocked waiting for
2961 other transactions.
2962 </para>
2963
2964 <para>
2965 The default test scenario is also quite sensitive to how long it's been
2966 since the tables were initialized: accumulation of dead rows and dead space
2967 in the tables changes the results. To understand the results you must keep
2968 track of the total number of updates and when vacuuming happens. If
2969 autovacuum is enabled it can result in unpredictable changes in measured
2970 performance.
2971 </para>
2972
2973 <para>
2974 A limitation of <application>pgbench</application> is that it can itself become
2975 the bottleneck when trying to test a large number of client sessions.
2976 This can be alleviated by running <application>pgbench</application> on a different
2977 machine from the database server, although low network latency will be
2978 essential. It might even be useful to run several <application>pgbench</application>
2979 instances concurrently, on several client machines, against the same
2980 database server.
2981 </para>
2982 </refsect2>
2983 <refsect2>
2984 <title>Security</title>
2985
2986 <para>
2987 If untrusted users have access to a database that has not adopted a
2988 <link linkend="ddl-schemas-patterns">secure schema usage pattern</link>,
2989 do not run <application>pgbench</application> in that
2990 database. <application>pgbench</application> uses unqualified names and
2991 does not manipulate the search path.
2992 </para>
2993 </refsect2>
2994 </refsect1>
2995 </refentry>