1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
15
TITLE="Additional Supplied Modules"
16
HREF="contrib.html"><LINK
18
TITLE="pg_archivecleanup"
19
HREF="pgarchivecleanup.html"><LINK
21
TITLE="pg_buffercache"
22
HREF="pgbuffercache.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
56
TITLE="pg_archivecleanup"
57
HREF="pgarchivecleanup.html"
66
TITLE="Additional Supplied Modules"
74
>Appendix F. Additional Supplied Modules</TD
80
TITLE="Additional Supplied Modules"
89
TITLE="pg_buffercache"
90
HREF="pgbuffercache.html"
111
> is a simple program for running benchmark
115
>. It runs the same sequence of SQL
116
commands over and over, possibly in multiple concurrent database sessions,
117
and then calculates the average transaction rate (transactions per second).
121
> tests a scenario that is
122
loosely based on TPC-B, involving five <TT
132
> commands per transaction.
133
However, it is easy to test other cases by writing your own transaction
137
> Typical output from pgbench looks like:
141
>transaction type: TPC-B (sort of)
144
number of clients: 10
146
number of transactions per client: 1000
147
number of transactions actually processed: 10000/10000
148
tps = 85.184871 (including connections establishing)
149
tps = 85.296346 (excluding connections establishing)</PRE
152
The first six lines report some of the most important parameter
153
settings. The next line reports the number of transactions completed
154
and intended (the latter being just the product of number of clients
155
and number of transactions per client); these will be equal unless the run
156
failed before completion. (In <TT
159
> mode, only the actual
160
number of transactions is printed.)
161
The last two lines report the number of transactions per second,
162
figured with and without counting the time to start database sessions.
173
> The default TPC-B-like transaction test requires specific tables to be
174
set up beforehand. <SPAN
177
> should be invoked with
181
> (initialize) option to create and populate these
182
tables. (When you are testing a custom script, you don't need this
183
step, but will instead need to do whatever setup your test needs.)
184
Initialization looks like:
187
CLASS="PROGRAMLISTING"
209
> is the name of the already-created
210
database to test in. (You may also need <TT
220
> options to specify how to
221
connect to the database server.)
245
> creates four tables <TT
247
>pgbench_accounts</TT
251
>pgbench_branches</TT
260
destroying any existing tables of these names.
261
Be very careful to use another database if you have tables having these
269
> At the default <SPAN
271
>"scale factor"</SPAN
272
> of 1, the tables initially
273
contain this many rows:
277
---------------------------------
280
pgbench_accounts 100000
281
pgbench_history 0</PRE
283
You can (and, for most purposes, probably should) increase the number
284
of rows by using the <TT
287
> (scale factor) option. The
291
> (fillfactor) option might also be used at this point.
294
> Once you have done the necessary setup, you can run your benchmark
295
with a command that doesn't include <TT
301
CLASS="PROGRAMLISTING"
318
In nearly all cases, you'll need some options to make a useful test.
319
The most important options are <TT
322
> (number of clients),
326
> (number of transactions), <TT
333
> (specify a custom script file).
334
See below for a full list.
338
HREF="pgbench.html#PGBENCH-INIT-OPTIONS"
340
> shows options that are used
341
during database initialization, while
343
HREF="pgbench.html#PGBENCH-RUN-OPTIONS"
345
> shows options that are used
346
while running benchmarks, and
348
HREF="pgbench.html#PGBENCH-COMMON-OPTIONS"
350
> shows options that are useful
359
NAME="PGBENCH-INIT-OPTIONS"
363
> Initialization Options</A
369
> accepts the following command-line
370
initialization arguments:
384
> Required to invoke initialization mode.
401
>pgbench_accounts</TT
409
>pgbench_branches</TT
410
> tables with the given fillfactor.
426
> Multiply the number of rows generated by the scale factor.
430
> will create 10,000,000 rows
433
>pgbench_accounts</TT
434
> table. Default is 1.
447
NAME="PGBENCH-RUN-OPTIONS"
451
> Benchmarking Options</A
457
> accepts the following command-line
458
benchmarking arguments:
477
> Number of clients simulated, that is, number of concurrent database
478
sessions. Default is 1.
488
> Establish a new connection for each transaction, rather than
489
doing it just once per client session.
490
This is useful to measure the connection overhead.
500
> Print debugging output.
523
> Define a variable for use by a custom script (see below).
527
> options are allowed.
542
> Read transaction script from <TT
548
See below for details.
559
are mutually exclusive.
574
> Number of worker threads within <SPAN
578
Using more than one thread can be helpful on multi-CPU machines.
579
The number of clients must be a multiple of the number of threads,
580
since each thread is given the same number of client sessions to manage.
591
> Write the time taken by each transaction to a log file.
592
See below for details.
607
> Protocol to use for submitting queries to the server:
616
>: use simple query protocol.</P
623
>: use extended query protocol.</P
630
>: use extended query protocol with prepared statements.</P
634
The default is simple query protocol. (See <A
638
for more information.)
648
> Perform no vacuuming before running the test.
656
if you are running a custom test scenario that does not include
657
the standard tables <TT
659
>pgbench_accounts</TT
663
>pgbench_branches</TT
687
>pgbench_branches</TT
689
This will avoid update contention on these tables, but
690
it makes the test case even less like TPC-B.
700
> Report the average per-statement latency (execution time from the
701
perspective of the client) of each command after the benchmark
702
finishes. See below for details.
717
> Report the specified scale factor in <SPAN
721
output. With the built-in tests, this is not necessary; the
722
correct scale factor will be detected by counting the number of
725
>pgbench_branches</TT
726
> table. However, when testing
727
custom benchmarks (<TT
730
> option), the scale factor
731
will be reported as 1 unless this option is used.
741
> Perform select-only transactions instead of TPC-B-like test.
756
> Number of transactions each client runs. Default is 10.
771
> Run the test for this many seconds, rather than a fixed number of
772
transactions per client. <TT
779
> are mutually exclusive.
789
> Vacuum all four standard tables before running the test.
796
>, pgbench will vacuum the
802
>pgbench_branches</TT
804
tables, and will truncate <TT
820
NAME="PGBENCH-COMMON-OPTIONS"
830
> accepts the following command-line
850
> The database server's host name
865
> The database server's port number
880
> The user name to connect as
894
>F.26.5. What is the <SPAN
897
> Actually Performed in pgbench?</A
900
> The default transaction script issues seven commands per transaction:
917
>UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;</TT
924
>SELECT abalance FROM pgbench_accounts WHERE aid = :aid;</TT
931
>UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;</TT
938
>UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;</TT
945
>INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);</TT
960
>, steps 4 and 5 aren't included in the
961
transaction. If you specify <TT
977
>F.26.6. Custom Scripts</A
983
> has support for running custom
984
benchmark scenarios by replacing the default transaction script
985
(described above) with a transaction script read from a file
989
> option). In this case a <SPAN
993
counts as one execution of a script file. You can even specify
994
multiple scripts (multiple <TT
998
case a random one of the scripts is chosen each time a client session
999
starts a new transaction.
1002
> The format of a script file is one SQL command per line; multiline
1003
SQL commands are not supported. Empty lines and lines beginning with
1007
> are ignored. Script file lines can also be
1010
>"meta commands"</SPAN
1011
>, which are interpreted by <SPAN
1015
itself, as described below.
1018
> There is a simple variable-substitution facility for script files.
1019
Variables can be set by the command-line <TT
1023
explained above, or by the meta commands explained below.
1024
In addition to any variables preset by <TT
1027
> command-line options,
1031
> is preset to the current scale factor.
1032
Once set, a variable's
1033
value can be inserted into a SQL command by writing
1042
>. When running more than
1043
one client session, each session has its own set of variables.
1046
> Script file meta commands begin with a backslash (<TT
1050
Arguments to a meta command are separated by white space.
1051
These meta commands are supported:
1056
CLASS="VARIABLELIST"
1090
> to a calculated integer value.
1096
> is either an integer constant or a
1105
> reference to a variable
1106
having an integer value. The <TT
1129
CLASS="PROGRAMLISTING"
1130
>\set ntellers 10 * :scale</PRE
1161
> to a random integer value
1162
between the limits <TT
1173
Each limit can be either an integer constant or a
1182
> reference to a variable
1183
having an integer value.
1188
CLASS="PROGRAMLISTING"
1189
>\setrandom aid 1 :naccounts</PRE
1201
> [ us | ms | s ]</TT
1205
> Causes script execution to sleep for the specified duration in
1209
>), milliseconds (<TT
1216
>). If the unit is omitted then seconds are the default.
1222
> can be either an integer constant or a
1231
> reference to a variable
1232
having an integer value.
1237
CLASS="PROGRAMLISTING"
1269
> to the result of the shell command
1275
>. The command must return an integer value
1276
through its standard output.
1284
> can be either a text constant or a
1293
> reference to a variable of
1294
any types. If you want to use <TT
1300
colons, you need to add an additional colon at the beginning of
1311
CLASS="PROGRAMLISTING"
1312
>\setshell variable_to_be_assigned command literal_argument :variable ::literal_starting_with_colon</PRE
1336
>, but the result is ignored.
1341
CLASS="PROGRAMLISTING"
1342
>\shell command literal_argument :variable ::literal_starting_with_colon</PRE
1349
> As an example, the full definition of the built-in TPC-B-like
1353
CLASS="PROGRAMLISTING"
1354
>\set nbranches :scale
1355
\set ntellers 10 * :scale
1356
\set naccounts 100000 * :scale
1357
\setrandom aid 1 :naccounts
1358
\setrandom bid 1 :nbranches
1359
\setrandom tid 1 :ntellers
1360
\setrandom delta -5000 5000
1362
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
1363
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
1364
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
1365
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
1366
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1370
This script allows each iteration of the transaction to reference
1371
different, randomly-chosen rows. (This example also shows why it's
1372
important for each client session to have its own variables —
1373
otherwise they'd not be independently touching different rows.)
1382
>F.26.7. Per-Transaction Logging</A
1392
taken by each transaction to a log file. The log file will be named
1407
> is the PID of the pgbench process.
1411
> option is 2 or higher, creating multiple worker
1412
threads, each will have its own log file. The first worker will use the
1413
same name for its log file as in the standard single worker case.
1414
The additional log files for the other workers will be named
1434
> is a sequential number for each worker starting
1438
> The format of the log is:
1480
> is the total elapsed transaction time in microseconds,
1486
> identifies which script file was used
1487
(useful when multiple scripts were specified with <TT
1502
UNIX epoch format timestamp and an offset
1503
in microseconds (suitable for creating a ISO 8601
1504
timestamp with fractional seconds) showing when
1505
the transaction completed.
1508
> Here are example outputs:
1511
> 0 199 2241 0 1175850568 995598
1512
0 200 2465 0 1175850568 998079
1513
0 201 2513 0 1175850569 608
1514
0 202 2038 0 1175850569 2663</PRE
1524
>F.26.8. Per-Statement Latencies</A
1534
the elapsed transaction time of each statement executed by every
1535
client. It then reports an average of those values, referred to
1536
as the latency for each statement, after the benchmark has finished.
1539
> For the default script, the output will look similar to this:
1542
>starting vacuum...end.
1543
transaction type: TPC-B (sort of)
1546
number of clients: 10
1547
number of threads: 1
1548
number of transactions per client: 1000
1549
number of transactions actually processed: 10000/10000
1550
tps = 618.764555 (including connections establishing)
1551
tps = 622.977698 (excluding connections establishing)
1552
statement latencies in milliseconds:
1553
0.004386 \set nbranches 1 * :scale
1554
0.001343 \set ntellers 10 * :scale
1555
0.001212 \set naccounts 100000 * :scale
1556
0.001310 \setrandom aid 1 :naccounts
1557
0.001073 \setrandom bid 1 :nbranches
1558
0.001005 \setrandom tid 1 :ntellers
1559
0.001078 \setrandom delta -5000 5000
1561
0.603376 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
1562
0.454643 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
1563
5.528491 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
1564
7.335435 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
1565
0.371851 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1570
> If multiple script files are specified, the averages are reported
1571
separately for each script file.
1574
> Note that collecting the additional timing information needed for
1575
per-statement latency computation adds some overhead. This will slow
1576
average execution speed and lower the computed TPS. The amount
1577
of slowdown varies significantly depending on platform and hardware.
1578
Comparing average TPS values with and without latency reporting enabled
1579
is a good way to measure if the timing overhead is significant.
1588
>F.26.9. Good Practices</A
1591
> It is very easy to use <SPAN
1594
> to produce completely
1595
meaningless numbers. Here are some guidelines to help you get useful
1599
> In the first place, <SPAN
1605
> believe any test that runs
1606
for only a few seconds. Use the <TT
1613
to make the run last at least a few minutes, so as to average out noise.
1614
In some cases you could need hours to get numbers that are reproducible.
1615
It's a good idea to try the test run a few times, to find out if your
1616
numbers are reproducible or not.
1619
> For the default TPC-B-like test scenario, the initialization scale factor
1623
>) should be at least as large as the largest number of
1624
clients you intend to test (<TT
1627
>); else you'll mostly be
1628
measuring update contention. There are only <TT
1634
>pgbench_branches</TT
1635
> table, and every transaction wants to
1636
update one of them, so <TT
1639
> values in excess of <TT
1643
will undoubtedly result in lots of transactions blocked waiting for
1647
> The default test scenario is also quite sensitive to how long it's been
1648
since the tables were initialized: accumulation of dead rows and dead space
1649
in the tables changes the results. To understand the results you must keep
1650
track of the total number of updates and when vacuuming happens. If
1651
autovacuum is enabled it can result in unpredictable changes in measured
1655
> A limitation of <SPAN
1658
> is that it can itself become
1659
the bottleneck when trying to test a large number of client sessions.
1660
This can be alleviated by running <SPAN
1664
machine from the database server, although low network latency will be
1665
essential. It might even be useful to run several <SPAN
1669
instances concurrently, on several client machines, against the same
1679
SUMMARY="Footer navigation table"
1690
HREF="pgarchivecleanup.html"
1708
HREF="pgbuffercache.html"
1718
>pg_archivecleanup</TD
b'\\ No newline at end of file'