diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/pgbench.sgml | 1270 |
1 files changed, 640 insertions, 630 deletions
diff --git a/doc/src/sgml/pgbench.sgml b/doc/src/sgml/pgbench.sgml index fc967b59a65..cb94e30c01c 100644 --- a/doc/src/sgml/pgbench.sgml +++ b/doc/src/sgml/pgbench.sgml @@ -1,630 +1,640 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/pgbench.sgml,v 1.12 2009/12/15 15:59:57 tgl 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 — - 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. - 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.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 —
+ 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>
|