1 <!-- doc/src/sgml/pgbench.sgml -->
3 <refentry id="pgbench">
5 <refentrytitle><application>pgbench</application></refentrytitle>
6 <manvolnum>1</manvolnum>
7 <refmiscinfo>Application</refmiscinfo>
11 <refname>pgbench</refname>
12 <refpurpose>run a benchmark test on <productname>PostgreSQL</productname></refpurpose>
15 <indexterm zone="pgbench">
16 <primary>pgbench</primary>
21 <command>pgbench</command>
22 <arg choice="plain"><option>-i</option></arg>
23 <arg rep="repeat"><replaceable>option</replaceable></arg>
24 <arg choice="opt"><replaceable>dbname</replaceable></arg>
27 <command>pgbench</command>
28 <arg rep="repeat"><replaceable>option</replaceable></arg>
29 <arg choice="opt"><replaceable>dbname</replaceable></arg>
34 <title>Description</title>
36 <application>pgbench</application> is a simple program for running benchmark
37 tests on <productname>PostgreSQL</>. It runs the same sequence of SQL
38 commands over and over, possibly in multiple concurrent database sessions,
39 and then calculates the average transaction rate (transactions per second).
40 By default, <application>pgbench</application> tests a scenario that is
41 loosely based on TPC-B, involving five <command>SELECT</>,
42 <command>UPDATE</>, and <command>INSERT</> commands per transaction.
43 However, it is easy to test other cases by writing your own transaction
48 Typical output from pgbench looks like:
51 transaction type: TPC-B (sort of)
56 number of transactions per client: 1000
57 number of transactions actually processed: 10000/10000
58 tps = 85.184871 (including connections establishing)
59 tps = 85.296346 (excluding connections establishing)
62 The first six lines report some of the most important parameter
63 settings. The next line reports the number of transactions completed
64 and intended (the latter being just the product of number of clients
65 and number of transactions per client); these will be equal unless the run
66 failed before completion. (In <option>-T</> mode, only the actual
67 number of transactions is printed.)
68 The last two lines report the number of transactions per second,
69 figured with and without counting the time to start database sessions.
73 The default TPC-B-like transaction test requires specific tables to be
74 set up beforehand. <application>pgbench</> should be invoked with
75 the <option>-i</> (initialize) option to create and populate these
76 tables. (When you are testing a custom script, you don't need this
77 step, but will instead need to do whatever setup your test needs.)
78 Initialization looks like:
81 pgbench -i <optional> <replaceable>other-options</> </optional> <replaceable>dbname</>
84 where <replaceable>dbname</> is the name of the already-created
85 database to test in. (You may also need <option>-h</>,
86 <option>-p</>, and/or <option>-U</> options to specify how to
87 connect to the database server.)
92 <literal>pgbench -i</> creates four tables <structname>pgbench_accounts</>,
93 <structname>pgbench_branches</>, <structname>pgbench_history</>, and
94 <structname>pgbench_tellers</>,
95 destroying any existing tables of these names.
96 Be very careful to use another database if you have tables having these
102 At the default <quote>scale factor</> of 1, the tables initially
103 contain this many rows:
106 ---------------------------------
109 pgbench_accounts 100000
112 You can (and, for most purposes, probably should) increase the number
113 of rows by using the <option>-s</> (scale factor) option. The
114 <option>-F</> (fillfactor) option might also be used at this point.
118 Once you have done the necessary setup, you can run your benchmark
119 with a command that doesn't include <option>-i</>, that is
122 pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
125 In nearly all cases, you'll need some options to make a useful test.
126 The most important options are <option>-c</> (number of clients),
127 <option>-t</> (number of transactions), <option>-T</> (time limit),
128 and <option>-f</> (specify a custom script file).
129 See below for a full list.
134 <title>Options</title>
137 The following is divided into three subsections: Different options are used
138 during database initialization and while running benchmarks, some options
139 are useful in both cases.
142 <refsect2 id="pgbench-init-options">
143 <title>Initialization Options</title>
146 <application>pgbench</application> accepts the following command-line
147 initialization arguments:
152 <term><option>-i</option></term>
155 Required to invoke initialization mode.
161 <term><option>-n</option></term>
164 Perform no vacuuming after initialization.
170 <term><option>-F</option> <replaceable>fillfactor</></term>
173 Create the <structname>pgbench_accounts</>,
174 <structname>pgbench_tellers</> and
175 <structname>pgbench_branches</> tables with the given fillfactor.
182 <term><option>-s</option> <replaceable>scale_factor</></term>
185 Multiply the number of rows generated by the scale factor.
186 For example, <literal>-s 100</> will create 10,000,000 rows
187 in the <structname>pgbench_accounts</> table. Default is 1.
188 When the scale is 20,000 or larger, the columns used to
189 hold account identifiers (<structfield>aid</structfield> columns)
190 will switch to using larger integers (<type>bigint</type>),
191 in order to be big enough to hold the range of account
198 <term><option>-q</option></term>
201 Switch logging to quiet mode, producing only one progress message per 5
202 seconds. The default logging prints one message each 100000 rows, which
203 often outputs many lines per second (especially on good hardware).
209 <term><option>--foreign-keys</option></term>
212 Create foreign key constraints between the standard tables.
218 <term><option>--index-tablespace=<replaceable>index_tablespace</replaceable></option></term>
221 Create indexes in the specified tablespace, rather than the default
228 <term><option>--tablespace=<replaceable>tablespace</replaceable></option></term>
231 Create tables in the specified tablespace, rather than the default
238 <term><option>--unlogged-tables</option></term>
241 Create all tables as unlogged tables, rather than permanent tables.
251 <refsect2 id="pgbench-run-options">
252 <title>Benchmarking Options</title>
255 <application>pgbench</application> accepts the following command-line
256 benchmarking arguments:
261 <term><option>-c</option> <replaceable>clients</></term>
264 Number of clients simulated, that is, number of concurrent database
265 sessions. Default is 1.
271 <term><option>-C</option></term>
274 Establish a new connection for each transaction, rather than
275 doing it just once per client session.
276 This is useful to measure the connection overhead.
282 <term><option>-d</option></term>
285 Print debugging output.
291 <term><option>-D</option> <replaceable>varname</><literal>=</><replaceable>value</></term>
294 Define a variable for use by a custom script (see below).
295 Multiple <option>-D</> options are allowed.
301 <term><option>-f</option> <replaceable>filename</></term>
304 Read transaction script from <replaceable>filename</>.
305 See below for details.
306 <option>-N</option>, <option>-S</option>, and <option>-f</option>
307 are mutually exclusive.
313 <term><option>-j</option> <replaceable>threads</></term>
316 Number of worker threads within <application>pgbench</application>.
317 Using more than one thread can be helpful on multi-CPU machines.
318 The number of clients must be a multiple of the number of threads,
319 since each thread is given the same number of client sessions to manage.
326 <term><option>-l</option></term>
329 Write the time taken by each transaction to a log file.
330 See below for details.
336 <term><option>--sampling-rate</option> <replaceable>rate</></term>
339 Sampling rate, used when writing data into the log, to reduce the
340 amount of log generated. If this option is given, only the specified
341 fraction of transactions are logged. 1.0 means all transactions will
342 be logged, 0.05 means only 5% of the transactions will be logged.
345 Remember to take the sampling rate into account when processing the
346 log file. For example, when computing tps values, you need to multiply
347 the numbers accordingly (e.g. with 0.01 sample rate, you'll only get
348 1/100 of the actual tps).
354 <term><option>-M</option> <replaceable>querymode</></term>
357 Protocol to use for submitting queries to the server:
360 <para><literal>simple</>: use simple query protocol.</para>
363 <para><literal>extended</>: use extended query protocol.</para>
366 <para><literal>prepared</>: use extended query protocol with prepared statements.</para>
369 The default is simple query protocol. (See <xref linkend="protocol">
370 for more information.)
376 <term><option>-n</option></term>
379 Perform no vacuuming before running the test.
380 This option is <emphasis>necessary</>
381 if you are running a custom test scenario that does not include
382 the standard tables <structname>pgbench_accounts</>,
383 <structname>pgbench_branches</>, <structname>pgbench_history</>, and
384 <structname>pgbench_tellers</>.
390 <term><option>-N</option></term>
393 Do not update <structname>pgbench_tellers</> and
394 <structname>pgbench_branches</>.
395 This will avoid update contention on these tables, but
396 it makes the test case even less like TPC-B.
402 <term><option>-r</option></term>
405 Report the average per-statement latency (execution time from the
406 perspective of the client) of each command after the benchmark
407 finishes. See below for details.
413 <term><option>-s</option> <replaceable>scale_factor</></term>
416 Report the specified scale factor in <application>pgbench</>'s
417 output. With the built-in tests, this is not necessary; the
418 correct scale factor will be detected by counting the number of
419 rows in the <structname>pgbench_branches</> table. However, when testing
420 custom benchmarks (<option>-f</> option), the scale factor
421 will be reported as 1 unless this option is used.
427 <term><option>-S</option></term>
430 Perform select-only transactions instead of TPC-B-like test.
436 <term><option>-t</option> <replaceable>transactions</></term>
439 Number of transactions each client runs. Default is 10.
445 <term><option>-T</option> <replaceable>seconds</></term>
448 Run the test for this many seconds, rather than a fixed number of
449 transactions per client. <option>-t</option> and
450 <option>-T</option> are mutually exclusive.
456 <term><option>-v</option></term>
459 Vacuum all four standard tables before running the test.
460 With neither <option>-n</> nor <option>-v</>, pgbench will vacuum the
461 <structname>pgbench_tellers</> and <structname>pgbench_branches</>
462 tables, and will truncate <structname>pgbench_history</>.
472 <refsect2 id="pgbench-common-options">
473 <title>Common Options</title>
476 <application>pgbench</application> accepts the following command-line
482 <term><option>-h</option> <replaceable>hostname</></term>
485 The database server's host name
491 <term><option>-p</option> <replaceable>port</></term>
494 The database server's port number
500 <term><option>-U</option> <replaceable>login</></term>
503 The user name to connect as
509 <term><option>-V</></term>
510 <term><option>--version</></term>
513 Print the <application>pgbench</application> version and exit.
519 <term><option>-?</></term>
520 <term><option>--help</></term>
523 Show help about <application>pgbench</application> command line
538 <title>What is the <quote>Transaction</> Actually Performed in pgbench?</title>
541 The default transaction script issues seven commands per transaction:
545 <listitem><para><literal>BEGIN;</literal></para></listitem>
546 <listitem><para><literal>UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;</literal></para></listitem>
547 <listitem><para><literal>SELECT abalance FROM pgbench_accounts WHERE aid = :aid;</literal></para></listitem>
548 <listitem><para><literal>UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;</literal></para></listitem>
549 <listitem><para><literal>UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;</literal></para></listitem>
550 <listitem><para><literal>INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);</literal></para></listitem>
551 <listitem><para><literal>END;</literal></para></listitem>
555 If you specify <option>-N</>, steps 4 and 5 aren't included in the
556 transaction. If you specify <option>-S</>, only the <command>SELECT</> is
562 <title>Custom Scripts</title>
565 <application>pgbench</application> has support for running custom
566 benchmark scenarios by replacing the default transaction script
567 (described above) with a transaction script read from a file
568 (<option>-f</option> option). In this case a <quote>transaction</>
569 counts as one execution of a script file. You can even specify
570 multiple scripts (multiple <option>-f</option> options), in which
571 case a random one of the scripts is chosen each time a client session
572 starts a new transaction.
576 The format of a script file is one SQL command per line; multiline
577 SQL commands are not supported. Empty lines and lines beginning with
578 <literal>--</> are ignored. Script file lines can also be
579 <quote>meta commands</>, which are interpreted by <application>pgbench</>
580 itself, as described below.
584 There is a simple variable-substitution facility for script files.
585 Variables can be set by the command-line <option>-D</> option,
586 explained above, or by the meta commands explained below.
587 In addition to any variables preset by <option>-D</> command-line options,
588 the variable <literal>scale</> is preset to the current scale factor.
589 Once set, a variable's
590 value can be inserted into a SQL command by writing
591 <literal>:</><replaceable>variablename</>. When running more than
592 one client session, each session has its own set of variables.
596 Script file meta commands begin with a backslash (<literal>\</>).
597 Arguments to a meta command are separated by white space.
598 These meta commands are supported:
604 <literal>\set <replaceable>varname</> <replaceable>operand1</> [ <replaceable>operator</> <replaceable>operand2</> ]</literal>
609 Sets variable <replaceable>varname</> to a calculated integer value.
610 Each <replaceable>operand</> is either an integer constant or a
611 <literal>:</><replaceable>variablename</> reference to a variable
612 having an integer value. The <replaceable>operator</> can be
613 <literal>+</>, <literal>-</>, <literal>*</>, or <literal>/</>.
619 \set ntellers 10 * :scale
620 </programlisting></para>
626 <literal>\setrandom <replaceable>varname</> <replaceable>min</> <replaceable>max</></literal>
631 Sets variable <replaceable>varname</> to a random integer value
632 between the limits <replaceable>min</> and <replaceable>max</> inclusive.
633 Each limit can be either an integer constant or a
634 <literal>:</><replaceable>variablename</> reference to a variable
635 having an integer value.
641 \setrandom aid 1 :naccounts
642 </programlisting></para>
648 <literal>\sleep <replaceable>number</> [ us | ms | s ]</literal>
653 Causes script execution to sleep for the specified duration in
654 microseconds (<literal>us</>), milliseconds (<literal>ms</>) or seconds
655 (<literal>s</>). If the unit is omitted then seconds are the default.
656 <replaceable>number</> can be either an integer constant or a
657 <literal>:</><replaceable>variablename</> reference to a variable
658 having an integer value.
665 </programlisting></para>
671 <literal>\setshell <replaceable>varname</> <replaceable>command</> [ <replaceable>argument</> ... ]</literal>
676 Sets variable <replaceable>varname</> to the result of the shell command
677 <replaceable>command</>. The command must return an integer value
678 through its standard output.
682 <replaceable>argument</> can be either a text constant or a
683 <literal>:</><replaceable>variablename</> reference to a variable of
684 any types. If you want to use <replaceable>argument</> starting with
685 colons, you need to add an additional colon at the beginning of
686 <replaceable>argument</>.
692 \setshell variable_to_be_assigned command literal_argument :variable ::literal_starting_with_colon
693 </programlisting></para>
699 <literal>\shell <replaceable>command</> [ <replaceable>argument</> ... ]</literal>
704 Same as <literal>\setshell</literal>, but the result is ignored.
710 \shell command literal_argument :variable ::literal_starting_with_colon
711 </programlisting></para>
717 As an example, the full definition of the built-in TPC-B-like
721 \set nbranches :scale
722 \set ntellers 10 * :scale
723 \set naccounts 100000 * :scale
724 \setrandom aid 1 :naccounts
725 \setrandom bid 1 :nbranches
726 \setrandom tid 1 :ntellers
727 \setrandom delta -5000 5000
729 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
730 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
731 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
732 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
733 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
737 This script allows each iteration of the transaction to reference
738 different, randomly-chosen rows. (This example also shows why it's
739 important for each client session to have its own variables —
740 otherwise they'd not be independently touching different rows.)
746 <title>Per-Transaction Logging</title>
749 With the <option>-l</> option, <application>pgbench</> writes the time
750 taken by each transaction to a log file. The log file will be named
751 <filename>pgbench_log.<replaceable>nnn</></filename>, where
752 <replaceable>nnn</> is the PID of the pgbench process.
753 If the <option>-j</> option is 2 or higher, creating multiple worker
754 threads, each will have its own log file. The first worker will use the
755 same name for its log file as in the standard single worker case.
756 The additional log files for the other workers will be named
757 <filename>pgbench_log.<replaceable>nnn</>.<replaceable>mmm</></filename>,
758 where <replaceable>mmm</> is a sequential number for each worker starting
763 The format of the log is:
766 <replaceable>client_id</> <replaceable>transaction_no</> <replaceable>time</> <replaceable>file_no</> <replaceable>time_epoch</> <replaceable>time_us</>
769 where <replaceable>time</> is the total elapsed transaction time in microseconds,
770 <replaceable>file_no</> identifies which script file was used
771 (useful when multiple scripts were specified with <option>-f</>),
772 and <replaceable>time_epoch</>/<replaceable>time_us</> are a
773 UNIX epoch format timestamp and an offset
774 in microseconds (suitable for creating a ISO 8601
775 timestamp with fractional seconds) showing when
776 the transaction completed.
780 Here are example outputs:
782 0 199 2241 0 1175850568 995598
783 0 200 2465 0 1175850568 998079
784 0 201 2513 0 1175850569 608
785 0 202 2038 0 1175850569 2663
789 When running a long test on hardware that can handle a lot of transactions,
790 the log files can become very large. The <option>--sampling-rate</> option
791 can be used to log only a random sample of transactions.
796 <title>Per-Statement Latencies</title>
799 With the <option>-r</> option, <application>pgbench</> collects
800 the elapsed transaction time of each statement executed by every
801 client. It then reports an average of those values, referred to
802 as the latency for each statement, after the benchmark has finished.
806 For the default script, the output will look similar to this:
808 starting vacuum...end.
809 transaction type: TPC-B (sort of)
812 number of clients: 10
814 number of transactions per client: 1000
815 number of transactions actually processed: 10000/10000
816 tps = 618.764555 (including connections establishing)
817 tps = 622.977698 (excluding connections establishing)
818 statement latencies in milliseconds:
819 0.004386 \set nbranches 1 * :scale
820 0.001343 \set ntellers 10 * :scale
821 0.001212 \set naccounts 100000 * :scale
822 0.001310 \setrandom aid 1 :naccounts
823 0.001073 \setrandom bid 1 :nbranches
824 0.001005 \setrandom tid 1 :ntellers
825 0.001078 \setrandom delta -5000 5000
827 0.603376 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
828 0.454643 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
829 5.528491 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
830 7.335435 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
831 0.371851 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
837 If multiple script files are specified, the averages are reported
838 separately for each script file.
842 Note that collecting the additional timing information needed for
843 per-statement latency computation adds some overhead. This will slow
844 average execution speed and lower the computed TPS. The amount
845 of slowdown varies significantly depending on platform and hardware.
846 Comparing average TPS values with and without latency reporting enabled
847 is a good way to measure if the timing overhead is significant.
852 <title>Good Practices</title>
855 It is very easy to use <application>pgbench</> to produce completely
856 meaningless numbers. Here are some guidelines to help you get useful
861 In the first place, <emphasis>never</> believe any test that runs
862 for only a few seconds. Use the <option>-t</> or <option>-T</> option
863 to make the run last at least a few minutes, so as to average out noise.
864 In some cases you could need hours to get numbers that are reproducible.
865 It's a good idea to try the test run a few times, to find out if your
866 numbers are reproducible or not.
870 For the default TPC-B-like test scenario, the initialization scale factor
871 (<option>-s</>) should be at least as large as the largest number of
872 clients you intend to test (<option>-c</>); else you'll mostly be
873 measuring update contention. There are only <option>-s</> rows in
874 the <structname>pgbench_branches</> table, and every transaction wants to
875 update one of them, so <option>-c</> values in excess of <option>-s</>
876 will undoubtedly result in lots of transactions blocked waiting for
881 The default test scenario is also quite sensitive to how long it's been
882 since the tables were initialized: accumulation of dead rows and dead space
883 in the tables changes the results. To understand the results you must keep
884 track of the total number of updates and when vacuuming happens. If
885 autovacuum is enabled it can result in unpredictable changes in measured
890 A limitation of <application>pgbench</> is that it can itself become
891 the bottleneck when trying to test a large number of client sessions.
892 This can be alleviated by running <application>pgbench</> on a different
893 machine from the database server, although low network latency will be
894 essential. It might even be useful to run several <application>pgbench</>
895 instances concurrently, on several client machines, against the same