Remove CRs for each line in pgbench.sgml.

1 files changed, 640 insertions, 640 deletions

diff --git a/doc/src/sgml/pgbench.sgml b/doc/src/sgml/pgbench.sgml
index cb94e30c01..50c98b658f 100644
--- a/doc/src/sgml/pgbench.sgml
+++ b/doc/src/sgml/pgbench.sgml
@@ -1,640 +1,640 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/pgbench.sgml,v 1.13 2010/03/23 01:29:22 itagaki Exp $ -->

-

-<sect1 id="pgbench">

- <title>pgbench</title>

-

- <indexterm zone="pgbench">

-  <primary>pgbench</primary>

- </indexterm>

-

- <para>

-  <application>pgbench</application> is a simple program for running benchmark

-  tests on <productname>PostgreSQL</>.  It runs the same sequence of SQL

-  commands over and over, possibly in multiple concurrent database sessions,

-  and then calculates the average transaction rate (transactions per second).

-  By default, <application>pgbench</application> tests a scenario that is

-  loosely based on TPC-B, involving five <command>SELECT</>,

-  <command>UPDATE</>, and <command>INSERT</> commands per transaction.

-  However, it is easy to test other cases by writing your own transaction

-  script files.

- </para>

-

- <para>

-  Typical output from pgbench looks like:

-

- <programlisting>

-transaction type: TPC-B (sort of)

-scaling factor: 10

-query mode: simple

-number of clients: 10

-number of threads: 1

-number of transactions per client: 1000

-number of transactions actually processed: 10000/10000

-tps = 85.184871 (including connections establishing)

-tps = 85.296346 (excluding connections establishing)

- </programlisting>

-

-  The first six lines report some of the most important parameter

-  settings.  The next line reports the number of transactions completed

-  and intended (the latter being just the product of number of clients

-  and number of transactions per client); these will be equal unless the run

-  failed before completion.  The last two lines report the TPS rate,

-  figured with and without counting the time to start database sessions.

- </para>

-

- <sect2>

-  <title>Overview</title>

-

-  <para>

-   The default TPC-B-like transaction test requires specific tables to be

-   set up beforehand.  <application>pgbench</> should be invoked with

-   the <literal>-i</> (initialize) option to create and populate these

-   tables.  (When you are testing a custom script, you don't need this

-   step, but will instead need to do whatever setup your test needs.)

-   Initialization looks like:

-

-   <programlisting>

-pgbench -i <optional> <replaceable>other-options</> </optional> <replaceable>dbname</>

-   </programlisting>

-

-   where <replaceable>dbname</> is the name of the already-created

-   database to test in.  (You may also need <literal>-h</>,

-   <literal>-p</>, and/or <literal>-U</> options to specify how to

-   connect to the database server.)

-  </para>

-

-  <caution>

-   <para>

-    <literal>pgbench -i</> creates four tables <structname>pgbench_accounts</>,

-    <structname>pgbench_branches</>, <structname>pgbench_history</>, and

-    <structname>pgbench_tellers</>,

-    destroying any existing tables of these names.

-    Be very careful to use another database if you have tables having these

-    names!

-   </para>

-  </caution>

-

-  <para>

-   At the default <quote>scale factor</> of 1, the tables initially

-   contain this many rows:

-  </para>

-  <programlisting>

-table                   # of rows

----------------------------------

-pgbench_branches        1

-pgbench_tellers         10

-pgbench_accounts        100000

-pgbench_history         0

-  </programlisting>

-  <para>

-   You can (and, for most purposes, probably should) increase the number

-   of rows by using the <literal>-s</> (scale factor) option.  The

-   <literal>-F</> (fillfactor) option might also be used at this point.

-  </para>

-

-  <para>

-   Once you have done the necessary setup, you can run your benchmark

-   with a command that doesn't include <literal>-i</>, that is

-

-   <programlisting>

-pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>

-   </programlisting>

-

-   In nearly all cases, you'll need some options to make a useful test.

-   The most important options are <literal>-c</> (number of clients),

-   <literal>-t</> (number of transactions), <literal>-T</> (time limit),

-   and <literal>-f</> (specify a custom script file).

-   See below for a full list.

-  </para>

-

-  <para>

-   <xref linkend="pgbench-init-options"> shows options that are used

-   during database initialization, while

-   <xref linkend="pgbench-run-options"> shows options that are used

-   while running benchmarks, and

-   <xref linkend="pgbench-common-options"> shows options that are useful

-   in both cases.

-  </para>

-

-  <table id="pgbench-init-options">

-   <title><application>pgbench</application> initialization options</title>

-   <tgroup cols="2">

-    <thead>

-     <row>

-      <entry>Option</entry>

-      <entry>Description</entry>

-     </row>

-    </thead>

-

-    <tbody>

-     <row>

-      <entry><literal>-i</literal></entry>

-      <entry>

-       Required to invoke initialization mode.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-s</literal> <replaceable>scale_factor</></entry>

-      <entry>

-       Multiply the number of rows generated by the scale factor.

-       For example, <literal>-s 100</> will create 10,000,000 rows

