diff options
Diffstat (limited to 'doc/src/sgml/html/pgbench.html')
| -rw-r--r-- | doc/src/sgml/html/pgbench.html | 1721 |
1 files changed, 1721 insertions, 0 deletions
diff --git a/doc/src/sgml/html/pgbench.html b/doc/src/sgml/html/pgbench.html new file mode 100644 index 0000000..a7386e0 --- /dev/null +++ b/doc/src/sgml/html/pgbench.html @@ -0,0 +1,1721 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>pgbench</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="app-pgbasebackup.html" title="pg_basebackup" /><link rel="next" href="app-pgconfig.html" title="pg_config" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center"><span class="application">pgbench</span></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="app-pgbasebackup.html" title="pg_basebackup">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="reference-client.html" title="PostgreSQL Client Applications">Up</a></td><th width="60%" align="center">PostgreSQL Client Applications</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="app-pgconfig.html" title="pg_config">Next</a></td></tr></table><hr /></div><div class="refentry" id="PGBENCH"><div class="titlepage"></div><a id="id-1.9.4.11.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle"><span class="application">pgbench</span></span></h2><p>pgbench — run a benchmark test on <span class="productname">PostgreSQL</span></p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><div class="cmdsynopsis"><p id="id-1.9.4.11.4.1"><code class="command">pgbench</code> <code class="option">-i</code> [<em class="replaceable"><code>option</code></em>...] [<em class="replaceable"><code>dbname</code></em>]</p></div><div class="cmdsynopsis"><p id="id-1.9.4.11.4.2"><code class="command">pgbench</code> [<em class="replaceable"><code>option</code></em>...] [<em class="replaceable"><code>dbname</code></em>]</p></div></div><div class="refsect1" id="id-1.9.4.11.5"><h2>Description</h2><p> + <span class="application">pgbench</span> is a simple program for running benchmark + tests on <span class="productname">PostgreSQL</span>. 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, <span class="application">pgbench</span> tests a scenario that is + loosely based on TPC-B, involving five <code class="command">SELECT</code>, + <code class="command">UPDATE</code>, and <code class="command">INSERT</code> commands per transaction. + However, it is easy to test other cases by writing your own transaction + script files. + </p><p> + Typical output from <span class="application">pgbench</span> looks like: + +</p><pre class="screen"> +transaction type: <builtin: TPC-B (sort of)> +scaling factor: 10 +query mode: simple +number of clients: 10 +number of threads: 1 +maximum number of tries: 1 +number of transactions per client: 1000 +number of transactions actually processed: 10000/10000 +number of failed transactions: 0 (0.000%) +latency average = 11.013 ms +latency stddev = 7.351 ms +initial connection time = 45.758 ms +tps = 896.967014 (without initial connection time) +</pre><p> + + The first seven lines report some of the most important parameter + settings. + The sixth line reports the maximum number of tries for transactions with + serialization or deadlock errors (see <a class="xref" href="pgbench.html#FAILURES-AND-RETRIES" title="Failures and Serialization/Deadlock Retries">Failures and Serialization/Deadlock Retries</a> + for more information). + The eighth 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 or some SQL command(s) failed. (In + <code class="option">-T</code> mode, only the actual number of transactions is printed.) + The next line reports the number of failed transactions due to + serialization or deadlock errors (see <a class="xref" href="pgbench.html#FAILURES-AND-RETRIES" title="Failures and Serialization/Deadlock Retries">Failures and Serialization/Deadlock Retries</a> + for more information). + The last line reports the number of transactions per second. + </p><p> + The default TPC-B-like transaction test requires specific tables to be + set up beforehand. <span class="application">pgbench</span> should be invoked with + the <code class="option">-i</code> (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: + +</p><pre class="programlisting"> +pgbench -i [<span class="optional"> <em class="replaceable"><code>other-options</code></em> </span>] <em class="replaceable"><code>dbname</code></em> +</pre><p> + + where <em class="replaceable"><code>dbname</code></em> is the name of the already-created + database to test in. (You may also need <code class="option">-h</code>, + <code class="option">-p</code>, and/or <code class="option">-U</code> options to specify how to + connect to the database server.) + </p><div class="caution"><h3 class="title">Caution</h3><p> + <code class="literal">pgbench -i</code> creates four tables <code class="structname">pgbench_accounts</code>, + <code class="structname">pgbench_branches</code>, <code class="structname">pgbench_history</code>, and + <code class="structname">pgbench_tellers</code>, + destroying any existing tables of these names. + Be very careful to use another database if you have tables having these + names! + </p></div><p> + At the default <span class="quote">“<span class="quote">scale factor</span>”</span> of 1, the tables initially + contain this many rows: +</p><pre class="screen"> +table # of rows +--------------------------------- +pgbench_branches 1 +pgbench_tellers 10 +pgbench_accounts 100000 +pgbench_history 0 +</pre><p> + You can (and, for most purposes, probably should) increase the number + of rows by using the <code class="option">-s</code> (scale factor) option. The + <code class="option">-F</code> (fillfactor) option might also be used at this point. + </p><p> + Once you have done the necessary setup, you can run your benchmark + with a command that doesn't include <code class="option">-i</code>, that is + +</p><pre class="programlisting"> +pgbench [<span class="optional"> <em class="replaceable"><code>options</code></em> </span>] <em class="replaceable"><code>dbname</code></em> +</pre><p> + + In nearly all cases, you'll need some options to make a useful test. + The most important options are <code class="option">-c</code> (number of clients), + <code class="option">-t</code> (number of transactions), <code class="option">-T</code> (time limit), + and <code class="option">-f</code> (specify a custom script file). + See below for a full list. + </p></div><div class="refsect1" id="id-1.9.4.11.6"><h2>Options</h2><p> + The following is divided into three subsections. Different options are + used during database initialization and while running benchmarks, but some + options are useful in both cases. + </p><div class="refsect2" id="PGBENCH-INIT-OPTIONS"><h3>Initialization Options</h3><p> + <span class="application">pgbench</span> accepts the following command-line + initialization arguments: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>dbname</code></em></span></dt><dd><p> + Specifies the name of the database to test in. If this is + not specified, the environment variable + <code class="envar">PGDATABASE</code> is used. If that is not set, the + user name specified for the connection is used. + </p></dd><dt><span class="term"><code class="option">-i</code><br /></span><span class="term"><code class="option">--initialize</code></span></dt><dd><p> + Required to invoke initialization mode. + </p></dd><dt><span class="term"><code class="option">-I <em class="replaceable"><code>init_steps</code></em></code><br /></span><span class="term"><code class="option">--init-steps=<em class="replaceable"><code>init_steps</code></em></code></span></dt><dd><p> + Perform just a selected set of the normal initialization steps. + <em class="replaceable"><code>init_steps</code></em> specifies the + initialization steps to be performed, using one character per step. + Each step is invoked in the specified order. + The default is <code class="literal">dtgvp</code>. + The available steps are: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">d</code> (Drop)</span></dt><dd><p> + Drop any existing <span class="application">pgbench</span> tables. + </p></dd><dt><span class="term"><code class="literal">t</code> (create Tables)</span></dt><dd><p> + Create the tables used by the + standard <span class="application">pgbench</span> scenario, namely + <code class="structname">pgbench_accounts</code>, + <code class="structname">pgbench_branches</code>, + <code class="structname">pgbench_history</code>, and + <code class="structname">pgbench_tellers</code>. + </p></dd><dt><span class="term"><code class="literal">g</code> or <code class="literal">G</code> (Generate data, client-side or server-side)</span></dt><dd><p> + Generate data and load it into the standard tables, + replacing any data already present. + </p><p> + With <code class="literal">g</code> (client-side data generation), + data is generated in <code class="command">pgbench</code> client and then + sent to the server. This uses the client/server bandwidth + extensively through a <code class="command">COPY</code>. + <code class="command">pgbench</code> uses the FREEZE option with version 14 or later + of <span class="productname">PostgreSQL</span> to speed up + subsequent <code class="command">VACUUM</code>, unless partitions are enabled. + Using <code class="literal">g</code> causes logging to print one message + every 100,000 rows while generating data for the + <code class="structname">pgbench_accounts</code> table. + </p><p> + With <code class="literal">G</code> (server-side data generation), + only small queries are sent from the <code class="command">pgbench</code> + client and then data is actually generated in the server. + No significant bandwidth is required for this variant, but + the server will do more work. + Using <code class="literal">G</code> causes logging not to print any progress + message while generating data. + </p><p> + The default initialization behavior uses client-side data + generation (equivalent to <code class="literal">g</code>). + </p></dd><dt><span class="term"><code class="literal">v</code> (Vacuum)</span></dt><dd><p> + Invoke <code class="command">VACUUM</code> on the standard tables. + </p></dd><dt><span class="term"><code class="literal">p</code> (create Primary keys)</span></dt><dd><p> + Create primary key indexes on the standard tables. + </p></dd><dt><span class="term"><code class="literal">f</code> (create Foreign keys)</span></dt><dd><p> + Create foreign key constraints between the standard tables. + (Note that this step is not performed by default.) + </p></dd></dl></div></dd><dt><span class="term"><code class="option">-F</code> <em class="replaceable"><code>fillfactor</code></em><br /></span><span class="term"><code class="option">--fillfactor=</code><em class="replaceable"><code>fillfactor</code></em></span></dt><dd><p> + Create the <code class="structname">pgbench_accounts</code>, + <code class="structname">pgbench_tellers</code> and + <code class="structname">pgbench_branches</code> tables with the given fillfactor. + Default is 100. + </p></dd><dt><span class="term"><code class="option">-n</code><br /></span><span class="term"><code class="option">--no-vacuum</code></span></dt><dd><p> + Perform no vacuuming during initialization. + (This option suppresses the <code class="literal">v</code> initialization step, + even if it was specified in <code class="option">-I</code>.) + </p></dd><dt><span class="term"><code class="option">-q</code><br /></span><span class="term"><code class="option">--quiet</code></span></dt><dd><p> + Switch logging to quiet mode, producing only one progress message per 5 + seconds. The default logging prints one message each 100,000 rows, which + often outputs many lines per second (especially on good hardware). + </p><p> + This setting has no effect if <code class="literal">G</code> is specified + in <code class="option">-I</code>. + </p></dd><dt><span class="term"><code class="option">-s</code> <em class="replaceable"><code>scale_factor</code></em><br /></span><span class="term"><code class="option">--scale=</code><em class="replaceable"><code>scale_factor</code></em></span></dt><dd><p> + Multiply the number of rows generated by the scale factor. + For example, <code class="literal">-s 100</code> will create 10,000,000 rows + in the <code class="structname">pgbench_accounts</code> table. Default is 1. + When the scale is 20,000 or larger, the columns used to + hold account identifiers (<code class="structfield">aid</code> columns) + will switch to using larger integers (<code class="type">bigint</code>), + in order to be big enough to hold the range of account + identifiers. + </p></dd><dt><span class="term"><code class="option">--foreign-keys</code></span></dt><dd><p> + Create foreign key constraints between the standard tables. + (This option adds the <code class="literal">f</code> step to the initialization + step sequence, if it is not already present.) + </p></dd><dt><span class="term"><code class="option">--index-tablespace=<em class="replaceable"><code>index_tablespace</code></em></code></span></dt><dd><p> + Create indexes in the specified tablespace, rather than the default + tablespace. + </p></dd><dt><span class="term"><code class="option">--partition-method=<em class="replaceable"><code>NAME</code></em></code></span></dt><dd><p> + Create a partitioned <code class="literal">pgbench_accounts</code> table with + <em class="replaceable"><code>NAME</code></em> method. + Expected values are <code class="literal">range</code> or <code class="literal">hash</code>. + This option requires that <code class="option">--partitions</code> is set to non-zero. + If unspecified, default is <code class="literal">range</code>. + </p></dd><dt><span class="term"><code class="option">--partitions=<em class="replaceable"><code>NUM</code></em></code></span></dt><dd><p> + Create a partitioned <code class="literal">pgbench_accounts</code> table with + <em class="replaceable"><code>NUM</code></em> partitions of nearly equal size for + the scaled number of accounts. + Default is <code class="literal">0</code>, meaning no partitioning. + </p></dd><dt><span class="term"><code class="option">--tablespace=<em class="replaceable"><code>tablespace</code></em></code></span></dt><dd><p> + Create tables in the specified tablespace, rather than the default + tablespace. + </p></dd><dt><span class="term"><code class="option">--unlogged-tables</code></span></dt><dd><p> + Create all tables as unlogged tables, rather than permanent tables. + </p></dd></dl></div><p> + </p></div><div class="refsect2" id="PGBENCH-RUN-OPTIONS"><h3>Benchmarking Options</h3><p> + <span class="application">pgbench</span> accepts the following command-line + benchmarking arguments: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">-b</code> <em class="replaceable"><code>scriptname[@weight]</code></em><br /></span><span class="term"><code class="option">--builtin</code>=<em class="replaceable"><code>scriptname[@weight]</code></em></span></dt><dd><p> + Add the specified built-in script to the list of scripts to be executed. + Available built-in scripts are: <code class="literal">tpcb-like</code>, + <code class="literal">simple-update</code> and <code class="literal">select-only</code>. + Unambiguous prefixes of built-in names are accepted. + With the special name <code class="literal">list</code>, show the list of built-in scripts + and exit immediately. + </p><p> + Optionally, write an integer weight after <code class="literal">@</code> to + adjust the probability of selecting this script versus other ones. + The default weight is 1. + See below for details. + </p></dd><dt><span class="term"><code class="option">-c</code> <em class="replaceable"><code>clients</code></em><br /></span><span class="term"><code class="option">--client=</code><em class="replaceable"><code>clients</code></em></span></dt><dd><p> + Number of clients simulated, that is, number of concurrent database + sessions. Default is 1. + </p></dd><dt><span class="term"><code class="option">-C</code><br /></span><span class="term"><code class="option">--connect</code></span></dt><dd><p> + Establish a new connection for each transaction, rather than + doing it just once per client session. + This is useful to measure the connection overhead. + </p></dd><dt><span class="term"><code class="option">-d</code><br /></span><span class="term"><code class="option">--debug</code></span></dt><dd><p> + Print debugging output. + </p></dd><dt><span class="term"><code class="option">-D</code> <em class="replaceable"><code>varname</code></em><code class="literal">=</code><em class="replaceable"><code>value</code></em><br /></span><span class="term"><code class="option">--define=</code><em class="replaceable"><code>varname</code></em><code class="literal">=</code><em class="replaceable"><code>value</code></em></span></dt><dd><p> + Define a variable for use by a custom script (see below). + Multiple <code class="option">-D</code> options are allowed. + </p></dd><dt><span class="term"><code class="option">-f</code> <em class="replaceable"><code>filename[@weight]</code></em><br /></span><span class="term"><code class="option">--file=</code><em class="replaceable"><code>filename[@weight]</code></em></span></dt><dd><p> + Add a transaction script read from <em class="replaceable"><code>filename</code></em> + to the list of scripts to be executed. + </p><p> + Optionally, write an integer weight after <code class="literal">@</code> to + adjust the probability of selecting this script versus other ones. + The default weight is 1. + (To use a script file name that includes an <code class="literal">@</code> + character, append a weight so that there is no ambiguity, for + example <code class="literal">filen@me@1</code>.) + See below for details. + </p></dd><dt><span class="term"><code class="option">-j</code> <em class="replaceable"><code>threads</code></em><br /></span><span class="term"><code class="option">--jobs=</code><em class="replaceable"><code>threads</code></em></span></dt><dd><p> + Number of worker threads within <span class="application">pgbench</span>. + Using more than one thread can be helpful on multi-CPU machines. + Clients are distributed as evenly as possible among available threads. + Default is 1. + </p></dd><dt><span class="term"><code class="option">-l</code><br /></span><span class="term"><code class="option">--log</code></span></dt><dd><p> + Write information about each transaction to a log file. + See below for details. + </p></dd><dt><span class="term"><code class="option">-L</code> <em class="replaceable"><code>limit</code></em><br /></span><span class="term"><code class="option">--latency-limit=</code><em class="replaceable"><code>limit</code></em></span></dt><dd><p> + Transactions that last more than <em class="replaceable"><code>limit</code></em> milliseconds + are counted and reported separately, as <em class="firstterm">late</em>. + </p><p> + When throttling is used (<code class="option">--rate=...</code>), transactions that + lag behind schedule by more than <em class="replaceable"><code>limit</code></em> ms, and thus + have no hope of meeting the latency limit, are not sent to the server + at all. They are counted and reported separately as + <em class="firstterm">skipped</em>. + </p><p> + When the <code class="option">--max-tries</code> option is used, a transaction + which fails due to a serialization anomaly or from a deadlock will not + be retried if the total time of all its tries is greater than + <em class="replaceable"><code>limit</code></em> ms. To limit only the time of tries + and not their number, use <code class="literal">--max-tries=0</code>. By + default, the option <code class="option">--max-tries</code> is set to 1 and + transactions with serialization/deadlock errors are not retried. See + <a class="xref" href="pgbench.html#FAILURES-AND-RETRIES" title="Failures and Serialization/Deadlock Retries">Failures and Serialization/Deadlock Retries</a> for more information about + retrying such transactions. + </p></dd><dt><span class="term"><code class="option">-M</code> <em class="replaceable"><code>querymode</code></em><br /></span><span class="term"><code class="option">--protocol=</code><em class="replaceable"><code>querymode</code></em></span></dt><dd><p> + Protocol to use for submitting queries to the server: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">simple</code>: use simple query protocol.</p></li><li class="listitem"><p><code class="literal">extended</code>: use extended query protocol.</p></li><li class="listitem"><p><code class="literal">prepared</code>: use extended query protocol with prepared statements.</p></li></ul></div><p> + + In the <code class="literal">prepared</code> mode, <span class="application">pgbench</span> + reuses the parse analysis result starting from the second query + iteration, so <span class="application">pgbench</span> runs faster + than in other modes. + </p><p> + The default is simple query protocol. (See <a class="xref" href="protocol.html" title="Chapter 55. Frontend/Backend Protocol">Chapter 55</a> + for more information.) + </p></dd><dt><span class="term"><code class="option">-n</code><br /></span><span class="term"><code class="option">--no-vacuum</code></span></dt><dd><p> + Perform no vacuuming before running the test. + This option is <span class="emphasis"><em>necessary</em></span> + if you are running a custom test scenario that does not include + the standard tables <code class="structname">pgbench_accounts</code>, + <code class="structname">pgbench_branches</code>, <code class="structname">pgbench_history</code>, and + <code class="structname">pgbench_tellers</code>. + </p></dd><dt><span class="term"><code class="option">-N</code><br /></span><span class="term"><code class="option">--skip-some-updates</code></span></dt><dd><p> + Run built-in simple-update script. + Shorthand for <code class="option">-b simple-update</code>. + </p></dd><dt><span class="term"><code class="option">-P</code> <em class="replaceable"><code>sec</code></em><br /></span><span class="term"><code class="option">--progress=</code><em class="replaceable"><code>sec</code></em></span></dt><dd><p> + Show progress report every <em class="replaceable"><code>sec</code></em> seconds. The report + includes the time since the beginning of the run, the TPS since the + last report, and the transaction latency average, standard deviation, + and the number of failed transactions since the last report. Under + throttling (<code class="option">-R</code>), the latency is computed with respect + to the transaction scheduled start time, not the actual transaction + beginning time, thus it also includes the average schedule lag time. + When <code class="option">--max-tries</code> is used to enable transaction retries + after serialization/deadlock errors, the report includes the number of + retried transactions and the sum of all retries. + </p></dd><dt><span class="term"><code class="option">-r</code><br /></span><span class="term"><code class="option">--report-per-command</code></span></dt><dd><p> + Report the following statistics for each command after the benchmark + finishes: the average per-statement latency (execution time from the + perspective of the client), the number of failures, and the number of + retries after serialization or deadlock errors in this command. The + report displays retry statistics only if the + <code class="option">--max-tries</code> option is not equal to 1. + </p></dd><dt><span class="term"><code class="option">-R</code> <em class="replaceable"><code>rate</code></em><br /></span><span class="term"><code class="option">--rate=</code><em class="replaceable"><code>rate</code></em></span></dt><dd><p> + Execute transactions targeting the specified rate instead of running + as fast as possible (the default). The rate is given in transactions + per second. If the targeted rate is above the maximum possible rate, + the rate limit won't impact the results. + </p><p> + The rate is targeted by starting transactions along a + Poisson-distributed schedule time line. The expected start time + schedule moves forward based on when the client first started, not + when the previous transaction ended. That approach means that when + transactions go past their original scheduled end time, it is + possible for later ones to catch up again. + </p><p> + When throttling is active, the transaction latency reported at the + end of the run is calculated from the scheduled start times, so it + includes the time each transaction had to wait for the previous + transaction to finish. The wait time is called the schedule lag time, + and its average and maximum are also reported separately. The + transaction latency with respect to the actual transaction start time, + i.e., the time spent executing the transaction in the database, can be + computed by subtracting the schedule lag time from the reported + latency. + </p><p> + If <code class="option">--latency-limit</code> is used together with <code class="option">--rate</code>, + a transaction can lag behind so much that it is already over the + latency limit when the previous transaction ends, because the latency + is calculated from the scheduled start time. Such transactions are + not sent to the server, but are skipped altogether and counted + separately. + </p><p> + A high schedule lag time is an indication that the system cannot + process transactions at the specified rate, with the chosen number of + clients and threads. When the average transaction execution time is + longer than the scheduled interval between each transaction, each + successive transaction will fall further behind, and the schedule lag + time will keep increasing the longer the test run is. When that + happens, you will have to reduce the specified transaction rate. + </p></dd><dt><span class="term"><code class="option">-s</code> <em class="replaceable"><code>scale_factor</code></em><br /></span><span class="term"><code class="option">--scale=</code><em class="replaceable"><code>scale_factor</code></em></span></dt><dd><p> + Report the specified scale factor in <span class="application">pgbench</span>'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 <code class="structname">pgbench_branches</code> table. + However, when testing only custom benchmarks (<code class="option">-f</code> option), + the scale factor will be reported as 1 unless this option is used. + </p></dd><dt><span class="term"><code class="option">-S</code><br /></span><span class="term"><code class="option">--select-only</code></span></dt><dd><p> + Run built-in select-only script. + Shorthand for <code class="option">-b select-only</code>. + </p></dd><dt><span class="term"><code class="option">-t</code> <em class="replaceable"><code>transactions</code></em><br /></span><span class="term"><code class="option">--transactions=</code><em class="replaceable"><code>transactions</code></em></span></dt><dd><p> + Number of transactions each client runs. Default is 10. + </p></dd><dt><span class="term"><code class="option">-T</code> <em class="replaceable"><code>seconds</code></em><br /></span><span class="term"><code class="option">--time=</code><em class="replaceable"><code>seconds</code></em></span></dt><dd><p> + Run the test for this many seconds, rather than a fixed number of + transactions per client. <code class="option">-t</code> and + <code class="option">-T</code> are mutually exclusive. + </p></dd><dt><span class="term"><code class="option">-v</code><br /></span><span class="term"><code class="option">--vacuum-all</code></span></dt><dd><p> + Vacuum all four standard tables before running the test. + With neither <code class="option">-n</code> nor <code class="option">-v</code>, <span class="application">pgbench</span> will vacuum the + <code class="structname">pgbench_tellers</code> and <code class="structname">pgbench_branches</code> + tables, and will truncate <code class="structname">pgbench_history</code>. + </p></dd><dt><span class="term"><code class="option">--aggregate-interval=<em class="replaceable"><code>seconds</code></em></code></span></dt><dd><p> + Length of aggregation interval (in seconds). May be used only + with <code class="option">-l</code> option. With this option, the log contains + per-interval summary data, as described below. + </p></dd><dt><span class="term"><code class="option">--failures-detailed</code></span></dt><dd><p> + Report failures in per-transaction and aggregation logs, as well as in + the main and per-script reports, grouped by the following types: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>serialization failures;</p></li><li class="listitem"><p>deadlock failures;</p></li></ul></div><p> + See <a class="xref" href="pgbench.html#FAILURES-AND-RETRIES" title="Failures and Serialization/Deadlock Retries">Failures and Serialization/Deadlock Retries</a> for more information. + </p></dd><dt><span class="term"><code class="option">--log-prefix=<em class="replaceable"><code>prefix</code></em></code></span></dt><dd><p> + Set the filename prefix for the log files created by + <code class="option">--log</code>. The default is <code class="literal">pgbench_log</code>. + </p></dd><dt><span class="term"><code class="option">--max-tries=<em class="replaceable"><code>number_of_tries</code></em></code></span></dt><dd><p> + Enable retries for transactions with serialization/deadlock errors and + set the maximum number of these tries. This option can be combined with + the <code class="option">--latency-limit</code> option which limits the total time + of all transaction tries; moreover, you cannot use an unlimited number + of tries (<code class="literal">--max-tries=0</code>) without + <code class="option">--latency-limit</code> or <code class="option">--time</code>. + The default value is 1 and transactions with serialization/deadlock + errors are not retried. See <a class="xref" href="pgbench.html#FAILURES-AND-RETRIES" title="Failures and Serialization/Deadlock Retries">Failures and Serialization/Deadlock Retries</a> + for more information about retrying such transactions. + </p></dd><dt><span class="term"><code class="option">--progress-timestamp</code></span></dt><dd><p> + When showing progress (option <code class="option">-P</code>), use a timestamp + (Unix epoch) instead of the number of seconds since the + beginning of the run. The unit is in seconds, with millisecond + precision after the dot. + This helps compare logs generated by various tools. + </p></dd><dt><span class="term"><code class="option">--random-seed=</code><em class="replaceable"><code>seed</code></em></span></dt><dd><p> + Set random generator seed. Seeds the system random number generator, + which then produces a sequence of initial generator states, one for + each thread. + Values for <em class="replaceable"><code>seed</code></em> may be: + <code class="literal">time</code> (the default, the seed is based on the current time), + <code class="literal">rand</code> (use a strong random source, failing if none + is available), or an unsigned decimal integer value. + The random generator is invoked explicitly from a pgbench script + (<code class="literal">random...</code> functions) or implicitly (for instance option + <code class="option">--rate</code> uses it to schedule transactions). + When explicitly set, the value used for seeding is shown on the terminal. + Any value allowed for <em class="replaceable"><code>seed</code></em> may also be + provided through the environment variable + <code class="literal">PGBENCH_RANDOM_SEED</code>. + To ensure that the provided seed impacts all possible uses, put this option + first or use the environment variable. + </p><p> + Setting the seed explicitly allows to reproduce a <code class="command">pgbench</code> + run exactly, as far as random numbers are concerned. + As the random state is managed per thread, this means the exact same + <code class="command">pgbench</code> run for an identical invocation if there is one + client per thread and there are no external or data dependencies. + From a statistical viewpoint reproducing runs exactly is a bad idea because + it can hide the performance variability or improve performance unduly, + e.g., by hitting the same pages as a previous run. + However, it may also be of great help for debugging, for instance + re-running a tricky case which leads to an error. + Use wisely. + </p></dd><dt><span class="term"><code class="option">--sampling-rate=<em class="replaceable"><code>rate</code></em></code></span></dt><dd><p> + Sampling rate, used when writing data into the log, to reduce the + amount of log generated. If this option is given, only the specified + fraction of transactions are logged. 1.0 means all transactions will + be logged, 0.05 means only 5% of the transactions will be logged. + </p><p> + Remember to take the sampling rate into account when processing the + log file. For example, when computing TPS values, you need to multiply + the numbers accordingly (e.g., with 0.01 sample rate, you'll only get + 1/100 of the actual TPS). + </p></dd><dt><span class="term"><code class="option">--show-script=</code><em class="replaceable"><code>scriptname</code></em></span></dt><dd><p> + Show the actual code of builtin script <em class="replaceable"><code>scriptname</code></em> + on stderr, and exit immediately. + </p></dd><dt><span class="term"><code class="option">--verbose-errors</code></span></dt><dd><p> + Print messages about all errors and failures (errors without retrying) + including which limit for retries was exceeded and how far it was + exceeded for the serialization/deadlock failures. (Note that in this + case the output can be significantly increased.). + See <a class="xref" href="pgbench.html#FAILURES-AND-RETRIES" title="Failures and Serialization/Deadlock Retries">Failures and Serialization/Deadlock Retries</a> for more information. + </p></dd></dl></div><p> + </p></div><div class="refsect2" id="PGBENCH-COMMON-OPTIONS"><h3>Common Options</h3><p> + <span class="application">pgbench</span> also accepts the following common command-line + arguments for connection parameters: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">-h</code> <em class="replaceable"><code>hostname</code></em><br /></span><span class="term"><code class="option">--host=</code><em class="replaceable"><code>hostname</code></em></span></dt><dd><p> + The database server's host name + </p></dd><dt><span class="term"><code class="option">-p</code> <em class="replaceable"><code>port</code></em><br /></span><span class="term"><code class="option">--port=</code><em class="replaceable"><code>port</code></em></span></dt><dd><p> + The database server's port number + </p></dd><dt><span class="term"><code class="option">-U</code> <em class="replaceable"><code>login</code></em><br /></span><span class="term"><code class="option">--username=</code><em class="replaceable"><code>login</code></em></span></dt><dd><p> + The user name to connect as + </p></dd><dt><span class="term"><code class="option">-V</code><br /></span><span class="term"><code class="option">--version</code></span></dt><dd><p> + Print the <span class="application">pgbench</span> version and exit. + </p></dd><dt><span class="term"><code class="option">-?</code><br /></span><span class="term"><code class="option">--help</code></span></dt><dd><p> + Show help about <span class="application">pgbench</span> command line + arguments, and exit. + </p></dd></dl></div><p> + </p></div></div><div class="refsect1" id="id-1.9.4.11.7"><h2>Exit Status</h2><p> + A successful run will exit with status 0. Exit status 1 indicates static + problems such as invalid command-line options or internal errors which + are supposed to never occur. Early errors that occur when starting + benchmark such as initial connection failures also exit with status 1. + Errors during the run such as database errors or problems in the script + will result in exit status 2. In the latter case, + <span class="application">pgbench</span> will print partial results. + </p></div><div class="refsect1" id="id-1.9.4.11.8"><h2>Environment</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="envar">PGDATABASE</code><br /></span><span class="term"><code class="envar">PGHOST</code><br /></span><span class="term"><code class="envar">PGPORT</code><br /></span><span class="term"><code class="envar">PGUSER</code></span></dt><dd><p> + Default connection parameters. + </p></dd></dl></div><p> + This utility, like most other <span class="productname">PostgreSQL</span> utilities, + uses the environment variables supported by <span class="application">libpq</span> + (see <a class="xref" href="libpq-envars.html" title="34.15. Environment Variables">Section 34.15</a>). + </p><p> + The environment variable <code class="envar">PG_COLOR</code> specifies whether to use + color in diagnostic messages. Possible values are + <code class="literal">always</code>, <code class="literal">auto</code> and + <code class="literal">never</code>. + </p></div><div class="refsect1" id="id-1.9.4.11.9"><h2>Notes</h2><div class="refsect2" id="TRANSACTIONS-AND-SCRIPTS"><h3>What Is the <span class="quote">“<span class="quote">Transaction</span>”</span> Actually Performed in <span class="application">pgbench</span>?</h3><p> + <span class="application">pgbench</span> executes test scripts chosen randomly + from a specified list. + The scripts may include built-in scripts specified with <code class="option">-b</code> + and user-provided scripts specified with <code class="option">-f</code>. + Each script may be given a relative weight specified after an + <code class="literal">@</code> so as to change its selection probability. + The default weight is <code class="literal">1</code>. + Scripts with a weight of <code class="literal">0</code> are ignored. + </p><p> + The default built-in transaction script (also invoked with <code class="option">-b tpcb-like</code>) + issues seven commands per transaction over randomly chosen <code class="literal">aid</code>, + <code class="literal">tid</code>, <code class="literal">bid</code> and <code class="literal">delta</code>. + The scenario is inspired by the TPC-B benchmark, but is not actually TPC-B, + hence the name. + </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p><code class="literal">BEGIN;</code></p></li><li class="listitem"><p><code class="literal">UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;</code></p></li><li class="listitem"><p><code class="literal">SELECT abalance FROM pgbench_accounts WHERE aid = :aid;</code></p></li><li class="listitem"><p><code class="literal">UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;</code></p></li><li class="listitem"><p><code class="literal">UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;</code></p></li><li class="listitem"><p><code class="literal">INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);</code></p></li><li class="listitem"><p><code class="literal">END;</code></p></li></ol></div><p> + If you select the <code class="literal">simple-update</code> built-in (also <code class="option">-N</code>), + steps 4 and 5 aren't included in the transaction. + This will avoid update contention on these tables, but + it makes the test case even less like TPC-B. + </p><p> + If you select the <code class="literal">select-only</code> built-in (also <code class="option">-S</code>), + only the <code class="command">SELECT</code> is issued. + </p></div><div class="refsect2" id="id-1.9.4.11.9.3"><h3>Custom Scripts</h3><p> + <span class="application">pgbench</span> has support for running custom + benchmark scenarios by replacing the default transaction script + (described above) with a transaction script read from a file + (<code class="option">-f</code> option). In this case a <span class="quote">“<span class="quote">transaction</span>”</span> + counts as one execution of a script file. + </p><p> + A script file contains one or more SQL commands terminated by + semicolons. Empty lines and lines beginning with + <code class="literal">--</code> are ignored. Script files can also contain + <span class="quote">“<span class="quote">meta commands</span>”</span>, which are interpreted by <span class="application">pgbench</span> + itself, as described below. + </p><div class="note"><h3 class="title">Note</h3><p> + Before <span class="productname">PostgreSQL</span> 9.6, SQL commands in script files + were terminated by newlines, and so they could not be continued across + lines. Now a semicolon is <span class="emphasis"><em>required</em></span> to separate consecutive + SQL commands (though an SQL command does not need one if it is followed + by a meta command). If you need to create a script file that works with + both old and new versions of <span class="application">pgbench</span>, be sure to write + each SQL command on a single line ending with a semicolon. + </p><p> + It is assumed that pgbench scripts do not contain incomplete blocks of SQL + transactions. If at runtime the client reaches the end of the script without + completing the last transaction block, it will be aborted. + </p></div><p> + There is a simple variable-substitution facility for script files. + Variable names must consist of letters (including non-Latin letters), + digits, and underscores, with the first character not being a digit. + Variables can be set by the command-line <code class="option">-D</code> option, + explained above, or by the meta commands explained below. + In addition to any variables preset by <code class="option">-D</code> command-line options, + there are a few variables that are preset automatically, listed in + <a class="xref" href="pgbench.html#PGBENCH-AUTOMATIC-VARIABLES" title="Table 288. pgbench Automatic Variables">Table 288</a>. A value specified for these + variables using <code class="option">-D</code> takes precedence over the automatic presets. + Once set, a variable's + value can be inserted into an SQL command by writing + <code class="literal">:</code><em class="replaceable"><code>variablename</code></em>. When running more than + one client session, each session has its own set of variables. + <span class="application">pgbench</span> supports up to 255 variable uses in one + statement. + </p><div class="table" id="PGBENCH-AUTOMATIC-VARIABLES"><p class="title"><strong>Table 288. pgbench Automatic Variables</strong></p><div class="table-contents"><table class="table" summary="pgbench Automatic Variables" border="1"><colgroup><col class="col1" /><col class="col2" /></colgroup><thead><tr><th>Variable</th><th>Description</th></tr></thead><tbody><tr><td> <code class="literal">client_id</code> </td><td>unique number identifying the client session (starts from zero)</td></tr><tr><td> <code class="literal">default_seed</code> </td><td>seed used in hash and pseudorandom permutation functions by default</td></tr><tr><td> <code class="literal">random_seed</code> </td><td>random generator seed (unless overwritten with <code class="option">-D</code>)</td></tr><tr><td> <code class="literal">scale</code> </td><td>current scale factor</td></tr></tbody></table></div></div><br class="table-break" /><p> + Script file meta commands begin with a backslash (<code class="literal">\</code>) and + normally extend to the end of the line, although they can be continued + to additional lines by writing backslash-return. + Arguments to a meta command are separated by white space. + These meta commands are supported: + </p><div class="variablelist"><dl class="variablelist"><dt id="PGBENCH-METACOMMAND-GSET"><span class="term"> + <code class="literal">\gset [<em class="replaceable"><code>prefix</code></em>]</code> + <code class="literal">\aset [<em class="replaceable"><code>prefix</code></em>]</code> + </span></dt><dd><p> + These commands may be used to end SQL queries, taking the place of the + terminating semicolon (<code class="literal">;</code>). + </p><p> + When the <code class="literal">\gset</code> command is used, the preceding SQL query is + expected to return one row, the columns of which are stored into variables + named after column names, and prefixed with <em class="replaceable"><code>prefix</code></em> + if provided. + </p><p> + When the <code class="literal">\aset</code> command is used, all combined SQL queries + (separated by <code class="literal">\;</code>) have their columns stored into variables + named after column names, and prefixed with <em class="replaceable"><code>prefix</code></em> + if provided. If a query returns no row, no assignment is made and the variable + can be tested for existence to detect this. If a query returns more than one + row, the last value is kept. + </p><p> + <code class="literal">\gset</code> and <code class="literal">\aset</code> cannot be used in + pipeline mode, since the query results are not yet available by the time + the commands would need them. + </p><p> + The following example puts the final account balance from the first query + into variable <em class="replaceable"><code>abalance</code></em>, and fills variables + <em class="replaceable"><code>p_two</code></em> and <em class="replaceable"><code>p_three</code></em> + with integers from the third query. + The result of the second query is discarded. + The result of the two last combined queries are stored in variables + <em class="replaceable"><code>four</code></em> and <em class="replaceable"><code>five</code></em>. +</p><pre class="programlisting"> +UPDATE pgbench_accounts + SET abalance = abalance + :delta + WHERE aid = :aid + RETURNING abalance \gset +-- compound of two queries +SELECT 1 \; +SELECT 2 AS two, 3 AS three \gset p_ +SELECT 4 AS four \; SELECT 5 AS five \aset +</pre></dd><dt><span class="term"><code class="literal">\if</code> <em class="replaceable"><code>expression</code></em><br /></span><span class="term"><code class="literal">\elif</code> <em class="replaceable"><code>expression</code></em><br /></span><span class="term"><code class="literal">\else</code><br /></span><span class="term"><code class="literal">\endif</code></span></dt><dd><p> + This group of commands implements nestable conditional blocks, + similarly to <code class="literal">psql</code>'s <a class="xref" href="app-psql.html#PSQL-METACOMMAND-IF"><code class="literal">\if</code> <em class="replaceable"><code>expression</code></em></a>. + Conditional expressions are identical to those with <code class="literal">\set</code>, + with non-zero values interpreted as true. + </p></dd><dt id="PGBENCH-METACOMMAND-SET"><span class="term"> + <code class="literal">\set <em class="replaceable"><code>varname</code></em> <em class="replaceable"><code>expression</code></em></code> + </span></dt><dd><p> + Sets variable <em class="replaceable"><code>varname</code></em> to a value calculated + from <em class="replaceable"><code>expression</code></em>. + The expression may contain the <code class="literal">NULL</code> constant, + Boolean constants <code class="literal">TRUE</code> and <code class="literal">FALSE</code>, + integer constants such as <code class="literal">5432</code>, + double constants such as <code class="literal">3.14159</code>, + references to variables <code class="literal">:</code><em class="replaceable"><code>variablename</code></em>, + <a class="link" href="pgbench.html#PGBENCH-BUILTIN-OPERATORS" title="Built-in Operators">operators</a> + with their usual SQL precedence and associativity, + <a class="link" href="pgbench.html#PGBENCH-BUILTIN-FUNCTIONS" title="Built-In Functions">function calls</a>, + SQL <a class="link" href="functions-conditional.html#FUNCTIONS-CASE" title="9.18.1. CASE"><code class="token">CASE</code> generic conditional + expressions</a> and parentheses. + </p><p> + Functions and most operators return <code class="literal">NULL</code> on + <code class="literal">NULL</code> input. + </p><p> + For conditional purposes, non zero numerical values are + <code class="literal">TRUE</code>, zero numerical values and <code class="literal">NULL</code> + are <code class="literal">FALSE</code>. + </p><p> + Too large or small integer and double constants, as well as + integer arithmetic operators (<code class="literal">+</code>, + <code class="literal">-</code>, <code class="literal">*</code> and <code class="literal">/</code>) + raise errors on overflows. + </p><p> + When no final <code class="token">ELSE</code> clause is provided to a + <code class="token">CASE</code>, the default value is <code class="literal">NULL</code>. + </p><p> + Examples: +</p><pre class="programlisting"> +\set ntellers 10 * :scale +\set aid (1021 * random(1, 100000 * :scale)) % \ + (100000 * :scale) + 1 +\set divx CASE WHEN :x <> 0 THEN :y/:x ELSE NULL END +</pre></dd><dt><span class="term"> + <code class="literal">\sleep <em class="replaceable"><code>number</code></em> [ us | ms | s ]</code> + </span></dt><dd><p> + Causes script execution to sleep for the specified duration in + microseconds (<code class="literal">us</code>), milliseconds (<code class="literal">ms</code>) or seconds + (<code class="literal">s</code>). If the unit is omitted then seconds are the default. + <em class="replaceable"><code>number</code></em> can be either an integer constant or a + <code class="literal">:</code><em class="replaceable"><code>variablename</code></em> reference to a variable + having an integer value. + </p><p> + Example: +</p><pre class="programlisting"> +\sleep 10 ms +</pre></dd><dt><span class="term"> + <code class="literal">\setshell <em class="replaceable"><code>varname</code></em> <em class="replaceable"><code>command</code></em> [ <em class="replaceable"><code>argument</code></em> ... ]</code> + </span></dt><dd><p> + Sets variable <em class="replaceable"><code>varname</code></em> to the result of the shell command + <em class="replaceable"><code>command</code></em> with the given <em class="replaceable"><code>argument</code></em>(s). + The command must return an integer value through its standard output. + </p><p> + <em class="replaceable"><code>command</code></em> and each <em class="replaceable"><code>argument</code></em> can be either + a text constant or a <code class="literal">:</code><em class="replaceable"><code>variablename</code></em> reference + to a variable. If you want to use an <em class="replaceable"><code>argument</code></em> starting + with a colon, write an additional colon at the beginning of + <em class="replaceable"><code>argument</code></em>. + </p><p> + Example: +</p><pre class="programlisting"> +\setshell variable_to_be_assigned command literal_argument :variable ::literal_starting_with_colon +</pre></dd><dt><span class="term"> + <code class="literal">\shell <em class="replaceable"><code>command</code></em> [ <em class="replaceable"><code>argument</code></em> ... ]</code> + </span></dt><dd><p> + Same as <code class="literal">\setshell</code>, but the result of the command + is discarded. + </p><p> + Example: +</p><pre class="programlisting"> +\shell command literal_argument :variable ::literal_starting_with_colon +</pre></dd><dt id="PGBENCH-METACOMMAND-PIPELINE"><span class="term"><code class="literal">\startpipeline</code><br /></span><span class="term"><code class="literal">\endpipeline</code></span></dt><dd><p> + These commands delimit the start and end of a pipeline of SQL + statements. In pipeline mode, statements are sent to the server + without waiting for the results of previous statements. See + <a class="xref" href="libpq-pipeline-mode.html" title="34.5. Pipeline Mode">Section 34.5</a> for more details. + Pipeline mode requires the use of extended query protocol. + </p></dd></dl></div></div><div class="refsect2" id="PGBENCH-BUILTIN-OPERATORS"><h3>Built-in Operators</h3><p> + The arithmetic, bitwise, comparison and logical operators listed in + <a class="xref" href="pgbench.html#PGBENCH-OPERATORS" title="Table 289. pgbench Operators">Table 289</a> are built into <span class="application">pgbench</span> + and may be used in expressions appearing in + <a class="link" href="pgbench.html#PGBENCH-METACOMMAND-SET"><code class="literal">\set</code></a>. + The operators are listed in increasing precedence order. + Except as noted, operators taking two numeric inputs will produce + a double value if either input is double, otherwise they produce + an integer result. + </p><div class="table" id="PGBENCH-OPERATORS"><p class="title"><strong>Table 289. pgbench Operators</strong></p><div class="table-contents"><table class="table" summary="pgbench Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Operator + </p> + <p> + Description + </p> + <p> + Example(s) + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>boolean</code></em> <code class="literal">OR</code> <em class="replaceable"><code>boolean</code></em> + → <code class="returnvalue"><em class="replaceable"><code>boolean</code></em></code> + </p> + <p> + Logical OR + </p> + <p> + <code class="literal">5 or 0</code> + → <code class="returnvalue">TRUE</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>boolean</code></em> <code class="literal">AND</code> <em class="replaceable"><code>boolean</code></em> + → <code class="returnvalue"><em class="replaceable"><code>boolean</code></em></code> + </p> + <p> + Logical AND + </p> + <p> + <code class="literal">3 and 0</code> + → <code class="returnvalue">FALSE</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="literal">NOT</code> <em class="replaceable"><code>boolean</code></em> + → <code class="returnvalue"><em class="replaceable"><code>boolean</code></em></code> + </p> + <p> + Logical NOT + </p> + <p> + <code class="literal">not false</code> + → <code class="returnvalue">TRUE</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>boolean</code></em> <code class="literal">IS [NOT] (NULL|TRUE|FALSE)</code> + → <code class="returnvalue"><em class="replaceable"><code>boolean</code></em></code> + </p> + <p> + Boolean value tests + </p> + <p> + <code class="literal">1 is null</code> + → <code class="returnvalue">FALSE</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>value</code></em> <code class="literal">ISNULL|NOTNULL</code> + → <code class="returnvalue"><em class="replaceable"><code>boolean</code></em></code> + </p> + <p> + Nullness tests + </p> + <p> + <code class="literal">1 notnull</code> + → <code class="returnvalue">TRUE</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>number</code></em> <code class="literal">=</code> <em class="replaceable"><code>number</code></em> + → <code class="returnvalue"><em class="replaceable"><code>boolean</code></em></code> + </p> + <p> + Equal + </p> + <p> + <code class="literal">5 = 4</code> + → <code class="returnvalue">FALSE</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>number</code></em> <code class="literal"><></code> <em class="replaceable"><code>number</code></em> + → <code class="returnvalue"><em class="replaceable"><code>boolean</code></em></code> + </p> + <p> + Not equal + </p> + <p> + <code class="literal">5 <> 4</code> + → <code class="returnvalue">TRUE</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>number</code></em> <code class="literal">!=</code> <em class="replaceable"><code>number</code></em> + → <code class="returnvalue"><em class="replaceable"><code>boolean</code></em></code> + </p> + <p> + Not equal + </p> + <p> + <code class="literal">5 != 5</code> + → <code class="returnvalue">FALSE</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>number</code></em> <code class="literal"><</code> <em class="replaceable"><code>number</code></em> + → <code class="returnvalue"><em class="replaceable"><code>boolean</code></em></code> + </p> + <p> + Less than + </p> + <p> + <code class="literal">5 < 4</code> + → <code class="returnvalue">FALSE</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>number</code></em> <code class="literal"><=</code> <em class="replaceable"><code>number</code></em> + → <code class="returnvalue"><em class="replaceable"><code>boolean</code></em></code> + </p> + <p> + Less than or equal to + </p> + <p> + <code class="literal">5 <= 4</code> + → <code class="returnvalue">FALSE</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>number</code></em> <code class="literal">></code> <em class="replaceable"><code>number</code></em> + → <code class="returnvalue"><em class="replaceable"><code>boolean</code></em></code> + </p> + <p> + Greater than + </p> + <p> + <code class="literal">5 > 4</code> + → <code class="returnvalue">TRUE</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>number</code></em> <code class="literal">>=</code> <em class="replaceable"><code>number</code></em> + → <code class="returnvalue"><em class="replaceable"><code>boolean</code></em></code> + </p> + <p> + Greater than or equal to + </p> + <p> + <code class="literal">5 >= 4</code> + → <code class="returnvalue">TRUE</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>integer</code></em> <code class="literal">|</code> <em class="replaceable"><code>integer</code></em> + → <code class="returnvalue"><em class="replaceable"><code>integer</code></em></code> + </p> + <p> + Bitwise OR + </p> + <p> + <code class="literal">1 | 2</code> + → <code class="returnvalue">3</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>integer</code></em> <code class="literal">#</code> <em class="replaceable"><code>integer</code></em> + → <code class="returnvalue"><em class="replaceable"><code>integer</code></em></code> + </p> + <p> + Bitwise XOR + </p> + <p> + <code class="literal">1 # 3</code> + → <code class="returnvalue">2</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>integer</code></em> <code class="literal">&</code> <em class="replaceable"><code>integer</code></em> + → <code class="returnvalue"><em class="replaceable"><code>integer</code></em></code> + </p> + <p> + Bitwise AND + </p> + <p> + <code class="literal">1 & 3</code> + → <code class="returnvalue">1</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="literal">~</code> <em class="replaceable"><code>integer</code></em> + → <code class="returnvalue"><em class="replaceable"><code>integer</code></em></code> + </p> + <p> + Bitwise NOT + </p> + <p> + <code class="literal">~ 1</code> + → <code class="returnvalue">-2</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>integer</code></em> <code class="literal"><<</code> <em class="replaceable"><code>integer</code></em> + → <code class="returnvalue"><em class="replaceable"><code>integer</code></em></code> + </p> + <p> + Bitwise shift left + </p> + <p> + <code class="literal">1 << 2</code> + → <code class="returnvalue">4</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>integer</code></em> <code class="literal">>></code> <em class="replaceable"><code>integer</code></em> + → <code class="returnvalue"><em class="replaceable"><code>integer</code></em></code> + </p> + <p> + Bitwise shift right + </p> + <p> + <code class="literal">8 >> 2</code> + → <code class="returnvalue">2</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>number</code></em> <code class="literal">+</code> <em class="replaceable"><code>number</code></em> + → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code> + </p> + <p> + Addition + </p> + <p> + <code class="literal">5 + 4</code> + → <code class="returnvalue">9</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>number</code></em> <code class="literal">-</code> <em class="replaceable"><code>number</code></em> + → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code> + </p> + <p> + Subtraction + </p> + <p> + <code class="literal">3 - 2.0</code> + → <code class="returnvalue">1.0</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>number</code></em> <code class="literal">*</code> <em class="replaceable"><code>number</code></em> + → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code> + </p> + <p> + Multiplication + </p> + <p> + <code class="literal">5 * 4</code> + → <code class="returnvalue">20</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>number</code></em> <code class="literal">/</code> <em class="replaceable"><code>number</code></em> + → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code> + </p> + <p> + Division (truncates the result towards zero if both inputs are integers) + </p> + <p> + <code class="literal">5 / 3</code> + → <code class="returnvalue">1</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <em class="replaceable"><code>integer</code></em> <code class="literal">%</code> <em class="replaceable"><code>integer</code></em> + → <code class="returnvalue"><em class="replaceable"><code>integer</code></em></code> + </p> + <p> + Modulo (remainder) + </p> + <p> + <code class="literal">3 % 2</code> + → <code class="returnvalue">1</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="literal">-</code> <em class="replaceable"><code>number</code></em> + → <code class="returnvalue"><em class="replaceable"><code>number</code></em></code> + </p> + <p> + Negation + </p> + <p> + <code class="literal">- 2.0</code> + → <code class="returnvalue">-2.0</code> + </p></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="refsect2" id="PGBENCH-BUILTIN-FUNCTIONS"><h3>Built-In Functions</h3><p> + The functions listed in <a class="xref" href="pgbench.html#PGBENCH-FUNCTIONS" title="Table 290. pgbench Functions">Table 290</a> are built + into <span class="application">pgbench</span> and may be used in expressions appearing in + <a class="link" href="pgbench.html#PGBENCH-METACOMMAND-SET"><code class="literal">\set</code></a>. + </p><div class="table" id="PGBENCH-FUNCTIONS"><p class="title"><strong>Table 290. pgbench Functions</strong></p><div class="table-contents"><table class="table" summary="pgbench Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p> + <p> + Example(s) + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">abs</code> ( <em class="replaceable"><code>number</code></em> ) + → <code class="returnvalue"></code> same type as input + </p> + <p> + Absolute value + </p> + <p> + <code class="literal">abs(-17)</code> + → <code class="returnvalue">17</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">debug</code> ( <em class="replaceable"><code>number</code></em> ) + → <code class="returnvalue"></code> same type as input + </p> + <p> + Prints the argument to <span class="systemitem">stderr</span>, + and returns the argument. + </p> + <p> + <code class="literal">debug(5432.1)</code> + → <code class="returnvalue">5432.1</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">double</code> ( <em class="replaceable"><code>number</code></em> ) + → <code class="returnvalue">double</code> + </p> + <p> + Casts to double. + </p> + <p> + <code class="literal">double(5432)</code> + → <code class="returnvalue">5432.0</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">exp</code> ( <em class="replaceable"><code>number</code></em> ) + → <code class="returnvalue">double</code> + </p> + <p> + Exponential (<code class="literal">e</code> raised to the given power) + </p> + <p> + <code class="literal">exp(1.0)</code> + → <code class="returnvalue">2.718281828459045</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">greatest</code> ( <em class="replaceable"><code>number</code></em> [<span class="optional">, <code class="literal">...</code> </span>] ) + → <code class="returnvalue"></code> <code class="type">double</code> if any argument is double, else <code class="type">integer</code> + </p> + <p> + Selects the largest value among the arguments. + </p> + <p> + <code class="literal">greatest(5, 4, 3, 2)</code> + → <code class="returnvalue">5</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">hash</code> ( <em class="parameter"><code>value</code></em> [<span class="optional">, <em class="parameter"><code>seed</code></em> </span>] ) + → <code class="returnvalue">integer</code> + </p> + <p> + This is an alias for <code class="function">hash_murmur2</code>. + </p> + <p> + <code class="literal">hash(10, 5432)</code> + → <code class="returnvalue">-5817877081768721676</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">hash_fnv1a</code> ( <em class="parameter"><code>value</code></em> [<span class="optional">, <em class="parameter"><code>seed</code></em> </span>] ) + → <code class="returnvalue">integer</code> + </p> + <p> + Computes <a class="ulink" href="https://en.wikipedia.org/wiki/Fowler%E2%80%93Noll%E2%80%93Vo_hash_function" target="_top">FNV-1a hash</a>. + </p> + <p> + <code class="literal">hash_fnv1a(10, 5432)</code> + → <code class="returnvalue">-7793829335365542153</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">hash_murmur2</code> ( <em class="parameter"><code>value</code></em> [<span class="optional">, <em class="parameter"><code>seed</code></em> </span>] ) + → <code class="returnvalue">integer</code> + </p> + <p> + Computes <a class="ulink" href="https://en.wikipedia.org/wiki/MurmurHash" target="_top">MurmurHash2 hash</a>. + </p> + <p> + <code class="literal">hash_murmur2(10, 5432)</code> + → <code class="returnvalue">-5817877081768721676</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">int</code> ( <em class="replaceable"><code>number</code></em> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Casts to integer. + </p> + <p> + <code class="literal">int(5.4 + 3.8)</code> + → <code class="returnvalue">9</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">least</code> ( <em class="replaceable"><code>number</code></em> [<span class="optional">, <code class="literal">...</code> </span>] ) + → <code class="returnvalue"></code> <code class="type">double</code> if any argument is double, else <code class="type">integer</code> + </p> + <p> + Selects the smallest value among the arguments. + </p> + <p> + <code class="literal">least(5, 4, 3, 2.1)</code> + → <code class="returnvalue">2.1</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">ln</code> ( <em class="replaceable"><code>number</code></em> ) + → <code class="returnvalue">double</code> + </p> + <p> + Natural logarithm + </p> + <p> + <code class="literal">ln(2.718281828459045)</code> + → <code class="returnvalue">1.0</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> +<code class="function">mod</code> ( <em class="replaceable"><code>integer</code></em>, <em class="replaceable"><code>integer</code></em> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Modulo (remainder) + </p> + <p> + <code class="literal">mod(54, 32)</code> + → <code class="returnvalue">22</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">permute</code> ( <em class="parameter"><code>i</code></em>, <em class="parameter"><code>size</code></em> [, <em class="parameter"><code>seed</code></em> ] ) + → <code class="returnvalue">integer</code> + </p> + <p> + Permuted value of <em class="parameter"><code>i</code></em>, in the range + <code class="literal">[0, size)</code>. This is the new position of + <em class="parameter"><code>i</code></em> (modulo <em class="parameter"><code>size</code></em>) in a + pseudorandom permutation of the integers <code class="literal">0...size-1</code>, + parameterized by <em class="parameter"><code>seed</code></em>, see below. + </p> + <p> + <code class="literal">permute(0, 4)</code> + → <code class="returnvalue">an integer between 0 and 3</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">pi</code> () + → <code class="returnvalue">double</code> + </p> + <p> + Approximate value of <span class="symbol_font">π</span> + </p> + <p> + <code class="literal">pi()</code> + → <code class="returnvalue">3.14159265358979323846</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">pow</code> ( <em class="parameter"><code>x</code></em>, <em class="parameter"><code>y</code></em> ) + → <code class="returnvalue">double</code> + </p> + <p class="func_signature"> + <code class="function">power</code> ( <em class="parameter"><code>x</code></em>, <em class="parameter"><code>y</code></em> ) + → <code class="returnvalue">double</code> + </p> + <p> + <em class="parameter"><code>x</code></em> raised to the power of <em class="parameter"><code>y</code></em> + </p> + <p> + <code class="literal">pow(2.0, 10)</code> + → <code class="returnvalue">1024.0</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">random</code> ( <em class="parameter"><code>lb</code></em>, <em class="parameter"><code>ub</code></em> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Computes a uniformly-distributed random integer in <code class="literal">[lb, + ub]</code>. + </p> + <p> + <code class="literal">random(1, 10)</code> + → <code class="returnvalue">an integer between 1 and 10</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">random_exponential</code> ( <em class="parameter"><code>lb</code></em>, <em class="parameter"><code>ub</code></em>, <em class="parameter"><code>parameter</code></em> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Computes an exponentially-distributed random integer in <code class="literal">[lb, + ub]</code>, see below. + </p> + <p> + <code class="literal">random_exponential(1, 10, 3.0)</code> + → <code class="returnvalue">an integer between 1 and 10</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">random_gaussian</code> ( <em class="parameter"><code>lb</code></em>, <em class="parameter"><code>ub</code></em>, <em class="parameter"><code>parameter</code></em> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Computes a Gaussian-distributed random integer in <code class="literal">[lb, + ub]</code>, see below. + </p> + <p> + <code class="literal">random_gaussian(1, 10, 2.5)</code> + → <code class="returnvalue">an integer between 1 and 10</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">random_zipfian</code> ( <em class="parameter"><code>lb</code></em>, <em class="parameter"><code>ub</code></em>, <em class="parameter"><code>parameter</code></em> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Computes a Zipfian-distributed random integer in <code class="literal">[lb, + ub]</code>, see below. + </p> + <p> + <code class="literal">random_zipfian(1, 10, 1.5)</code> + → <code class="returnvalue">an integer between 1 and 10</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">sqrt</code> ( <em class="replaceable"><code>number</code></em> ) + → <code class="returnvalue">double</code> + </p> + <p> + Square root + </p> + <p> + <code class="literal">sqrt(2.0)</code> + → <code class="returnvalue">1.414213562</code> + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + The <code class="literal">random</code> function generates values using a uniform + distribution, that is all the values are drawn within the specified + range with equal probability. The <code class="literal">random_exponential</code>, + <code class="literal">random_gaussian</code> and <code class="literal">random_zipfian</code> + functions require an additional double parameter which determines the precise + shape of the distribution. + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + For an exponential distribution, <em class="replaceable"><code>parameter</code></em> + controls the distribution by truncating a quickly-decreasing + exponential distribution at <em class="replaceable"><code>parameter</code></em>, and then + projecting onto integers between the bounds. + To be precise, with +</p><div class="literallayout"><p><br /> +f(x) = exp(-parameter * (x - min) / (max - min + 1)) / (1 - exp(-parameter))<br /> +</p></div><p> + Then value <em class="replaceable"><code>i</code></em> between <em class="replaceable"><code>min</code></em> and + <em class="replaceable"><code>max</code></em> inclusive is drawn with probability: + <code class="literal">f(i) - f(i + 1)</code>. + </p><p> + Intuitively, the larger the <em class="replaceable"><code>parameter</code></em>, the more + frequently values close to <em class="replaceable"><code>min</code></em> are accessed, and the + less frequently values close to <em class="replaceable"><code>max</code></em> are accessed. + The closer to 0 <em class="replaceable"><code>parameter</code></em> is, the flatter (more + uniform) the access distribution. + A crude approximation of the distribution is that the most frequent 1% + values in the range, close to <em class="replaceable"><code>min</code></em>, are drawn + <em class="replaceable"><code>parameter</code></em>% of the time. + The <em class="replaceable"><code>parameter</code></em> value must be strictly positive. + </p></li><li class="listitem"><p> + For a Gaussian distribution, the interval is mapped onto a standard + normal distribution (the classical bell-shaped Gaussian curve) truncated + at <code class="literal">-parameter</code> on the left and <code class="literal">+parameter</code> + on the right. + Values in the middle of the interval are more likely to be drawn. + To be precise, if <code class="literal">PHI(x)</code> is the cumulative distribution + function of the standard normal distribution, with mean <code class="literal">mu</code> + defined as <code class="literal">(max + min) / 2.0</code>, with +</p><div class="literallayout"><p><br /> +f(x) = PHI(2.0 * parameter * (x - mu) / (max - min + 1)) /<br /> + (2.0 * PHI(parameter) - 1)<br /> +</p></div><p> + then value <em class="replaceable"><code>i</code></em> between <em class="replaceable"><code>min</code></em> and + <em class="replaceable"><code>max</code></em> inclusive is drawn with probability: + <code class="literal">f(i + 0.5) - f(i - 0.5)</code>. + Intuitively, the larger the <em class="replaceable"><code>parameter</code></em>, the more + frequently values close to the middle of the interval are drawn, and the + less frequently values close to the <em class="replaceable"><code>min</code></em> and + <em class="replaceable"><code>max</code></em> bounds. About 67% of values are drawn from the + middle <code class="literal">1.0 / parameter</code>, that is a relative + <code class="literal">0.5 / parameter</code> around the mean, and 95% in the middle + <code class="literal">2.0 / parameter</code>, that is a relative + <code class="literal">1.0 / parameter</code> around the mean; for instance, if + <em class="replaceable"><code>parameter</code></em> is 4.0, 67% of values are drawn from the + middle quarter (1.0 / 4.0) of the interval (i.e., from + <code class="literal">3.0 / 8.0</code> to <code class="literal">5.0 / 8.0</code>) and 95% from + the middle half (<code class="literal">2.0 / 4.0</code>) of the interval (second and third + quartiles). The minimum allowed <em class="replaceable"><code>parameter</code></em> + value is 2.0. + </p></li><li class="listitem"><p> + <code class="literal">random_zipfian</code> generates a bounded Zipfian + distribution. + <em class="replaceable"><code>parameter</code></em> defines how skewed the distribution + is. The larger the <em class="replaceable"><code>parameter</code></em>, the more + frequently values closer to the beginning of the interval are drawn. + The distribution is such that, assuming the range starts from 1, + the ratio of the probability of drawing <em class="replaceable"><code>k</code></em> + versus drawing <em class="replaceable"><code>k+1</code></em> is + <code class="literal">((<em class="replaceable"><code>k</code></em>+1)/<em class="replaceable"><code>k</code></em>)**<em class="replaceable"><code>parameter</code></em></code>. + For example, <code class="literal">random_zipfian(1, ..., 2.5)</code> produces + the value <code class="literal">1</code> about <code class="literal">(2/1)**2.5 = + 5.66</code> times more frequently than <code class="literal">2</code>, which + itself is produced <code class="literal">(3/2)**2.5 = 2.76</code> times more + frequently than <code class="literal">3</code>, and so on. + </p><p> + <span class="application">pgbench</span>'s implementation is based on + "Non-Uniform Random Variate Generation", Luc Devroye, p. 550-551, + Springer 1986. Due to limitations of that algorithm, + the <em class="replaceable"><code>parameter</code></em> value is restricted to + the range [1.001, 1000]. + </p></li></ul></div><div class="note"><h3 class="title">Note</h3><p> + When designing a benchmark which selects rows non-uniformly, be aware + that the rows chosen may be correlated with other data such as IDs from + a sequence or the physical row ordering, which may skew performance + measurements. + </p><p> + To avoid this, you may wish to use the <code class="function">permute</code> + function, or some other additional step with similar effect, to shuffle + the selected rows and remove such correlations. + </p></div><p> + Hash functions <code class="literal">hash</code>, <code class="literal">hash_murmur2</code> and + <code class="literal">hash_fnv1a</code> accept an input value and an optional seed parameter. + In case the seed isn't provided the value of <code class="literal">:default_seed</code> + is used, which is initialized randomly unless set by the command-line + <code class="literal">-D</code> option. + </p><p> + <code class="literal">permute</code> accepts an input value, a size, and an optional + seed parameter. It generates a pseudorandom permutation of integers in + the range <code class="literal">[0, size)</code>, and returns the index of the input + value in the permuted values. The permutation chosen is parameterized by + the seed, which defaults to <code class="literal">:default_seed</code>, if not + specified. Unlike the hash functions, <code class="literal">permute</code> ensures + that there are no collisions or holes in the output values. Input values + outside the interval are interpreted modulo the size. The function raises + an error if the size is not positive. <code class="function">permute</code> can be + used to scatter the distribution of non-uniform random functions such as + <code class="literal">random_zipfian</code> or <code class="literal">random_exponential</code> + so that values drawn more often are not trivially correlated. For + instance, the following <span class="application">pgbench</span> script + simulates a possible real world workload typical for social media and + blogging platforms where a few accounts generate excessive load: + +</p><pre class="programlisting"> +\set size 1000000 +\set r random_zipfian(1, :size, 1.07) +\set k 1 + permute(:r, :size) +</pre><p> + + In some cases several distinct distributions are needed which don't correlate + with each other and this is when the optional seed parameter comes in handy: + +</p><pre class="programlisting"> +\set k1 1 + permute(:r, :size, :default_seed + 123) +\set k2 1 + permute(:r, :size, :default_seed + 321) +</pre><p> + + A similar behavior can also be approximated with <code class="function">hash</code>: + +</p><pre class="programlisting"> +\set size 1000000 +\set r random_zipfian(1, 100 * :size, 1.07) +\set k 1 + abs(hash(:r)) % :size +</pre><p> + + However, since <code class="function">hash</code> generates collisions, some values + will not be reachable and others will be more frequent than expected from + the original distribution. + </p><p> + As an example, the full definition of the built-in TPC-B-like + transaction is: + +</p><pre class="programlisting"> +\set aid random(1, 100000 * :scale) +\set bid random(1, 1 * :scale) +\set tid random(1, 10 * :scale) +\set delta random(-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; +</pre><p> + + 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.) + </p></div><div class="refsect2" id="id-1.9.4.11.9.6"><h3>Per-Transaction Logging</h3><p> + With the <code class="option">-l</code> option (but without + the <code class="option">--aggregate-interval</code> option), + <span class="application">pgbench</span> writes information about each transaction + to a log file. The log file will be named + <code class="filename"><em class="replaceable"><code>prefix</code></em>.<em class="replaceable"><code>nnn</code></em></code>, + where <em class="replaceable"><code>prefix</code></em> defaults to <code class="literal">pgbench_log</code>, and + <em class="replaceable"><code>nnn</code></em> is the PID of the + <span class="application">pgbench</span> process. + The prefix can be changed by using the <code class="option">--log-prefix</code> option. + If the <code class="option">-j</code> option is 2 or higher, so that there are multiple + worker threads, each will have its own log file. The first worker will + use 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 + <code class="filename"><em class="replaceable"><code>prefix</code></em>.<em class="replaceable"><code>nnn</code></em>.<em class="replaceable"><code>mmm</code></em></code>, + where <em class="replaceable"><code>mmm</code></em> is a sequential number for each worker starting + with 1. + </p><p> + Each line in a log file describes one transaction. + It contains the following space-separated fields: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>client_id</code></em></span></dt><dd><p> + identifies the client session that ran the transaction + </p></dd><dt><span class="term"><em class="replaceable"><code>transaction_no</code></em></span></dt><dd><p> + counts how many transactions have been run by that session + </p></dd><dt><span class="term"><em class="replaceable"><code>time</code></em></span></dt><dd><p> + transaction's elapsed time, in microseconds + </p></dd><dt><span class="term"><em class="replaceable"><code>script_no</code></em></span></dt><dd><p> + identifies the script file that was used for the transaction + (useful when multiple scripts are specified + with <code class="option">-f</code> or <code class="option">-b</code>) + </p></dd><dt><span class="term"><em class="replaceable"><code>time_epoch</code></em></span></dt><dd><p> + transaction's completion time, as a Unix-epoch time stamp + </p></dd><dt><span class="term"><em class="replaceable"><code>time_us</code></em></span></dt><dd><p> + fractional-second part of transaction's completion time, in + microseconds + </p></dd><dt><span class="term"><em class="replaceable"><code>schedule_lag</code></em></span></dt><dd><p> + transaction start delay, that is the difference between the + transaction's scheduled start time and the time it actually + started, in microseconds + (present only if <code class="option">--rate</code> is specified) + </p></dd><dt><span class="term"><em class="replaceable"><code>retries</code></em></span></dt><dd><p> + count of retries after serialization or deadlock errors during the + transaction + (present only if <code class="option">--max-tries</code> is not equal to one) + </p></dd></dl></div><p> + </p><p> + When both <code class="option">--rate</code> and <code class="option">--latency-limit</code> are used, + the <em class="replaceable"><code>time</code></em> for a skipped transaction will be reported as + <code class="literal">skipped</code>. + If the transaction ends with a failure, its <em class="replaceable"><code>time</code></em> + will be reported as <code class="literal">failed</code>. If you use the + <code class="option">--failures-detailed</code> option, the + <em class="replaceable"><code>time</code></em> of the failed transaction will be reported as + <code class="literal">serialization</code> or + <code class="literal">deadlock</code> depending on the type of failure (see + <a class="xref" href="pgbench.html#FAILURES-AND-RETRIES" title="Failures and Serialization/Deadlock Retries">Failures and Serialization/Deadlock Retries</a> for more information). + </p><p> + Here is a snippet of a log file generated in a single-client run: +</p><pre class="screen"> +0 199 2241 0 1175850568 995598 +0 200 2465 0 1175850568 998079 +0 201 2513 0 1175850569 608 +0 202 2038 0 1175850569 2663 +</pre><p> + + Another example with <code class="literal">--rate=100</code> + and <code class="literal">--latency-limit=5</code> (note the additional + <em class="replaceable"><code>schedule_lag</code></em> column): +</p><pre class="screen"> +0 81 4621 0 1412881037 912698 3005 +0 82 6173 0 1412881037 914578 4304 +0 83 skipped 0 1412881037 914578 5217 +0 83 skipped 0 1412881037 914578 5099 +0 83 4722 0 1412881037 916203 3108 +0 84 4142 0 1412881037 918023 2333 +0 85 2465 0 1412881037 919759 740 +</pre><p> + In this example, transaction 82 was late, because its latency (6.173 ms) was + over the 5 ms limit. The next two transactions were skipped, because they + were already late before they were even started. + </p><p> + The following example shows a snippet of a log file with failures and + retries, with the maximum number of tries set to 10 (note the additional + <em class="replaceable"><code>retries</code></em> column): +</p><pre class="screen"> +3 0 47423 0 1499414498 34501 3 +3 1 8333 0 1499414498 42848 0 +3 2 8358 0 1499414498 51219 0 +4 0 72345 0 1499414498 59433 6 +1 3 41718 0 1499414498 67879 4 +1 4 8416 0 1499414498 76311 0 +3 3 33235 0 1499414498 84469 3 +0 0 failed 0 1499414498 84905 9 +2 0 failed 0 1499414498 86248 9 +3 4 8307 0 1499414498 92788 0 +</pre><p> + </p><p> + If the <code class="option">--failures-detailed</code> option is used, the type of + failure is reported in the <em class="replaceable"><code>time</code></em> like this: +</p><pre class="screen"> +3 0 47423 0 1499414498 34501 3 +3 1 8333 0 1499414498 42848 0 +3 2 8358 0 1499414498 51219 0 +4 0 72345 0 1499414498 59433 6 +1 3 41718 0 1499414498 67879 4 +1 4 8416 0 1499414498 76311 0 +3 3 33235 0 1499414498 84469 3 +0 0 serialization 0 1499414498 84905 9 +2 0 serialization 0 1499414498 86248 9 +3 4 8307 0 1499414498 92788 0 +</pre><p> + </p><p> + When running a long test on hardware that can handle a lot of transactions, + the log files can become very large. The <code class="option">--sampling-rate</code> option + can be used to log only a random sample of transactions. + </p></div><div class="refsect2" id="id-1.9.4.11.9.7"><h3>Aggregated Logging</h3><p> + With the <code class="option">--aggregate-interval</code> option, a different + format is used for the log files. Each log line describes one + aggregation interval. It contains the following space-separated + fields: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>interval_start</code></em></span></dt><dd><p> + start time of the interval, as a Unix-epoch time stamp + </p></dd><dt><span class="term"><em class="replaceable"><code>num_transactions</code></em></span></dt><dd><p> + number of transactions within the interval + </p></dd><dt><span class="term"><em class="replaceable"><code>sum_latency</code></em></span></dt><dd><p> + sum of transaction latencies + </p></dd><dt><span class="term"><em class="replaceable"><code>sum_latency_2</code></em></span></dt><dd><p> + sum of squares of transaction latencies + </p></dd><dt><span class="term"><em class="replaceable"><code>min_latency</code></em></span></dt><dd><p> + minimum transaction latency + </p></dd><dt><span class="term"><em class="replaceable"><code>max_latency</code></em></span></dt><dd><p> + maximum transaction latency + </p></dd><dt><span class="term"><em class="replaceable"><code>sum_lag</code></em></span></dt><dd><p> + sum of transaction start delays + (zero unless <code class="option">--rate</code> is specified) + </p></dd><dt><span class="term"><em class="replaceable"><code>sum_lag_2</code></em></span></dt><dd><p> + sum of squares of transaction start delays + (zero unless <code class="option">--rate</code> is specified) + </p></dd><dt><span class="term"><em class="replaceable"><code>min_lag</code></em></span></dt><dd><p> + minimum transaction start delay + (zero unless <code class="option">--rate</code> is specified) + </p></dd><dt><span class="term"><em class="replaceable"><code>max_lag</code></em></span></dt><dd><p> + maximum transaction start delay + (zero unless <code class="option">--rate</code> is specified) + </p></dd><dt><span class="term"><em class="replaceable"><code>skipped</code></em></span></dt><dd><p> + number of transactions skipped because they would have started too late + (zero unless <code class="option">--rate</code> + and <code class="option">--latency-limit</code> are specified) + </p></dd><dt><span class="term"><em class="replaceable"><code>retried</code></em></span></dt><dd><p> + number of retried transactions + (zero unless <code class="option">--max-tries</code> is not equal to one) + </p></dd><dt><span class="term"><em class="replaceable"><code>retries</code></em></span></dt><dd><p> + number of retries after serialization or deadlock errors + (zero unless <code class="option">--max-tries</code> is not equal to one) + </p></dd><dt><span class="term"><em class="replaceable"><code>serialization_failures</code></em></span></dt><dd><p> + number of transactions that got a serialization error and were not + retried afterwards + (zero unless <code class="option">--failures-detailed</code> is specified) + </p></dd><dt><span class="term"><em class="replaceable"><code>deadlock_failures</code></em></span></dt><dd><p> + number of transactions that got a deadlock error and were not + retried afterwards + (zero unless <code class="option">--failures-detailed</code> is specified) + </p></dd></dl></div><p> + </p><p> + Here is some example output generated with these options: +</p><pre class="screen"> +<strong class="userinput"><code>pgbench --aggregate-interval=10 --time=20 --client=10 --log --rate=1000 --latency-limit=10 --failures-detailed --max-tries=10 test</code></strong> + +1650260552 5178 26171317 177284491527 1136 44462 2647617 7321113867 0 9866 64 7564 28340 4148 0 +1650260562 4808 25573984 220121792172 1171 62083 3037380 9666800914 0 9998 598 7392 26621 4527 0 +</pre><p> + </p><p> + Notice that while the plain (unaggregated) log format shows which script + was used for each transaction, the aggregated format does not. Therefore if + you need per-script data, you need to aggregate the data on your own. + </p></div><div class="refsect2" id="id-1.9.4.11.9.8"><h3>Per-Statement Report</h3><p> + With the <code class="option">-r</code> option, <span class="application">pgbench</span> + collects the following statistics for each statement: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + <code class="literal">latency</code> — elapsed transaction time for each + statement. <span class="application">pgbench</span> reports an average value + of all successful runs of the statement. + </p></li><li class="listitem"><p> + The number of failures in this statement. See + <a class="xref" href="pgbench.html#FAILURES-AND-RETRIES" title="Failures and Serialization/Deadlock Retries">Failures and Serialization/Deadlock Retries</a> for more information. + </p></li><li class="listitem"><p> + The number of retries after a serialization or a deadlock error in this + statement. See <a class="xref" href="pgbench.html#FAILURES-AND-RETRIES" title="Failures and Serialization/Deadlock Retries">Failures and Serialization/Deadlock Retries</a> for more information. + </p></li></ul></div><p> + </p><p> + The report displays retry statistics only if the <code class="option">--max-tries</code> + option is not equal to 1. + </p><p> + All values are computed for each statement executed by every client and are + reported after the benchmark has finished. + </p><p> + For the default script, the output will look similar to this: +</p><pre class="screen"> +starting vacuum...end. +transaction type: <builtin: TPC-B (sort of)> +scaling factor: 1 +query mode: simple +number of clients: 10 +number of threads: 1 +maximum number of tries: 1 +number of transactions per client: 1000 +number of transactions actually processed: 10000/10000 +number of failed transactions: 0 (0.000%) +number of transactions above the 50.0 ms latency limit: 1311/10000 (13.110 %) +latency average = 28.488 ms +latency stddev = 21.009 ms +initial connection time = 69.068 ms +tps = 346.224794 (without initial connection time) +statement latencies in milliseconds and failures: + 0.012 0 \set aid random(1, 100000 * :scale) + 0.002 0 \set bid random(1, 1 * :scale) + 0.002 0 \set tid random(1, 10 * :scale) + 0.002 0 \set delta random(-5000, 5000) + 0.319 0 BEGIN; + 0.834 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; + 0.641 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; + 11.126 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; + 12.961 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; + 0.634 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); + 1.957 0 END; +</pre><p> + + Another example of output for the default script using serializable default + transaction isolation level (<code class="command">PGOPTIONS='-c + default_transaction_isolation=serializable' pgbench ...</code>): +</p><pre class="screen"> +starting vacuum...end. +transaction type: <builtin: TPC-B (sort of)> +scaling factor: 1 +query mode: simple +number of clients: 10 +number of threads: 1 +maximum number of tries: 10 +number of transactions per client: 1000 +number of transactions actually processed: 6317/10000 +number of failed transactions: 3683 (36.830%) +number of transactions retried: 7667 (76.670%) +total number of retries: 45339 +number of transactions above the 50.0 ms latency limit: 106/6317 (1.678 %) +latency average = 17.016 ms +latency stddev = 13.283 ms +initial connection time = 45.017 ms +tps = 186.792667 (without initial connection time) +statement latencies in milliseconds, failures and retries: + 0.006 0 0 \set aid random(1, 100000 * :scale) + 0.001 0 0 \set bid random(1, 1 * :scale) + 0.001 0 0 \set tid random(1, 10 * :scale) + 0.001 0 0 \set delta random(-5000, 5000) + 0.385 0 0 BEGIN; + 0.773 0 1 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; + 0.624 0 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; + 1.098 320 3762 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; + 0.582 3363 41576 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; + 0.465 0 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); + 1.933 0 0 END; +</pre><p> + If multiple script files are specified, all statistics are reported + separately for each script file. + </p><p> + Note that collecting the additional timing information needed for + per-statement latency computation adds some overhead. This will slow + average execution speed and lower the computed TPS. The amount + of slowdown varies significantly depending on platform and hardware. + Comparing average TPS values with and without latency reporting enabled + is a good way to measure if the timing overhead is significant. + </p></div><div class="refsect2" id="FAILURES-AND-RETRIES"><h3>Failures and Serialization/Deadlock Retries</h3><p> + When executing <span class="application">pgbench</span>, there are three main types + of errors: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + Errors of the main program. They are the most serious and always result + in an immediate exit from <span class="application">pgbench</span> with the + corresponding error message. They include: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p> + errors at the beginning of <span class="application">pgbench</span> + (e.g. an invalid option value); + </p></li><li class="listitem"><p> + errors in the initialization mode (e.g. the query to create + tables for built-in scripts fails); + </p></li><li class="listitem"><p> + errors before starting threads (e.g. could not connect to the + database server, syntax error in the meta command, thread + creation failure); + </p></li><li class="listitem"><p> + internal <span class="application">pgbench</span> errors (which are + supposed to never occur...). + </p></li></ul></div></li><li class="listitem"><p> + Errors when the thread manages its clients (e.g. the client could not + start a connection to the database server / the socket for connecting + the client to the database server has become invalid). In such cases + all clients of this thread stop while other threads continue to work. + </p></li><li class="listitem"><p> + Direct client errors. They lead to immediate exit from + <span class="application">pgbench</span> with the corresponding error message + only in the case of an internal <span class="application">pgbench</span> + error (which are supposed to never occur...). Otherwise in the worst + case they only lead to the abortion of the failed client while other + clients continue their run (but some client errors are handled without + an abortion of the client and reported separately, see below). Later in + this section it is assumed that the discussed errors are only the + direct client errors and they are not internal + <span class="application">pgbench</span> errors. + </p></li></ul></div><p> + </p><p> + A client's run is aborted in case of a serious error; for example, the + connection with the database server was lost or the end of script was reached + without completing the last transaction. In addition, if execution of an SQL + or meta command fails for reasons other than serialization or deadlock errors, + the client is aborted. Otherwise, if an SQL command fails with serialization or + deadlock errors, the client is not aborted. In such cases, the current + transaction is rolled back, which also includes setting the client variables + as they were before the run of this transaction (it is assumed that one + transaction script contains only one transaction; see + <a class="xref" href="pgbench.html#TRANSACTIONS-AND-SCRIPTS" title="What Is the “Transaction” Actually Performed in pgbench?">What Is the "Transaction" Actually Performed in pgbench?</a> for more information). + Transactions with serialization or deadlock errors are repeated after + rollbacks until they complete successfully or reach the maximum + number of tries (specified by the <code class="option">--max-tries</code> option) / the maximum + time of retries (specified by the <code class="option">--latency-limit</code> option) / the end + of benchmark (specified by the <code class="option">--time</code> option). If + the last trial run fails, this transaction will be reported as failed but + the client is not aborted and continues to work. + </p><div class="note"><h3 class="title">Note</h3><p> + Without specifying the <code class="option">--max-tries</code> option, a transaction will + never be retried after a serialization or deadlock error because its default + value is 1. Use an unlimited number of tries (<code class="literal">--max-tries=0</code>) + and the <code class="option">--latency-limit</code> option to limit only the maximum time + of tries. You can also use the <code class="option">--time</code> option to limit the + benchmark duration under an unlimited number of tries. + </p><p> + Be careful when repeating scripts that contain multiple transactions: the + script is always retried completely, so successful transactions can be + performed several times. + </p><p> + Be careful when repeating transactions with shell commands. Unlike the + results of SQL commands, the results of shell commands are not rolled back, + except for the variable value of the <code class="command">\setshell</code> command. + </p></div><p> + The latency of a successful transaction includes the entire time of + transaction execution with rollbacks and retries. The latency is measured + only for successful transactions and commands but not for failed transactions + or commands. + </p><p> + The main report contains the number of failed transactions. If the + <code class="option">--max-tries</code> option is not equal to 1, the main report also + contains statistics related to retries: the total number of retried + transactions and total number of retries. The per-script report inherits all + these fields from the main report. The per-statement report displays retry + statistics only if the <code class="option">--max-tries</code> option is not equal to 1. + </p><p> + If you want to group failures by basic types in per-transaction and + aggregation logs, as well as in the main and per-script reports, use the + <code class="option">--failures-detailed</code> option. If you also want to distinguish + all errors and failures (errors without retrying) by type including which + limit for retries was exceeded and how much it was exceeded by for the + serialization/deadlock failures, use the <code class="option">--verbose-errors</code> + option. + </p></div><div class="refsect2" id="id-1.9.4.11.9.10"><h3>Good Practices</h3><p> + It is very easy to use <span class="application">pgbench</span> to produce completely + meaningless numbers. Here are some guidelines to help you get useful + results. + </p><p> + In the first place, <span class="emphasis"><em>never</em></span> believe any test that runs + for only a few seconds. Use the <code class="option">-t</code> or <code class="option">-T</code> 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. + </p><p> + For the default TPC-B-like test scenario, the initialization scale factor + (<code class="option">-s</code>) should be at least as large as the largest number of + clients you intend to test (<code class="option">-c</code>); else you'll mostly be + measuring update contention. There are only <code class="option">-s</code> rows in + the <code class="structname">pgbench_branches</code> table, and every transaction wants to + update one of them, so <code class="option">-c</code> values in excess of <code class="option">-s</code> + will undoubtedly result in lots of transactions blocked waiting for + other transactions. + </p><p> + 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. + </p><p> + A limitation of <span class="application">pgbench</span> is that it can itself become + the bottleneck when trying to test a large number of client sessions. + This can be alleviated by running <span class="application">pgbench</span> on a different + machine from the database server, although low network latency will be + essential. It might even be useful to run several <span class="application">pgbench</span> + instances concurrently, on several client machines, against the same + database server. + </p></div><div class="refsect2" id="id-1.9.4.11.9.11"><h3>Security</h3><p> + If untrusted users have access to a database that has not adopted a + <a class="link" href="ddl-schemas.html#DDL-SCHEMAS-PATTERNS" title="5.9.6. Usage Patterns">secure schema usage pattern</a>, + do not run <span class="application">pgbench</span> in that + database. <span class="application">pgbench</span> uses unqualified names and + does not manipulate the search path. + </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="app-pgbasebackup.html" title="pg_basebackup">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="reference-client.html" title="PostgreSQL Client Applications">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="app-pgconfig.html" title="pg_config">Next</a></td></tr><tr><td width="40%" align="left" valign="top"><span class="application">pg_basebackup</span> </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> <span class="application">pg_config</span></td></tr></table></div></body></html>
\ No newline at end of file |