doc/src/sgml/pgbench.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="pgbench">
   4  <title>pgbench</title>
   5
   6  <indexterm zone="pgbench">
   7   <primary>pgbench</primary>
   8  </indexterm>
   9
  10  <para>
  11   <application>pgbench</application> is a simple program for running benchmark
  12   tests on <productname>PostgreSQL</>.  It runs the same sequence of SQL
  13   commands over and over, possibly in multiple concurrent database sessions,
  14   and then calculates the average transaction rate (transactions per second).
  15   By default, <application>pgbench</application> tests a scenario that is
  16   loosely based on TPC-B, involving five <command>SELECT</>,
  17   <command>UPDATE</>, and <command>INSERT</> commands per transaction.
  18   However, it is easy to test other cases by writing your own transaction
  19   script files.
  20  </para>
  21
  22  <para>
  23   Typical output from pgbench looks like:
  24
  25  <programlisting>
  26 transaction type: TPC-B (sort of)
  27 scaling factor: 10
  28 query mode: simple
  29 number of clients: 10
  30 number of transactions per client: 1000
  31 number of transactions actually processed: 10000/10000
  32 tps = 85.184871 (including connections establishing)
  33 tps = 85.296346 (excluding connections establishing)
  34  </programlisting>
  35
  36   The first five lines report some of the most important parameter
  37   settings.  The next line reports the number of transactions completed
  38   and intended (the latter being just the product of number of clients
  39   and number of transactions per client); these will be equal unless the run
  40   failed before completion.  The last two lines report the TPS rate,
  41   figured with and without counting the time to start database sessions.
  42  </para>
  43
  44  <sect2>
  45   <title>Overview</title>
  46
  47   <para>
  48    The default TPC-B-like transaction test requires specific tables to be
  49    set up beforehand.  <application>pgbench</> should be invoked with
  50    the <literal>-i</> (initialize) option to create and populate these
  51    tables.  (When you are testing a custom script, you don't need this
  52    step, but will instead need to do whatever setup your test needs.)
  53    Initialization looks like:
  54
  55    <programlisting>
  56 pgbench -i <optional> <replaceable>other-options</> </optional> <replaceable>dbname</>
  57    </programlisting>
  58
  59    where <replaceable>dbname</> is the name of the already-created
  60    database to test in.  (You may also need <literal>-h</>,
  61    <literal>-p</>, and/or <literal>-U</> options to specify how to
  62    connect to the database server.)
  63   </para>
  64
  65   <caution>
  66    <para>
  67     <literal>pgbench -i</> creates four tables <structname>pgbench_accounts</>,
  68     <structname>pgbench_branches</>, <structname>pgbench_history</>, and
  69     <structname>pgbench_tellers</>,
  70     destroying any existing tables of these names.
  71     Be very careful to use another database if you have tables having these
  72     names!
  73    </para>
  74   </caution>
  75
  76   <para>
  77    At the default <quote>scale factor</> of 1, the tables initially
  78    contain this many rows:
  79   </para>
  80   <programlisting>
  81 table                   # of rows
  82 ---------------------------------
  83 pgbench_branches        1
  84 pgbench_tellers         10
  85 pgbench_accounts        100000
  86 pgbench_history         0
  87   </programlisting>
  88   <para>
  89    You can (and, for most purposes, probably should) increase the number
  90    of rows by using the <literal>-s</> (scale factor) option.  The
  91    <literal>-F</> (fillfactor) option might also be used at this point.
  92   </para>
  93
  94   <para>
  95    Once you have done the necessary setup, you can run your benchmark
  96    with a command that doesn't include <literal>-i</>, that is
  97
  98    <programlisting>
  99 pgbench <optional> <replaceable>options</> </optional> <replaceable>dbname</>
 100    </programlisting>
 101
 102    In nearly all cases, you'll need some options to make a useful test.
 103    The most important options are <literal>-c</> (number of clients),
 104    <literal>-t</> (number of transactions), <literal>-T</> (time limit),
 105    and <literal>-f</> (specify a custom script file).
 106    See below for a full list.
 107   </para>
 108
 109   <para>
 110    <xref linkend="pgbench-init-options"> shows options that are used
 111    during database initialization, while
 112    <xref linkend="pgbench-run-options"> shows options that are used
 113    while running benchmarks, and
 114    <xref linkend="pgbench-common-options"> shows options that are useful
 115    in both cases.
 116   </para>
 117
 118   <table id="pgbench-init-options">
 119    <title><application>pgbench</application> initialization options</title>
 120    <tgroup cols="2">
 121     <thead>
 122      <row>
 123       <entry>Option</entry>
 124       <entry>Description</entry>
 125      </row>
 126     </thead>
 127
 128     <tbody>
 129      <row>
 130       <entry><literal>-i</literal></entry>
 131       <entry>
 132        Required to invoke initialization mode.
 133       </entry>
 134      </row>
 135      <row>
 136       <entry><literal>-s</literal> <replaceable>scale_factor</></entry>
 137       <entry>
 138        Multiply the number of rows generated by the scale factor.
 139        For example, <literal>-s 100</> will create 10,000,000 rows
 140        in the <structname>pgbench_accounts</> table. Default is 1.
 141       </entry>
 142      </row>
 143      <row>
 144       <entry><literal>-F</literal> <replaceable>fillfactor</></entry>
 145       <entry>
 146        Create the <structname>pgbench_accounts</>,
 147        <structname>pgbench_tellers</> and
 148        <structname>pgbench_branches</> tables with the given fillfactor.
 149        Default is 100.
 150       </entry>
 151      </row>
 152     </tbody>
 153    </tgroup>
 154   </table>
 155
 156   <table id="pgbench-run-options">
 157    <title><application>pgbench</application> benchmarking options</title>
 158    <tgroup cols="2">
 159     <thead>
 160      <row>
 161       <entry>Option</entry>
 162       <entry>Description</entry>
 163      </row>
 164     </thead>
 165
 166     <tbody>
 167      <row>
 168       <entry><literal>-c</literal> <replaceable>clients</></entry>
 169       <entry>
 170        Number of clients simulated, that is, number of concurrent database
 171        sessions.  Default is 1.
 172       </entry>
 173      </row>
 174      <row>
 175       <entry><literal>-t</literal> <replaceable>transactions</></entry>
 176       <entry>
 177        Number of transactions each client runs.  Default is 10.
 178       </entry>
 179      </row>
 180      <row>
 181       <entry><literal>-T</literal> <replaceable>seconds</></entry>
 182       <entry>
 183        Run the test for this many seconds, rather than a fixed number of
 184        transactions per client. <literal>-t</literal> and
 185        <literal>-T</literal> are mutually exclusive.
 186       </entry>
 187      </row>
 188      <row>
 189       <entry><literal>-M</literal> <replaceable>querymode</></entry>
 190       <entry>
 191        Protocol to use for submitting queries to the server:
 192          <itemizedlist>
 193           <listitem>
 194            <para><literal>simple</>: use simple query protocol.</para>
 195           </listitem>
 196           <listitem>
 197            <para><literal>extended</>: use extended query protocol.</para>
 198           </listitem>
 199           <listitem>
 200            <para><literal>prepared</>: use extended query protocol with prepared statements.</para>
 201           </listitem>
 202          </itemizedlist>
 203        The default is simple query protocol.  (See <xref linkend="protocol">
 204        for more information.)
 205       </entry>
 206      </row>
 207      <row>
 208       <entry><literal>-N</literal></entry>
 209       <entry>
 210        Do not update <structname>pgbench_tellers</> and
 211        <structname>pgbench_branches</>.
 212        This will avoid update contention on these tables, but
 213        it makes the test case even less like TPC-B.
 214       </entry>
 215      </row>
 216      <row>
 217       <entry><literal>-S</literal></entry>
 218       <entry>
 219        Perform select-only transactions instead of TPC-B-like test.
 220       </entry>
 221      </row>
 222      <row>
 223       <entry><literal>-f</literal> <replaceable>filename</></entry>
 224       <entry>
 225        Read transaction script from <replaceable>filename</>.
 226        See below for details.
 227        <literal>-N</literal>, <literal>-S</literal>, and <literal>-f</literal>
 228        are mutually exclusive.
 229       </entry>
 230      </row>
 231      <row>
 232       <entry><literal>-n</literal></entry>
 233       <entry>
 234        Perform no vacuuming before running the test.
 235        This option is <emphasis>necessary</>
 236        if you are running a custom test scenario that does not include
 237        the standard tables <structname>pgbench_accounts</>,
 238        <structname>pgbench_branches</>, <structname>pgbench_history</>, and
 239        <structname>pgbench_tellers</>.
 240       </entry>
 241      </row>
 242      <row>
 243       <entry><literal>-v</literal></entry>
 244       <entry>
 245        Vacuum all four standard tables before running the test.
 246        With neither <literal>-n</> nor <literal>-v</>, pgbench will vacuum the
 247        <structname>pgbench_tellers</> and <structname>pgbench_branches</>
 248        tables, and will truncate <structname>pgbench_history</>.
 249       </entry>
 250      </row>
 251      <row>
 252       <entry><literal>-D</literal> <replaceable>varname</><literal>=</><replaceable>value</></entry>
 253       <entry>
 254        Define a variable for use by a custom script (see below).
 255        Multiple <literal>-D</> options are allowed.
 256       </entry>
 257      </row>
 258      <row>
 259       <entry><literal>-C</literal></entry>
 260       <entry>
 261        Establish a new connection for each transaction, rather than
 262        doing it just once per client thread.
 263        This is useful to measure the connection overhead.
 264       </entry>
 265      </row>
 266      <row>
 267       <entry><literal>-l</literal></entry>
 268       <entry>
 269        Write the time taken by each transaction to a logfile.
 270        See below for details.
 271       </entry>
 272      </row>
 273      <row>
 274       <entry><literal>-s</literal> <replaceable>scale_factor</></entry>
 275       <entry>
 276        Report the specified scale factor in <application>pgbench</>'s
 277        output.  With the built-in tests, this is not necessary; the
 278        correct scale factor will be detected by counting the number of
 279        rows in the <structname>pgbench_branches</> table.  However, when testing
 280        custom benchmarks (<literal>-f</> option), the scale factor
 281        will be reported as 1 unless this option is used.
 282       </entry>
 283      </row>
 284      <row>
 285       <entry><literal>-d</literal></entry>
 286       <entry>
 287        Print debugging output.
 288       </entry>
 289      </row>
 290     </tbody>
 291    </tgroup>
 292   </table>
 293
 294   <table id="pgbench-common-options">
 295    <title><application>pgbench</application> common options</title>
 296    <tgroup cols="2">
 297     <thead>
 298      <row>
 299       <entry>Option</entry>
 300       <entry>Description</entry>
 301      </row>
 302     </thead>
 303
 304     <tbody>
 305      <row>
 306       <entry><literal>-h</literal> <replaceable>hostname</></entry>
 307       <entry>database server's host</entry>
 308      </row>
 309      <row>
 310       <entry><literal>-p</literal> <replaceable>port</></entry>
 311       <entry>database server's port</entry>
 312      </row>
 313      <row>
 314       <entry><literal>-U</literal> <replaceable>login</></entry>
 315       <entry>username to connect as</entry>
 316      </row>
 317     </tbody>
 318    </tgroup>
 319   </table>
 320  </sect2>
 321
 322  <sect2>
 323   <title>What is the <quote>transaction</> actually performed in pgbench?</title>
 324
 325   <para>
 326    The default transaction script issues seven commands per transaction:
 327   </para>
 328
 329   <orderedlist>
 330    <listitem><para><literal>BEGIN;</literal></para></listitem>
 331    <listitem><para><literal>UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;</literal></para></listitem>
 332    <listitem><para><literal>SELECT abalance FROM pgbench_accounts WHERE aid = :aid;</literal></para></listitem>
 333    <listitem><para><literal>UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;</literal></para></listitem>
 334    <listitem><para><literal>UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;</literal></para></listitem>
 335    <listitem><para><literal>INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);</literal></para></listitem>
 336    <listitem><para><literal>END;</literal></para></listitem>
 337   </orderedlist>
 338
 339   <para>
 340    If you specify <literal>-N</>, steps 4 and 5 aren't included in the
 341    transaction.  If you specify <literal>-S</>, only the <command>SELECT</> is
 342    issued.
 343   </para>
 344  </sect2>
 345
 346  <sect2>
 347   <title>Custom Scripts</title>
 348
 349   <para>
 350    <application>pgbench</application> has support for running custom
 351    benchmark scenarios by replacing the default transaction script
 352    (described above) with a transaction script read from a file
 353    (<literal>-f</literal> option).  In this case a <quote>transaction</>
 354    counts as one execution of a script file.  You can even specify
 355    multiple scripts (multiple <literal>-f</literal> options), in which
 356    case a random one of the scripts is chosen each time a client session
 357    starts a new transaction.
 358   </para>
 359
 360   <para>
 361    The format of a script file is one SQL command per line; multi-line
 362    SQL commands are not supported.  Empty lines and lines beginning with
 363    <literal>--</> are ignored.  Script file lines can also be
 364    <quote>meta commands</>, which are interpreted by <application>pgbench</>
 365    itself, as described below.
 366   </para>
 367
 368   <para>
 369    There is a simple variable-substitution facility for script files.
 370    Variables can be set by the command-line <literal>-D</> option,
 371    explained above, or by the meta commands explained below.
 372    In addition to any variables preset by <literal>-D</> command-line options,
 373    the variable <literal>scale</> is preset to the current scale factor.
 374    Once set, a variable's
 375    value can be inserted into a SQL command by writing
 376    <literal>:</><replaceable>variablename</>.  When running more than
 377    one client session, each session has its own set of variables.
 378   </para>
 379
 380   <para>
 381    Script file meta commands begin with a backslash (<literal>\</>).
 382    Arguments to a meta command are separated by white space.
 383    These meta commands are supported:
 384   </para>
 385
 386   <variablelist>
 387    <varlistentry>
 388     <term>
 389      <literal>\set <replaceable>varname</> <replaceable>operand1</> [ <replaceable>operator</> <replaceable>operand2</> ]</literal>
 390     </term>
 391
 392     <listitem>
 393      <para>
 394       Sets variable <replaceable>varname</> to a calculated integer value.
 395       Each <replaceable>operand</> is either an integer constant or a
 396       <literal>:</><replaceable>variablename</> reference to a variable
 397       having an integer value.  The <replaceable>operator</> can be
 398       <literal>+</>, <literal>-</>, <literal>*</>, or <literal>/</>.
 399      </para>
 400
 401      <para>
 402       Example:
 403       <programlisting>
 404 \set ntellers 10 * :scale
 405       </programlisting>
 406      </para>
 407     </listitem>
 408    </varlistentry>
 409
 410    <varlistentry>
 411     <term>
 412      <literal>\setrandom <replaceable>varname</> <replaceable>min</> <replaceable>max</></literal>
 413     </term>
 414
 415     <listitem>
 416      <para>
 417       Sets variable <replaceable>varname</> to a random integer value
 418       between the limits <replaceable>min</> and <replaceable>max</> inclusive.
 419       Each limit can be either an integer constant or a
 420       <literal>:</><replaceable>variablename</> reference to a variable
 421       having an integer value.
 422      </para>
 423
 424      <para>
 425       Example:
 426       <programlisting>
 427 \setrandom aid 1 :naccounts
 428       </programlisting>
 429      </para>
 430     </listitem>
 431    </varlistentry>
 432
 433    <varlistentry>
 434     <term>
 435      <literal>\sleep <replaceable>number</> [ us | ms | s ]</literal>
 436     </term>
 437
 438     <listitem>
 439      <para>
 440       Causes script execution to sleep for the specified duration in
 441       microseconds (<literal>us</>), milliseconds (<literal>ms</>) or seconds
 442       (<literal>s</>).  If the unit is omitted then seconds are the default.
 443       <replaceable>number</> can be either an integer constant or a
 444       <literal>:</><replaceable>variablename</> reference to a variable
 445       having an integer value.
 446      </para>
 447
 448      <para>
 449       Example:
 450       <programlisting>
 451 \sleep 10 ms
 452       </programlisting>
 453      </para>
 454     </listitem>
 455    </varlistentry>
 456   </variablelist>
 457
 458   <para>
 459    As an example, the full definition of the built-in TPC-B-like
 460    transaction is:
 461
 462    <programlisting>
 463 \set nbranches :scale
 464 \set ntellers 10 * :scale
 465 \set naccounts 100000 * :scale
 466 \setrandom aid 1 :naccounts
 467 \setrandom bid 1 :nbranches
 468 \setrandom tid 1 :ntellers
 469 \setrandom delta -5000 5000
 470 BEGIN;
 471 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
 472 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
 473 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
 474 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
 475 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
 476 END;
 477    </programlisting>
 478
 479    This script allows each iteration of the transaction to reference
 480    different, randomly-chosen rows.  (This example also shows why it's
 481    important for each client session to have its own variables &mdash;
 482    otherwise they'd not be independently touching different rows.)
 483   </para>
 484
 485  </sect2>
 486
 487  <sect2>
 488   <title>Per-transaction logging</title>
 489
 490   <para>
 491    With the <literal>-l</> option, <application>pgbench</> writes the time
 492    taken by each transaction to a logfile.  The logfile will be named
 493    <filename>pgbench_log.<replaceable>nnn</></filename>, where
 494    <replaceable>nnn</> is the PID of the pgbench process.
 495    The format of the log is:
 496
 497    <programlisting>
 498     <replaceable>client_id</> <replaceable>transaction_no</> <replaceable>time</> <replaceable>file_no</> <replaceable>time_epoch</> <replaceable>time_us</>
 499    </programlisting>
 500
 501    where <replaceable>time</> is the elapsed transaction time in microseconds,
 502    <replaceable>file_no</> identifies which script file was used
 503    (useful when multiple scripts were specified with <literal>-f</>),
 504    and <replaceable>time_epoch</>/<replaceable>time_us</> are a
 505    UNIX epoch format timestamp and an offset
 506    in microseconds (suitable for creating a ISO 8601
 507    timestamp with fractional seconds) showing when
 508    the transaction completed.
 509   </para>
 510
 511   <para>
 512    Here are example outputs:
 513    <programlisting>
 514  0 199 2241 0 1175850568 995598
 515  0 200 2465 0 1175850568 998079
 516  0 201 2513 0 1175850569 608
 517  0 202 2038 0 1175850569 2663
 518    </programlisting>
 519   </para>
 520  </sect2>
 521
 522  <sect2>
 523   <title>Good Practices</title>
 524
 525   <para>
 526    It is very easy to use <application>pgbench</> to produce completely
 527    meaningless numbers.  Here are some guidelines to help you get useful
 528    results.
 529   </para>
 530
 531   <para>
 532    In the first place, <emphasis>never</> believe any test that runs
 533    for only a few seconds.  Use the <literal>-t</> or <literal>-T</> option
 534    to make the run last at least a few minutes, so as to average out noise.
 535    In some cases you could need hours to get numbers that are reproducible.
 536    It's a good idea to try the test run a few times, to find out if your
 537    numbers are reproducible or not.
 538   </para>
 539
 540   <para>
 541    For the default TPC-B-like test scenario, the initialization scale factor
 542    (<literal>-s</>) should be at least as large as the largest number of
 543    clients you intend to test (<literal>-c</>); else you'll mostly be
 544    measuring update contention.  There are only <literal>-s</> rows in
 545    the <structname>pgbench_branches</> table, and every transaction wants to
 546    update one of them, so <literal>-c</> values in excess of <literal>-s</>
 547    will undoubtedly result in lots of transactions blocked waiting for
 548    other transactions.
 549   </para>
 550
 551   <para>
 552    The default test scenario is also quite sensitive to how long it's been
 553    since the tables were initialized: accumulation of dead rows and dead space
 554    in the tables changes the results.  To understand the results you must keep
 555    track of the total number of updates and when vacuuming happens.  If
 556    autovacuum is enabled it can result in unpredictable changes in measured
 557    performance.
 558   </para>
 559
 560   <para>
 561    A limitation of <application>pgbench</> is that it can itself become
 562    the bottleneck when trying to test a large number of client sessions.
 563    This can be alleviated by running <application>pgbench</> on a different
 564    machine from the database server, although low network latency will be
 565    essential.  It might even be useful to run several <application>pgbench</>
 566    instances concurrently, on several client machines, against the same
 567    database server.
 568   </para>
 569  </sect2>
 570
 571 </sect1>