-       in the <structname>pgbench_accounts</> table. Default is 1.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-F</literal> <replaceable>fillfactor</></entry>

-      <entry>

-       Create the <structname>pgbench_accounts</>,

-       <structname>pgbench_tellers</> and

-       <structname>pgbench_branches</> tables with the given fillfactor.

-       Default is 100.

-      </entry>

-     </row>

-    </tbody>

-   </tgroup>

-  </table>

-

-  <table id="pgbench-run-options">

-   <title><application>pgbench</application> benchmarking options</title>

-   <tgroup cols="2">

-    <thead>

-     <row>

-      <entry>Option</entry>

-      <entry>Description</entry>

-     </row>

-    </thead>

-

-    <tbody>

-     <row>

-      <entry><literal>-c</literal> <replaceable>clients</></entry>

-      <entry>

-       Number of clients simulated, that is, number of concurrent database

-       sessions.  Default is 1.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-j</literal> <replaceable>threads</></entry>

-      <entry>

-       Number of worker threads within <application>pgbench</application>.

-       Using more than one thread can be helpful on multi-CPU machines.

-       The number of clients must be a multiple of the number of threads,

-       since each thread is given the same number of client sessions to manage.

-       Default is 1.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-t</literal> <replaceable>transactions</></entry>

-      <entry>

-       Number of transactions each client runs.  Default is 10.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-T</literal> <replaceable>seconds</></entry>

-      <entry>

-       Run the test for this many seconds, rather than a fixed number of

-       transactions per client. <literal>-t</literal> and

-       <literal>-T</literal> are mutually exclusive.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-M</literal> <replaceable>querymode</></entry>

-      <entry>

-       Protocol to use for submitting queries to the server:

-         <itemizedlist>

-          <listitem>

-           <para><literal>simple</>: use simple query protocol.</para>

-          </listitem>

-          <listitem>

-           <para><literal>extended</>: use extended query protocol.</para>

-          </listitem>

-          <listitem>

-           <para><literal>prepared</>: use extended query protocol with prepared statements.</para>

-          </listitem>

-         </itemizedlist>

-       The default is simple query protocol.  (See <xref linkend="protocol">

-       for more information.)

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-N</literal></entry>

-      <entry>

-       Do not update <structname>pgbench_tellers</> and

-       <structname>pgbench_branches</>.

-       This will avoid update contention on these tables, but

-       it makes the test case even less like TPC-B.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-S</literal></entry>

-      <entry>

-       Perform select-only transactions instead of TPC-B-like test.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-f</literal> <replaceable>filename</></entry>

-      <entry>

-       Read transaction script from <replaceable>filename</>.

-       See below for details.

-       <literal>-N</literal>, <literal>-S</literal>, and <literal>-f</literal>

-       are mutually exclusive.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-n</literal></entry>

-      <entry>

-       Perform no vacuuming before running the test.

-       This option is <emphasis>necessary</>

-       if you are running a custom test scenario that does not include

-       the standard tables <structname>pgbench_accounts</>,

-       <structname>pgbench_branches</>, <structname>pgbench_history</>, and

-       <structname>pgbench_tellers</>.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-v</literal></entry>

-      <entry>

-       Vacuum all four standard tables before running the test.

-       With neither <literal>-n</> nor <literal>-v</>, pgbench will vacuum the

-       <structname>pgbench_tellers</> and <structname>pgbench_branches</>

-       tables, and will truncate <structname>pgbench_history</>.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-D</literal> <replaceable>varname</><literal>=</><replaceable>value</></entry>

-      <entry>

-       Define a variable for use by a custom script (see below).

-       Multiple <literal>-D</> options are allowed.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-C</literal></entry>

-      <entry>

-       Establish a new connection for each transaction, rather than

-       doing it just once per client session.

-       This is useful to measure the connection overhead.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-l</literal></entry>

-      <entry>

-       Write the time taken by each transaction to a logfile.

-       See below for details.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-s</literal> <replaceable>scale_factor</></entry>

-      <entry>

-       Report the specified scale factor in <application>pgbench</>'s

-       output.  With the built-in tests, this is not necessary; the

-       correct scale factor will be detected by counting the number of

-       rows in the <structname>pgbench_branches</> table.  However, when testing

-       custom benchmarks (<literal>-f</> option), the scale factor

-       will be reported as 1 unless this option is used.

-      </entry>

-     </row>

-     <row>

-      <entry><literal>-d</literal></entry>

-      <entry>

-       Print debugging output.

-      </entry>

-     </row>

-    </tbody>

-   </tgroup>

-  </table>

-

-  <table id="pgbench-common-options">

-   <title><application>pgbench</application> common options</title>

-   <tgroup cols="2">

-    <thead>

-     <row>

-      <entry>Option</entry>

-      <entry>Description</entry>

-     </row>

-    </thead>

-

-    <tbody>

-     <row>

-      <entry><literal>-h</literal> <replaceable>hostname</></entry>

-      <entry>database server's host</entry>

-     </row>

-     <row>

-      <entry><literal>-p</literal> <replaceable>port</></entry>

-      <entry>database server's port</entry>

-     </row>

-     <row>

