1 <!-- $PostgreSQL: pgsql/doc/src/sgml/pgbench.sgml,v 1.6 2008/03/19 03:33:21 ishii Exp $ -->
6 <indexterm zone="pgbench">
7 <primary>pgbench</primary>
11 <application>pgbench</application> is a simple program for running benchmark
12 tests on <productname>PostgreSQL</>. It runs the same sequence of SQL
13 commands over and over, possibly in multiple concurrent database sessions,
14 and then calculates the average transaction rate (transactions per second).
15 By default, <application>pgbench</application> tests a scenario that is
16 loosely based on TPC-B, involving five <command>SELECT</>,
17 <command>UPDATE</>, and <command>INSERT</> commands per transaction.
18 However, it is easy to test other cases by writing your own transaction
23 Typical output from pgbench looks like:
26 transaction type: TPC-B (sort of)
29 number of transactions per client: 1000
30 number of transactions actually processed: 10000/10000
31 tps = 85.184871 (including connections establishing)
32 tps = 85.296346 (excluding connections establishing)
35 The first four lines just report some of the most important parameter
36 settings. The next line reports the number of transactions completed
37 and intended (the latter being just the product of number of clients
38 and number of transactions); these will be equal unless the run
39 failed before completion. The last two lines report the TPS rate,
40 figured with and without counting the time to start database sessions.
44 <title>Overview</title>
47 The default TPC-B-like transaction test requires specific tables to be
48 set up beforehand. <application>pgbench</> should be invoked with
49 the <literal>-i</> (initialize) option to create and populate these
50 tables. (When you are testing a custom script, you don't need this
51 step, but will instead need to do whatever setup your test needs.)
52 Initialization looks like:
55 pgbench -i <optional> <replaceable>other-options</> </optional> <replaceable>dbname</>
58 where <replaceable>dbname</> is the name of the already-created
59 database to test in. (You may also need <literal>-h</>,
60 <literal>-p</>, and/or <literal>-U</> options to specify how to
61 connect to the database server.)
66 <literal>pgbench -i</> creates four tables <structname>accounts</>,
67 <structname>branches</>, <structname>history</>, and
68 <structname>tellers</>, destroying any existing tables of these names.
69 Be very careful to use another database if you have tables having these
75 At the default <quote>scale factor</> of 1, the tables initially
76 contain this many rows:
80 -------------------------
87 You can (and, for most purposes, probably should) increase the number
88 of rows by using the <literal>-s</> (scale factor) option. The
89 <literal>-F</> (fillfactor) option might also be used at this point.
93 Once you have done the necessary setup, you can run your benchmark
94 with a command that doesn't include <literal>-i</>, that is
97 pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
100 In nearly all cases, you'll need some options to make a useful test.
101 The most important options are <literal>-c</> (number of clients),
102 <literal>-t</> (number of transactions), and <literal>-f</> (specify
103 a custom script file). See below for a full list.
107 <xref linkend="pgbench-init-options"> shows options that are used
108 during database initialization, while
109 <xref linkend="pgbench-run-options"> shows options that are used
110 while running benchmarks, and
111 <xref linkend="pgbench-common-options"> shows options that are useful
115 <table id="pgbench-init-options">
116 <title><application>pgbench</application> initialization options</title>
120 <entry>Option</entry>
121 <entry>Description</entry>
127 <entry><literal>-i</literal></entry>
129 Required to invoke initialization mode.
133 <entry><literal>-s</literal> <replaceable>scale_factor</></entry>
135 Multiply the number of rows generated by the scale factor.
136 For example, <literal>-s 100</> will imply 10,000,000 rows
137 in the <structname>accounts</> table. Default is 1.
141 <entry><literal>-F</literal> <replaceable>fillfactor</></entry>
143 Create the <structname>accounts</>, <structname>tellers</> and
144 <structname>branches</> tables with the given fillfactor.
152 <table id="pgbench-run-options">
153 <title><application>pgbench</application> benchmarking options</title>
157 <entry>Option</entry>
158 <entry>Description</entry>
164 <entry><literal>-c</literal> <replaceable>clients</></entry>
166 Number of clients simulated, that is, number of concurrent database
167 sessions. Default is 1.
171 <entry><literal>-t</literal> <replaceable>transactions</></entry>
173 Number of transactions each client runs. Default is 10.
177 <entry><literal>-M</literal> <replaceable>querymode</></entry>
179 Choose the query mode from the follows. default is simple.
182 <para>simple: using simple query protocol.</para>
185 <para>extended: using extended protocol.</para>
188 <para>prepared: using extended protocol with prepared statements.</para>
194 <entry><literal>-N</literal></entry>
196 Do not update <structname>tellers</> and <structname>branches</>.
197 This will avoid update contention on these tables, but
198 it makes the test case even less like TPC-B.
202 <entry><literal>-S</literal></entry>
204 Perform select-only transactions instead of TPC-B-like test.
208 <entry><literal>-f</literal> <replaceable>filename</></entry>
210 Read transaction script from <replaceable>filename</>.
211 See below for details.
212 <literal>-N</literal>, <literal>-S</literal>, and <literal>-f</literal>
213 are mutually exclusive.
217 <entry><literal>-n</literal></entry>
219 No vacuuming is performed before running the test.
220 This option is <emphasis>necessary</>
221 if you are running a custom test scenario that does not include
222 the standard tables <structname>accounts</>,
223 <structname>branches</>, <structname>history</>, and
224 <structname>tellers</>.
228 <entry><literal>-v</literal></entry>
230 Vacuum all four standard tables before running the test.
231 With neither <literal>-n</> nor <literal>-v</>, pgbench will vacuum
232 <structname>tellers</> and <structname>branches</> tables, and
233 will remove all entries in <structname>history</>.
237 <entry><literal>-D</literal> <replaceable>varname</><literal>=</><replaceable>value</></entry>
239 Define a variable for use by a custom script (see below).
240 Multiple <literal>-D</> options are allowed.
244 <entry><literal>-C</literal></entry>
246 Establish a new connection for each transaction, rather than
247 doing it just once per client thread.
248 This is useful to measure the connection overhead.
252 <entry><literal>-l</literal></entry>
254 Write the time taken by each transaction to a logfile.
255 See below for details.
259 <entry><literal>-s</literal> <replaceable>scale_factor</></entry>
261 Report the specified scale factor in <application>pgbench</>'s
262 output. With the built-in tests, this is not necessary; the
263 correct scale factor will be detected by counting the number of
264 rows in the <structname>branches</> table. However, when testing
265 custom benchmarks (<literal>-f</> option), the scale factor
266 will be reported as 1 unless this option is used.
270 <entry><literal>-d</literal></entry>
272 Print debugging output.
279 <table id="pgbench-common-options">
280 <title><application>pgbench</application> common options</title>
284 <entry>Option</entry>
285 <entry>Description</entry>
291 <entry><literal>-h</literal> <replaceable>hostname</></entry>
292 <entry>database server's host</entry>
295 <entry><literal>-p</literal> <replaceable>port</></entry>
296 <entry>database server's port</entry>
299 <entry><literal>-U</literal> <replaceable>login</></entry>
300 <entry>username to connect as</entry>
308 <title>What is the <quote>transaction</> actually performed in pgbench?</title>
311 The default transaction script issues seven commands per transaction:
315 <listitem><para><literal>BEGIN;</literal></para></listitem>
316 <listitem><para><literal>UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid;</literal></para></listitem>
317 <listitem><para><literal>SELECT abalance FROM accounts WHERE aid = :aid;</literal></para></listitem>
318 <listitem><para><literal>UPDATE tellers SET tbalance = tbalance + :delta WHERE tid = :tid;</literal></para></listitem>
319 <listitem><para><literal>UPDATE branches SET bbalance = bbalance + :delta WHERE bid = :bid;</literal></para></listitem>
320 <listitem><para><literal>INSERT INTO history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);</literal></para></listitem>
321 <listitem><para><literal>END;</literal></para></listitem>
325 If you specify <literal>-N</>, steps 4 and 5 aren't included in the
326 transaction. If you specify <literal>-S</>, only the <command>SELECT</> is
332 <title>Custom Scripts</title>
335 <application>pgbench</application> has support for running custom
336 benchmark scenarios by replacing the default transaction script
337 (described above) with a transaction script read from a file
338 (<literal>-f</literal> option). In this case a <quote>transaction</>
339 counts as one execution of a script file. You can even specify
340 multiple scripts (multiple <literal>-f</literal> options), in which
341 case a random one of the scripts is chosen each time a client session
342 starts a new transaction.
346 The format of a script file is one SQL command per line; multi-line
347 SQL commands are not supported. Empty lines and lines beginning with
348 <literal>--</> are ignored. Script file lines can also be
349 <quote>meta commands</>, which are interpreted by <application>pgbench</>
350 itself, as described below.
354 There is a simple variable-substitution facility for script files.
355 Variables can be set by the command-line <literal>-D</> option,
356 explained above, or by the meta commands explained below.
357 In addition to any variables preset by <literal>-D</> command-line options,
358 the variable <literal>scale</> is preset to the current scale factor.
359 Once set, a variable's
360 value can be inserted into a SQL command by writing
361 <literal>:</><replaceable>variablename</>. When running more than
362 one client session, each session has its own set of variables.
366 Script file meta commands begin with a backslash (<literal>\</>).
367 Arguments to a meta command are separated by white space.
368 These meta commands are supported:
374 <literal>\set <replaceable>varname</> <replaceable>operand1</> [ <replaceable>operator</> <replaceable>operand2</> ]</literal>
379 Sets variable <replaceable>varname</> to a calculated integer value.
380 Each <replaceable>operand</> is either an integer constant or a
381 <literal>:</><replaceable>variablename</> reference to a variable
382 having an integer value. The <replaceable>operator</> can be
383 <literal>+</>, <literal>-</>, <literal>*</>, or <literal>/</>.
389 \set ntellers 10 * :scale
397 <literal>\setrandom <replaceable>varname</> <replaceable>min</> <replaceable>max</></literal>
402 Sets variable <replaceable>varname</> to a random integer value
403 between the limits <replaceable>min</> and <replaceable>max</>.
404 Each limit can be either an integer constant or a
405 <literal>:</><replaceable>variablename</> reference to a variable
406 having an integer value.
412 \setrandom aid 1 :naccounts
420 <literal>\sleep <replaceable>number</> [ us | ms | s ]</literal>
425 Causes script execution to sleep for the specified duration in
426 microseconds (<literal>us</>), milliseconds (<literal>ms</>) or seconds
427 (<literal>s</>). If the unit is omitted then seconds are the default.
428 <replaceable>number</> can be either an integer constant or a
429 <literal>:</><replaceable>variablename</> reference to a variable
430 having an integer value.
444 As an example, the full definition of the built-in TPC-B-like
448 \set nbranches :scale
449 \set ntellers 10 * :scale
450 \set naccounts 100000 * :scale
451 \setrandom aid 1 :naccounts
452 \setrandom bid 1 :nbranches
453 \setrandom tid 1 :ntellers
454 \setrandom delta -5000 5000
456 UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid;
457 SELECT abalance FROM accounts WHERE aid = :aid;
458 UPDATE tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
459 UPDATE branches SET bbalance = bbalance + :delta WHERE bid = :bid;
460 INSERT INTO history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
464 This script allows each iteration of the transaction to reference
465 different, randomly-chosen rows. (This example also shows why it's
466 important for each client session to have its own variables —
467 otherwise they'd not be independently touching different rows.)
473 <title>Per-transaction logging</title>
476 With the <literal>-l</> option, <application>pgbench</> writes the time
477 taken by each transaction to a logfile. The logfile will be named
478 <filename>pgbench_log.<replaceable>nnn</></filename>, where
479 <replaceable>nnn</> is the PID of the pgbench process.
480 The format of the log is:
483 <replaceable>client_id</> <replaceable>transaction_no</> <replaceable>time</> <replaceable>file_no</> <replaceable>time_epoch</> <replaceable>time_us</>
486 where <replaceable>time</> is the elapsed transaction time in microseconds,
487 <replaceable>file_no</> identifies which script file was used
488 (useful when multiple scripts were specified with <literal>-f</>),
489 and <replaceable>time_epoch</>/<replaceable>time_us</> are a
490 UNIX epoch format timestamp and an offset
491 in microseconds (suitable for creating a ISO 8601
492 timestamp with fractional seconds) showing when
493 the transaction completed.
497 Here are example outputs:
499 0 199 2241 0 1175850568 995598
500 0 200 2465 0 1175850568 998079
501 0 201 2513 0 1175850569 608
502 0 202 2038 0 1175850569 2663
508 <title>Good Practices</title>
511 It is very easy to use <application>pgbench</> to produce completely
512 meaningless numbers. Here are some guidelines to help you get useful
517 In the first place, <emphasis>never</> believe any test that runs
518 for only a few seconds. Increase the <literal>-t</> setting enough
519 to make the run last at least a few minutes, so as to average out noise.
520 In some cases you could need hours to get numbers that are reproducible.
521 It's a good idea to try the test run a few times, to find out if your
522 numbers are reproducible or not.
526 For the default TPC-B-like test scenario, the initialization scale factor
527 (<literal>-s</>) should be at least as large as the largest number of
528 clients you intend to test (<literal>-c</>); else you'll mostly be
529 measuring update contention. There are only <literal>-s</> rows in
530 the <structname>branches</> table, and every transaction wants to
531 update one of them, so <literal>-c</> values in excess of <literal>-s</>
532 will undoubtedly result in lots of transactions blocked waiting for
537 The default test scenario is also quite sensitive to how long it's been
538 since the tables were initialized: accumulation of dead rows and dead space
539 in the tables changes the results. To understand the results you must keep
540 track of the total number of updates and when vacuuming happens. If
541 autovacuum is enabled it can result in unpredictable changes in measured
546 A limitation of <application>pgbench</> is that it can itself become
547 the bottleneck when trying to test a large number of client sessions.
548 This can be alleviated by running <application>pgbench</> on a different
549 machine from the database server, although low network latency will be
550 essential. It might even be useful to run several <application>pgbench</>
551 instances concurrently, on several client machines, against the same