| blob | 05d3f81619f16ba1a3de64124fcc723267a3b51a |
1 <!--
2 doc/src/sgml/ref/pgbench.sgml
3 PostgreSQL documentation
4 -->
9 </indexterm>
11 <refmeta>
15 </refmeta>
17 <refnamediv>
20 </refnamediv>
22 <refsynopsisdiv>
23 <cmdsynopsis>
28 </cmdsynopsis>
29 <cmdsynopsis>
33 </cmdsynopsis>
34 </refsynopsisdiv>
36 <refsect1>
38 <para>
41 commands over and over, possibly in multiple concurrent database sessions,
42 and then calculates the average transaction rate (transactions per second).
46 However, it is easy to test other cases by writing your own transaction
47 script files.
48 </para>
50 <para>
53 <screen>
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
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>
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
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
79 The next line reports the number of failed transactions due to
81 for more information).
82 The last line reports the number of transactions per second.
83 </para>
85 <para>
86 The default TPC-B-like transaction test requires specific tables to be
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:
93 <programlisting>
94 pgbench -i <optional> <replaceable>other-options</replaceable> </optional> <replaceable>dbname</replaceable>
95 </programlisting>
100 connect to the database server.)
101 </para>
103 <caution>
104 <para>
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>
114 <para>
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
128 </para>
130 <para>
131 Once you have done the necessary setup, you can run your benchmark
134 <programlisting>
135 pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>dbname</replaceable>
136 </programlisting>
138 In nearly all cases, you'll need some options to make a useful test.
142 See below for a full list.
143 </para>
144 </refsect1>
146 <refsect1>
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>
158 <para>
160 initialization arguments:
162 <variablelist>
166 <listitem>
167 <para>
168 Specifies the name of the database to test in. If this is
169 not specified, the environment variable
171 user name specified for the connection is used.
172 </para>
173 </listitem>
174 </varlistentry>
179 <listitem>
180 <para>
181 Required to invoke initialization mode.
182 </para>
183 </listitem>
184 </varlistentry>
189 <listitem>
190 <para>
191 Perform just a selected set of the normal initialization steps.
193 initialization steps to be performed, using one character per step.
194 Each step is invoked in the specified order.
196 The available steps are:
198 <variablelist>
201 <listitem>
202 <para>
204 </para>
205 </listitem>
206 </varlistentry>
209 <listitem>
210 <para>
211 Create the tables used by the
217 </para>
218 </listitem>
219 </varlistentry>
221 <term><literal>g</literal> or <literal>G</literal> (Generate data, client-side or server-side)</term>
222 <listitem>
223 <para>
224 Generate data and load it into the standard tables,
225 replacing any data already present.
226 </para>
227 <para>
230 sent to the server. This uses the client/server bandwidth
233 with version 14 or later
239 tables.
240 </para>
241 <para>
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.
248 message while generating data.
249 </para>
250 <para>
251 The default initialization behavior uses client-side data
253 </para>
254 </listitem>
255 </varlistentry>
258 <listitem>
259 <para>
261 </para>
262 </listitem>
263 </varlistentry>
266 <listitem>
267 <para>
268 Create primary key indexes on the standard tables.
269 </para>
270 </listitem>
271 </varlistentry>
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>
288 <listitem>
289 <para>
293 Default is 100.
294 </para>
295 </listitem>
296 </varlistentry>
301 <listitem>
302 <para>
303 Perform no vacuuming during initialization.
306 </para>
307 </listitem>
308 </varlistentry>
313 <listitem>
314 <para>
315 Switch logging to quiet mode, producing only one progress message per 5
317 often outputs many lines per second (especially on good hardware).
318 </para>
319 <para>
322 </para>
323 </listitem>
324 </varlistentry>
329 <listitem>
330 <para>
331 Multiply the number of rows generated by the scale factor.
337 in order to be big enough to hold the range of account
338 identifiers.
339 </para>
340 </listitem>
341 </varlistentry>
345 <listitem>
346 <para>
347 Create foreign key constraints between the standard tables.
349 step sequence, if it is not already present.)
350 </para>
351 </listitem>
352 </varlistentry>
356 <listitem>
357 <para>
358 Create indexes in the specified tablespace, rather than the default
359 tablespace.
360 </para>
361 </listitem>
362 </varlistentry>
366 <listitem>
367 <para>
373 </para>
374 </listitem>
375 </varlistentry>
379 <listitem>
380 <para>
383 the scaled number of accounts.
385 </para>
386 </listitem>
387 </varlistentry>
391 <listitem>
392 <para>
393 Create tables in the specified tablespace, rather than the default
394 tablespace.
395 </para>
396 </listitem>
397 </varlistentry>
401 <listitem>
402 <para>
403 Create all tables as unlogged tables, rather than permanent tables.
404 </para>
405 </listitem>
406 </varlistentry>
408 </variablelist>
409 </para>
411 </refsect2>
416 <para>
418 benchmarking arguments:
420 <variablelist>
424 <listitem>
425 <para>
426 Add the specified built-in script to the list of scripts to be executed.
429 Unambiguous prefixes of built-in names are accepted.
431 and exit immediately.
432 </para>
433 <para>
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>
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>
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>
468 <listitem>
469 <para>
470 Print debugging output.
471 </para>
472 </listitem>
473 </varlistentry>
476 <term><option>-D</option> <replaceable>varname</replaceable><literal>=</literal><replaceable>value</replaceable></term>
477 <term><option>--define=</option><replaceable>varname</replaceable><literal>=</literal><replaceable>value</replaceable></term>
478 <listitem>
479 <para>
480 Define a variable for use by a custom script (see below).
482 </para>
483 </listitem>
484 </varlistentry>
489 <listitem>
490 <para>
492 to the list of scripts to be executed.
493 </para>
494 <para>
496 adjust the probability of selecting this script versus other ones.
497 The default weight is 1.
499 character, append a weight so that there is no ambiguity, for
501 See below for details.
502 </para>
503 </listitem>
504 </varlistentry>
509 <listitem>
510 <para>
512 Using more than one thread can be helpful on multi-CPU machines.
513 Clients are distributed as evenly as possible among available threads.
514 Default is 1.
515 </para>
516 </listitem>
517 </varlistentry>
522 <listitem>
523 <para>
524 Write information about each transaction to a log file.
525 See below for details.
526 </para>
527 </listitem>
528 </varlistentry>
533 <listitem>
534 <para>
537 </para>
538 <para>
541 have no hope of meeting the latency limit, are not sent to the server
542 at all. They are counted and reported separately as
544 </para>
545 <para>
547 which fails due to a serialization anomaly or from a deadlock will not
548 be retried if the total time of all its tries is greater than
552 transactions with serialization/deadlock errors are not retried. See
554 retrying such transactions.
555 </para>
556 </listitem>
557 </varlistentry>
562 <listitem>
563 <para>
564 Protocol to use for submitting queries to the server:
565 <itemizedlist>
566 <listitem>
568 </listitem>
569 <listitem>
571 </listitem>
572 <listitem>
574 </listitem>
575 </itemizedlist>
578 reuses the parse analysis result starting from the second query
580 than in other modes.
581 </para>
582 <para>
584 for more information.)
585 </para>
586 </listitem>
587 </varlistentry>
592 <listitem>
593 <para>
594 Perform no vacuuming before running the test.
596 if you are running a custom test scenario that does not include
600 </para>
601 </listitem>
602 </varlistentry>
607 <listitem>
608 <para>
609 Run built-in simple-update script.
611 </para>
612 </listitem>
613 </varlistentry>
618 <listitem>
619 <para>
621 includes the time since the beginning of the run, the TPS since the
622 last report, and the transaction latency average, standard deviation,
623 and the number of failed transactions since the last report. Under
625 to the transaction scheduled start time, not the actual transaction
626 beginning time, thus it also includes the average schedule lag time.
628 after serialization/deadlock errors, the report includes the number of
629 retried transactions and the sum of all retries.
630 </para>
631 </listitem>
632 </varlistentry>
637 <listitem>
638 <para>
639 Report the following statistics for each command after the benchmark
640 finishes: the average per-statement latency (execution time from the
641 perspective of the client), the number of failures, and the number of
642 retries after serialization or deadlock errors in this command. The
643 report displays retry statistics only if the
645 </para>
646 </listitem>
647 </varlistentry>
652 <listitem>
653 <para>
654 Execute transactions targeting the specified rate instead of running
655 as fast as possible (the default). The rate is given in transactions
656 per second. If the targeted rate is above the maximum possible rate,
657 the rate limit won't impact the results.
658 </para>
659 <para>
660 The rate is targeted by starting transactions along a
661 Poisson-distributed schedule time line. The expected start time
662 schedule moves forward based on when the client first started, not
663 when the previous transaction ended. That approach means that when
664 transactions go past their original scheduled end time, it is
665 possible for later ones to catch up again.
666 </para>
667 <para>
668 When throttling is active, the transaction latency reported at the
669 end of the run is calculated from the scheduled start times, so it
670 includes the time each transaction had to wait for the previous
671 transaction to finish. The wait time is called the schedule lag time,
672 and its average and maximum are also reported separately. The
673 transaction latency with respect to the actual transaction start time,
674 i.e., the time spent executing the transaction in the database, can be
675 computed by subtracting the schedule lag time from the reported
676 latency.
677 </para>
679 <para>
681 a transaction can lag behind so much that it is already over the
682 latency limit when the previous transaction ends, because the latency
683 is calculated from the scheduled start time. Such transactions are
684 not sent to the server, but are skipped altogether and counted
685 separately.
686 </para>
688 <para>
689 A high schedule lag time is an indication that the system cannot
690 process transactions at the specified rate, with the chosen number of
691 clients and threads. When the average transaction execution time is
692 longer than the scheduled interval between each transaction, each
693 successive transaction will fall further behind, and the schedule lag
694 time will keep increasing the longer the test run is. When that
695 happens, you will have to reduce the specified transaction rate.
696 </para>
697 </listitem>
698 </varlistentry>
703 <listitem>
704 <para>
706 output. With the built-in tests, this is not necessary; the
707 correct scale factor will be detected by counting the number of
710 the scale factor will be reported as 1 unless this option is used.
711 </para>
712 </listitem>
713 </varlistentry>
718 <listitem>
719 <para>
720 Run built-in select-only script.
722 </para>
723 </listitem>
724 </varlistentry>
729 <listitem>
730 <para>
731 Number of transactions each client runs. Default is 10.
732 </para>
733 </listitem>
734 </varlistentry>
739 <listitem>
740 <para>
741 Run the test for this many seconds, rather than a fixed number of
744 </para>
745 </listitem>
746 </varlistentry>
751 <listitem>
752 <para>
753 Vacuum all four standard tables before running the test.
754 With neither <option>-n</option> nor <option>-v</option>, <application>pgbench</application> will vacuum the
757 </para>
758 </listitem>
759 </varlistentry>
763 <listitem>
764 <para>
765 Length of aggregation interval (in seconds). May be used only
767 per-interval summary data, as described below.
768 </para>
769 </listitem>
770 </varlistentry>
774 <listitem>
775 <para>
776 Exit immediately when any client is aborted due to some error. Without
777 this option, even when a client is aborted, other clients could
780 will print an incomplete results in this case.
781 </para>
782 <para>
783 Note that serialization failures or deadlock failures do not abort the
784 client, so they are not affected by this option.
786 </para>
787 </listitem>
788 </varlistentry>
792 <listitem>
793 <para>
794 Report failures in per-transaction and aggregation logs, as well as in
795 the main and per-script reports, grouped by the following types:
796 <itemizedlist>
797 <listitem>
799 </listitem>
800 <listitem>
802 </listitem>
803 </itemizedlist>
805 </para>
806 </listitem>
807 </varlistentry>
811 <listitem>
812 <para>
813 Set the filename prefix for the log files created by
815 </para>
816 </listitem>
817 </varlistentry>
821 <listitem>
822 <para>
823 Enable retries for transactions with serialization/deadlock errors and
824 set the maximum number of these tries. This option can be combined with
826 of all transaction tries; moreover, you cannot use an unlimited number
829 The default value is 1 and transactions with serialization/deadlock
831 for more information about retrying such transactions.
832 </para>
833 </listitem>
834 </varlistentry>
838 <listitem>
839 <para>
841 (Unix epoch) instead of the number of seconds since the
842 beginning of the run. The unit is in seconds, with millisecond
843 precision after the dot.
844 This helps compare logs generated by various tools.
845 </para>
846 </listitem>
847 </varlistentry>
851 <listitem>
852 <para>
853 Set random generator seed. Seeds the system random number generator,
854 which then produces a sequence of initial generator states, one for
855 each thread.
859 is available), or an unsigned decimal integer value.
860 The random generator is invoked explicitly from a pgbench script
863 When explicitly set, the value used for seeding is shown on the terminal.
865 provided through the environment variable
867 To ensure that the provided seed impacts all possible uses, put this option
868 first or use the environment variable.
869 </para>
870 <para>
872 run exactly, as far as random numbers are concerned.
873 As the random state is managed per thread, this means the exact same
875 client per thread and there are no external or data dependencies.
876 From a statistical viewpoint reproducing runs exactly is a bad idea because
877 it can hide the performance variability or improve performance unduly,
878 e.g., by hitting the same pages as a previous run.
879 However, it may also be of great help for debugging, for instance
880 re-running a tricky case which leads to an error.
881 Use wisely.
882 </para>
883 </listitem>
884 </varlistentry>
888 <listitem>
889 <para>
890 Sampling rate, used when writing data into the log, to reduce the
891 amount of log generated. If this option is given, only the specified
892 fraction of transactions are logged. 1.0 means all transactions will
894 </para>
895 <para>
896 Remember to take the sampling rate into account when processing the
897 log file. For example, when computing TPS values, you need to multiply
898 the numbers accordingly (e.g., with 0.01 sample rate, you'll only get
900 </para>
901 </listitem>
902 </varlistentry>
906 <listitem>
907 <para>
909 on stderr, and exit immediately.
910 </para>
911 </listitem>
912 </varlistentry>
916 <listitem>
917 <para>
918 Print messages about all errors and failures (errors without retrying)
919 including which limit for retries was exceeded and how far it was
920 exceeded for the serialization/deadlock failures. (Note that in this
921 case the output can be significantly increased.).
923 </para>
924 </listitem>
925 </varlistentry>
927 </variablelist>
928 </para>
930 </refsect2>
935 <para>
937 arguments for connection parameters:
939 <variablelist>
944 <listitem>
945 <para>
946 The database server's host name
947 </para>
948 </listitem>
949 </varlistentry>
954 <listitem>
955 <para>
956 The database server's port number
957 </para>
958 </listitem>
959 </varlistentry>
964 <listitem>
965 <para>
966 The user name to connect as
967 </para>
968 </listitem>
969 </varlistentry>
974 <listitem>
975 <para>
977 </para>
978 </listitem>
979 </varlistentry>
984 <listitem>
985 <para>
987 arguments, and exit.
988 </para>
989 </listitem>
990 </varlistentry>
991 </variablelist>
992 </para>
994 </refsect2>
995 </refsect1>
997 <refsect1>
1000 <para>
1002 problems such as invalid command-line options or internal errors which
1003 are supposed to never occur. Early errors that occur when starting
1004 benchmark such as initial connection failures also exit with status 1.
1005 Errors during the run such as database errors or problems in the script
1006 will result in exit status 2. In the latter case,
1009 </para>
1010 </refsect1>
1012 <refsect1>
1015 <variablelist>
1022 <listitem>
1023 <para>
1024 Default connection parameters.
1025 </para>
1026 </listitem>
1027 </varlistentry>
1028 </variablelist>
1030 <para>
1034 </para>
1036 <para>
1038 color in diagnostic messages. Possible values are
1041 </para>
1042 </refsect1>
1044 <refsect1>
1047 <refsect2 id="transactions-and-scripts" xreflabel="What Is the "Transaction" Actually Performed in pgbench?">
1048 <title>What Is the <quote>Transaction</quote> Actually Performed in <application>pgbench</application>?</title>
1050 <para>
1052 from a specified list.
1055 Each script may be given a relative weight specified after an
1059 </para>
1061 <para>
1065 The scenario is inspired by the TPC-B benchmark, but is not actually TPC-B,
1066 hence the name.
1067 </para>
1069 <orderedlist>
1071 <listitem><para><literal>UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;</literal></para></listitem>
1072 <listitem><para><literal>SELECT abalance FROM pgbench_accounts WHERE aid = :aid;</literal></para></listitem>
1073 <listitem><para><literal>UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;</literal></para></listitem>
1074 <listitem><para><literal>UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;</literal></para></listitem>
1075 <listitem><para><literal>INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);</literal></para></listitem>
1077 </orderedlist>
1079 <para>
1082 This will avoid update contention on these tables, but
1083 it makes the test case even less like TPC-B.
1084 </para>
1086 <para>
1089 </para>
1090 </refsect2>
1092 <refsect2>
1095 <para>
1097 benchmark scenarios by replacing the default transaction script
1098 (described above) with a transaction script read from a file
1100 counts as one execution of a script file.
1101 </para>
1103 <para>
1104 A script file contains one or more SQL commands terminated by
1105 semicolons. Empty lines and lines beginning with
1108 itself, as described below.
1109 </para>
1111 <note>
1112 <para>
1114 were terminated by newlines, and so they could not be continued across
1116 SQL commands (though an SQL command does not need one if it is followed
1117 by a meta command). If you need to create a script file that works with
1119 each SQL command on a single line ending with a semicolon.
1120 </para>
1121 <para>
1123 incomplete blocks of SQL
1124 transactions. If at runtime the client reaches the end of the script without
1125 completing the last transaction block, it will be aborted.
1126 </para>
1127 </note>
1129 <para>
1130 There is a simple variable-substitution facility for script files.
1131 Variable names must consist of letters (including non-Latin letters),
1132 digits, and underscores, with the first character not being a digit.
1134 explained above, or by the meta commands explained below.
1136 there are a few variables that are preset automatically, listed in
1139 Once set, a variable's
1140 value can be inserted into an SQL command by writing
1142 one client session, each session has its own set of variables.
1144 statement.
1145 </para>
1152 <thead>
1153 <row>
1156 </row>
1157 </thead>
1159 <tbody>
1160 <row>
1163 </row>
1165 <row>
1168 </row>
1170 <row>
1173 </row>
1175 <row>
1178 </row>
1179 </tbody>
1180 </tgroup>
1181 </table>
1183 <para>
1185 normally extend to the end of the line, although they can be continued
1186 to additional lines by writing backslash-return.
1187 Arguments to a meta command are separated by white space.
1188 These meta commands are supported:
1189 </para>
1191 <variablelist>
1193 <term>
1196 </term>
1198 <listitem>
1199 <para>
1200 These commands may be used to end SQL queries, taking the place of the
1202 </para>
1204 <para>
1206 expected to return one row, the columns of which are stored into variables
1208 if provided.
1209 </para>
1211 <para>
1215 if provided. If a query returns no row, no assignment is made and the variable
1216 can be tested for existence to detect this. If a query returns more than one
1217 row, the last value is kept.
1218 </para>
1220 <para>
1222 pipeline mode, since the query results are not yet available by the time
1223 the commands would need them.
1224 </para>
1226 <para>
1227 The following example puts the final account balance from the first query
1230 with integers from the third query.
1231 The result of the second query is discarded.
1232 The result of the two last combined queries are stored in variables
1234 <programlisting>
1235 UPDATE pgbench_accounts
1236 SET abalance = abalance + :delta
1237 WHERE aid = :aid
1238 RETURNING abalance \gset
1239 -- compound of two queries
1240 SELECT 1 \;
1243 </programlisting></para>
1244 </listitem>
1245 </varlistentry>
1252 <listitem>
1253 <para>
1254 This group of commands implements nestable conditional blocks,
1257 with non-zero values interpreted as true.
1258 </para>
1259 </listitem>
1260 </varlistentry>
1263 <term>
1264 <literal>\set <replaceable>varname</replaceable> <replaceable>expression</replaceable></literal>
1265 </term>
1267 <listitem>
1268 <para>
1277 with their usual SQL precedence and associativity,
1280 expressions</link> and parentheses.
1281 </para>
1283 <para>
1286 </para>
1288 <para>
1289 For conditional purposes, non zero numerical values are
1292 </para>
1294 <para>
1295 Too large or small integer and double constants, as well as
1298 raise errors on overflows.
1299 </para>
1301 <para>
1304 </para>
1306 <para>
1307 Examples:
1308 <programlisting>
1309 \set ntellers 10 * :scale
1313 </programlisting></para>
1314 </listitem>
1315 </varlistentry>
1318 <term>
1320 </term>
1322 <listitem>
1323 <para>
1324 Causes script execution to sleep for the specified duration in
1329 having an integer value.
1330 </para>
1332 <para>
1333 Example:
1334 <programlisting>
1335 \sleep 10 ms
1336 </programlisting></para>
1337 </listitem>
1338 </varlistentry>
1341 <term>
1342 <literal>\setshell <replaceable>varname</replaceable> <replaceable>command</replaceable> [ <replaceable>argument</replaceable> ... ]</literal>
1343 </term>
1345 <listitem>
1346 <para>
1349 The command must return an integer value through its standard output.
1350 </para>
1352 <para>
1356 with a colon, write an additional colon at the beginning of
1358 </para>
1360 <para>
1361 Example:
1362 <programlisting>
1363 \setshell variable_to_be_assigned command literal_argument :variable ::literal_starting_with_colon
1364 </programlisting></para>
1365 </listitem>
1366 </varlistentry>
1369 <term>
1370 <literal>\shell <replaceable>command</replaceable> [ <replaceable>argument</replaceable> ... ]</literal>
1371 </term>
1373 <listitem>
1374 <para>
1376 is discarded.
1377 </para>
1379 <para>
1380 Example:
1381 <programlisting>
1382 \shell command literal_argument :variable ::literal_starting_with_colon
1383 </programlisting></para>
1384 </listitem>
1385 </varlistentry>
1391 <listitem>
1392 <para>
1393 These commands delimit the start and end of a pipeline of SQL
1394 statements. In pipeline mode, statements are sent to the server
1395 without waiting for the results of previous statements. See
1397 Pipeline mode requires the use of extended query protocol.
1398 </para>
1399 </listitem>
1400 </varlistentry>
1402 </variablelist>
1403 </refsect2>
1408 <para>
1409 The arithmetic, bitwise, comparison and logical operators listed in
1411 and may be used in expressions appearing in
1413 The operators are listed in increasing precedence order.
1414 Except as noted, operators taking two numeric inputs will produce
1415 a double value if either input is double, otherwise they produce
1416 an integer result.
1417 </para>
1422 <thead>
1423 <row>
1425 Operator
1426 </para>
1427 <para>
1428 Description
1429 </para>
1430 <para>
1431 Example(s)
1432 </para></entry>
1433 </row>
1434 </thead>
1436 <tbody>
1437 <row>
1441 </para>
1442 <para>
1443 Logical OR
1444 </para>
1445 <para>
1448 </para></entry>
1449 </row>
1451 <row>
1455 </para>
1456 <para>
1457 Logical AND
1458 </para>
1459 <para>
1462 </para></entry>
1463 </row>
1465 <row>
1469 </para>
1470 <para>
1471 Logical NOT
1472 </para>
1473 <para>
1476 </para></entry>
1477 </row>
1479 <row>
1483 </para>
1484 <para>
1485 Boolean value tests
1486 </para>
1487 <para>
1490 </para></entry>
1491 </row>
1493 <row>
1497 </para>
1498 <para>
1499 Nullness tests
1500 </para>
1501 <para>
1504 </para></entry>
1505 </row>
1507 <row>
1511 </para>
1512 <para>
1513 Equal
1514 </para>
1515 <para>
1518 </para></entry>
1519 </row>
1521 <row>
1523 <replaceable>number</replaceable> <literal><></literal> <replaceable>number</replaceable>
1525 </para>
1526 <para>
1527 Not equal
1528 </para>
1529 <para>
1532 </para></entry>
1533 </row>
1535 <row>
1539 </para>
1540 <para>
1541 Not equal
1542 </para>
1543 <para>
1546 </para></entry>
1547 </row>
1549 <row>
1553 </para>
1554 <para>
1555 Less than
1556 </para>
1557 <para>
1560 </para></entry>
1561 </row>
1563 <row>
1567 </para>
1568 <para>
1569 Less than or equal to
1570 </para>
1571 <para>
1574 </para></entry>
1575 </row>
1577 <row>
1581 </para>
1582 <para>
1583 Greater than
1584 </para>
1585 <para>
1588 </para></entry>
1589 </row>
1591 <row>
1595 </para>
1596 <para>
1597 Greater than or equal to
1598 </para>
1599 <para>
1602 </para></entry>
1603 </row>
1605 <row>
1609 </para>
1610 <para>
1611 Bitwise OR
1612 </para>
1613 <para>
1616 </para></entry>
1617 </row>
1619 <row>
1623 </para>
1624 <para>
1625 Bitwise XOR
1626 </para>
1627 <para>
1630 </para></entry>
1631 </row>
1633 <row>
1637 </para>
1638 <para>
1639 Bitwise AND
1640 </para>
1641 <para>
1644 </para></entry>
1645 </row>
1647 <row>
1651 </para>
1652 <para>
1653 Bitwise NOT
1654 </para>
1655 <para>
1658 </para></entry>
1659 </row>
1661 <row>
1663 <replaceable>integer</replaceable> <literal><<</literal> <replaceable>integer</replaceable>
1665 </para>
1666 <para>
1667 Bitwise shift left
1668 </para>
1669 <para>
1672 </para></entry>
1673 </row>
1675 <row>
1677 <replaceable>integer</replaceable> <literal>>></literal> <replaceable>integer</replaceable>
1679 </para>
1680 <para>
1681 Bitwise shift right
1682 </para>
1683 <para>
1686 </para></entry>
1687 </row>
1689 <row>
1693 </para>
1694 <para>
1695 Addition
1696 </para>
1697 <para>
1700 </para></entry>
1701 </row>
1703 <row>
1707 </para>
1708 <para>
1709 Subtraction
1710 </para>
1711 <para>
1714 </para></entry>
1715 </row>
1717 <row>
1721 </para>
1722 <para>
1723 Multiplication
1724 </para>
1725 <para>
1728 </para></entry>
1729 </row>
1731 <row>
1735 </para>
1736 <para>
1737 Division (truncates the result towards zero if both inputs are integers)
1738 </para>
1739 <para>
1742 </para></entry>
1743 </row>
1745 <row>
1749 </para>
1750 <para>
1751 Modulo (remainder)
1752 </para>
1753 <para>
1756 </para></entry>
1757 </row>
1759 <row>
1763 </para>
1764 <para>
1765 Negation
1766 </para>
1767 <para>
1770 </para></entry>
1771 </row>
1772 </tbody>
1773 </tgroup>
1774 </table>
1775 </refsect2>
1780 <para>
1784 </para>
1786 <!-- list pgbench functions in alphabetical order -->
1790 <thead>
1791 <row>
1793 Function
1794 </para>
1795 <para>
1796 Description
1797 </para>
1798 <para>
1799 Example(s)
1800 </para></entry>
1801 </row>
1802 </thead>
1804 <tbody>
1805 <row>
1808 <returnvalue></returnvalue> same type as input
1809 </para>
1810 <para>
1811 Absolute value
1812 </para>
1813 <para>
1816 </para></entry>
1817 </row>
1819 <row>
1822 <returnvalue></returnvalue> same type as input
1823 </para>
1824 <para>
1826 and returns the argument.
1827 </para>
1828 <para>
1831 </para></entry>
1832 </row>
1834 <row>
1838 </para>
1839 <para>
1840 Casts to double.
1841 </para>
1842 <para>
1845 </para></entry>
1846 </row>
1848 <row>
1852 </para>
1853 <para>
1855 </para>
1856 <para>
1859 </para></entry>
1860 </row>
1862 <row>
1864 <function>greatest</function> ( <replaceable>number</replaceable> <optional>, <literal>...</literal> </optional> )
1865 <returnvalue></returnvalue> <type>double</type> if any argument is double, else <type>integer</type>
1866 </para>
1867 <para>
1868 Selects the largest value among the arguments.
1869 </para>
1870 <para>
1873 </para></entry>
1874 </row>
1876 <row>
1878 <function>hash</function> ( <parameter>value</parameter> <optional>, <parameter>seed</parameter> </optional> )
1880 </para>
1881 <para>
1883 </para>
1884 <para>
1887 </para></entry>
1888 </row>
1890 <row>
1892 <function>hash_fnv1a</function> ( <parameter>value</parameter> <optional>, <parameter>seed</parameter> </optional> )
1894 </para>
1895 <para>
1896 Computes <ulink url="https://en.wikipedia.org/wiki/Fowler%E2%80%93Noll%E2%80%93Vo_hash_function">FNV-1a hash</ulink>.
1897 </para>
1898 <para>
1901 </para></entry>
1902 </row>
1904 <row>
1906 <function>hash_murmur2</function> ( <parameter>value</parameter> <optional>, <parameter>seed</parameter> </optional> )
1908 </para>
1909 <para>
1911 </para>
1912 <para>
1915 </para></entry>
1916 </row>
1918 <row>
1922 </para>
1923 <para>
1924 Casts to integer.
1925 </para>
1926 <para>
1929 </para></entry>
1930 </row>
1932 <row>
1934 <function>least</function> ( <replaceable>number</replaceable> <optional>, <literal>...</literal> </optional> )
1935 <returnvalue></returnvalue> <type>double</type> if any argument is double, else <type>integer</type>
1936 </para>
1937 <para>
1938 Selects the smallest value among the arguments.
1939 </para>
1940 <para>
1943 </para></entry>
1944 </row>
1946 <row>
1950 </para>
1951 <para>
1952 Natural logarithm
1953 </para>
1954 <para>
1957 </para></entry>
1958 </row>
1960 <row>
1962 <function>mod</function> ( <replaceable>integer</replaceable>, <replaceable>integer</replaceable> )
1964 </para>
1965 <para>
1966 Modulo (remainder)
1967 </para>
1968 <para>
1971 </para></entry>
1972 </row>
1974 <row>
1976 <function>permute</function> ( <parameter>i</parameter>, <parameter>size</parameter> [, <parameter>seed</parameter> ] )
1978 </para>
1979 <para>
1985 </para>
1986 <para>
1989 </para></entry>
1990 </row>
1992 <row>
1996 </para>
1997 <para>
1999 </para>
2000 <para>
2003 </para></entry>
2004 </row>
2006 <row>
2010 </para>
2014 </para>
2015 <para>
2017 </para>
2018 <para>
2021 </para></entry>
2022 </row>
2024 <row>
2028 </para>
2029 <para>
2030 Computes a uniformly-distributed random integer in <literal>[lb,
2031 ub]</literal>.
2032 </para>
2033 <para>
2036 </para></entry>
2037 </row>
2039 <row>
2041 <function>random_exponential</function> ( <parameter>lb</parameter>, <parameter>ub</parameter>, <parameter>parameter</parameter> )
2043 </para>
2044 <para>
2045 Computes an exponentially-distributed random integer in <literal>[lb,
2046 ub]</literal>, see below.
2047 </para>
2048 <para>
2051 </para></entry>
2052 </row>
2054 <row>
2056 <function>random_gaussian</function> ( <parameter>lb</parameter>, <parameter>ub</parameter>, <parameter>parameter</parameter> )
2058 </para>
2059 <para>
2060 Computes a Gaussian-distributed random integer in <literal>[lb,
2061 ub]</literal>, see below.
2062 </para>
2063 <para>
2066 </para></entry>
2067 </row>
2069 <row>
2071 <function>random_zipfian</function> ( <parameter>lb</parameter>, <parameter>ub</parameter>, <parameter>parameter</parameter> )
2073 </para>
2074 <para>
2075 Computes a Zipfian-distributed random integer in <literal>[lb,
2076 ub]</literal>, see below.
2077 </para>
2078 <para>
2081 </para></entry>
2082 </row>
2084 <row>
2088 </para>
2089 <para>
2090 Square root
2091 </para>
2092 <para>
2095 </para></entry>
2096 </row>
2097 </tbody>
2098 </tgroup>
2099 </table>
2101 <para>
2103 distribution, that is all the values are drawn within the specified
2106 functions require an additional double parameter which determines the precise
2107 shape of the distribution.
2108 </para>
2110 <itemizedlist>
2111 <listitem>
2112 <para>
2114 controls the distribution by truncating a quickly-decreasing
2116 projecting onto integers between the bounds.
2117 To be precise, with
2118 <literallayout>
2120 </literallayout>
2124 </para>
2126 <para>
2131 uniform) the access distribution.
2132 A crude approximation of the distribution is that the most frequent 1%
2136 </para>
2137 </listitem>
2139 <listitem>
2140 <para>
2141 For a Gaussian distribution, the interval is mapped onto a standard
2142 normal distribution (the classical bell-shaped Gaussian curve) truncated
2144 on the right.
2145 Values in the middle of the interval are more likely to be drawn.
2149 <literallayout>
2152 </literallayout>
2157 frequently values close to the middle of the interval are drawn, and the
2169 value is 2.0.
2170 </para>
2171 </listitem>
2172 <listitem>
2173 <para>
2175 distribution.
2178 frequently values closer to the beginning of the interval are drawn.
2179 The distribution is such that, assuming the range starts from 1,
2182 <literal>((<replaceable>k</replaceable>+1)/<replaceable>k</replaceable>)**<replaceable>parameter</replaceable></literal>.
2188 </para>
2189 <para>
2192 Springer 1986. Due to limitations of that algorithm,
2195 </para>
2196 </listitem>
2197 </itemizedlist>
2199 <note>
2200 <para>
2201 When designing a benchmark which selects rows non-uniformly, be aware
2202 that the rows chosen may be correlated with other data such as IDs from
2203 a sequence or the physical row ordering, which may skew performance
2204 measurements.
2205 </para>
2206 <para>
2208 function, or some other additional step with similar effect, to shuffle
2209 the selected rows and remove such correlations.
2210 </para>
2211 </note>
2213 <para>
2217 is used, which is initialized randomly unless set by the command-line
2219 </para>
2221 <para>
2223 seed parameter. It generates a pseudorandom permutation of integers in
2225 value in the permuted values. The permutation chosen is parameterized by
2228 that there are no collisions or holes in the output values. Input values
2229 outside the interval are interpreted modulo the size. The function raises
2231 used to scatter the distribution of non-uniform random functions such as
2233 so that values drawn more often are not trivially correlated. For
2235 simulates a possible real world workload typical for social media and
2236 blogging platforms where a few accounts generate excessive load:
2238 <programlisting>
2239 \set size 1000000
2241 \set k 1 + permute(:r, :size)
2242 </programlisting>
2244 In some cases several distinct distributions are needed which don't correlate
2245 with each other and this is when the optional seed parameter comes in handy:
2247 <programlisting>
2250 </programlisting>
2254 <programlisting>
2255 \set size 1000000
2257 \set k 1 + abs(hash(:r)) % :size
2258 </programlisting>
2261 will not be reachable and others will be more frequent than expected from
2262 the original distribution.
2263 </para>
2265 <para>
2266 As an example, the full definition of the built-in TPC-B-like
2267 transaction is:
2269 <programlisting>
2274 BEGIN;
2275 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
2276 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
2277 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
2278 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
2279 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
2280 END;
2281 </programlisting>
2283 This script allows each iteration of the transaction to reference
2284 different, randomly-chosen rows. (This example also shows why it's
2285 important for each client session to have its own variables —
2286 otherwise they'd not be independently touching different rows.)
2287 </para>
2289 </refsect2>
2291 <refsect2>
2294 <para>
2298 to a log file. The log file will be named
2305 worker threads, each will have its own log file. The first worker will
2306 use the same name for its log file as in the standard single worker case.
2307 The additional log files for the other workers will be named
2308 <filename><replaceable>prefix</replaceable>.<replaceable>nnn</replaceable>.<replaceable>mmm</replaceable></filename>,
2310 with 1.
2311 </para>
2313 <para>
2314 Each line in a log file describes one transaction.
2315 It contains the following space-separated fields:
2317 <variablelist>
2318 <varlistentry>
2320 <listitem>
2321 <para>
2322 identifies the client session that ran the transaction
2323 </para>
2324 </listitem>
2325 </varlistentry>
2327 <varlistentry>
2329 <listitem>
2330 <para>
2331 counts how many transactions have been run by that session
2332 </para>
2333 </listitem>
2334 </varlistentry>
2336 <varlistentry>
2338 <listitem>
2339 <para>
2340 transaction's elapsed time, in microseconds
2341 </para>
2342 </listitem>
2343 </varlistentry>
2345 <varlistentry>
2347 <listitem>
2348 <para>
2349 identifies the script file that was used for the transaction
2350 (useful when multiple scripts are specified
2352 </para>
2353 </listitem>
2354 </varlistentry>
2356 <varlistentry>
2358 <listitem>
2359 <para>
2360 transaction's completion time, as a Unix-epoch time stamp
2361 </para>
2362 </listitem>
2363 </varlistentry>
2365 <varlistentry>
2367 <listitem>
2368 <para>
2369 fractional-second part of transaction's completion time, in
2370 microseconds
2371 </para>
2372 </listitem>
2373 </varlistentry>
2375 <varlistentry>
2377 <listitem>
2378 <para>
2379 transaction start delay, that is the difference between the
2380 transaction's scheduled start time and the time it actually
2381 started, in microseconds
2383 </para>
2384 </listitem>
2385 </varlistentry>
2387 <varlistentry>
2389 <listitem>
2390 <para>
2391 count of retries after serialization or deadlock errors during the
2392 transaction
2394 </para>
2395 </listitem>
2396 </varlistentry>
2397 </variablelist>
2398 </para>
2400 <para>
2411 </para>
2413 <para>
2414 Here is a snippet of a log file generated in a single-client run:
2415 <screen>
2416 0 199 2241 0 1175850568 995598
2417 0 200 2465 0 1175850568 998079
2418 0 201 2513 0 1175850569 608
2419 0 202 2038 0 1175850569 2663
2420 </screen>
2425 <screen>
2426 0 81 4621 0 1412881037 912698 3005
2427 0 82 6173 0 1412881037 914578 4304
2430 0 83 4722 0 1412881037 916203 3108
2431 0 84 4142 0 1412881037 918023 2333
2432 0 85 2465 0 1412881037 919759 740
2433 </screen>
2435 over the 5 ms limit. The next two transactions were skipped, because they
2436 were already late before they were even started.
2437 </para>
2439 <para>
2440 The following example shows a snippet of a log file with failures and
2441 retries, with the maximum number of tries set to 10 (note the additional
2443 <screen>
2444 3 0 47423 0 1499414498 34501 3
2445 3 1 8333 0 1499414498 42848 0
2446 3 2 8358 0 1499414498 51219 0
2447 4 0 72345 0 1499414498 59433 6
2448 1 3 41718 0 1499414498 67879 4
2449 1 4 8416 0 1499414498 76311 0
2450 3 3 33235 0 1499414498 84469 3
2453 3 4 8307 0 1499414498 92788 0
2454 </screen>
2455 </para>
2457 <para>
2460 <screen>
2461 3 0 47423 0 1499414498 34501 3
2462 3 1 8333 0 1499414498 42848 0
2463 3 2 8358 0 1499414498 51219 0
2464 4 0 72345 0 1499414498 59433 6
2465 1 3 41718 0 1499414498 67879 4
2466 1 4 8416 0 1499414498 76311 0
2467 3 3 33235 0 1499414498 84469 3
2470 3 4 8307 0 1499414498 92788 0
2471 </screen>
2472 </para>
2474 <para>
2475 When running a long test on hardware that can handle a lot of transactions,
2477 can be used to log only a random sample of transactions.
2478 </para>
2479 </refsect2>
2481 <refsect2>
2484 <para>
2486 format is used for the log files. Each log line describes one
2487 aggregation interval. It contains the following space-separated
2488 fields:
2490 <variablelist>
2491 <varlistentry>
2493 <listitem>
2494 <para>
2495 start time of the interval, as a Unix-epoch time stamp
2496 </para>
2497 </listitem>
2498 </varlistentry>
2500 <varlistentry>
2502 <listitem>
2503 <para>
2504 number of transactions within the interval
2505 </para>
2506 </listitem>
2507 </varlistentry>
2509 <varlistentry>
2511 <listitem>
2512 <para>
2513 sum of transaction latencies
2514 </para>
2515 </listitem>
2516 </varlistentry>
2518 <varlistentry>
2520 <listitem>
2521 <para>
2522 sum of squares of transaction latencies
2523 </para>
2524 </listitem>
2525 </varlistentry>
2527 <varlistentry>
2529 <listitem>
2530 <para>
2531 minimum transaction latency
2532 </para>
2533 </listitem>
2534 </varlistentry>
2536 <varlistentry>
2538 <listitem>
2539 <para>
2540 maximum transaction latency
2541 </para>
2542 </listitem>
2543 </varlistentry>
2545 <varlistentry>
2547 <listitem>
2548 <para>
2549 sum of transaction start delays
2551 </para>
2552 </listitem>
2553 </varlistentry>
2555 <varlistentry>
2557 <listitem>
2558 <para>
2559 sum of squares of transaction start delays
2561 </para>
2562 </listitem>
2563 </varlistentry>
2565 <varlistentry>
2567 <listitem>
2568 <para>
2569 minimum transaction start delay
2571 </para>
2572 </listitem>
2573 </varlistentry>
2575 <varlistentry>
2577 <listitem>
2578 <para>
2579 maximum transaction start delay
2581 </para>
2582 </listitem>
2583 </varlistentry>
2585 <varlistentry>
2587 <listitem>
2588 <para>
2589 number of transactions skipped because they would have started too late
2592 </para>
2593 </listitem>
2594 </varlistentry>
2596 <varlistentry>
2598 <listitem>
2599 <para>
2600 number of retried transactions
2602 </para>
2603 </listitem>
2604 </varlistentry>
2606 <varlistentry>
2608 <listitem>
2609 <para>
2610 number of retries after serialization or deadlock errors
2612 </para>
2613 </listitem>
2614 </varlistentry>
2616 <varlistentry>
2618 <listitem>
2619 <para>
2620 number of transactions that got a serialization error and were not
2621 retried afterwards
2623 </para>
2624 </listitem>
2625 </varlistentry>
2627 <varlistentry>
2629 <listitem>
2630 <para>
2631 number of transactions that got a deadlock error and were not
2632 retried afterwards
2634 </para>
2635 </listitem>
2636 </varlistentry>
2637 </variablelist>
2638 </para>
2640 <para>
2641 Here is some example output generated with this option:
2642 <screen>
2643 <userinput>pgbench --aggregate-interval=10 --time=20 --client=10 --log --rate=1000 --latency-limit=10 --failures-detailed --max-tries=10 test</userinput>
2645 1650260552 5178 26171317 177284491527 1136 44462 2647617 7321113867 0 9866 64 7564 28340 4148 0
2646 1650260562 4808 25573984 220121792172 1171 62083 3037380 9666800914 0 9998 598 7392 26621 4527 0
2647 </screen>
2648 </para>
2650 <para>
2651 Notice that while the plain (unaggregated) log format shows which script
2652 was used for each transaction, the aggregated format does not. Therefore if
2653 you need per-script data, you need to aggregate the data on your own.
2654 </para>
2656 </refsect2>
2658 <refsect2>
2661 <para>
2663 collects the following statistics for each statement:
2664 <itemizedlist>
2665 <listitem>
2666 <para>
2669 of all successful runs of the statement.
2670 </para>
2671 </listitem>
2672 <listitem>
2673 <para>
2674 The number of failures in this statement. See
2676 </para>
2677 </listitem>
2678 <listitem>
2679 <para>
2680 The number of retries after a serialization or a deadlock error in this
2682 </para>
2683 </listitem>
2684 </itemizedlist>
2685 </para>
2687 <para>
2689 option is not equal to 1.
2690 </para>
2692 <para>
2693 All values are computed for each statement executed by every client and are
2694 reported after the benchmark has finished.
2695 </para>
2697 <para>
2698 For the default script, the output will look similar to this:
2699 <screen>
2700 starting vacuum...end.
2702 scaling factor: 1
2703 query mode: simple
2704 number of clients: 10
2705 number of threads: 1
2706 maximum number of tries: 1
2707 number of transactions per client: 1000
2711 latency average = 28.488 ms
2712 latency stddev = 21.009 ms
2713 initial connection time = 69.068 ms
2714 tps = 346.224794 (without initial connection time)
2715 statement latencies in milliseconds and failures:
2720 0.319 0 BEGIN;
2721 0.834 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
2722 0.641 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
2723 11.126 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
2724 12.961 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
2725 0.634 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
2726 1.957 0 END;
2727 </screen>
2729 Another example of output for the default script using serializable default
2730 transaction isolation level (<command>PGOPTIONS='-c
2731 default_transaction_isolation=serializable' pgbench ...</command>):
2732 <screen>
2733 starting vacuum...end.
2735 scaling factor: 1
2736 query mode: simple
2737 number of clients: 10
2738 number of threads: 1
2739 maximum number of tries: 10
2740 number of transactions per client: 1000
2744 total number of retries: 45339
2746 latency average = 17.016 ms
2747 latency stddev = 13.283 ms
2748 initial connection time = 45.017 ms
2749 tps = 186.792667 (without initial connection time)
2750 statement latencies in milliseconds, failures and retries:
2755 0.385 0 0 BEGIN;
2756 0.773 0 1 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
2757 0.624 0 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
2758 1.098 320 3762 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
2759 0.582 3363 41576 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
2760 0.465 0 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
2761 1.933 0 0 END;
2762 </screen></para>
2764 <para>
2765 If multiple script files are specified, all statistics are reported
2766 separately for each script file.
2767 </para>
2769 <para>
2770 Note that collecting the additional timing information needed for
2771 per-statement latency computation adds some overhead. This will slow
2772 average execution speed and lower the computed TPS. The amount
2773 of slowdown varies significantly depending on platform and hardware.
2774 Comparing average TPS values with and without latency reporting enabled
2775 is a good way to measure if the timing overhead is significant.
2776 </para>
2777 </refsect2>
2782 <para>
2784 of errors:
2785 <itemizedlist>
2786 <listitem>
2787 <para>
2788 Errors of the main program. They are the most serious and always result
2790 corresponding error message. They include:
2791 <itemizedlist>
2792 <listitem>
2793 <para>
2795 (e.g. an invalid option value);
2796 </para>
2797 </listitem>
2798 <listitem>
2799 <para>
2800 errors in the initialization mode (e.g. the query to create
2801 tables for built-in scripts fails);
2802 </para>
2803 </listitem>
2804 <listitem>
2805 <para>
2806 errors before starting threads (e.g. could not connect to the
2807 database server, syntax error in the meta command, thread
2808 creation failure);
2809 </para>
2810 </listitem>
2811 <listitem>
2812 <para>
2814 supposed to never occur...).
2815 </para>
2816 </listitem>
2817 </itemizedlist></para>
2818 </listitem>
2819 <listitem>
2820 <para>
2821 Errors when the thread manages its clients (e.g. the client could not
2822 start a connection to the database server / the socket for connecting
2823 the client to the database server has become invalid). In such cases
2824 all clients of this thread stop while other threads continue to work.
2826 threads stop immediately in this case.
2827 </para>
2828 </listitem>
2829 <listitem>
2830 <para>
2831 Direct client errors. They lead to immediate exit from
2834 error (which are supposed to never occur...) or when
2836 case they only lead to the abortion of the failed client while other
2837 clients continue their run (but some client errors are handled without
2838 an abortion of the client and reported separately, see below). Later in
2839 this section it is assumed that the discussed errors are only the
2840 direct client errors and they are not internal
2842 </para>
2843 </listitem>
2844 </itemizedlist>
2845 </para>
2847 <para>
2848 A client's run is aborted in case of a serious error; for example, the
2849 connection with the database server was lost or the end of script was reached
2850 without completing the last transaction. In addition, if execution of an SQL
2851 or meta command fails for reasons other than serialization or deadlock errors,
2852 the client is aborted. Otherwise, if an SQL command fails with serialization or
2853 deadlock errors, the client is not aborted. In such cases, the current
2854 transaction is rolled back, which also includes setting the client variables
2855 as they were before the run of this transaction (it is assumed that one
2856 transaction script contains only one transaction; see
2858 Transactions with serialization or deadlock errors are repeated after
2859 rollbacks until they complete successfully or reach the maximum
2863 the last trial run fails, this transaction will be reported as failed but
2864 the client is not aborted and continues to work.
2865 </para>
2867 <note>
2868 <para>
2870 never be retried after a serialization or deadlock error because its default
2874 benchmark duration under an unlimited number of tries.
2875 </para>
2876 <para>
2877 Be careful when repeating scripts that contain multiple transactions: the
2878 script is always retried completely, so successful transactions can be
2879 performed several times.
2880 </para>
2881 <para>
2882 Be careful when repeating transactions with shell commands. Unlike the
2883 results of SQL commands, the results of shell commands are not rolled back,
2885 </para>
2886 </note>
2888 <para>
2889 The latency of a successful transaction includes the entire time of
2890 transaction execution with rollbacks and retries. The latency is measured
2891 only for successful transactions and commands but not for failed transactions
2892 or commands.
2893 </para>
2895 <para>
2896 The main report contains the number of failed transactions. If the
2898 contains statistics related to retries: the total number of retried
2899 transactions and total number of retries. The per-script report inherits all
2900 these fields from the main report. The per-statement report displays retry
2902 </para>
2904 <para>
2905 If you want to group failures by basic types in per-transaction and
2906 aggregation logs, as well as in the main and per-script reports, use the
2908 all errors and failures (errors without retrying) by type including which
2909 limit for retries was exceeded and how much it was exceeded by for the
2911 option.
2912 </para>
2913 </refsect2>
2915 <refsect2>
2918 <para>
2921 specifies database configuration options that are passed to PostgreSQL via
2923 For example, a hypothetical default Table Access Method for the tables that
2925 <programlisting>
2926 PGOPTIONS='-c default_table_access_method=wuzza'
2927 </programlisting>
2928 </para>
2929 </refsect2>
2931 <refsect2>
2934 <para>
2936 meaningless numbers. Here are some guidelines to help you get useful
2937 results.
2938 </para>
2940 <para>
2943 to make the run last at least a few minutes, so as to average out noise.
2944 In some cases you could need hours to get numbers that are reproducible.
2945 It's a good idea to try the test run a few times, to find out if your
2946 numbers are reproducible or not.
2947 </para>
2949 <para>
2950 For the default TPC-B-like test scenario, the initialization scale factor
2956 will undoubtedly result in lots of transactions blocked waiting for
2957 other transactions.
2958 </para>
2960 <para>
2961 The default test scenario is also quite sensitive to how long it's been
2962 since the tables were initialized: accumulation of dead rows and dead space
2963 in the tables changes the results. To understand the results you must keep
2964 track of the total number of updates and when vacuuming happens. If
2965 autovacuum is enabled it can result in unpredictable changes in measured
2966 performance.
2967 </para>
2969 <para>
2971 the bottleneck when trying to test a large number of client sessions.
2973 machine from the database server, although low network latency will be
2975 instances concurrently, on several client machines, against the same
2976 database server.
2977 </para>
2978 </refsect2>
2979 <refsect2>
2982 <para>
2983 If untrusted users have access to a database that has not adopted a
2987 does not manipulate the search path.
2988 </para>
2989 </refsect2>
2990 </refsect1>
2991 </refentry>