-      <entry><literal>-U</literal> <replaceable>login</></entry>

-      <entry>username to connect as</entry>

-     </row>

-    </tbody>

-   </tgroup>

-  </table>

- </sect2>

-

- <sect2>

-  <title>What is the <quote>transaction</> actually performed in pgbench?</title>

-

-  <para>

-   The default transaction script issues seven commands per transaction:

-  </para>

-

-  <orderedlist>

-   <listitem><para><literal>BEGIN;</literal></para></listitem>

-   <listitem><para><literal>UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;</literal></para></listitem>

-   <listitem><para><literal>SELECT abalance FROM pgbench_accounts WHERE aid = :aid;</literal></para></listitem>

-   <listitem><para><literal>UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;</literal></para></listitem>

-   <listitem><para><literal>UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;</literal></para></listitem>

-   <listitem><para><literal>INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);</literal></para></listitem>

-   <listitem><para><literal>END;</literal></para></listitem>

-  </orderedlist>

-

-  <para>

-   If you specify <literal>-N</>, steps 4 and 5 aren't included in the

-   transaction.  If you specify <literal>-S</>, only the <command>SELECT</> is

-   issued.

-  </para>

- </sect2>

-

- <sect2>

-  <title>Custom Scripts</title>

-

-  <para>

-   <application>pgbench</application> has support for running custom

-   benchmark scenarios by replacing the default transaction script

-   (described above) with a transaction script read from a file

-   (<literal>-f</literal> option).  In this case a <quote>transaction</>

-   counts as one execution of a script file.  You can even specify

-   multiple scripts (multiple <literal>-f</literal> options), in which

-   case a random one of the scripts is chosen each time a client session

-   starts a new transaction.

-  </para>

-

-  <para>

-   The format of a script file is one SQL command per line; multi-line

-   SQL commands are not supported.  Empty lines and lines beginning with

-   <literal>--</> are ignored.  Script file lines can also be

-   <quote>meta commands</>, which are interpreted by <application>pgbench</>

-   itself, as described below.

-  </para>

-

-  <para>

-   There is a simple variable-substitution facility for script files.

-   Variables can be set by the command-line <literal>-D</> option,

-   explained above, or by the meta commands explained below.

-   In addition to any variables preset by <literal>-D</> command-line options,

-   the variable <literal>scale</> is preset to the current scale factor.

-   Once set, a variable's

-   value can be inserted into a SQL command by writing

-   <literal>:</><replaceable>variablename</>.  When running more than

-   one client session, each session has its own set of variables.

-  </para>

-

-  <para>

-   Script file meta commands begin with a backslash (<literal>\</>).

-   Arguments to a meta command are separated by white space.

-   These meta commands are supported:

-  </para>

-

-  <variablelist>

-   <varlistentry>

-    <term>

-     <literal>\set <replaceable>varname</> <replaceable>operand1</> [ <replaceable>operator</> <replaceable>operand2</> ]</literal>

-    </term>

-

-    <listitem>

-     <para>

-      Sets variable <replaceable>varname</> to a calculated integer value.

-      Each <replaceable>operand</> is either an integer constant or a

-      <literal>:</><replaceable>variablename</> reference to a variable

-      having an integer value.  The <replaceable>operator</> can be

-      <literal>+</>, <literal>-</>, <literal>*</>, or <literal>/</>.

-     </para>

-

-     <para>

-      Example:

-      <programlisting>

-\set ntellers 10 * :scale

-      </programlisting>

-     </para>

-    </listitem>

-   </varlistentry>

-

-   <varlistentry>

-    <term>

-     <literal>\setrandom <replaceable>varname</> <replaceable>min</> <replaceable>max</></literal>

-    </term>

-

-    <listitem>

-     <para>

-      Sets variable <replaceable>varname</> to a random integer value

-      between the limits <replaceable>min</> and <replaceable>max</> inclusive.

-      Each limit can be either an integer constant or a

-      <literal>:</><replaceable>variablename</> reference to a variable

-      having an integer value.

-     </para>

-

-     <para>

-      Example:

-      <programlisting>

-\setrandom aid 1 :naccounts

-      </programlisting>

-     </para>

-    </listitem>

-   </varlistentry>

-

-   <varlistentry>

-    <term>

-     <literal>\sleep <replaceable>number</> [ us | ms | s ]</literal>

-    </term>

-

-    <listitem>

-     <para>

-      Causes script execution to sleep for the specified duration in

-      microseconds (<literal>us</>), milliseconds (<literal>ms</>) or seconds

-      (<literal>s</>).  If the unit is omitted then seconds are the default.

-      <replaceable>number</> can be either an integer constant or a

-      <literal>:</><replaceable>variablename</> reference to a variable

-      having an integer value.

-     </para>

-

-     <para>

-      Example:

-      <programlisting>

-\sleep 10 ms

-      </programlisting>

-     </para>

-    </listitem>

-   </varlistentry>

-

-   <varlistentry>

-    <term>

-     <literal>\setshell <replaceable>varname</> <replaceable>command</> [ <replaceable>argument</> ... ]</literal>

-    </term>

-

-    <listitem>

-     <para>

-      Sets variable <replaceable>varname</> to the result of the shell command

-      <replaceable>command</>. The command must return an integer value

-      through its standard output.

-     </para>

-

-     <para>

-      <replaceable>argument</> can be either a text constant or a

-      <literal>:</><replaceable>variablename</> reference to a variable of

-      any types. If you want to use <replaceable>argument</> starting with

-      colons, you need to add an additional colon at the beginning of

-      <replaceable>argument</>.

-     </para>

-

-     <para>

-      Example:

-      <programlisting>

-\setshell variable_to_be_assigned command literal_argument :variable ::literal_starting_with_colon

-      </programlisting>

-     </para>

-    </listitem>

-   </varlistentry>

-

-   <varlistentry>

-    <term>

-     <literal>\shell <replaceable>command</> [ <replaceable>argument</> ... ]</literal>

-    </term>

-

-    <listitem>

-     <para>

-      Same as <literal>\setshell</literal>, but the result is ignored.

-     </para>

-

-     <para>

-      Example:

-      <programlisting>

-\shell command literal_argument :variable ::literal_starting_with_colon

-      </programlisting>

-     </para>

-    </listitem>

-   </varlistentry>

-  </variablelist>

-

-  <para>

-   As an example, the full definition of the built-in TPC-B-like

-   transaction is:

-

-   <programlisting>

-\set nbranches :scale

-\set ntellers 10 * :scale

-\set naccounts 100000 * :scale

-\setrandom aid 1 :naccounts

-\setrandom bid 1 :nbranches

-\setrandom tid 1 :ntellers

-\setrandom delta -5000 5000

-BEGIN;

-UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

-SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

-UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

-UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

-INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

-END;

-   </programlisting>

-

-   This script allows each iteration of the transaction to reference

-   different, randomly-chosen rows.  (This example also shows why it's

-   important for each client session to have its own variables &mdash;

-   otherwise they'd not be independently touching different rows.)

-  </para>

-

- </sect2>

-

- <sect2>

-  <title>Per-transaction logging</title>

-

-  <para>

-   With the <literal>-l</> option, <application>pgbench</> writes the time

-   taken by each transaction to a logfile.  The logfile will be named

-   <filename>pgbench_log.<replaceable>nnn</></filename>, where

-   <replaceable>nnn</> is the PID of the pgbench process.

-   If the <literal>-j</> option is 2 or higher, creating multiple worker

-   threads, each will have its own log file. The first worker will use the

-   the same name for its log file as in the standard single worker case.  

-   The additional log files for the other workers will be named

-   <filename>pgbench_log.<replaceable>nnn</>.<replaceable>mmm</></filename>,

-   where <replaceable>mmm</> is a sequential number for each worker starting

-   with 1.

-  </para>

-

-  <para>

-   The format of the log is:

-

-   <programlisting>

-    <replaceable>client_id</> <replaceable>transaction_no</> <replaceable>time</> <replaceable>file_no</> <replaceable>time_epoch</> <replaceable>time_us</>

-   </programlisting>

-

-   where <replaceable>time</> is the elapsed transaction time in microseconds,

-   <replaceable>file_no</> identifies which script file was used

-   (useful when multiple scripts were specified with <literal>-f</>),

-   and <replaceable>time_epoch</>/<replaceable>time_us</> are a

-   UNIX epoch format timestamp and an offset

-   in microseconds (suitable for creating a ISO 8601

-   timestamp with fractional seconds) showing when

-   the transaction completed.

-  </para>

-

-  <para>

-   Here are example outputs:

-   <programlisting>

- 0 199 2241 0 1175850568 995598

- 0 200 2465 0 1175850568 998079

- 0 201 2513 0 1175850569 608

- 0 202 2038 0 1175850569 2663

-   </programlisting>

-  </para>

- </sect2>

-

- <sect2>

-  <title>Good Practices</title>

-

-  <para>

-   It is very easy to use <application>pgbench</> to produce completely

-   meaningless numbers.  Here are some guidelines to help you get useful

-   results.

-  </para>

-

-  <para>

-   In the first place, <emphasis>never</> believe any test that runs

-   for only a few seconds.  Use the <literal>-t</> or <literal>-T</> option

-   to make the run last at least a few minutes, so as to average out noise.

-   In some cases you could need hours to get numbers that are reproducible.

-   It's a good idea to try the test run a few times, to find out if your

-   numbers are reproducible or not.

-  </para>

-

-  <para>

-   For the default TPC-B-like test scenario, the initialization scale factor

-   (<literal>-s</>) should be at least as large as the largest number of

-   clients you intend to test (<literal>-c</>); else you'll mostly be

-   measuring update contention.  There are only <literal>-s</> rows in

-   the <structname>pgbench_branches</> table, and every transaction wants to

-   update one of them, so <literal>-c</> values in excess of <literal>-s</>

-   will undoubtedly result in lots of transactions blocked waiting for

-   other transactions.

-  </para>

-

-  <para>

-   The default test scenario is also quite sensitive to how long it's been

-   since the tables were initialized: accumulation of dead rows and dead space

-   in the tables changes the results.  To understand the results you must keep

-   track of the total number of updates and when vacuuming happens.  If

-   autovacuum is enabled it can result in unpredictable changes in measured

-   performance.

-  </para>

-

-  <para>

-   A limitation of <application>pgbench</> is that it can itself become

-   the bottleneck when trying to test a large number of client sessions.

-   This can be alleviated by running <application>pgbench</> on a different

-   machine from the database server, although low network latency will be

-   essential.  It might even be useful to run several <application>pgbench</>

-   instances concurrently, on several client machines, against the same

-   database server.

-  </para>

- </sect2>

-

-</sect1>

+<!-- $PostgreSQL: pgsql/doc/src/sgml/pgbench.sgml,v 1.14 2010/03/23 04:09:17 itagaki Exp $ -->
+
+<sect1 id="pgbench">
+ <title>pgbench</title>
+
+ <indexterm zone="pgbench">
+  <primary>pgbench</primary>
+ </indexterm>
+
+ <para>
+  <application>pgbench</application> is a simple program for running benchmark
+  tests on <productname>PostgreSQL</>.  It runs the same sequence of SQL
+  commands over and over, possibly in multiple concurrent database sessions,
+  and then calculates the average transaction rate (transactions per second).
+  By default, <application>pgbench</application> tests a scenario that is
+  loosely based on TPC-B, involving five <command>SELECT</>,
+  <command>UPDATE</>, and <command>INSERT</> commands per transaction.
+  However, it is easy to test other cases by writing your own transaction
+  script files.
+ </para>
+
+ <para>
+  Typical output from pgbench looks like:
+
+ <programlisting>
+transaction type: TPC-B (sort of)
+scaling factor: 10
+query mode: simple
+number of clients: 10
+number of threads: 1
+number of transactions per client: 1000
+number of transactions actually processed: 10000/10000
+tps = 85.184871 (including connections establishing)
+tps = 85.296346 (excluding connections establishing)
+ </programlisting>
+
+  The first six lines report some of the most important parameter
+  settings.  The next line reports the number of transactions completed
+  and intended (the latter being just the product of number of clients
+  and number of transactions per client); these will be equal unless the run
+  failed before completion.  The last two lines report the TPS rate,
+  figured with and without counting the time to start database sessions.
+ </para>
+
+ <sect2>
+  <title>Overview</title>
+
+  <para>
+   The default TPC-B-like transaction test requires specific tables to be
+   set up beforehand.  <application>pgbench</> should be invoked with
+   the <literal>-i</> (initialize) option to create and populate these
+   tables.  (When you are testing a custom script, you don't need this
+   step, but will instead need to do whatever setup your test needs.)
+   Initialization looks like:
+
+   <programlisting>
+pgbench -i <optional> <replaceable>other-options</> </optional> <replaceable>dbname</>
+   </programlisting>
+
+   where <replaceable>dbname</> is the name of the already-created
+   database to test in.  (You may also need <literal>-h</>,
+   <literal>-p</>, and/or <literal>-U</> options to specify how to
+   connect to the database server.)
+  </para>
+
+  <caution>
+   <para>
+    <literal>pgbench -i</> creates four tables <structname>pgbench_accounts</>,
+    <structname>pgbench_branches</>, <structname>pgbench_history</>, and
+    <structname>pgbench_tellers</>,
+    destroying any existing tables of these names.
+    Be very careful to use another database if you have tables having these
+    names!
+   </para>
+  </caution>
+
+  <para>
+   At the default <quote>scale factor</> of 1, the tables initially
+   contain this many rows:
+  </para>
+  <programlisting>
+table                   # of rows
+---------------------------------
+pgbench_branches        1
+pgbench_tellers         10
+pgbench_accounts        100000
+pgbench_history         0
+  </programlisting>
+  <para>
+   You can (and, for most purposes, probably should) increase the number
+   of rows by using the <literal>-s</> (scale factor) option.  The
+   <literal>-F</> (fillfactor) option might also be used at this point.
+  </para>
+
+  <para>
+   Once you have done the necessary setup, you can run your benchmark
+   with a command that doesn't include <literal>-i</>, that is
+
+   <programlisting>
+pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
+   </programlisting>
+
+   In nearly all cases, you'll need some options to make a useful test.
+   The most important options are <literal>-c</> (number of clients),
+   <literal>-t</> (number of transactions), <literal>-T</> (time limit),
+   and <literal>-f</> (specify a custom script file).
+   See below for a full list.
+  </para>
+
+  <para>
+   <xref linkend="pgbench-init-options"> shows options that are used
+   during database initialization, while
+   <xref linkend="pgbench-run-options"> shows options that are used
+   while running benchmarks, and
+   <xref linkend="pgbench-common-options"> shows options that are useful
+   in both cases.
+  </para>
+
+  <table id="pgbench-init-options">
+   <title><application>pgbench</application> initialization options</title>
+   <tgroup cols="2">
+    <thead>
+     <row>
+      <entry>Option</entry>
+      <entry>Description</entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry><literal>-i</literal></entry>
+      <entry>
+       Required to invoke initialization mode.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-s</literal> <replaceable>scale_factor</></entry>
+      <entry>
+       Multiply the number of rows generated by the scale factor.
+       For example, <literal>-s 100</> will create 10,000,000 rows
+       in the <structname>pgbench_accounts</> table. Default is 1.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-F</literal> <replaceable>fillfactor</></entry>
+      <entry>
+       Create the <structname>pgbench_accounts</>,
+       <structname>pgbench_tellers</> and
+       <structname>pgbench_branches</> tables with the given fillfactor.
+       Default is 100.
+      </entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+
+  <table id="pgbench-run-options">
+   <title><application>pgbench</application> benchmarking options</title>
+   <tgroup cols="2">
+    <thead>
+     <row>
+      <entry>Option</entry>
+      <entry>Description</entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry><literal>-c</literal> <replaceable>clients</></entry>
+      <entry>
+       Number of clients simulated, that is, number of concurrent database
+       sessions.  Default is 1.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-j</literal> <replaceable>threads</></entry>
+      <entry>
+       Number of worker threads within <application>pgbench</application>.
+       Using more than one thread can be helpful on multi-CPU machines.
+       The number of clients must be a multiple of the number of threads,
+       since each thread is given the same number of client sessions to manage.
+       Default is 1.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-t</literal> <replaceable>transactions</></entry>
+      <entry>
+       Number of transactions each client runs.  Default is 10.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-T</literal> <replaceable>seconds</></entry>
+      <entry>
+       Run the test for this many seconds, rather than a fixed number of
+       transactions per client. <literal>-t</literal> and
+       <literal>-T</literal> are mutually exclusive.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-M</literal> <replaceable>querymode</></entry>
+      <entry>
+       Protocol to use for submitting queries to the server:
+         <itemizedlist>
+          <listitem>
+           <para><literal>simple</>: use simple query protocol.</para>
+          </listitem>
+          <listitem>
+           <para><literal>extended</>: use extended query protocol.</para>
+          </listitem>
+          <listitem>
+           <para><literal>prepared</>: use extended query protocol with prepared statements.</para>
+          </listitem>
+         </itemizedlist>
+       The default is simple query protocol.  (See <xref linkend="protocol">
+       for more information.)
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-N</literal></entry>
+      <entry>
+       Do not update <structname>pgbench_tellers</> and
+       <structname>pgbench_branches</>.
+       This will avoid update contention on these tables, but
+       it makes the test case even less like TPC-B.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-S</literal></entry>
+      <entry>
+       Perform select-only transactions instead of TPC-B-like test.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-f</literal> <replaceable>filename</></entry>
+      <entry>
+       Read transaction script from <replaceable>filename</>.
+       See below for details.
+       <literal>-N</literal>, <literal>-S</literal>, and <literal>-f</literal>
+       are mutually exclusive.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-n</literal></entry>
+      <entry>
+       Perform no vacuuming before running the test.
+       This option is <emphasis>necessary</>
+       if you are running a custom test scenario that does not include
+       the standard tables <structname>pgbench_accounts</>,
+       <structname>pgbench_branches</>, <structname>pgbench_history</>, and
+       <structname>pgbench_tellers</>.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-v</literal></entry>
+      <entry>
+       Vacuum all four standard tables before running the test.
+       With neither <literal>-n</> nor <literal>-v</>, pgbench will vacuum the
+       <structname>pgbench_tellers</> and <structname>pgbench_branches</>
+       tables, and will truncate <structname>pgbench_history</>.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-D</literal> <replaceable>varname</><literal>=</><replaceable>value</></entry>
+      <entry>
+       Define a variable for use by a custom script (see below).
+       Multiple <literal>-D</> options are allowed.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-C</literal></entry>
+      <entry>
+       Establish a new connection for each transaction, rather than
+       doing it just once per client session.
+       This is useful to measure the connection overhead.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-l</literal></entry>
+      <entry>
+       Write the time taken by each transaction to a logfile.
+       See below for details.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-s</literal> <replaceable>scale_factor</></entry>
+      <entry>
+       Report the specified scale factor in <application>pgbench</>'s
+       output.  With the built-in tests, this is not necessary; the
+       correct scale factor will be detected by counting the number of
+       rows in the <structname>pgbench_branches</> table.  However, when testing
+       custom benchmarks (<literal>-f</> option), the scale factor
+       will be reported as 1 unless this option is used.
+      </entry>
+     </row>
+     <row>
+      <entry><literal>-d</literal></entry>
+      <entry>
+       Print debugging output.
+      </entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+
+  <table id="pgbench-common-options">
+   <title><application>pgbench</application> common options</title>
+   <tgroup cols="2">
+    <thead>
+     <row>
+      <entry>Option</entry>
+      <entry>Description</entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry><literal>-h</literal> <replaceable>hostname</></entry>
+      <entry>database server's host</entry>
+     </row>
+     <row>
+      <entry><literal>-p</literal> <replaceable>port</></entry>
+      <entry>database server's port</entry>
+     </row>
+     <row>
+      <entry><literal>-U</literal> <replaceable>login</></entry>
+      <entry>username to connect as</entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+ </sect2>
+
+ <sect2>
+  <title>What is the <quote>transaction</> actually performed in pgbench?</title>
+
+  <para>
+   The default transaction script issues seven commands per transaction:
+  </para>
+
+  <orderedlist>
+   <listitem><para><literal>BEGIN;</literal></para></listitem>
+   <listitem><para><literal>UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;</literal></para></listitem>
+   <listitem><para><literal>SELECT abalance FROM pgbench_accounts WHERE aid = :aid;</literal></para></listitem>
+   <listitem><para><literal>UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;</literal></para></listitem>
+   <listitem><para><literal>UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;</literal></para></listitem>
+   <listitem><para><literal>INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);</literal></para></listitem>
+   <listitem><para><literal>END;</literal></para></listitem>
+  </orderedlist>
+
+  <para>
+   If you specify <literal>-N</>, steps 4 and 5 aren't included in the
+   transaction.  If you specify <literal>-S</>, only the <command>SELECT</> is
+   issued.
+  </para>
+ </sect2>
+
+ <sect2>
+  <title>Custom Scripts</title>
+
+  <para>
+   <application>pgbench</application> has support for running custom
+   benchmark scenarios by replacing the default transaction script
+   (described above) with a transaction script read from a file
+   (<literal>-f</literal> option).  In this case a <quote>transaction</>
+   counts as one execution of a script file.  You can even specify
+   multiple scripts (multiple <literal>-f</literal> options), in which
+   case a random one of the scripts is chosen each time a client session
+   starts a new transaction.
+  </para>
+
+  <para>
+   The format of a script file is one SQL command per line; multi-line
+   SQL commands are not supported.  Empty lines and lines beginning with
+   <literal>--</> are ignored.  Script file lines can also be
+   <quote>meta commands</>, which are interpreted by <application>pgbench</>
+   itself, as described below.
+  </para>
+
+  <para>
+   There is a simple variable-substitution facility for script files.
+   Variables can be set by the command-line <literal>-D</> option,
+   explained above, or by the meta commands explained below.
+   In addition to any variables preset by <literal>-D</> command-line options,
+   the variable <literal>scale</> is preset to the current scale factor.
+   Once set, a variable's
+   value can be inserted into a SQL command by writing
+   <literal>:</><replaceable>variablename</>.  When running more than
+   one client session, each session has its own set of variables.
+  </para>
+
+  <para>
+   Script file meta commands begin with a backslash (<literal>\</>).
+   Arguments to a meta command are separated by white space.
+   These meta commands are supported:
+  </para>
+
+  <variablelist>
+   <varlistentry>
+    <term>
+     <literal>\set <replaceable>varname</> <replaceable>operand1</> [ <replaceable>operator</> <replaceable>operand2</> ]</literal>
+    </term>
+
+    <listitem>
+     <para>
+      Sets variable <replaceable>varname</> to a calculated integer value.
+      Each <replaceable>operand</> is either an integer constant or a
+      <literal>:</><replaceable>variablename</> reference to a variable
+      having an integer value.  The <replaceable>operator</> can be
+      <literal>+</>, <literal>-</>, <literal>*</>, or <literal>/</>.
+     </para>
+
+     <para>
+      Example:
+      <programlisting>
+\set ntellers 10 * :scale
+      </programlisting>
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term>
+     <literal>\setrandom <replaceable>varname</> <replaceable>min</> <replaceable>max</></literal>
+    </term>
+
+    <listitem>
+     <para>
+      Sets variable <replaceable>varname</> to a random integer value
+      between the limits <replaceable>min</> and <replaceable>max</> inclusive.
+      Each limit can be either an integer constant or a
+      <literal>:</><replaceable>variablename</> reference to a variable
+      having an integer value.
+     </para>
+
+     <para>
+      Example:
+      <programlisting>
+\setrandom aid 1 :naccounts
+      </programlisting>
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term>
+     <literal>\sleep <replaceable>number</> [ us | ms | s ]</literal>
+    </term>
+
+    <listitem>
+     <para>
+      Causes script execution to sleep for the specified duration in
+      microseconds (<literal>us</>), milliseconds (<literal>ms</>) or seconds
+      (<literal>s</>).  If the unit is omitted then seconds are the default.
+      <replaceable>number</> can be either an integer constant or a
+      <literal>:</><replaceable>variablename</> reference to a variable
+      having an integer value.
+     </para>
+
+     <para>
+      Example:
+      <programlisting>
+\sleep 10 ms
+      </programlisting>
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term>
+     <literal>\setshell <replaceable>varname</> <replaceable>command</> [ <replaceable>argument</> ... ]</literal>
+    </term>
+
+    <listitem>
+     <para>
+      Sets variable <replaceable>varname</> to the result of the shell command
+      <replaceable>command</>. The command must return an integer value
+      through its standard output.
+     </para>
+
+     <para>
+      <replaceable>argument</> can be either a text constant or a
+      <literal>:</><replaceable>variablename</> reference to a variable of
+      any types. If you want to use <replaceable>argument</> starting with
+      colons, you need to add an additional colon at the beginning of
+      <replaceable>argument</>.
+     </para>
+
+     <para>
+      Example:
+      <programlisting>
+\setshell variable_to_be_assigned command literal_argument :variable ::literal_starting_with_colon
+      </programlisting>
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term>
+     <literal>\shell <replaceable>command</> [ <replaceable>argument</> ... ]</literal>
+    </term>
+
+    <listitem>
+     <para>
+      Same as <literal>\setshell</literal>, but the result is ignored.
+     </para>
+
+     <para>
+      Example:
+      <programlisting>
+\shell command literal_argument :variable ::literal_starting_with_colon
+      </programlisting>
+     </para>
+    </listitem>
+   </varlistentry>
+  </variablelist>
+
+  <para>
+   As an example, the full definition of the built-in TPC-B-like
+   transaction is:
+
+   <programlisting>
+\set nbranches :scale
+\set ntellers 10 * :scale
+\set naccounts 100000 * :scale
+\setrandom aid 1 :naccounts
+\setrandom bid 1 :nbranches
+\setrandom tid 1 :ntellers
+\setrandom delta -5000 5000
+BEGIN;
+UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
+SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
+UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
+UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
+INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
+END;
+   </programlisting>
+
+   This script allows each iteration of the transaction to reference
+   different, randomly-chosen rows.  (This example also shows why it's
+   important for each client session to have its own variables &mdash;
+   otherwise they'd not be independently touching different rows.)
+  </para>
+
+ </sect2>
+
+ <sect2>
+  <title>Per-transaction logging</title>
+
+  <para>
+   With the <literal>-l</> option, <application>pgbench</> writes the time
+   taken by each transaction to a logfile.  The logfile will be named
+   <filename>pgbench_log.<replaceable>nnn</></filename>, where
+   <replaceable>nnn</> is the PID of the pgbench process.
+   If the <literal>-j</> option is 2 or higher, creating multiple worker
+   threads, each will have its own log file. The first worker will use the
+   the same name for its log file as in the standard single worker case.  
+   The additional log files for the other workers will be named
+   <filename>pgbench_log.<replaceable>nnn</>.<replaceable>mmm</></filename>,
+   where <replaceable>mmm</> is a sequential number for each worker starting
+   with 1.
+  </para>
+
+  <para>
+   The format of the log is:
+
+   <programlisting>
+    <replaceable>client_id</> <replaceable>transaction_no</> <replaceable>time</> <replaceable>file_no</> <replaceable>time_epoch</> <replaceable>time_us</>
+   </programlisting>
+
+   where <replaceable>time</> is the elapsed transaction time in microseconds,
+   <replaceable>file_no</> identifies which script file was used
+   (useful when multiple scripts were specified with <literal>-f</>),
+   and <replaceable>time_epoch</>/<replaceable>time_us</> are a
+   UNIX epoch format timestamp and an offset
+   in microseconds (suitable for creating a ISO 8601
+   timestamp with fractional seconds) showing when
+   the transaction completed.
+  </para>
+
+  <para>
+   Here are example outputs:
+   <programlisting>
+ 0 199 2241 0 1175850568 995598
+ 0 200 2465 0 1175850568 998079
+ 0 201 2513 0 1175850569 608
+ 0 202 2038 0 1175850569 2663
+   </programlisting>
+  </para>
+ </sect2>
+
+ <sect2>
+  <title>Good Practices</title>
+
+  <para>
+   It is very easy to use <application>pgbench</> to produce completely
+   meaningless numbers.  Here are some guidelines to help you get useful
+   results.
+  </para>
+
+  <para>
+   In the first place, <emphasis>never</> believe any test that runs
+   for only a few seconds.  Use the <literal>-t</> or <literal>-T</> option
+   to make the run last at least a few minutes, so as to average out noise.
+   In some cases you could need hours to get numbers that are reproducible.
+   It's a good idea to try the test run a few times, to find out if your
+   numbers are reproducible or not.
+  </para>
+
+  <para>
+   For the default TPC-B-like test scenario, the initialization scale factor
+   (<literal>-s</>) should be at least as large as the largest number of
+   clients you intend to test (<literal>-c</>); else you'll mostly be
+   measuring update contention.  There are only <literal>-s</> rows in
+   the <structname>pgbench_branches</> table, and every transaction wants to
+   update one of them, so <literal>-c</> values in excess of <literal>-s</>
+   will undoubtedly result in lots of transactions blocked waiting for
+   other transactions.
+  </para>
+
+  <para>
+   The default test scenario is also quite sensitive to how long it's been
+   since the tables were initialized: accumulation of dead rows and dead space
+   in the tables changes the results.  To understand the results you must keep
+   track of the total number of updates and when vacuuming happens.  If
+   autovacuum is enabled it can result in unpredictable changes in measured
+   performance.
+  </para>
+
+  <para>
+   A limitation of <application>pgbench</> is that it can itself become
+   the bottleneck when trying to test a large number of client sessions.
+   This can be alleviated by running <application>pgbench</> on a different
+   machine from the database server, although low network latency will be
+   essential.  It might even be useful to run several <application>pgbench</>
+   instances concurrently, on several client machines, against the same
+   database server.
+  </para>
+ </sect2>
+
+</sect1>