| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/pgbench.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="pgbench"> |
| 7 |
<indexterm zone="pgbench"> |
| 8 |
<primary>pgbench</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle><application>pgbench</application></refentrytitle> |
| 13 |
<manvolnum>1</manvolnum> |
| 14 |
<refmiscinfo>Application</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>pgbench</refname> |
| 19 |
<refpurpose>run a benchmark test on <productname>PostgreSQL</productname></refpurpose> |
| 20 |
</refnamediv> |
| 21 |
|
| 22 |
<refsynopsisdiv> |
| 23 |
<cmdsynopsis> |
| 24 |
<command>pgbench</command> |
| 25 |
<arg choice="plain"><option>-i</option></arg> |
| 26 |
<arg rep="repeat"><replaceable>option</replaceable></arg> |
| 27 |
<arg choice="opt"><replaceable>dbname</replaceable></arg> |
| 28 |
</cmdsynopsis> |
| 29 |
<cmdsynopsis> |
| 30 |
<command>pgbench</command> |
| 31 |
<arg rep="repeat"><replaceable>option</replaceable></arg> |
| 32 |
<arg choice="opt"><replaceable>dbname</replaceable></arg> |
| 33 |
</cmdsynopsis> |
| 34 |
</refsynopsisdiv> |
| 35 |
|
| 36 |
<refsect1> |
| 37 |
<title>Description</title> |
| 38 |
<para> |
| 39 |
<application>pgbench</application> is a simple program for running benchmark |
| 40 |
tests on <productname>PostgreSQL</productname>. It runs the same sequence of SQL |
| 41 |
commands over and over, possibly in multiple concurrent database sessions, |
| 42 |
and then calculates the average transaction rate (transactions per second). |
| 43 |
By default, <application>pgbench</application> tests a scenario that is |
| 44 |
loosely based on TPC-B, involving five <command>SELECT</command>, |
| 45 |
<command>UPDATE</command>, and <command>INSERT</command> commands per transaction. |
| 46 |
However, it is easy to test other cases by writing your own transaction |
| 47 |
script files. |
| 48 |
</para> |
| 49 |
|
| 50 |
<para> |
| 51 |
Typical output from <application>pgbench</application> looks like: |
| 52 |
|
| 53 |
<screen> |
| 54 |
transaction type: <builtin: TPC-B (sort of)> |
| 55 |
scaling factor: 10 |
| 56 |
query mode: simple |
| 57 |
number of clients: 10 |
| 58 |
number of threads: 1 |
| 59 |
maximum number of tries: 1 |
| 60 |
number of transactions per client: 1000 |
| 61 |
number of transactions actually processed: 10000/10000 |
| 62 |
number of failed transactions: 0 (0.000%) |
| 63 |
latency average = 11.013 ms |
| 64 |
latency stddev = 7.351 ms |
| 65 |
initial connection time = 45.758 ms |
| 66 |
tps = 896.967014 (without initial connection time) |
| 67 |
</screen> |
| 68 |
|
| 69 |
The first seven lines report some of the most important parameter |
| 70 |
settings. |
| 71 |
The sixth line reports the maximum number of tries for transactions with |
| 72 |
serialization or deadlock errors (see <xref linkend="failures-and-retries"/> |
| 73 |
for more information). |
| 74 |
The eighth line reports the number of transactions completed |
| 75 |
and intended (the latter being just the product of number of clients |
| 76 |
and number of transactions per client); these will be equal unless the run |
| 77 |
failed before completion or some SQL command(s) failed. (In |
| 78 |
<option>-T</option> mode, only the actual number of transactions is printed.) |
| 79 |
The next line reports the number of failed transactions due to |
| 80 |
serialization or deadlock errors (see <xref linkend="failures-and-retries"/> |
| 81 |
for more information). |
| 82 |
The last line reports the number of transactions per second. |
| 83 |
</para> |
| 84 |
|
| 85 |
<para> |
| 86 |
The default TPC-B-like transaction test requires specific tables to be |
| 87 |
set up beforehand. <application>pgbench</application> should be invoked with |
| 88 |
the <option>-i</option> (initialize) option to create and populate these |
| 89 |
tables. (When you are testing a custom script, you don't need this |
| 90 |
step, but will instead need to do whatever setup your test needs.) |
| 91 |
Initialization looks like: |
| 92 |
|
| 93 |
<programlisting> |
| 94 |
pgbench -i <optional> <replaceable>other-options</replaceable> </optional> <replaceable>dbname</replaceable> |
| 95 |
</programlisting> |
| 96 |
|
| 97 |
where <replaceable>dbname</replaceable> is the name of the already-created |
| 98 |
database to test in. (You may also need <option>-h</option>, |
| 99 |
<option>-p</option>, and/or <option>-U</option> options to specify how to |
| 100 |
connect to the database server.) |
| 101 |
</para> |
| 102 |
|
| 103 |
<caution> |
| 104 |
<para> |
| 105 |
<literal>pgbench -i</literal> creates four tables <structname>pgbench_accounts</structname>, |
| 106 |
<structname>pgbench_branches</structname>, <structname>pgbench_history</structname>, and |
| 107 |
<structname>pgbench_tellers</structname>, |
| 108 |
destroying any existing tables of these names. |
| 109 |
Be very careful to use another database if you have tables having these |
| 110 |
names! |
| 111 |
</para> |
| 112 |
</caution> |
| 113 |
|
| 114 |
<para> |
| 115 |
At the default <quote>scale factor</quote> of 1, the tables initially |
| 116 |
contain this many rows: |
| 117 |
<screen> |
| 118 |
table # of rows |
| 119 |
--------------------------------- |
| 120 |
pgbench_branches 1 |
| 121 |
pgbench_tellers 10 |
| 122 |
pgbench_accounts 100000 |
| 123 |
pgbench_history 0 |
| 124 |
</screen> |
| 125 |
You can (and, for most purposes, probably should) increase the number |
| 126 |
of rows by using the <option>-s</option> (scale factor) option. The |
| 127 |
<option>-F</option> (fillfactor) option might also be used at this point. |
| 128 |
</para> |
| 129 |
|
| 130 |
<para> |
| 131 |
Once you have done the necessary setup, you can run your benchmark |
| 132 |
with a command that doesn't include <option>-i</option>, that is |
| 133 |
|
| 134 |
<programlisting> |
| 135 |
pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>dbname</replaceable> |
| 136 |
</programlisting> |
| 137 |
|
| 138 |
In nearly all cases, you'll need some options to make a useful test. |
| 139 |
The most important options are <option>-c</option> (number of clients), |
| 140 |
<option>-t</option> (number of transactions), <option>-T</option> (time limit), |
| 141 |
and <option>-f</option> (specify a custom script file). |
| 142 |
See below for a full list. |
| 143 |
</para> |
| 144 |
</refsect1> |
| 145 |
|
| 146 |
<refsect1> |
| 147 |
<title>Options</title> |
| 148 |
|
| 149 |
<para> |
| 150 |
The following is divided into three subsections. Different options are |
| 151 |
used during database initialization and while running benchmarks, but some |
| 152 |
options are useful in both cases. |
| 153 |
</para> |
| 154 |
|
| 155 |
<refsect2 id="pgbench-init-options"> |
| 156 |
<title>Initialization Options</title> |
| 157 |
|
| 158 |
<para> |
| 159 |
<application>pgbench</application> accepts the following command-line |
| 160 |
initialization arguments: |
| 161 |
|
| 162 |
<variablelist> |
| 163 |
|
| 164 |
<varlistentry id="pgbench-option-dbname"> |
| 165 |
<term><option><optional>-d</optional> <replaceable class="parameter">dbname</replaceable></option></term> |
| 166 |
<term><option><optional>--dbname=</optional><replaceable class="parameter">dbname</replaceable></option></term> |
| 167 |
<listitem> |
| 168 |
<para> |
| 169 |
Specifies the name of the database to test in. If this is |
| 170 |
not specified, the environment variable |
| 171 |
<envar>PGDATABASE</envar> is used. If that is not set, the |
| 172 |
user name specified for the connection is used. |
| 173 |
</para> |
| 174 |
</listitem> |
| 175 |
</varlistentry> |
| 176 |
|
| 177 |
<varlistentry id="pgbench-option-initialize"> |
| 178 |
<term><option>-i</option></term> |
| 179 |
<term><option>--initialize</option></term> |
| 180 |
<listitem> |
| 181 |
<para> |
| 182 |
Required to invoke initialization mode. |
| 183 |
</para> |
| 184 |
</listitem> |
| 185 |
</varlistentry> |
| 186 |
|
| 187 |
<varlistentry id="pgbench-option-init-steps"> |
| 188 |
<term><option>-I <replaceable>init_steps</replaceable></option></term> |
| 189 |
<term><option>--init-steps=<replaceable>init_steps</replaceable></option></term> |
| 190 |
<listitem> |
| 191 |
<para> |
| 192 |
Perform just a selected set of the normal initialization steps. |
| 193 |
<replaceable>init_steps</replaceable> specifies the |
| 194 |
initialization steps to be performed, using one character per step. |
| 195 |
Each step is invoked in the specified order. |
| 196 |
The default is <literal>dtgvp</literal>. |
| 197 |
The available steps are: |
| 198 |
|
| 199 |
<variablelist> |
| 200 |
<varlistentry id="pgbench-option-init-steps-d"> |
| 201 |
<term><literal>d</literal> (Drop)</term> |
| 202 |
<listitem> |
| 203 |
<para> |
| 204 |
Drop any existing <application>pgbench</application> tables. |
| 205 |
</para> |
| 206 |
</listitem> |
| 207 |
</varlistentry> |
| 208 |
<varlistentry id="pgbench-option-init-steps-t"> |
| 209 |
<term><literal>t</literal> (create Tables)</term> |
| 210 |
<listitem> |
| 211 |
<para> |
| 212 |
Create the tables used by the |
| 213 |
standard <application>pgbench</application> scenario, namely |
| 214 |
<structname>pgbench_accounts</structname>, |
| 215 |
<structname>pgbench_branches</structname>, |
| 216 |
<structname>pgbench_history</structname>, and |
| 217 |
<structname>pgbench_tellers</structname>. |
| 218 |
</para> |
| 219 |
</listitem> |
| 220 |
</varlistentry> |
| 221 |
<varlistentry id="pgbench-option-init-steps-g"> |
| 222 |
<term><literal>g</literal> or <literal>G</literal> (Generate data, client-side or server-side)</term> |
| 223 |
<listitem> |
| 224 |
<para> |
| 225 |
Generate data and load it into the standard tables, |
| 226 |
replacing any data already present. |
| 227 |
</para> |
| 228 |
<para> |
| 229 |
With <literal>g</literal> (client-side data generation), |
| 230 |
data is generated in <command>pgbench</command> client and then |
| 231 |
sent to the server. This uses the client/server bandwidth |
| 232 |
extensively through a <command>COPY</command>. |
| 233 |
<command>pgbench</command> uses the <option>FREEZE</option> option |
| 234 |
to load data into ordinary (non-partition) tables with version 14 |
| 235 |
or later of <productname>PostgreSQL</productname> to speed up |
| 236 |
subsequent <command>VACUUM</command>. |
| 237 |
Using <literal>g</literal> causes logging to |
| 238 |
print one message every 100,000 rows while generating data for all |
| 239 |
tables. |
| 240 |
</para> |
| 241 |
<para> |
| 242 |
With <literal>G</literal> (server-side data generation), |
| 243 |
only small queries are sent from the <command>pgbench</command> |
| 244 |
client and then data is actually generated in the server. |
| 245 |
No significant bandwidth is required for this variant, but |
| 246 |
the server will do more work. |
| 247 |
Using <literal>G</literal> causes logging not to print any progress |
| 248 |
message while generating data. |
| 249 |
</para> |
| 250 |
<para> |
| 251 |
The default initialization behavior uses client-side data |
| 252 |
generation (equivalent to <literal>g</literal>). |
| 253 |
</para> |
| 254 |
</listitem> |
| 255 |
</varlistentry> |
| 256 |
<varlistentry id="pgbench-option-init-steps-v"> |
| 257 |
<term><literal>v</literal> (Vacuum)</term> |
| 258 |
<listitem> |
| 259 |
<para> |
| 260 |
Invoke <command>VACUUM</command> on the standard tables. |
| 261 |
</para> |
| 262 |
</listitem> |
| 263 |
</varlistentry> |
| 264 |
<varlistentry id="pgbench-option-init-steps-p"> |
| 265 |
<term><literal>p</literal> (create Primary keys)</term> |
| 266 |
<listitem> |
| 267 |
<para> |
| 268 |
Create primary key indexes on the standard tables. |
| 269 |
</para> |
| 270 |
</listitem> |
| 271 |
</varlistentry> |
| 272 |
<varlistentry id="pgbench-option-init-steps-f"> |
| 273 |
<term><literal>f</literal> (create Foreign keys)</term> |
| 274 |
<listitem> |
| 275 |
<para> |
| 276 |
Create foreign key constraints between the standard tables. |
| 277 |
(Note that this step is not performed by default.) |
| 278 |
</para> |
| 279 |
</listitem> |
| 280 |
</varlistentry> |
| 281 |
</variablelist></para> |
| 282 |
</listitem> |
| 283 |
</varlistentry> |
| 284 |
|
| 285 |
<varlistentry id="pgbench-option-fillfactor"> |
| 286 |
<term><option>-F</option> <replaceable>fillfactor</replaceable></term> |
| 287 |
<term><option>--fillfactor=</option><replaceable>fillfactor</replaceable></term> |
| 288 |
<listitem> |
| 289 |
<para> |
| 290 |
Create the <structname>pgbench_accounts</structname>, |
| 291 |
<structname>pgbench_tellers</structname> and |
| 292 |
<structname>pgbench_branches</structname> tables with the given fillfactor. |
| 293 |
Default is 100. |
| 294 |
</para> |
| 295 |
</listitem> |
| 296 |
</varlistentry> |
| 297 |
|
| 298 |
<varlistentry id="pgbench-option-no-vacuum-init"> |
| 299 |
<term><option>-n</option></term> |
| 300 |
<term><option>--no-vacuum</option></term> |
| 301 |
<listitem> |
| 302 |
<para> |
| 303 |
Perform no vacuuming during initialization. |
| 304 |
(This option suppresses the <literal>v</literal> initialization step, |
| 305 |
even if it was specified in <option>-I</option>.) |
| 306 |
</para> |
| 307 |
</listitem> |
| 308 |
</varlistentry> |
| 309 |
|
| 310 |
<varlistentry id="pgbench-option-quiet"> |
| 311 |
<term><option>-q</option></term> |
| 312 |
<term><option>--quiet</option></term> |
| 313 |
<listitem> |
| 314 |
<para> |
| 315 |
Switch logging to quiet mode, producing only one progress message per 5 |
| 316 |
seconds. The default logging prints one message each 100,000 rows, which |
| 317 |
often outputs many lines per second (especially on good hardware). |
| 318 |
</para> |
| 319 |
<para> |
| 320 |
This setting has no effect if <literal>G</literal> is specified |
| 321 |
in <option>-I</option>. |
| 322 |
</para> |
| 323 |
</listitem> |
| 324 |
</varlistentry> |
| 325 |
|
| 326 |
<varlistentry id="pgbench-option-scale-init"> |
| 327 |
<term><option>-s</option> <replaceable>scale_factor</replaceable></term> |
| 328 |
<term><option>--scale=</option><replaceable>scale_factor</replaceable></term> |
| 329 |
<listitem> |
| 330 |
<para> |
| 331 |
Multiply the number of rows generated by the scale factor. |
| 332 |
For example, <literal>-s 100</literal> will create 10,000,000 rows |
| 333 |
in the <structname>pgbench_accounts</structname> table. Default is 1. |
| 334 |
When the scale is 20,000 or larger, the columns used to |
| 335 |
hold account identifiers (<structfield>aid</structfield> columns) |
| 336 |
will switch to using larger integers (<type>bigint</type>), |
| 337 |
in order to be big enough to hold the range of account |
| 338 |
identifiers. |
| 339 |
</para> |
| 340 |
</listitem> |
| 341 |
</varlistentry> |
| 342 |
|
| 343 |
<varlistentry id="pgbench-option-foreign-keys"> |
| 344 |
<term><option>--foreign-keys</option></term> |
| 345 |
<listitem> |
| 346 |
<para> |
| 347 |
Create foreign key constraints between the standard tables. |
| 348 |
(This option adds the <literal>f</literal> step to the initialization |
| 349 |
step sequence, if it is not already present.) |
| 350 |
</para> |
| 351 |
</listitem> |
| 352 |
</varlistentry> |
| 353 |
|
| 354 |
<varlistentry id="pgbench-option-index-tablespace"> |
| 355 |
<term><option>--index-tablespace=<replaceable>index_tablespace</replaceable></option></term> |
| 356 |
<listitem> |
| 357 |
<para> |
| 358 |
Create indexes in the specified tablespace, rather than the default |
| 359 |
tablespace. |
| 360 |
</para> |
| 361 |
</listitem> |
| 362 |
</varlistentry> |
| 363 |
|
| 364 |
<varlistentry id="pgbench-option-partition-method"> |
| 365 |
<term><option>--partition-method=<replaceable>NAME</replaceable></option></term> |
| 366 |
<listitem> |
| 367 |
<para> |
| 368 |
Create a partitioned <literal>pgbench_accounts</literal> table with |
| 369 |
<replaceable>NAME</replaceable> method. |
| 370 |
Expected values are <literal>range</literal> or <literal>hash</literal>. |
| 371 |
This option requires that <option>--partitions</option> is set to non-zero. |
| 372 |
If unspecified, default is <literal>range</literal>. |
| 373 |
</para> |
| 374 |
</listitem> |
| 375 |
</varlistentry> |
| 376 |
|
| 377 |
<varlistentry id="pgbench-option-partitions"> |
| 378 |
<term><option>--partitions=<replaceable>NUM</replaceable></option></term> |
| 379 |
<listitem> |
| 380 |
<para> |
| 381 |
Create a partitioned <literal>pgbench_accounts</literal> table with |
| 382 |
<replaceable>NUM</replaceable> partitions of nearly equal size for |
| 383 |
the scaled number of accounts. |
| 384 |
Default is <literal>0</literal>, meaning no partitioning. |
| 385 |
</para> |
| 386 |
</listitem> |
| 387 |
</varlistentry> |
| 388 |
|
| 389 |
<varlistentry id="pgbench-option-tablespace"> |
| 390 |
<term><option>--tablespace=<replaceable>tablespace</replaceable></option></term> |
| 391 |
<listitem> |
| 392 |
<para> |
| 393 |
Create tables in the specified tablespace, rather than the default |
| 394 |
tablespace. |
| 395 |
</para> |
| 396 |
</listitem> |
| 397 |
</varlistentry> |
| 398 |
|
| 399 |
<varlistentry id="pgbench-option-unlogged-tables"> |
| 400 |
<term><option>--unlogged-tables</option></term> |
| 401 |
<listitem> |
| 402 |
<para> |
| 403 |
Create all tables as unlogged tables, rather than permanent tables. |
| 404 |
</para> |
| 405 |
</listitem> |
| 406 |
</varlistentry> |
| 407 |
|
| 408 |
</variablelist> |
| 409 |
</para> |
| 410 |
|
| 411 |
</refsect2> |
| 412 |
|
| 413 |
<refsect2 id="pgbench-run-options"> |
| 414 |
<title>Benchmarking Options</title> |
| 415 |
|
| 416 |
<para> |
| 417 |
<application>pgbench</application> accepts the following command-line |
| 418 |
benchmarking arguments: |
| 419 |
|
| 420 |
<variablelist> |
| 421 |
<varlistentry id="pgbench-option-builtin"> |
| 422 |
<term><option>-b</option> <replaceable>scriptname[@weight]</replaceable></term> |
| 423 |
<term><option>--builtin</option>=<replaceable>scriptname[@weight]</replaceable></term> |
| 424 |
<listitem> |
| 425 |
<para> |
| 426 |
Add the specified built-in script to the list of scripts to be executed. |
| 427 |
Available built-in scripts are: <literal>tpcb-like</literal>, |
| 428 |
<literal>simple-update</literal> and <literal>select-only</literal>. |
| 429 |
Unambiguous prefixes of built-in names are accepted. |
| 430 |
With the special name <literal>list</literal>, show the list of built-in scripts |
| 431 |
and exit immediately. |
| 432 |
</para> |
| 433 |
<para> |
| 434 |
Optionally, write an integer weight after <literal>@</literal> to |
| 435 |
adjust the probability of selecting this script versus other ones. |
| 436 |
The default weight is 1. |
| 437 |
See below for details. |
| 438 |
</para> |
| 439 |
</listitem> |
| 440 |
</varlistentry> |
| 441 |
|
| 442 |
<varlistentry id="pgbench-option-client"> |
| 443 |
<term><option>-c</option> <replaceable>clients</replaceable></term> |
| 444 |
<term><option>--client=</option><replaceable>clients</replaceable></term> |
| 445 |
<listitem> |
| 446 |
<para> |
| 447 |
Number of clients simulated, that is, number of concurrent database |
| 448 |
sessions. Default is 1. |
| 449 |
</para> |
| 450 |
</listitem> |
| 451 |
</varlistentry> |
| 452 |
|
| 453 |
<varlistentry id="pgbench-option-connect"> |
| 454 |
<term><option>-C</option></term> |
| 455 |
<term><option>--connect</option></term> |
| 456 |
<listitem> |
| 457 |
<para> |
| 458 |
Establish a new connection for each transaction, rather than |
| 459 |
doing it just once per client session. |
| 460 |
This is useful to measure the connection overhead. |
| 461 |
</para> |
| 462 |
</listitem> |
| 463 |
</varlistentry> |
| 464 |
|
| 465 |
<varlistentry id="pgbench-option-define"> |
| 466 |
<term><option>-D</option> <replaceable>varname</replaceable><literal>=</literal><replaceable>value</replaceable></term> |
| 467 |
<term><option>--define=</option><replaceable>varname</replaceable><literal>=</literal><replaceable>value</replaceable></term> |
| 468 |
<listitem> |
| 469 |
<para> |
| 470 |
Define a variable for use by a custom script (see below). |
| 471 |
Multiple <option>-D</option> options are allowed. |
| 472 |
</para> |
| 473 |
</listitem> |
| 474 |
</varlistentry> |
| 475 |
|
| 476 |
<varlistentry id="pgbench-option-file"> |
| 477 |
<term><option>-f</option> <replaceable>filename[@weight]</replaceable></term> |
| 478 |
<term><option>--file=</option><replaceable>filename[@weight]</replaceable></term> |
| 479 |
<listitem> |
| 480 |
<para> |
| 481 |
Add a transaction script read from <replaceable>filename</replaceable> |
| 482 |
to the list of scripts to be executed. |
| 483 |
</para> |
| 484 |
<para> |
| 485 |
Optionally, write an integer weight after <literal>@</literal> to |
| 486 |
adjust the probability of selecting this script versus other ones. |
| 487 |
The default weight is 1. |
| 488 |
(To use a script file name that includes an <literal>@</literal> |
| 489 |
character, append a weight so that there is no ambiguity, for |
| 490 |
example <literal>filen@me@1</literal>.) |
| 491 |
See below for details. |
| 492 |
</para> |
| 493 |
</listitem> |
| 494 |
</varlistentry> |
| 495 |
|
| 496 |
<varlistentry id="pgbench-option-jobs"> |
| 497 |
<term><option>-j</option> <replaceable>threads</replaceable></term> |
| 498 |
<term><option>--jobs=</option><replaceable>threads</replaceable></term> |
| 499 |
<listitem> |
| 500 |
<para> |
| 501 |
Number of worker threads within <application>pgbench</application>. |
| 502 |
Using more than one thread can be helpful on multi-CPU machines. |
| 503 |
Clients are distributed as evenly as possible among available threads. |
| 504 |
Default is 1. |
| 505 |
</para> |
| 506 |
</listitem> |
| 507 |
</varlistentry> |
| 508 |
|
| 509 |
<varlistentry id="pgbench-option-log"> |
| 510 |
<term><option>-l</option></term> |
| 511 |
<term><option>--log</option></term> |
| 512 |
<listitem> |
| 513 |
<para> |
| 514 |
Write information about each transaction to a log file. |
| 515 |
See below for details. |
| 516 |
</para> |
| 517 |
</listitem> |
| 518 |
</varlistentry> |
| 519 |
|
| 520 |
<varlistentry id="pgbench-option-latency-limit"> |
| 521 |
<term><option>-L</option> <replaceable>limit</replaceable></term> |
| 522 |
<term><option>--latency-limit=</option><replaceable>limit</replaceable></term> |
| 523 |
<listitem> |
| 524 |
<para> |
| 525 |
Transactions that last more than <replaceable>limit</replaceable> milliseconds |
| 526 |
are counted and reported separately, as <firstterm>late</firstterm>. |
| 527 |
</para> |
| 528 |
<para> |
| 529 |
When throttling is used (<option>--rate=...</option>), transactions that |
| 530 |
lag behind schedule by more than <replaceable>limit</replaceable> ms, and thus |
| 531 |
have no hope of meeting the latency limit, are not sent to the server |
| 532 |
at all. They are counted and reported separately as |
| 533 |
<firstterm>skipped</firstterm>. |
| 534 |
</para> |
| 535 |
<para> |
| 536 |
When the <option>--max-tries</option> option is used, a transaction |
| 537 |
which fails due to a serialization anomaly or from a deadlock will not |
| 538 |
be retried if the total time of all its tries is greater than |
| 539 |
<replaceable>limit</replaceable> ms. To limit only the time of tries |
| 540 |
and not their number, use <literal>--max-tries=0</literal>. By |
| 541 |
default, the option <option>--max-tries</option> is set to 1 and |
| 542 |
transactions with serialization/deadlock errors are not retried. See |
| 543 |
<xref linkend="failures-and-retries"/> for more information about |
| 544 |
retrying such transactions. |
| 545 |
</para> |
| 546 |
</listitem> |
| 547 |
</varlistentry> |
| 548 |
|
| 549 |
<varlistentry id="pgbench-option-protocol"> |
| 550 |
<term><option>-M</option> <replaceable>querymode</replaceable></term> |
| 551 |
<term><option>--protocol=</option><replaceable>querymode</replaceable></term> |
| 552 |
<listitem> |
| 553 |
<para> |
| 554 |
Protocol to use for submitting queries to the server: |
| 555 |
<itemizedlist> |
| 556 |
<listitem> |
| 557 |
<para><literal>simple</literal>: use simple query protocol.</para> |
| 558 |
</listitem> |
| 559 |
<listitem> |
| 560 |
<para><literal>extended</literal>: use extended query protocol.</para> |
| 561 |
</listitem> |
| 562 |
<listitem> |
| 563 |
<para><literal>prepared</literal>: use extended query protocol with prepared statements.</para> |
| 564 |
</listitem> |
| 565 |
</itemizedlist> |
| 566 |
|
| 567 |
In the <literal>prepared</literal> mode, <application>pgbench</application> |
| 568 |
reuses the parse analysis result starting from the second query |
| 569 |
iteration, so <application>pgbench</application> runs faster |
| 570 |
than in other modes. |
| 571 |
</para> |
| 572 |
<para> |
| 573 |
The default is simple query protocol. (See <xref linkend="protocol"/> |
| 574 |
for more information.) |
| 575 |
</para> |
| 576 |
</listitem> |
| 577 |
</varlistentry> |
| 578 |
|
| 579 |
<varlistentry id="pgbench-option-no-vacuum-run"> |
| 580 |
<term><option>-n</option></term> |
| 581 |
<term><option>--no-vacuum</option></term> |
| 582 |
<listitem> |
| 583 |
<para> |
| 584 |
Perform no vacuuming before running the test. |
| 585 |
This option is <emphasis>necessary</emphasis> |
| 586 |
if you are running a custom test scenario that does not include |
| 587 |
the standard tables <structname>pgbench_accounts</structname>, |
| 588 |
<structname>pgbench_branches</structname>, <structname>pgbench_history</structname>, and |
| 589 |
<structname>pgbench_tellers</structname>. |
| 590 |
</para> |
| 591 |
</listitem> |
| 592 |
</varlistentry> |
| 593 |
|
| 594 |
<varlistentry id="pgbench-option-skip-some-updates"> |
| 595 |
<term><option>-N</option></term> |
| 596 |
<term><option>--skip-some-updates</option></term> |
| 597 |
<listitem> |
| 598 |
<para> |
| 599 |
Run built-in simple-update script. |
| 600 |
Shorthand for <option>-b simple-update</option>. |
| 601 |
</para> |
| 602 |
</listitem> |
| 603 |
</varlistentry> |
| 604 |
|
| 605 |
<varlistentry id="pgbench-option-progress"> |
| 606 |
<term><option>-P</option> <replaceable>sec</replaceable></term> |
| 607 |
<term><option>--progress=</option><replaceable>sec</replaceable></term> |
| 608 |
<listitem> |
| 609 |
<para> |
| 610 |
Show progress report every <replaceable>sec</replaceable> seconds. The report |
| 611 |
includes the time since the beginning of the run, the TPS since the |
| 612 |
last report, and the transaction latency average, standard deviation, |
| 613 |
and the number of failed transactions since the last report. Under |
| 614 |
throttling (<option>-R</option>), the latency is computed with respect |
| 615 |
to the transaction scheduled start time, not the actual transaction |
| 616 |
beginning time, thus it also includes the average schedule lag time. |
| 617 |
When <option>--max-tries</option> is used to enable transaction retries |
| 618 |
after serialization/deadlock errors, the report includes the number of |
| 619 |
retried transactions and the sum of all retries. |
| 620 |
</para> |
| 621 |
</listitem> |
| 622 |
</varlistentry> |
| 623 |
|
| 624 |
<varlistentry id="pgbench-option-report-latencies"> |
| 625 |
<term><option>-r</option></term> |
| 626 |
<term><option>--report-per-command</option></term> |
| 627 |
<listitem> |
| 628 |
<para> |
| 629 |
Report the following statistics for each command after the benchmark |
| 630 |
finishes: the average per-statement latency (execution time from the |
| 631 |
perspective of the client), the number of failures, and the number of |
| 632 |
retries after serialization or deadlock errors in this command. The |
| 633 |
report displays retry statistics only if the |
| 634 |
<option>--max-tries</option> option is not equal to 1. |
| 635 |
</para> |
| 636 |
</listitem> |
| 637 |
</varlistentry> |
| 638 |
|
| 639 |
<varlistentry id="pgbench-option-rate"> |
| 640 |
<term><option>-R</option> <replaceable>rate</replaceable></term> |
| 641 |
<term><option>--rate=</option><replaceable>rate</replaceable></term> |
| 642 |
<listitem> |
| 643 |
<para> |
| 644 |
Execute transactions targeting the specified rate instead of running |
| 645 |
as fast as possible (the default). The rate is given in transactions |
| 646 |
per second. If the targeted rate is above the maximum possible rate, |
| 647 |
the rate limit won't impact the results. |
| 648 |
</para> |
| 649 |
<para> |
| 650 |
The rate is targeted by starting transactions along a |
| 651 |
Poisson-distributed schedule time line. The expected start time |
| 652 |
schedule moves forward based on when the client first started, not |
| 653 |
when the previous transaction ended. That approach means that when |
| 654 |
transactions go past their original scheduled end time, it is |
| 655 |
possible for later ones to catch up again. |
| 656 |
</para> |
| 657 |
<para> |
| 658 |
When throttling is active, the transaction latency reported at the |
| 659 |
end of the run is calculated from the scheduled start times, so it |
| 660 |
includes the time each transaction had to wait for the previous |
| 661 |
transaction to finish. The wait time is called the schedule lag time, |
| 662 |
and its average and maximum are also reported separately. The |
| 663 |
transaction latency with respect to the actual transaction start time, |
| 664 |
i.e., the time spent executing the transaction in the database, can be |
| 665 |
computed by subtracting the schedule lag time from the reported |
| 666 |
latency. |
| 667 |
</para> |
| 668 |
|
| 669 |
<para> |
| 670 |
If <option>--latency-limit</option> is used together with <option>--rate</option>, |
| 671 |
a transaction can lag behind so much that it is already over the |
| 672 |
latency limit when the previous transaction ends, because the latency |
| 673 |
is calculated from the scheduled start time. Such transactions are |
| 674 |
not sent to the server, but are skipped altogether and counted |
| 675 |
separately. |
| 676 |
</para> |
| 677 |
|
| 678 |
<para> |
| 679 |
A high schedule lag time is an indication that the system cannot |
| 680 |
process transactions at the specified rate, with the chosen number of |
| 681 |
clients and threads. When the average transaction execution time is |
| 682 |
longer than the scheduled interval between each transaction, each |
| 683 |
successive transaction will fall further behind, and the schedule lag |
| 684 |
time will keep increasing the longer the test run is. When that |
| 685 |
happens, you will have to reduce the specified transaction rate. |
| 686 |
</para> |
| 687 |
</listitem> |
| 688 |
</varlistentry> |
| 689 |
|
| 690 |
<varlistentry id="pgbench-option-scale-run"> |
| 691 |
<term><option>-s</option> <replaceable>scale_factor</replaceable></term> |
| 692 |
<term><option>--scale=</option><replaceable>scale_factor</replaceable></term> |
| 693 |
<listitem> |
| 694 |
<para> |
| 695 |
Report the specified scale factor in <application>pgbench</application>'s |
| 696 |
output. With the built-in tests, this is not necessary; the |
| 697 |
correct scale factor will be detected by counting the number of |
| 698 |
rows in the <structname>pgbench_branches</structname> table. |
| 699 |
However, when testing only custom benchmarks (<option>-f</option> option), |
| 700 |
the scale factor will be reported as 1 unless this option is used. |
| 701 |
</para> |
| 702 |
</listitem> |
| 703 |
</varlistentry> |
| 704 |
|
| 705 |
<varlistentry id="pgbench-option-select-only"> |
| 706 |
<term><option>-S</option></term> |
| 707 |
<term><option>--select-only</option></term> |
| 708 |
<listitem> |
| 709 |
<para> |
| 710 |
Run built-in select-only script. |
| 711 |
Shorthand for <option>-b select-only</option>. |
| 712 |
</para> |
| 713 |
</listitem> |
| 714 |
</varlistentry> |
| 715 |
|
| 716 |
<varlistentry id="pgbench-option-transactions"> |
| 717 |
<term><option>-t</option> <replaceable>transactions</replaceable></term> |
| 718 |
<term><option>--transactions=</option><replaceable>transactions</replaceable></term> |
| 719 |
<listitem> |
| 720 |
<para> |
| 721 |
Number of transactions each client runs. Default is 10. |
| 722 |
</para> |
| 723 |
</listitem> |
| 724 |
</varlistentry> |
| 725 |
|
| 726 |
<varlistentry id="pgbench-option-time"> |
| 727 |
<term><option>-T</option> <replaceable>seconds</replaceable></term> |
| 728 |
<term><option>--time=</option><replaceable>seconds</replaceable></term> |
| 729 |
<listitem> |
| 730 |
<para> |
| 731 |
Run the test for this many seconds, rather than a fixed number of |
| 732 |
transactions per client. <option>-t</option> and |
| 733 |
<option>-T</option> are mutually exclusive. |
| 734 |
</para> |
| 735 |
</listitem> |
| 736 |
</varlistentry> |
| 737 |
|
| 738 |
<varlistentry id="pgbench-option-vacuum-all"> |
| 739 |
<term><option>-v</option></term> |
| 740 |
<term><option>--vacuum-all</option></term> |
| 741 |
<listitem> |
| 742 |
<para> |
| 743 |
Vacuum all four standard tables before running the test. |
| 744 |
With neither <option>-n</option> nor <option>-v</option>, <application>pgbench</application> will vacuum the |
| 745 |
<structname>pgbench_tellers</structname> and <structname>pgbench_branches</structname> |
| 746 |
tables, and will truncate <structname>pgbench_history</structname>. |
| 747 |
</para> |
| 748 |
</listitem> |
| 749 |
</varlistentry> |
| 750 |
|
| 751 |
<varlistentry id="pgbench-option-aggregate-interval"> |
| 752 |
<term><option>--aggregate-interval=<replaceable>seconds</replaceable></option></term> |
| 753 |
<listitem> |
| 754 |
<para> |
| 755 |
Length of aggregation interval (in seconds). May be used only |
| 756 |
with <option>-l</option> option. With this option, the log contains |
| 757 |
per-interval summary data, as described below. |
| 758 |
</para> |
| 759 |
</listitem> |
| 760 |
</varlistentry> |
| 761 |
|
| 762 |
<varlistentry id="pgbench-option-exit-on-abort"> |
| 763 |
<term><option>--exit-on-abort</option></term> |
| 764 |
<listitem> |
| 765 |
<para> |
| 766 |
Exit immediately when any client is aborted due to some error. Without |
| 767 |
this option, even when a client is aborted, other clients could |
| 768 |
continue their run as specified by <option>-t</option> |
| 769 |
or <option>-T</option> option, and <application>pgbench</application> |
| 770 |
will print an incomplete results in this case. |
| 771 |
</para> |
| 772 |
<para> |
| 773 |
Note that serialization failures or deadlock failures do not abort the |
| 774 |
client, so they are not affected by this option. |
| 775 |
See <xref linkend="failures-and-retries"/> for more information. |
| 776 |
</para> |
| 777 |
</listitem> |
| 778 |
</varlistentry> |
| 779 |
|
| 780 |
<varlistentry id="pgbench-option-failures-detailed"> |
| 781 |
<term><option>--failures-detailed</option></term> |
| 782 |
<listitem> |
| 783 |
<para> |
| 784 |
Report failures in per-transaction and aggregation logs, as well as in |
| 785 |
the main and per-script reports, grouped by the following types: |
| 786 |
<itemizedlist> |
| 787 |
<listitem> |
| 788 |
<para>serialization failures;</para> |
| 789 |
</listitem> |
| 790 |
<listitem> |
| 791 |
<para>deadlock failures;</para> |
| 792 |
</listitem> |
| 793 |
</itemizedlist> |
| 794 |
See <xref linkend="failures-and-retries"/> for more information. |
| 795 |
</para> |
| 796 |
</listitem> |
| 797 |
</varlistentry> |
| 798 |
|
| 799 |
<varlistentry id="pgbench-option-log-prefix"> |
| 800 |
<term><option>--log-prefix=<replaceable>prefix</replaceable></option></term> |
| 801 |
<listitem> |
| 802 |
<para> |
| 803 |
Set the filename prefix for the log files created by |
| 804 |
<option>--log</option>. The default is <literal>pgbench_log</literal>. |
| 805 |
</para> |
| 806 |
</listitem> |
| 807 |
</varlistentry> |
| 808 |
|
| 809 |
<varlistentry id="pgbench-option-max-tries"> |
| 810 |
<term><option>--max-tries=<replaceable>number_of_tries</replaceable></option></term> |
| 811 |
<listitem> |
| 812 |
<para> |
| 813 |
Enable retries for transactions with serialization/deadlock errors and |
| 814 |
set the maximum number of these tries. This option can be combined with |
| 815 |
the <option>--latency-limit</option> option which limits the total time |
| 816 |
of all transaction tries; moreover, you cannot use an unlimited number |
| 817 |
of tries (<literal>--max-tries=0</literal>) without |
| 818 |
<option>--latency-limit</option> or <option>--time</option>. |
| 819 |
The default value is 1 and transactions with serialization/deadlock |
| 820 |
errors are not retried. See <xref linkend="failures-and-retries"/> |
| 821 |
for more information about retrying such transactions. |
| 822 |
</para> |
| 823 |
</listitem> |
| 824 |
</varlistentry> |
| 825 |
|
| 826 |
<varlistentry id="pgbench-option-progress-timestamp"> |
| 827 |
<term><option>--progress-timestamp</option></term> |
| 828 |
<listitem> |
| 829 |
<para> |
| 830 |
When showing progress (option <option>-P</option>), use a timestamp |
| 831 |
(Unix epoch) instead of the number of seconds since the |
| 832 |
beginning of the run. The unit is in seconds, with millisecond |
| 833 |
precision after the dot. |
| 834 |
This helps compare logs generated by various tools. |
| 835 |
</para> |
| 836 |
</listitem> |
| 837 |
</varlistentry> |
| 838 |
|
| 839 |
<varlistentry id="pgbench-option-random-seed"> |
| 840 |
<term><option>--random-seed=</option><replaceable>seed</replaceable></term> |
| 841 |
<listitem> |
| 842 |
<para> |
| 843 |
Set random generator seed. Seeds the system random number generator, |
| 844 |
which then produces a sequence of initial generator states, one for |
| 845 |
each thread. |
| 846 |
Values for <replaceable>seed</replaceable> may be: |
| 847 |
<literal>time</literal> (the default, the seed is based on the current time), |
| 848 |
<literal>rand</literal> (use a strong random source, failing if none |
| 849 |
is available), or an unsigned decimal integer value. |
| 850 |
The random generator is invoked explicitly from a pgbench script |
| 851 |
(<literal>random...</literal> functions) or implicitly (for instance option |
| 852 |
<option>--rate</option> uses it to schedule transactions). |
| 853 |
When explicitly set, the value used for seeding is shown on the terminal. |
| 854 |
Any value allowed for <replaceable>seed</replaceable> may also be |
| 855 |
provided through the environment variable |
| 856 |
<literal>PGBENCH_RANDOM_SEED</literal>. |
| 857 |
To ensure that the provided seed impacts all possible uses, put this option |
| 858 |
first or use the environment variable. |
| 859 |
</para> |
| 860 |
<para> |
| 861 |
Setting the seed explicitly allows to reproduce a <command>pgbench</command> |
| 862 |
run exactly, as far as random numbers are concerned. |
| 863 |
As the random state is managed per thread, this means the exact same |
| 864 |
<command>pgbench</command> run for an identical invocation if there is one |
| 865 |
client per thread and there are no external or data dependencies. |
| 866 |
From a statistical viewpoint reproducing runs exactly is a bad idea because |
| 867 |
it can hide the performance variability or improve performance unduly, |
| 868 |
e.g., by hitting the same pages as a previous run. |
| 869 |
However, it may also be of great help for debugging, for instance |
| 870 |
re-running a tricky case which leads to an error. |
| 871 |
Use wisely. |
| 872 |
</para> |
| 873 |
</listitem> |
| 874 |
</varlistentry> |
| 875 |
|
| 876 |
<varlistentry id="pgbench-option-sampling-rate"> |
| 877 |
<term><option>--sampling-rate=<replaceable>rate</replaceable></option></term> |
| 878 |
<listitem> |
| 879 |
<para> |
| 880 |
Sampling rate, used when writing data into the log, to reduce the |
| 881 |
amount of log generated. If this option is given, only the specified |
| 882 |
fraction of transactions are logged. 1.0 means all transactions will |
| 883 |
be logged, 0.05 means only 5% of the transactions will be logged. |
| 884 |
</para> |
| 885 |
<para> |
| 886 |
Remember to take the sampling rate into account when processing the |
| 887 |
log file. For example, when computing TPS values, you need to multiply |
| 888 |
the numbers accordingly (e.g., with 0.01 sample rate, you'll only get |
| 889 |
1/100 of the actual TPS). |
| 890 |
</para> |
| 891 |
</listitem> |
| 892 |
</varlistentry> |
| 893 |
|
| 894 |
<varlistentry id="pgbench-option-show-script"> |
| 895 |
<term><option>--show-script=</option><replaceable>scriptname</replaceable></term> |
| 896 |
<listitem> |
| 897 |
<para> |
| 898 |
Show the actual code of builtin script <replaceable>scriptname</replaceable> |
| 899 |
on stderr, and exit immediately. |
| 900 |
</para> |
| 901 |
</listitem> |
| 902 |
</varlistentry> |
| 903 |
|
| 904 |
<varlistentry id="pgbench-option-verbose-errors"> |
| 905 |
<term><option>--verbose-errors</option></term> |
| 906 |
<listitem> |
| 907 |
<para> |
| 908 |
Print messages about all errors and failures (errors without retrying) |
| 909 |
including which limit for retries was exceeded and how far it was |
| 910 |
exceeded for the serialization/deadlock failures. (Note that in this |
| 911 |
case the output can be significantly increased.) |
| 912 |
See <xref linkend="failures-and-retries"/> for more information. |
| 913 |
</para> |
| 914 |
</listitem> |
| 915 |
</varlistentry> |
| 916 |
|
| 917 |
</variablelist> |
| 918 |
</para> |
| 919 |
|
| 920 |
</refsect2> |
| 921 |
|
| 922 |
<refsect2 id="pgbench-common-options"> |
| 923 |
<title>Common Options</title> |
| 924 |
|
| 925 |
<para> |
| 926 |
<application>pgbench</application> also accepts the following common command-line |
| 927 |
arguments for connection parameters and other common settings: |
| 928 |
|
| 929 |
<variablelist> |
| 930 |
<varlistentry id="pgbench-option-debug"> |
| 931 |
<term><option>--debug</option></term> |
| 932 |
<listitem> |
| 933 |
<para> |
| 934 |
Print debugging output. |
| 935 |
</para> |
| 936 |
</listitem> |
| 937 |
</varlistentry> |
| 938 |
|
| 939 |
<varlistentry id="pgbench-option-host"> |
| 940 |
<term><option>-h</option> <replaceable>hostname</replaceable></term> |
| 941 |
<term><option>--host=</option><replaceable>hostname</replaceable></term> |
| 942 |
<listitem> |
| 943 |
<para> |
| 944 |
The database server's host name |
| 945 |
</para> |
| 946 |
</listitem> |
| 947 |
</varlistentry> |
| 948 |
|
| 949 |
<varlistentry id="pgbench-option-port"> |
| 950 |
<term><option>-p</option> <replaceable>port</replaceable></term> |
| 951 |
<term><option>--port=</option><replaceable>port</replaceable></term> |
| 952 |
<listitem> |
| 953 |
<para> |
| 954 |
The database server's port number |
| 955 |
</para> |
| 956 |
</listitem> |
| 957 |
</varlistentry> |
| 958 |
|
| 959 |
<varlistentry id="pgbench-option-username"> |
| 960 |
<term><option>-U</option> <replaceable>login</replaceable></term> |
| 961 |
<term><option>--username=</option><replaceable>login</replaceable></term> |
| 962 |
<listitem> |
| 963 |
<para> |
| 964 |
The user name to connect as |
| 965 |
</para> |
| 966 |
</listitem> |
| 967 |
</varlistentry> |
| 968 |
|
| 969 |
<varlistentry id="pgbench-option-version"> |
| 970 |
<term><option>-V</option></term> |
| 971 |
<term><option>--version</option></term> |
| 972 |
<listitem> |
| 973 |
<para> |
| 974 |
Print the <application>pgbench</application> version and exit. |
| 975 |
</para> |
| 976 |
</listitem> |
| 977 |
</varlistentry> |
| 978 |
|
| 979 |
<varlistentry id="pgbench-option-help"> |
| 980 |
<term><option>-?</option></term> |
| 981 |
<term><option>--help</option></term> |
| 982 |
<listitem> |
| 983 |
<para> |
| 984 |
Show help about <application>pgbench</application> command line |
| 985 |
arguments, and exit. |
| 986 |
</para> |
| 987 |
</listitem> |
| 988 |
</varlistentry> |
| 989 |
</variablelist> |
| 990 |
</para> |
| 991 |
|
| 992 |
</refsect2> |
| 993 |
</refsect1> |
| 994 |
|
| 995 |
<refsect1> |
| 996 |
<title>Exit Status</title> |
| 997 |
|
| 998 |
<para> |
| 999 |
A successful run will exit with status 0. Exit status 1 indicates static |
| 1000 |
problems such as invalid command-line options or internal errors which |
| 1001 |
are supposed to never occur. Early errors that occur when starting |
| 1002 |
benchmark such as initial connection failures also exit with status 1. |
| 1003 |
Errors during the run such as database errors or problems in the script |
| 1004 |
will result in exit status 2. In the latter case, |
| 1005 |
<application>pgbench</application> will print partial results if |
| 1006 |
<option>--exit-on-abort</option> option is not specified. |
| 1007 |
</para> |
| 1008 |
</refsect1> |
| 1009 |
|
| 1010 |
<refsect1> |
| 1011 |
<title>Environment</title> |
| 1012 |
|
| 1013 |
<variablelist> |
| 1014 |
<varlistentry id="pgbench-environment-pgdatabase"> |
| 1015 |
<term><envar>PGDATABASE</envar></term> |
| 1016 |
<term><envar>PGHOST</envar></term> |
| 1017 |
<term><envar>PGPORT</envar></term> |
| 1018 |
<term><envar>PGUSER</envar></term> |
| 1019 |
|
| 1020 |
<listitem> |
| 1021 |
<para> |
| 1022 |
Default connection parameters. |
| 1023 |
</para> |
| 1024 |
</listitem> |
| 1025 |
</varlistentry> |
| 1026 |
</variablelist> |
| 1027 |
|
| 1028 |
<para> |
| 1029 |
This utility, like most other <productname>PostgreSQL</productname> utilities, |
| 1030 |
uses the environment variables supported by <application>libpq</application> |
| 1031 |
(see <xref linkend="libpq-envars"/>). |
| 1032 |
</para> |
| 1033 |
|
| 1034 |
<para> |
| 1035 |
The environment variable <envar>PG_COLOR</envar> specifies whether to use |
| 1036 |
color in diagnostic messages. Possible values are |
| 1037 |
<literal>always</literal>, <literal>auto</literal> and |
| 1038 |
<literal>never</literal>. |
| 1039 |
</para> |
| 1040 |
</refsect1> |
| 1041 |
|
| 1042 |
<refsect1> |
| 1043 |
<title>Notes</title> |
| 1044 |
|
| 1045 |
<refsect2 id="transactions-and-scripts" xreflabel="What Is the "Transaction" Actually Performed in pgbench?"> |
| 1046 |
<title>What Is the <quote>Transaction</quote> Actually Performed in <application>pgbench</application>?</title> |
| 1047 |
|
| 1048 |
<para> |
| 1049 |
<application>pgbench</application> executes test scripts chosen randomly |
| 1050 |
from a specified list. |
| 1051 |
The scripts may include built-in scripts specified with <option>-b</option> |
| 1052 |
and user-provided scripts specified with <option>-f</option>. |
| 1053 |
Each script may be given a relative weight specified after an |
| 1054 |
<literal>@</literal> so as to change its selection probability. |
| 1055 |
The default weight is <literal>1</literal>. |
| 1056 |
Scripts with a weight of <literal>0</literal> are ignored. |
| 1057 |
</para> |
| 1058 |
|
| 1059 |
<para> |
| 1060 |
The default built-in transaction script (also invoked with <option>-b tpcb-like</option>) |
| 1061 |
issues seven commands per transaction over randomly chosen <literal>aid</literal>, |
| 1062 |
<literal>tid</literal>, <literal>bid</literal> and <literal>delta</literal>. |
| 1063 |
The scenario is inspired by the TPC-B benchmark, but is not actually TPC-B, |
| 1064 |
hence the name. |
| 1065 |
</para> |
| 1066 |
|
| 1067 |
<orderedlist> |
| 1068 |
<listitem><para><literal>BEGIN;</literal></para></listitem> |
| 1069 |
<listitem><para><literal>UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;</literal></para></listitem> |
| 1070 |
<listitem><para><literal>SELECT abalance FROM pgbench_accounts WHERE aid = :aid;</literal></para></listitem> |
| 1071 |
<listitem><para><literal>UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;</literal></para></listitem> |
| 1072 |
<listitem><para><literal>UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;</literal></para></listitem> |
| 1073 |
<listitem><para><literal>INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);</literal></para></listitem> |
| 1074 |
<listitem><para><literal>END;</literal></para></listitem> |
| 1075 |
</orderedlist> |
| 1076 |
|
| 1077 |
<para> |
| 1078 |
If you select the <literal>simple-update</literal> built-in (also <option>-N</option>), |
| 1079 |
steps 4 and 5 aren't included in the transaction. |
| 1080 |
This will avoid update contention on these tables, but |
| 1081 |
it makes the test case even less like TPC-B. |
| 1082 |
</para> |
| 1083 |
|
| 1084 |
<para> |
| 1085 |
If you select the <literal>select-only</literal> built-in (also <option>-S</option>), |
| 1086 |
only the <command>SELECT</command> is issued. |
| 1087 |
</para> |
| 1088 |
</refsect2> |
| 1089 |
|
| 1090 |
<refsect2> |
| 1091 |
<title>Custom Scripts</title> |
| 1092 |
|
| 1093 |
<para> |
| 1094 |
<application>pgbench</application> has support for running custom |
| 1095 |
benchmark scenarios by replacing the default transaction script |
| 1096 |
(described above) with a transaction script read from a file |
| 1097 |
(<option>-f</option> option). In this case a <quote>transaction</quote> |
| 1098 |
counts as one execution of a script file. |
| 1099 |
</para> |
| 1100 |
|
| 1101 |
<para> |
| 1102 |
A script file contains one or more SQL commands terminated by |
| 1103 |
semicolons. Empty lines and lines beginning with |
| 1104 |
<literal>--</literal> are ignored. Script files can also contain |
| 1105 |
<quote>meta commands</quote>, which are interpreted by <application>pgbench</application> |
| 1106 |
itself, as described below. |
| 1107 |
</para> |
| 1108 |
|
| 1109 |
<note> |
| 1110 |
<para> |
| 1111 |
Before <productname>PostgreSQL</productname> 9.6, SQL commands in script files |
| 1112 |
were terminated by newlines, and so they could not be continued across |
| 1113 |
lines. Now a semicolon is <emphasis>required</emphasis> to separate consecutive |
| 1114 |
SQL commands (though an SQL command does not need one if it is followed |
| 1115 |
by a meta command). If you need to create a script file that works with |
| 1116 |
both old and new versions of <application>pgbench</application>, be sure to write |
| 1117 |
each SQL command on a single line ending with a semicolon. |
| 1118 |
</para> |
| 1119 |
<para> |
| 1120 |
It is assumed that <application>pgbench</application> scripts do not contain |
| 1121 |
incomplete blocks of SQL |
| 1122 |
transactions. If at runtime the client reaches the end of the script without |
| 1123 |
completing the last transaction block, it will be aborted. |
| 1124 |
</para> |
| 1125 |
</note> |
| 1126 |
|
| 1127 |
<para> |
| 1128 |
There is a simple variable-substitution facility for script files. |
| 1129 |
Variable names must consist of letters (including non-Latin letters), |
| 1130 |
digits, and underscores, with the first character not being a digit. |
| 1131 |
Variables can be set by the command-line <option>-D</option> option, |
| 1132 |
explained above, or by the meta commands explained below. |
| 1133 |
In addition to any variables preset by <option>-D</option> command-line options, |
| 1134 |
there are a few variables that are preset automatically, listed in |
| 1135 |
<xref linkend="pgbench-automatic-variables"/>. A value specified for these |
| 1136 |
variables using <option>-D</option> takes precedence over the automatic presets. |
| 1137 |
Once set, a variable's |
| 1138 |
value can be inserted into an SQL command by writing |
| 1139 |
<literal>:</literal><replaceable>variablename</replaceable>. When running more than |
| 1140 |
one client session, each session has its own set of variables. |
| 1141 |
<application>pgbench</application> supports up to 255 variable uses in one |
| 1142 |
statement. |
| 1143 |
</para> |
| 1144 |
|
| 1145 |
<table id="pgbench-automatic-variables"> |
| 1146 |
<title>pgbench Automatic Variables</title> |
| 1147 |
<tgroup cols="2"> |
| 1148 |
<colspec colname="col1" colwidth="1*"/> |
| 1149 |
<colspec colname="col2" colwidth="2*"/> |
| 1150 |
<thead> |
| 1151 |
<row> |
| 1152 |
<entry>Variable</entry> |
| 1153 |
<entry>Description</entry> |
| 1154 |
</row> |
| 1155 |
</thead> |
| 1156 |
|
| 1157 |
<tbody> |
| 1158 |
<row> |
| 1159 |
<entry> <literal>client_id</literal> </entry> |
| 1160 |
<entry>unique number identifying the client session (starts from zero)</entry> |
| 1161 |
</row> |
| 1162 |
|
| 1163 |
<row> |
| 1164 |
<entry> <literal>default_seed</literal> </entry> |
| 1165 |
<entry>seed used in hash and pseudorandom permutation functions by default</entry> |
| 1166 |
</row> |
| 1167 |
|
| 1168 |
<row> |
| 1169 |
<entry> <literal>random_seed</literal> </entry> |
| 1170 |
<entry>random generator seed (unless overwritten with <option>-D</option>)</entry> |
| 1171 |
</row> |
| 1172 |
|
| 1173 |
<row> |
| 1174 |
<entry> <literal>scale</literal> </entry> |
| 1175 |
<entry>current scale factor</entry> |
| 1176 |
</row> |
| 1177 |
</tbody> |
| 1178 |
</tgroup> |
| 1179 |
</table> |
| 1180 |
|
| 1181 |
<para> |
| 1182 |
Script file meta commands begin with a backslash (<literal>\</literal>) and |
| 1183 |
normally extend to the end of the line, although they can be continued |
| 1184 |
to additional lines by writing backslash-return. |
| 1185 |
Arguments to a meta command are separated by white space. |
| 1186 |
These meta commands are supported: |
| 1187 |
</para> |
| 1188 |
|
| 1189 |
<variablelist> |
| 1190 |
<varlistentry id="pgbench-metacommand-gset"> |
| 1191 |
<term> |
| 1192 |
<literal>\gset [<replaceable>prefix</replaceable>]</literal> |
| 1193 |
<literal>\aset [<replaceable>prefix</replaceable>]</literal> |
| 1194 |
</term> |
| 1195 |
|
| 1196 |
<listitem> |
| 1197 |
<para> |
| 1198 |
These commands may be used to end SQL queries, taking the place of the |
| 1199 |
terminating semicolon (<literal>;</literal>). |
| 1200 |
</para> |
| 1201 |
|
| 1202 |
<para> |
| 1203 |
When the <literal>\gset</literal> command is used, the preceding SQL query is |
| 1204 |
expected to return one row, the columns of which are stored into variables |
| 1205 |
named after column names, and prefixed with <replaceable>prefix</replaceable> |
| 1206 |
if provided. |
| 1207 |
</para> |
| 1208 |
|
| 1209 |
<para> |
| 1210 |
When the <literal>\aset</literal> command is used, all combined SQL queries |
| 1211 |
(separated by <literal>\;</literal>) have their columns stored into variables |
| 1212 |
named after column names, and prefixed with <replaceable>prefix</replaceable> |
| 1213 |
if provided. If a query returns no row, no assignment is made and the variable |
| 1214 |
can be tested for existence to detect this. If a query returns more than one |
| 1215 |
row, the last value is kept. |
| 1216 |
</para> |
| 1217 |
|
| 1218 |
<para> |
| 1219 |
<literal>\gset</literal> and <literal>\aset</literal> cannot be used in |
| 1220 |
pipeline mode, since the query results are not yet available by the time |
| 1221 |
the commands would need them. |
| 1222 |
</para> |
| 1223 |
|
| 1224 |
<para> |
| 1225 |
The following example puts the final account balance from the first query |
| 1226 |
into variable <replaceable>abalance</replaceable>, and fills variables |
| 1227 |
<replaceable>p_two</replaceable> and <replaceable>p_three</replaceable> |
| 1228 |
with integers from the third query. |
| 1229 |
The result of the second query is discarded. |
| 1230 |
The result of the two last combined queries are stored in variables |
| 1231 |
<replaceable>four</replaceable> and <replaceable>five</replaceable>. |
| 1232 |
<programlisting> |
| 1233 |
UPDATE pgbench_accounts |
| 1234 |
SET abalance = abalance + :delta |
| 1235 |
WHERE aid = :aid |
| 1236 |
RETURNING abalance \gset |
| 1237 |
-- compound of two queries |
| 1238 |
SELECT 1 \; |
| 1239 |
SELECT 2 AS two, 3 AS three \gset p_ |
| 1240 |
SELECT 4 AS four \; SELECT 5 AS five \aset |
| 1241 |
</programlisting></para> |
| 1242 |
</listitem> |
| 1243 |
</varlistentry> |
| 1244 |
|
| 1245 |
<varlistentry id="pgbench-metacommand-if-else"> |
| 1246 |
<term><literal>\if</literal> <replaceable class="parameter">expression</replaceable></term> |
| 1247 |
<term><literal>\elif</literal> <replaceable class="parameter">expression</replaceable></term> |
| 1248 |
<term><literal>\else</literal></term> |
| 1249 |
<term><literal>\endif</literal></term> |
| 1250 |
<listitem> |
| 1251 |
<para> |
| 1252 |
This group of commands implements nestable conditional blocks, |
| 1253 |
similarly to <literal>psql</literal>'s <xref linkend="psql-metacommand-if"/>. |
| 1254 |
Conditional expressions are identical to those with <literal>\set</literal>, |
| 1255 |
with non-zero values interpreted as true. |
| 1256 |
</para> |
| 1257 |
</listitem> |
| 1258 |
</varlistentry> |
| 1259 |
|
| 1260 |
<varlistentry id="pgbench-metacommand-set"> |
| 1261 |
<term> |
| 1262 |
<literal>\set <replaceable>varname</replaceable> <replaceable>expression</replaceable></literal> |
| 1263 |
</term> |
| 1264 |
|
| 1265 |
<listitem> |
| 1266 |
<para> |
| 1267 |
Sets variable <replaceable>varname</replaceable> to a value calculated |
| 1268 |
from <replaceable>expression</replaceable>. |
| 1269 |
The expression may contain the <literal>NULL</literal> constant, |
| 1270 |
Boolean constants <literal>TRUE</literal> and <literal>FALSE</literal>, |
| 1271 |
integer constants such as <literal>5432</literal>, |
| 1272 |
double constants such as <literal>3.14159</literal>, |
| 1273 |
references to variables <literal>:</literal><replaceable>variablename</replaceable>, |
| 1274 |
<link linkend="pgbench-builtin-operators">operators</link> |
| 1275 |
with their usual SQL precedence and associativity, |
| 1276 |
<link linkend="pgbench-builtin-functions">function calls</link>, |
| 1277 |
SQL <link linkend="functions-case"><token>CASE</token> generic conditional |
| 1278 |
expressions</link> and parentheses. |
| 1279 |
</para> |
| 1280 |
|
| 1281 |
<para> |
| 1282 |
Functions and most operators return <literal>NULL</literal> on |
| 1283 |
<literal>NULL</literal> input. |
| 1284 |
</para> |
| 1285 |
|
| 1286 |
<para> |
| 1287 |
For conditional purposes, non zero numerical values are |
| 1288 |
<literal>TRUE</literal>, zero numerical values and <literal>NULL</literal> |
| 1289 |
are <literal>FALSE</literal>. |
| 1290 |
</para> |
| 1291 |
|
| 1292 |
<para> |
| 1293 |
Too large or small integer and double constants, as well as |
| 1294 |
integer arithmetic operators (<literal>+</literal>, |
| 1295 |
<literal>-</literal>, <literal>*</literal> and <literal>/</literal>) |
| 1296 |
raise errors on overflows. |
| 1297 |
</para> |
| 1298 |
|
| 1299 |
<para> |
| 1300 |
When no final <token>ELSE</token> clause is provided to a |
| 1301 |
<token>CASE</token>, the default value is <literal>NULL</literal>. |
| 1302 |
</para> |
| 1303 |
|
| 1304 |
<para> |
| 1305 |
Examples: |
| 1306 |
<programlisting> |
| 1307 |
\set ntellers 10 * :scale |
| 1308 |
\set aid (1021 * random(1, 100000 * :scale)) % \ |
| 1309 |
(100000 * :scale) + 1 |
| 1310 |
\set divx CASE WHEN :x <> 0 THEN :y/:x ELSE NULL END |
| 1311 |
</programlisting></para> |
| 1312 |
</listitem> |
| 1313 |
</varlistentry> |
| 1314 |
|
| 1315 |
<varlistentry id="pgbench-metacommand-sleep"> |
| 1316 |
<term> |
| 1317 |
<literal>\sleep <replaceable>number</replaceable> [ us | ms | s ]</literal> |
| 1318 |
</term> |
| 1319 |
|
| 1320 |
<listitem> |
| 1321 |
<para> |
| 1322 |
Causes script execution to sleep for the specified duration in |
| 1323 |
microseconds (<literal>us</literal>), milliseconds (<literal>ms</literal>) or seconds |
| 1324 |
(<literal>s</literal>). If the unit is omitted then seconds are the default. |
| 1325 |
<replaceable>number</replaceable> can be either an integer constant or a |
| 1326 |
<literal>:</literal><replaceable>variablename</replaceable> reference to a variable |
| 1327 |
having an integer value. |
| 1328 |
</para> |
| 1329 |
|
| 1330 |
<para> |
| 1331 |
Example: |
| 1332 |
<programlisting> |
| 1333 |
\sleep 10 ms |
| 1334 |
</programlisting></para> |
| 1335 |
</listitem> |
| 1336 |
</varlistentry> |
| 1337 |
|
| 1338 |
<varlistentry id="pgbench-metacommand-setshell"> |
| 1339 |
<term> |
| 1340 |
<literal>\setshell <replaceable>varname</replaceable> <replaceable>command</replaceable> [ <replaceable>argument</replaceable> ... ]</literal> |
| 1341 |
</term> |
| 1342 |
|
| 1343 |
<listitem> |
| 1344 |
<para> |
| 1345 |
Sets variable <replaceable>varname</replaceable> to the result of the shell command |
| 1346 |
<replaceable>command</replaceable> with the given <replaceable>argument</replaceable>(s). |
| 1347 |
The command must return an integer value through its standard output. |
| 1348 |
</para> |
| 1349 |
|
| 1350 |
<para> |
| 1351 |
<replaceable>command</replaceable> and each <replaceable>argument</replaceable> can be either |
| 1352 |
a text constant or a <literal>:</literal><replaceable>variablename</replaceable> reference |
| 1353 |
to a variable. If you want to use an <replaceable>argument</replaceable> starting |
| 1354 |
with a colon, write an additional colon at the beginning of |
| 1355 |
<replaceable>argument</replaceable>. |
| 1356 |
</para> |
| 1357 |
|
| 1358 |
<para> |
| 1359 |
Example: |
| 1360 |
<programlisting> |
| 1361 |
\setshell variable_to_be_assigned command literal_argument :variable ::literal_starting_with_colon |
| 1362 |
</programlisting></para> |
| 1363 |
</listitem> |
| 1364 |
</varlistentry> |
| 1365 |
|
| 1366 |
<varlistentry id="pgbench-metacommand-shell"> |
| 1367 |
<term> |
| 1368 |
<literal>\shell <replaceable>command</replaceable> [ <replaceable>argument</replaceable> ... ]</literal> |
| 1369 |
</term> |
| 1370 |
|
| 1371 |
<listitem> |
| 1372 |
<para> |
| 1373 |
Same as <literal>\setshell</literal>, but the result of the command |
| 1374 |
is discarded. |
| 1375 |
</para> |
| 1376 |
|
| 1377 |
<para> |
| 1378 |
Example: |
| 1379 |
<programlisting> |
| 1380 |
\shell command literal_argument :variable ::literal_starting_with_colon |
| 1381 |
</programlisting></para> |
| 1382 |
</listitem> |
| 1383 |
</varlistentry> |
| 1384 |
|
| 1385 |
<varlistentry id="pgbench-metacommand-pipeline"> |
| 1386 |
<term><literal>\startpipeline</literal></term> |
| 1387 |
<term><literal>\syncpipeline</literal></term> |
| 1388 |
<term><literal>\endpipeline</literal></term> |
| 1389 |
|
| 1390 |
<listitem> |
| 1391 |
<para> |
| 1392 |
This group of commands implements pipelining of SQL statements. |
| 1393 |
A pipeline must begin with a <command>\startpipeline</command> |
| 1394 |
and end with an <command>\endpipeline</command>. In between there |
| 1395 |
may be any number of <command>\syncpipeline</command> commands, |
| 1396 |
which sends a <link linkend="protocol-flow-ext-query">sync message</link> |
| 1397 |
without ending the ongoing pipeline and flushing the send buffer. |
| 1398 |
In pipeline mode, statements are sent to the server without waiting |
| 1399 |
for the results of previous statements. See |
| 1400 |
<xref linkend="libpq-pipeline-mode"/> for more details. |
| 1401 |
Pipeline mode requires the use of extended query protocol. |
| 1402 |
</para> |
| 1403 |
</listitem> |
| 1404 |
</varlistentry> |
| 1405 |
|
| 1406 |
</variablelist> |
| 1407 |
</refsect2> |
| 1408 |
|
| 1409 |
<refsect2 id="pgbench-builtin-operators"> |
| 1410 |
<title>Built-in Operators</title> |
| 1411 |
|
| 1412 |
<para> |
| 1413 |
The arithmetic, bitwise, comparison and logical operators listed in |
| 1414 |
<xref linkend="pgbench-operators"/> are built into <application>pgbench</application> |
| 1415 |
and may be used in expressions appearing in |
| 1416 |
<link linkend="pgbench-metacommand-set"><literal>\set</literal></link>. |
| 1417 |
The operators are listed in increasing precedence order. |
| 1418 |
Except as noted, operators taking two numeric inputs will produce |
| 1419 |
a double value if either input is double, otherwise they produce |
| 1420 |
an integer result. |
| 1421 |
</para> |
| 1422 |
|
| 1423 |
<table id="pgbench-operators"> |
| 1424 |
<title>pgbench Operators</title> |
| 1425 |
<tgroup cols="1"> |
| 1426 |
<thead> |
| 1427 |
<row> |
| 1428 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1429 |
Operator |
| 1430 |
</para> |
| 1431 |
<para> |
| 1432 |
Description |
| 1433 |
</para> |
| 1434 |
<para> |
| 1435 |
Example(s) |
| 1436 |
</para></entry> |
| 1437 |
</row> |
| 1438 |
</thead> |
| 1439 |
|
| 1440 |
<tbody> |
| 1441 |
<row> |
| 1442 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1443 |
<replaceable>boolean</replaceable> <literal>OR</literal> <replaceable>boolean</replaceable> |
| 1444 |
<returnvalue><replaceable>boolean</replaceable></returnvalue> |
| 1445 |
</para> |
| 1446 |
<para> |
| 1447 |
Logical OR |
| 1448 |
</para> |
| 1449 |
<para> |
| 1450 |
<literal>5 or 0</literal> |
| 1451 |
<returnvalue>TRUE</returnvalue> |
| 1452 |
</para></entry> |
| 1453 |
</row> |
| 1454 |
|
| 1455 |
<row> |
| 1456 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1457 |
<replaceable>boolean</replaceable> <literal>AND</literal> <replaceable>boolean</replaceable> |
| 1458 |
<returnvalue><replaceable>boolean</replaceable></returnvalue> |
| 1459 |
</para> |
| 1460 |
<para> |
| 1461 |
Logical AND |
| 1462 |
</para> |
| 1463 |
<para> |
| 1464 |
<literal>3 and 0</literal> |
| 1465 |
<returnvalue>FALSE</returnvalue> |
| 1466 |
</para></entry> |
| 1467 |
</row> |
| 1468 |
|
| 1469 |
<row> |
| 1470 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1471 |
<literal>NOT</literal> <replaceable>boolean</replaceable> |
| 1472 |
<returnvalue><replaceable>boolean</replaceable></returnvalue> |
| 1473 |
</para> |
| 1474 |
<para> |
| 1475 |
Logical NOT |
| 1476 |
</para> |
| 1477 |
<para> |
| 1478 |
<literal>not false</literal> |
| 1479 |
<returnvalue>TRUE</returnvalue> |
| 1480 |
</para></entry> |
| 1481 |
</row> |
| 1482 |
|
| 1483 |
<row> |
| 1484 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1485 |
<replaceable>boolean</replaceable> <literal>IS [NOT] (NULL|TRUE|FALSE)</literal> |
| 1486 |
<returnvalue><replaceable>boolean</replaceable></returnvalue> |
| 1487 |
</para> |
| 1488 |
<para> |
| 1489 |
Boolean value tests |
| 1490 |
</para> |
| 1491 |
<para> |
| 1492 |
<literal>1 is null</literal> |
| 1493 |
<returnvalue>FALSE</returnvalue> |
| 1494 |
</para></entry> |
| 1495 |
</row> |
| 1496 |
|
| 1497 |
<row> |
| 1498 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1499 |
<replaceable>value</replaceable> <literal>ISNULL|NOTNULL</literal> |
| 1500 |
<returnvalue><replaceable>boolean</replaceable></returnvalue> |
| 1501 |
</para> |
| 1502 |
<para> |
| 1503 |
Nullness tests |
| 1504 |
</para> |
| 1505 |
<para> |
| 1506 |
<literal>1 notnull</literal> |
| 1507 |
<returnvalue>TRUE</returnvalue> |
| 1508 |
</para></entry> |
| 1509 |
</row> |
| 1510 |
|
| 1511 |
<row> |
| 1512 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1513 |
<replaceable>number</replaceable> <literal>=</literal> <replaceable>number</replaceable> |
| 1514 |
<returnvalue><replaceable>boolean</replaceable></returnvalue> |
| 1515 |
</para> |
| 1516 |
<para> |
| 1517 |
Equal |
| 1518 |
</para> |
| 1519 |
<para> |
| 1520 |
<literal>5 = 4</literal> |
| 1521 |
<returnvalue>FALSE</returnvalue> |
| 1522 |
</para></entry> |
| 1523 |
</row> |
| 1524 |
|
| 1525 |
<row> |
| 1526 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1527 |
<replaceable>number</replaceable> <literal><></literal> <replaceable>number</replaceable> |
| 1528 |
<returnvalue><replaceable>boolean</replaceable></returnvalue> |
| 1529 |
</para> |
| 1530 |
<para> |
| 1531 |
Not equal |
| 1532 |
</para> |
| 1533 |
<para> |
| 1534 |
<literal>5 <> 4</literal> |
| 1535 |
<returnvalue>TRUE</returnvalue> |
| 1536 |
</para></entry> |
| 1537 |
</row> |
| 1538 |
|
| 1539 |
<row> |
| 1540 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1541 |
<replaceable>number</replaceable> <literal>!=</literal> <replaceable>number</replaceable> |
| 1542 |
<returnvalue><replaceable>boolean</replaceable></returnvalue> |
| 1543 |
</para> |
| 1544 |
<para> |
| 1545 |
Not equal |
| 1546 |
</para> |
| 1547 |
<para> |
| 1548 |
<literal>5 != 5</literal> |
| 1549 |
<returnvalue>FALSE</returnvalue> |
| 1550 |
</para></entry> |
| 1551 |
</row> |
| 1552 |
|
| 1553 |
<row> |
| 1554 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1555 |
<replaceable>number</replaceable> <literal><</literal> <replaceable>number</replaceable> |
| 1556 |
<returnvalue><replaceable>boolean</replaceable></returnvalue> |
| 1557 |
</para> |
| 1558 |
<para> |
| 1559 |
Less than |
| 1560 |
</para> |
| 1561 |
<para> |
| 1562 |
<literal>5 < 4</literal> |
| 1563 |
<returnvalue>FALSE</returnvalue> |
| 1564 |
</para></entry> |
| 1565 |
</row> |
| 1566 |
|
| 1567 |
<row> |
| 1568 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1569 |
<replaceable>number</replaceable> <literal><=</literal> <replaceable>number</replaceable> |
| 1570 |
<returnvalue><replaceable>boolean</replaceable></returnvalue> |
| 1571 |
</para> |
| 1572 |
<para> |
| 1573 |
Less than or equal to |
| 1574 |
</para> |
| 1575 |
<para> |
| 1576 |
<literal>5 <= 4</literal> |
| 1577 |
<returnvalue>FALSE</returnvalue> |
| 1578 |
</para></entry> |
| 1579 |
</row> |
| 1580 |
|
| 1581 |
<row> |
| 1582 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1583 |
<replaceable>number</replaceable> <literal>></literal> <replaceable>number</replaceable> |
| 1584 |
<returnvalue><replaceable>boolean</replaceable></returnvalue> |
| 1585 |
</para> |
| 1586 |
<para> |
| 1587 |
Greater than |
| 1588 |
</para> |
| 1589 |
<para> |
| 1590 |
<literal>5 > 4</literal> |
| 1591 |
<returnvalue>TRUE</returnvalue> |
| 1592 |
</para></entry> |
| 1593 |
</row> |
| 1594 |
|
| 1595 |
<row> |
| 1596 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1597 |
<replaceable>number</replaceable> <literal>>=</literal> <replaceable>number</replaceable> |
| 1598 |
<returnvalue><replaceable>boolean</replaceable></returnvalue> |
| 1599 |
</para> |
| 1600 |
<para> |
| 1601 |
Greater than or equal to |
| 1602 |
</para> |
| 1603 |
<para> |
| 1604 |
<literal>5 >= 4</literal> |
| 1605 |
<returnvalue>TRUE</returnvalue> |
| 1606 |
</para></entry> |
| 1607 |
</row> |
| 1608 |
|
| 1609 |
<row> |
| 1610 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1611 |
<replaceable>integer</replaceable> <literal>|</literal> <replaceable>integer</replaceable> |
| 1612 |
<returnvalue><replaceable>integer</replaceable></returnvalue> |
| 1613 |
</para> |
| 1614 |
<para> |
| 1615 |
Bitwise OR |
| 1616 |
</para> |
| 1617 |
<para> |
| 1618 |
<literal>1 | 2</literal> |
| 1619 |
<returnvalue>3</returnvalue> |
| 1620 |
</para></entry> |
| 1621 |
</row> |
| 1622 |
|
| 1623 |
<row> |
| 1624 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1625 |
<replaceable>integer</replaceable> <literal>#</literal> <replaceable>integer</replaceable> |
| 1626 |
<returnvalue><replaceable>integer</replaceable></returnvalue> |
| 1627 |
</para> |
| 1628 |
<para> |
| 1629 |
Bitwise XOR |
| 1630 |
</para> |
| 1631 |
<para> |
| 1632 |
<literal>1 # 3</literal> |
| 1633 |
<returnvalue>2</returnvalue> |
| 1634 |
</para></entry> |
| 1635 |
</row> |
| 1636 |
|
| 1637 |
<row> |
| 1638 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1639 |
<replaceable>integer</replaceable> <literal>&</literal> <replaceable>integer</replaceable> |
| 1640 |
<returnvalue><replaceable>integer</replaceable></returnvalue> |
| 1641 |
</para> |
| 1642 |
<para> |
| 1643 |
Bitwise AND |
| 1644 |
</para> |
| 1645 |
<para> |
| 1646 |
<literal>1 & 3</literal> |
| 1647 |
<returnvalue>1</returnvalue> |
| 1648 |
</para></entry> |
| 1649 |
</row> |
| 1650 |
|
| 1651 |
<row> |
| 1652 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1653 |
<literal>~</literal> <replaceable>integer</replaceable> |
| 1654 |
<returnvalue><replaceable>integer</replaceable></returnvalue> |
| 1655 |
</para> |
| 1656 |
<para> |
| 1657 |
Bitwise NOT |
| 1658 |
</para> |
| 1659 |
<para> |
| 1660 |
<literal>~ 1</literal> |
| 1661 |
<returnvalue>-2</returnvalue> |
| 1662 |
</para></entry> |
| 1663 |
</row> |
| 1664 |
|
| 1665 |
<row> |
| 1666 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1667 |
<replaceable>integer</replaceable> <literal><<</literal> <replaceable>integer</replaceable> |
| 1668 |
<returnvalue><replaceable>integer</replaceable></returnvalue> |
| 1669 |
</para> |
| 1670 |
<para> |
| 1671 |
Bitwise shift left |
| 1672 |
</para> |
| 1673 |
<para> |
| 1674 |
<literal>1 << 2</literal> |
| 1675 |
<returnvalue>4</returnvalue> |
| 1676 |
</para></entry> |
| 1677 |
</row> |
| 1678 |
|
| 1679 |
<row> |
| 1680 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1681 |
<replaceable>integer</replaceable> <literal>>></literal> <replaceable>integer</replaceable> |
| 1682 |
<returnvalue><replaceable>integer</replaceable></returnvalue> |
| 1683 |
</para> |
| 1684 |
<para> |
| 1685 |
Bitwise shift right |
| 1686 |
</para> |
| 1687 |
<para> |
| 1688 |
<literal>8 >> 2</literal> |
| 1689 |
<returnvalue>2</returnvalue> |
| 1690 |
</para></entry> |
| 1691 |
</row> |
| 1692 |
|
| 1693 |
<row> |
| 1694 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1695 |
<replaceable>number</replaceable> <literal>+</literal> <replaceable>number</replaceable> |
| 1696 |
<returnvalue><replaceable>number</replaceable></returnvalue> |
| 1697 |
</para> |
| 1698 |
<para> |
| 1699 |
Addition |
| 1700 |
</para> |
| 1701 |
<para> |
| 1702 |
<literal>5 + 4</literal> |
| 1703 |
<returnvalue>9</returnvalue> |
| 1704 |
</para></entry> |
| 1705 |
</row> |
| 1706 |
|
| 1707 |
<row> |
| 1708 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1709 |
<replaceable>number</replaceable> <literal>-</literal> <replaceable>number</replaceable> |
| 1710 |
<returnvalue><replaceable>number</replaceable></returnvalue> |
| 1711 |
</para> |
| 1712 |
<para> |
| 1713 |
Subtraction |
| 1714 |
</para> |
| 1715 |
<para> |
| 1716 |
<literal>3 - 2.0</literal> |
| 1717 |
<returnvalue>1.0</returnvalue> |
| 1718 |
</para></entry> |
| 1719 |
</row> |
| 1720 |
|
| 1721 |
<row> |
| 1722 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1723 |
<replaceable>number</replaceable> <literal>*</literal> <replaceable>number</replaceable> |
| 1724 |
<returnvalue><replaceable>number</replaceable></returnvalue> |
| 1725 |
</para> |
| 1726 |
<para> |
| 1727 |
Multiplication |
| 1728 |
</para> |
| 1729 |
<para> |
| 1730 |
<literal>5 * 4</literal> |
| 1731 |
<returnvalue>20</returnvalue> |
| 1732 |
</para></entry> |
| 1733 |
</row> |
| 1734 |
|
| 1735 |
<row> |
| 1736 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1737 |
<replaceable>number</replaceable> <literal>/</literal> <replaceable>number</replaceable> |
| 1738 |
<returnvalue><replaceable>number</replaceable></returnvalue> |
| 1739 |
</para> |
| 1740 |
<para> |
| 1741 |
Division (truncates the result towards zero if both inputs are integers) |
| 1742 |
</para> |
| 1743 |
<para> |
| 1744 |
<literal>5 / 3</literal> |
| 1745 |
<returnvalue>1</returnvalue> |
| 1746 |
</para></entry> |
| 1747 |
</row> |
| 1748 |
|
| 1749 |
<row> |
| 1750 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1751 |
<replaceable>integer</replaceable> <literal>%</literal> <replaceable>integer</replaceable> |
| 1752 |
<returnvalue><replaceable>integer</replaceable></returnvalue> |
| 1753 |
</para> |
| 1754 |
<para> |
| 1755 |
Modulo (remainder) |
| 1756 |
</para> |
| 1757 |
<para> |
| 1758 |
<literal>3 % 2</literal> |
| 1759 |
<returnvalue>1</returnvalue> |
| 1760 |
</para></entry> |
| 1761 |
</row> |
| 1762 |
|
| 1763 |
<row> |
| 1764 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1765 |
<literal>-</literal> <replaceable>number</replaceable> |
| 1766 |
<returnvalue><replaceable>number</replaceable></returnvalue> |
| 1767 |
</para> |
| 1768 |
<para> |
| 1769 |
Negation |
| 1770 |
</para> |
| 1771 |
<para> |
| 1772 |
<literal>- 2.0</literal> |
| 1773 |
<returnvalue>-2.0</returnvalue> |
| 1774 |
</para></entry> |
| 1775 |
</row> |
| 1776 |
</tbody> |
| 1777 |
</tgroup> |
| 1778 |
</table> |
| 1779 |
</refsect2> |
| 1780 |
|
| 1781 |
<refsect2 id="pgbench-builtin-functions"> |
| 1782 |
<title>Built-In Functions</title> |
| 1783 |
|
| 1784 |
<para> |
| 1785 |
The functions listed in <xref linkend="pgbench-functions"/> are built |
| 1786 |
into <application>pgbench</application> and may be used in expressions appearing in |
| 1787 |
<link linkend="pgbench-metacommand-set"><literal>\set</literal></link>. |
| 1788 |
</para> |
| 1789 |
|
| 1790 |
<!-- list pgbench functions in alphabetical order --> |
| 1791 |
<table id="pgbench-functions"> |
| 1792 |
<title>pgbench Functions</title> |
| 1793 |
<tgroup cols="1"> |
| 1794 |
<thead> |
| 1795 |
<row> |
| 1796 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1797 |
Function |
| 1798 |
</para> |
| 1799 |
<para> |
| 1800 |
Description |
| 1801 |
</para> |
| 1802 |
<para> |
| 1803 |
Example(s) |
| 1804 |
</para></entry> |
| 1805 |
</row> |
| 1806 |
</thead> |
| 1807 |
|
| 1808 |
<tbody> |
| 1809 |
<row> |
| 1810 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1811 |
<function>abs</function> ( <replaceable>number</replaceable> ) |
| 1812 |
<returnvalue></returnvalue> same type as input |
| 1813 |
</para> |
| 1814 |
<para> |
| 1815 |
Absolute value |
| 1816 |
</para> |
| 1817 |
<para> |
| 1818 |
<literal>abs(-17)</literal> |
| 1819 |
<returnvalue>17</returnvalue> |
| 1820 |
</para></entry> |
| 1821 |
</row> |
| 1822 |
|
| 1823 |
<row> |
| 1824 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1825 |
<function>debug</function> ( <replaceable>number</replaceable> ) |
| 1826 |
<returnvalue></returnvalue> same type as input |
| 1827 |
</para> |
| 1828 |
<para> |
| 1829 |
Prints the argument to <systemitem>stderr</systemitem>, |
| 1830 |
and returns the argument. |
| 1831 |
</para> |
| 1832 |
<para> |
| 1833 |
<literal>debug(5432.1)</literal> |
| 1834 |
<returnvalue>5432.1</returnvalue> |
| 1835 |
</para></entry> |
| 1836 |
</row> |
| 1837 |
|
| 1838 |
<row> |
| 1839 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1840 |
<function>double</function> ( <replaceable>number</replaceable> ) |
| 1841 |
<returnvalue>double</returnvalue> |
| 1842 |
</para> |
| 1843 |
<para> |
| 1844 |
Casts to double. |
| 1845 |
</para> |
| 1846 |
<para> |
| 1847 |
<literal>double(5432)</literal> |
| 1848 |
<returnvalue>5432.0</returnvalue> |
| 1849 |
</para></entry> |
| 1850 |
</row> |
| 1851 |
|
| 1852 |
<row> |
| 1853 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1854 |
<function>exp</function> ( <replaceable>number</replaceable> ) |
| 1855 |
<returnvalue>double</returnvalue> |
| 1856 |
</para> |
| 1857 |
<para> |
| 1858 |
Exponential (<literal>e</literal> raised to the given power) |
| 1859 |
</para> |
| 1860 |
<para> |
| 1861 |
<literal>exp(1.0)</literal> |
| 1862 |
<returnvalue>2.718281828459045</returnvalue> |
| 1863 |
</para></entry> |
| 1864 |
</row> |
| 1865 |
|
| 1866 |
<row> |
| 1867 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1868 |
<function>greatest</function> ( <replaceable>number</replaceable> <optional>, <literal>...</literal> </optional> ) |
| 1869 |
<returnvalue></returnvalue> <type>double</type> if any argument is double, else <type>integer</type> |
| 1870 |
</para> |
| 1871 |
<para> |
| 1872 |
Selects the largest value among the arguments. |
| 1873 |
</para> |
| 1874 |
<para> |
| 1875 |
<literal>greatest(5, 4, 3, 2)</literal> |
| 1876 |
<returnvalue>5</returnvalue> |
| 1877 |
</para></entry> |
| 1878 |
</row> |
| 1879 |
|
| 1880 |
<row> |
| 1881 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1882 |
<function>hash</function> ( <parameter>value</parameter> <optional>, <parameter>seed</parameter> </optional> ) |
| 1883 |
<returnvalue>integer</returnvalue> |
| 1884 |
</para> |
| 1885 |
<para> |
| 1886 |
This is an alias for <function>hash_murmur2</function>. |
| 1887 |
</para> |
| 1888 |
<para> |
| 1889 |
<literal>hash(10, 5432)</literal> |
| 1890 |
<returnvalue>-5817877081768721676</returnvalue> |
| 1891 |
</para></entry> |
| 1892 |
</row> |
| 1893 |
|
| 1894 |
<row> |
| 1895 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1896 |
<function>hash_fnv1a</function> ( <parameter>value</parameter> <optional>, <parameter>seed</parameter> </optional> ) |
| 1897 |
<returnvalue>integer</returnvalue> |
| 1898 |
</para> |
| 1899 |
<para> |
| 1900 |
Computes <ulink url="https://en.wikipedia.org/wiki/Fowler%E2%80%93Noll%E2%80%93Vo_hash_function">FNV-1a hash</ulink>. |
| 1901 |
</para> |
| 1902 |
<para> |
| 1903 |
<literal>hash_fnv1a(10, 5432)</literal> |
| 1904 |
<returnvalue>-7793829335365542153</returnvalue> |
| 1905 |
</para></entry> |
| 1906 |
</row> |
| 1907 |
|
| 1908 |
<row> |
| 1909 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1910 |
<function>hash_murmur2</function> ( <parameter>value</parameter> <optional>, <parameter>seed</parameter> </optional> ) |
| 1911 |
<returnvalue>integer</returnvalue> |
| 1912 |
</para> |
| 1913 |
<para> |
| 1914 |
Computes <ulink url="https://en.wikipedia.org/wiki/MurmurHash">MurmurHash2 hash</ulink>. |
| 1915 |
</para> |
| 1916 |
<para> |
| 1917 |
<literal>hash_murmur2(10, 5432)</literal> |
| 1918 |
<returnvalue>-5817877081768721676</returnvalue> |
| 1919 |
</para></entry> |
| 1920 |
</row> |
| 1921 |
|
| 1922 |
<row> |
| 1923 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1924 |
<function>int</function> ( <replaceable>number</replaceable> ) |
| 1925 |
<returnvalue>integer</returnvalue> |
| 1926 |
</para> |
| 1927 |
<para> |
| 1928 |
Casts to integer. |
| 1929 |
</para> |
| 1930 |
<para> |
| 1931 |
<literal>int(5.4 + 3.8)</literal> |
| 1932 |
<returnvalue>9</returnvalue> |
| 1933 |
</para></entry> |
| 1934 |
</row> |
| 1935 |
|
| 1936 |
<row> |
| 1937 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1938 |
<function>least</function> ( <replaceable>number</replaceable> <optional>, <literal>...</literal> </optional> ) |
| 1939 |
<returnvalue></returnvalue> <type>double</type> if any argument is double, else <type>integer</type> |
| 1940 |
</para> |
| 1941 |
<para> |
| 1942 |
Selects the smallest value among the arguments. |
| 1943 |
</para> |
| 1944 |
<para> |
| 1945 |
<literal>least(5, 4, 3, 2.1)</literal> |
| 1946 |
<returnvalue>2.1</returnvalue> |
| 1947 |
</para></entry> |
| 1948 |
</row> |
| 1949 |
|
| 1950 |
<row> |
| 1951 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1952 |
<function>ln</function> ( <replaceable>number</replaceable> ) |
| 1953 |
<returnvalue>double</returnvalue> |
| 1954 |
</para> |
| 1955 |
<para> |
| 1956 |
Natural logarithm |
| 1957 |
</para> |
| 1958 |
<para> |
| 1959 |
<literal>ln(2.718281828459045)</literal> |
| 1960 |
<returnvalue>1.0</returnvalue> |
| 1961 |
</para></entry> |
| 1962 |
</row> |
| 1963 |
|
| 1964 |
<row> |
| 1965 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1966 |
<function>mod</function> ( <replaceable>integer</replaceable>, <replaceable>integer</replaceable> ) |
| 1967 |
<returnvalue>integer</returnvalue> |
| 1968 |
</para> |
| 1969 |
<para> |
| 1970 |
Modulo (remainder) |
| 1971 |
</para> |
| 1972 |
<para> |
| 1973 |
<literal>mod(54, 32)</literal> |
| 1974 |
<returnvalue>22</returnvalue> |
| 1975 |
</para></entry> |
| 1976 |
</row> |
| 1977 |
|
| 1978 |
<row> |
| 1979 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1980 |
<function>permute</function> ( <parameter>i</parameter>, <parameter>size</parameter> [, <parameter>seed</parameter> ] ) |
| 1981 |
<returnvalue>integer</returnvalue> |
| 1982 |
</para> |
| 1983 |
<para> |
| 1984 |
Permuted value of <parameter>i</parameter>, in the range |
| 1985 |
<literal>[0, size)</literal>. This is the new position of |
| 1986 |
<parameter>i</parameter> (modulo <parameter>size</parameter>) in a |
| 1987 |
pseudorandom permutation of the integers <literal>0...size-1</literal>, |
| 1988 |
parameterized by <parameter>seed</parameter>, see below. |
| 1989 |
</para> |
| 1990 |
<para> |
| 1991 |
<literal>permute(0, 4)</literal> |
| 1992 |
<returnvalue>an integer between 0 and 3</returnvalue> |
| 1993 |
</para></entry> |
| 1994 |
</row> |
| 1995 |
|
| 1996 |
<row> |
| 1997 |
<entry role="func_table_entry"><para role="func_signature"> |
| 1998 |
<function>pi</function> () |
| 1999 |
<returnvalue>double</returnvalue> |
| 2000 |
</para> |
| 2001 |
<para> |
| 2002 |
Approximate value of <phrase role="symbol_font">π</phrase> |
| 2003 |
</para> |
| 2004 |
<para> |
| 2005 |
<literal>pi()</literal> |
| 2006 |
<returnvalue>3.14159265358979323846</returnvalue> |
| 2007 |
</para></entry> |
| 2008 |
</row> |
| 2009 |
|
| 2010 |
<row> |
| 2011 |
<entry role="func_table_entry"><para role="func_signature"> |
| 2012 |
<function>pow</function> ( <parameter>x</parameter>, <parameter>y</parameter> ) |
| 2013 |
<returnvalue>double</returnvalue> |
| 2014 |
</para> |
| 2015 |
<para role="func_signature"> |
| 2016 |
<function>power</function> ( <parameter>x</parameter>, <parameter>y</parameter> ) |
| 2017 |
<returnvalue>double</returnvalue> |
| 2018 |
</para> |
| 2019 |
<para> |
| 2020 |
<parameter>x</parameter> raised to the power of <parameter>y</parameter> |
| 2021 |
</para> |
| 2022 |
<para> |
| 2023 |
<literal>pow(2.0, 10)</literal> |
| 2024 |
<returnvalue>1024.0</returnvalue> |
| 2025 |
</para></entry> |
| 2026 |
</row> |
| 2027 |
|
| 2028 |
<row> |
| 2029 |
<entry role="func_table_entry"><para role="func_signature"> |
| 2030 |
<function>random</function> ( <parameter>lb</parameter>, <parameter>ub</parameter> ) |
| 2031 |
<returnvalue>integer</returnvalue> |
| 2032 |
</para> |
| 2033 |
<para> |
| 2034 |
Computes a uniformly-distributed random integer in <literal>[lb, |
| 2035 |
ub]</literal>. |
| 2036 |
</para> |
| 2037 |
<para> |
| 2038 |
<literal>random(1, 10)</literal> |
| 2039 |
<returnvalue>an integer between 1 and 10</returnvalue> |
| 2040 |
</para></entry> |
| 2041 |
</row> |
| 2042 |
|
| 2043 |
<row> |
| 2044 |
<entry role="func_table_entry"><para role="func_signature"> |
| 2045 |
<function>random_exponential</function> ( <parameter>lb</parameter>, <parameter>ub</parameter>, <parameter>parameter</parameter> ) |
| 2046 |
<returnvalue>integer</returnvalue> |
| 2047 |
</para> |
| 2048 |
<para> |
| 2049 |
Computes an exponentially-distributed random integer in <literal>[lb, |
| 2050 |
ub]</literal>, see below. |
| 2051 |
</para> |
| 2052 |
<para> |
| 2053 |
<literal>random_exponential(1, 10, 3.0)</literal> |
| 2054 |
<returnvalue>an integer between 1 and 10</returnvalue> |
| 2055 |
</para></entry> |
| 2056 |
</row> |
| 2057 |
|
| 2058 |
<row> |
| 2059 |
<entry role="func_table_entry"><para role="func_signature"> |
| 2060 |
<function>random_gaussian</function> ( <parameter>lb</parameter>, <parameter>ub</parameter>, <parameter>parameter</parameter> ) |
| 2061 |
<returnvalue>integer</returnvalue> |
| 2062 |
</para> |
| 2063 |
<para> |
| 2064 |
Computes a Gaussian-distributed random integer in <literal>[lb, |
| 2065 |
ub]</literal>, see below. |
| 2066 |
</para> |
| 2067 |
<para> |
| 2068 |
<literal>random_gaussian(1, 10, 2.5)</literal> |
| 2069 |
<returnvalue>an integer between 1 and 10</returnvalue> |
| 2070 |
</para></entry> |
| 2071 |
</row> |
| 2072 |
|
| 2073 |
<row> |
| 2074 |
<entry role="func_table_entry"><para role="func_signature"> |
| 2075 |
<function>random_zipfian</function> ( <parameter>lb</parameter>, <parameter>ub</parameter>, <parameter>parameter</parameter> ) |
| 2076 |
<returnvalue>integer</returnvalue> |
| 2077 |
</para> |
| 2078 |
<para> |
| 2079 |
Computes a Zipfian-distributed random integer in <literal>[lb, |
| 2080 |
ub]</literal>, see below. |
| 2081 |
</para> |
| 2082 |
<para> |
| 2083 |
<literal>random_zipfian(1, 10, 1.5)</literal> |
| 2084 |
<returnvalue>an integer between 1 and 10</returnvalue> |
| 2085 |
</para></entry> |
| 2086 |
</row> |
| 2087 |
|
| 2088 |
<row> |
| 2089 |
<entry role="func_table_entry"><para role="func_signature"> |
| 2090 |
<function>sqrt</function> ( <replaceable>number</replaceable> ) |
| 2091 |
<returnvalue>double</returnvalue> |
| 2092 |
</para> |
| 2093 |
<para> |
| 2094 |
Square root |
| 2095 |
</para> |
| 2096 |
<para> |
| 2097 |
<literal>sqrt(2.0)</literal> |
| 2098 |
<returnvalue>1.414213562</returnvalue> |
| 2099 |
</para></entry> |
| 2100 |
</row> |
| 2101 |
</tbody> |
| 2102 |
</tgroup> |
| 2103 |
</table> |
| 2104 |
|
| 2105 |
<para> |
| 2106 |
The <literal>random</literal> function generates values using a uniform |
| 2107 |
distribution, that is all the values are drawn within the specified |
| 2108 |
range with equal probability. The <literal>random_exponential</literal>, |
| 2109 |
<literal>random_gaussian</literal> and <literal>random_zipfian</literal> |
| 2110 |
functions require an additional double parameter which determines the precise |
| 2111 |
shape of the distribution. |
| 2112 |
</para> |
| 2113 |
|
| 2114 |
<itemizedlist> |
| 2115 |
<listitem> |
| 2116 |
<para> |
| 2117 |
For an exponential distribution, <replaceable>parameter</replaceable> |
| 2118 |
controls the distribution by truncating a quickly-decreasing |
| 2119 |
exponential distribution at <replaceable>parameter</replaceable>, and then |
| 2120 |
projecting onto integers between the bounds. |
| 2121 |
To be precise, with |
| 2122 |
<literallayout> |
| 2123 |
f(x) = exp(-parameter * (x - min) / (max - min + 1)) / (1 - exp(-parameter)) |
| 2124 |
</literallayout> |
| 2125 |
Then value <replaceable>i</replaceable> between <replaceable>min</replaceable> and |
| 2126 |
<replaceable>max</replaceable> inclusive is drawn with probability: |
| 2127 |
<literal>f(i) - f(i + 1)</literal>. |
| 2128 |
</para> |
| 2129 |
|
| 2130 |
<para> |
| 2131 |
Intuitively, the larger the <replaceable>parameter</replaceable>, the more |
| 2132 |
frequently values close to <replaceable>min</replaceable> are accessed, and the |
| 2133 |
less frequently values close to <replaceable>max</replaceable> are accessed. |
| 2134 |
The closer to 0 <replaceable>parameter</replaceable> is, the flatter (more |
| 2135 |
uniform) the access distribution. |
| 2136 |
A crude approximation of the distribution is that the most frequent 1% |
| 2137 |
values in the range, close to <replaceable>min</replaceable>, are drawn |
| 2138 |
<replaceable>parameter</replaceable>% of the time. |
| 2139 |
The <replaceable>parameter</replaceable> value must be strictly positive. |
| 2140 |
</para> |
| 2141 |
</listitem> |
| 2142 |
|
| 2143 |
<listitem> |
| 2144 |
<para> |
| 2145 |
For a Gaussian distribution, the interval is mapped onto a standard |
| 2146 |
normal distribution (the classical bell-shaped Gaussian curve) truncated |
| 2147 |
at <literal>-parameter</literal> on the left and <literal>+parameter</literal> |
| 2148 |
on the right. |
| 2149 |
Values in the middle of the interval are more likely to be drawn. |
| 2150 |
To be precise, if <literal>PHI(x)</literal> is the cumulative distribution |
| 2151 |
function of the standard normal distribution, with mean <literal>mu</literal> |
| 2152 |
defined as <literal>(max + min) / 2.0</literal>, with |
| 2153 |
<literallayout> |
| 2154 |
f(x) = PHI(2.0 * parameter * (x - mu) / (max - min + 1)) / |
| 2155 |
(2.0 * PHI(parameter) - 1) |
| 2156 |
</literallayout> |
| 2157 |
then value <replaceable>i</replaceable> between <replaceable>min</replaceable> and |
| 2158 |
<replaceable>max</replaceable> inclusive is drawn with probability: |
| 2159 |
<literal>f(i + 0.5) - f(i - 0.5)</literal>. |
| 2160 |
Intuitively, the larger the <replaceable>parameter</replaceable>, the more |
| 2161 |
frequently values close to the middle of the interval are drawn, and the |
| 2162 |
less frequently values close to the <replaceable>min</replaceable> and |
| 2163 |
<replaceable>max</replaceable> bounds. About 67% of values are drawn from the |
| 2164 |
middle <literal>1.0 / parameter</literal>, that is a relative |
| 2165 |
<literal>0.5 / parameter</literal> around the mean, and 95% in the middle |
| 2166 |
<literal>2.0 / parameter</literal>, that is a relative |
| 2167 |
<literal>1.0 / parameter</literal> around the mean; for instance, if |
| 2168 |
<replaceable>parameter</replaceable> is 4.0, 67% of values are drawn from the |
| 2169 |
middle quarter (1.0 / 4.0) of the interval (i.e., from |
| 2170 |
<literal>3.0 / 8.0</literal> to <literal>5.0 / 8.0</literal>) and 95% from |
| 2171 |
the middle half (<literal>2.0 / 4.0</literal>) of the interval (second and third |
| 2172 |
quartiles). The minimum allowed <replaceable>parameter</replaceable> |
| 2173 |
value is 2.0. |
| 2174 |
</para> |
| 2175 |
</listitem> |
| 2176 |
<listitem> |
| 2177 |
<para> |
| 2178 |
<literal>random_zipfian</literal> generates a bounded Zipfian |
| 2179 |
distribution. |
| 2180 |
<replaceable>parameter</replaceable> defines how skewed the distribution |
| 2181 |
is. The larger the <replaceable>parameter</replaceable>, the more |
| 2182 |
frequently values closer to the beginning of the interval are drawn. |
| 2183 |
The distribution is such that, assuming the range starts from 1, |
| 2184 |
the ratio of the probability of drawing <replaceable>k</replaceable> |
| 2185 |
versus drawing <replaceable>k+1</replaceable> is |
| 2186 |
<literal>((<replaceable>k</replaceable>+1)/<replaceable>k</replaceable>)**<replaceable>parameter</replaceable></literal>. |
| 2187 |
For example, <literal>random_zipfian(1, ..., 2.5)</literal> produces |
| 2188 |
the value <literal>1</literal> about <literal>(2/1)**2.5 = |
| 2189 |
5.66</literal> times more frequently than <literal>2</literal>, which |
| 2190 |
itself is produced <literal>(3/2)**2.5 = 2.76</literal> times more |
| 2191 |
frequently than <literal>3</literal>, and so on. |
| 2192 |
</para> |
| 2193 |
<para> |
| 2194 |
<application>pgbench</application>'s implementation is based on |
| 2195 |
"Non-Uniform Random Variate Generation", Luc Devroye, p. 550-551, |
| 2196 |
Springer 1986. Due to limitations of that algorithm, |
| 2197 |
the <replaceable>parameter</replaceable> value is restricted to |
| 2198 |
the range [1.001, 1000]. |
| 2199 |
</para> |
| 2200 |
</listitem> |
| 2201 |
</itemizedlist> |
| 2202 |
|
| 2203 |
<note> |
| 2204 |
<para> |
| 2205 |
When designing a benchmark which selects rows non-uniformly, be aware |
| 2206 |
that the rows chosen may be correlated with other data such as IDs from |
| 2207 |
a sequence or the physical row ordering, which may skew performance |
| 2208 |
measurements. |
| 2209 |
</para> |
| 2210 |
<para> |
| 2211 |
To avoid this, you may wish to use the <function>permute</function> |
| 2212 |
function, or some other additional step with similar effect, to shuffle |
| 2213 |
the selected rows and remove such correlations. |
| 2214 |
</para> |
| 2215 |
</note> |
| 2216 |
|
| 2217 |
<para> |
| 2218 |
Hash functions <literal>hash</literal>, <literal>hash_murmur2</literal> and |
| 2219 |
<literal>hash_fnv1a</literal> accept an input value and an optional seed parameter. |
| 2220 |
In case the seed isn't provided the value of <literal>:default_seed</literal> |
| 2221 |
is used, which is initialized randomly unless set by the command-line |
| 2222 |
<literal>-D</literal> option. |
| 2223 |
</para> |
| 2224 |
|
| 2225 |
<para> |
| 2226 |
<literal>permute</literal> accepts an input value, a size, and an optional |
| 2227 |
seed parameter. It generates a pseudorandom permutation of integers in |
| 2228 |
the range <literal>[0, size)</literal>, and returns the index of the input |
| 2229 |
value in the permuted values. The permutation chosen is parameterized by |
| 2230 |
the seed, which defaults to <literal>:default_seed</literal>, if not |
| 2231 |
specified. Unlike the hash functions, <literal>permute</literal> ensures |
| 2232 |
that there are no collisions or holes in the output values. Input values |
| 2233 |
outside the interval are interpreted modulo the size. The function raises |
| 2234 |
an error if the size is not positive. <function>permute</function> can be |
| 2235 |
used to scatter the distribution of non-uniform random functions such as |
| 2236 |
<literal>random_zipfian</literal> or <literal>random_exponential</literal> |
| 2237 |
so that values drawn more often are not trivially correlated. For |
| 2238 |
instance, the following <application>pgbench</application> script |
| 2239 |
simulates a possible real world workload typical for social media and |
| 2240 |
blogging platforms where a few accounts generate excessive load: |
| 2241 |
|
| 2242 |
<programlisting> |
| 2243 |
\set size 1000000 |
| 2244 |
\set r random_zipfian(1, :size, 1.07) |
| 2245 |
\set k 1 + permute(:r, :size) |
| 2246 |
</programlisting> |
| 2247 |
|
| 2248 |
In some cases several distinct distributions are needed which don't correlate |
| 2249 |
with each other and this is when the optional seed parameter comes in handy: |
| 2250 |
|
| 2251 |
<programlisting> |
| 2252 |
\set k1 1 + permute(:r, :size, :default_seed + 123) |
| 2253 |
\set k2 1 + permute(:r, :size, :default_seed + 321) |
| 2254 |
</programlisting> |
| 2255 |
|
| 2256 |
A similar behavior can also be approximated with <function>hash</function>: |
| 2257 |
|
| 2258 |
<programlisting> |
| 2259 |
\set size 1000000 |
| 2260 |
\set r random_zipfian(1, 100 * :size, 1.07) |
| 2261 |
\set k 1 + abs(hash(:r)) % :size |
| 2262 |
</programlisting> |
| 2263 |
|
| 2264 |
However, since <function>hash</function> generates collisions, some values |
| 2265 |
will not be reachable and others will be more frequent than expected from |
| 2266 |
the original distribution. |
| 2267 |
</para> |
| 2268 |
|
| 2269 |
<para> |
| 2270 |
As an example, the full definition of the built-in TPC-B-like |
| 2271 |
transaction is: |
| 2272 |
|
| 2273 |
<programlisting> |
| 2274 |
\set aid random(1, 100000 * :scale) |
| 2275 |
\set bid random(1, 1 * :scale) |
| 2276 |
\set tid random(1, 10 * :scale) |
| 2277 |
\set delta random(-5000, 5000) |
| 2278 |
BEGIN; |
| 2279 |
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; |
| 2280 |
SELECT abalance FROM pgbench_accounts WHERE aid = :aid; |
| 2281 |
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; |
| 2282 |
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; |
| 2283 |
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); |
| 2284 |
END; |
| 2285 |
</programlisting> |
| 2286 |
|
| 2287 |
This script allows each iteration of the transaction to reference |
| 2288 |
different, randomly-chosen rows. (This example also shows why it's |
| 2289 |
important for each client session to have its own variables — |
| 2290 |
otherwise they'd not be independently touching different rows.) |
| 2291 |
</para> |
| 2292 |
|
| 2293 |
</refsect2> |
| 2294 |
|
| 2295 |
<refsect2> |
| 2296 |
<title>Per-Transaction Logging</title> |
| 2297 |
|
| 2298 |
<para> |
| 2299 |
With the <option>-l</option> option (but without |
| 2300 |
the <option>--aggregate-interval</option> option), |
| 2301 |
<application>pgbench</application> writes information about each transaction |
| 2302 |
to a log file. The log file will be named |
| 2303 |
<filename><replaceable>prefix</replaceable>.<replaceable>nnn</replaceable></filename>, |
| 2304 |
where <replaceable>prefix</replaceable> defaults to <literal>pgbench_log</literal>, and |
| 2305 |
<replaceable>nnn</replaceable> is the PID of the |
| 2306 |
<application>pgbench</application> process. |
| 2307 |
The prefix can be changed by using the <option>--log-prefix</option> option. |
| 2308 |
If the <option>-j</option> option is 2 or higher, so that there are multiple |
| 2309 |
worker threads, each will have its own log file. The first worker will |
| 2310 |
use the same name for its log file as in the standard single worker case. |
| 2311 |
The additional log files for the other workers will be named |
| 2312 |
<filename><replaceable>prefix</replaceable>.<replaceable>nnn</replaceable>.<replaceable>mmm</replaceable></filename>, |
| 2313 |
where <replaceable>mmm</replaceable> is a sequential number for each worker starting |
| 2314 |
with 1. |
| 2315 |
</para> |
| 2316 |
|
| 2317 |
<para> |
| 2318 |
Each line in a log file describes one transaction. |
| 2319 |
It contains the following space-separated fields: |
| 2320 |
|
| 2321 |
<variablelist> |
| 2322 |
<varlistentry> |
| 2323 |
<term><replaceable>client_id</replaceable></term> |
| 2324 |
<listitem> |
| 2325 |
<para> |
| 2326 |
identifies the client session that ran the transaction |
| 2327 |
</para> |
| 2328 |
</listitem> |
| 2329 |
</varlistentry> |
| 2330 |
|
| 2331 |
<varlistentry> |
| 2332 |
<term><replaceable>transaction_no</replaceable></term> |
| 2333 |
<listitem> |
| 2334 |
<para> |
| 2335 |
counts how many transactions have been run by that session |
| 2336 |
</para> |
| 2337 |
</listitem> |
| 2338 |
</varlistentry> |
| 2339 |
|
| 2340 |
<varlistentry> |
| 2341 |
<term><replaceable>time</replaceable></term> |
| 2342 |
<listitem> |
| 2343 |
<para> |
| 2344 |
transaction's elapsed time, in microseconds |
| 2345 |
</para> |
| 2346 |
</listitem> |
| 2347 |
</varlistentry> |
| 2348 |
|
| 2349 |
<varlistentry> |
| 2350 |
<term><replaceable>script_no</replaceable></term> |
| 2351 |
<listitem> |
| 2352 |
<para> |
| 2353 |
identifies the script file that was used for the transaction |
| 2354 |
(useful when multiple scripts are specified |
| 2355 |
with <option>-f</option> or <option>-b</option>) |
| 2356 |
</para> |
| 2357 |
</listitem> |
| 2358 |
</varlistentry> |
| 2359 |
|
| 2360 |
<varlistentry> |
| 2361 |
<term><replaceable>time_epoch</replaceable></term> |
| 2362 |
<listitem> |
| 2363 |
<para> |
| 2364 |
transaction's completion time, as a Unix-epoch time stamp |
| 2365 |
</para> |
| 2366 |
</listitem> |
| 2367 |
</varlistentry> |
| 2368 |
|
| 2369 |
<varlistentry> |
| 2370 |
<term><replaceable>time_us</replaceable></term> |
| 2371 |
<listitem> |
| 2372 |
<para> |
| 2373 |
fractional-second part of transaction's completion time, in |
| 2374 |
microseconds |
| 2375 |
</para> |
| 2376 |
</listitem> |
| 2377 |
</varlistentry> |
| 2378 |
|
| 2379 |
<varlistentry> |
| 2380 |
<term><replaceable>schedule_lag</replaceable></term> |
| 2381 |
<listitem> |
| 2382 |
<para> |
| 2383 |
transaction start delay, that is the difference between the |
| 2384 |
transaction's scheduled start time and the time it actually |
| 2385 |
started, in microseconds |
| 2386 |
(present only if <option>--rate</option> is specified) |
| 2387 |
</para> |
| 2388 |
</listitem> |
| 2389 |
</varlistentry> |
| 2390 |
|
| 2391 |
<varlistentry> |
| 2392 |
<term><replaceable>retries</replaceable></term> |
| 2393 |
<listitem> |
| 2394 |
<para> |
| 2395 |
count of retries after serialization or deadlock errors during the |
| 2396 |
transaction |
| 2397 |
(present only if <option>--max-tries</option> is not equal to one) |
| 2398 |
</para> |
| 2399 |
</listitem> |
| 2400 |
</varlistentry> |
| 2401 |
</variablelist> |
| 2402 |
</para> |
| 2403 |
|
| 2404 |
<para> |
| 2405 |
When both <option>--rate</option> and <option>--latency-limit</option> are used, |
| 2406 |
the <replaceable>time</replaceable> for a skipped transaction will be reported as |
| 2407 |
<literal>skipped</literal>. |
| 2408 |
If the transaction ends with a failure, its <replaceable>time</replaceable> |
| 2409 |
will be reported as <literal>failed</literal>. If you use the |
| 2410 |
<option>--failures-detailed</option> option, the |
| 2411 |
<replaceable>time</replaceable> of the failed transaction will be reported as |
| 2412 |
<literal>serialization</literal> or |
| 2413 |
<literal>deadlock</literal> depending on the type of failure (see |
| 2414 |
<xref linkend="failures-and-retries"/> for more information). |
| 2415 |
</para> |
| 2416 |
|
| 2417 |
<para> |
| 2418 |
Here is a snippet of a log file generated in a single-client run: |
| 2419 |
<screen> |
| 2420 |
0 199 2241 0 1175850568 995598 |
| 2421 |
0 200 2465 0 1175850568 998079 |
| 2422 |
0 201 2513 0 1175850569 608 |
| 2423 |
0 202 2038 0 1175850569 2663 |
| 2424 |
</screen> |
| 2425 |
|
| 2426 |
Another example with <literal>--rate=100</literal> |
| 2427 |
and <literal>--latency-limit=5</literal> (note the additional |
| 2428 |
<replaceable>schedule_lag</replaceable> column): |
| 2429 |
<screen> |
| 2430 |
0 81 4621 0 1412881037 912698 3005 |
| 2431 |
0 82 6173 0 1412881037 914578 4304 |
| 2432 |
0 83 skipped 0 1412881037 914578 5217 |
| 2433 |
0 83 skipped 0 1412881037 914578 5099 |
| 2434 |
0 83 4722 0 1412881037 916203 3108 |
| 2435 |
0 84 4142 0 1412881037 918023 2333 |
| 2436 |
0 85 2465 0 1412881037 919759 740 |
| 2437 |
</screen> |
| 2438 |
In this example, transaction 82 was late, because its latency (6.173 ms) was |
| 2439 |
over the 5 ms limit. The next two transactions were skipped, because they |
| 2440 |
were already late before they were even started. |
| 2441 |
</para> |
| 2442 |
|
| 2443 |
<para> |
| 2444 |
The following example shows a snippet of a log file with failures and |
| 2445 |
retries, with the maximum number of tries set to 10 (note the additional |
| 2446 |
<replaceable>retries</replaceable> column): |
| 2447 |
<screen> |
| 2448 |
3 0 47423 0 1499414498 34501 3 |
| 2449 |
3 1 8333 0 1499414498 42848 0 |
| 2450 |
3 2 8358 0 1499414498 51219 0 |
| 2451 |
4 0 72345 0 1499414498 59433 6 |
| 2452 |
1 3 41718 0 1499414498 67879 4 |
| 2453 |
1 4 8416 0 1499414498 76311 0 |
| 2454 |
3 3 33235 0 1499414498 84469 3 |
| 2455 |
0 0 failed 0 1499414498 84905 9 |
| 2456 |
2 0 failed 0 1499414498 86248 9 |
| 2457 |
3 4 8307 0 1499414498 92788 0 |
| 2458 |
</screen> |
| 2459 |
</para> |
| 2460 |
|
| 2461 |
<para> |
| 2462 |
If the <option>--failures-detailed</option> option is used, the type of |
| 2463 |
failure is reported in the <replaceable>time</replaceable> like this: |
| 2464 |
<screen> |
| 2465 |
3 0 47423 0 1499414498 34501 3 |
| 2466 |
3 1 8333 0 1499414498 42848 0 |
| 2467 |
3 2 8358 0 1499414498 51219 0 |
| 2468 |
4 0 72345 0 1499414498 59433 6 |
| 2469 |
1 3 41718 0 1499414498 67879 4 |
| 2470 |
1 4 8416 0 1499414498 76311 0 |
| 2471 |
3 3 33235 0 1499414498 84469 3 |
| 2472 |
0 0 serialization 0 1499414498 84905 9 |
| 2473 |
2 0 serialization 0 1499414498 86248 9 |
| 2474 |
3 4 8307 0 1499414498 92788 0 |
| 2475 |
</screen> |
| 2476 |
</para> |
| 2477 |
|
| 2478 |
<para> |
| 2479 |
When running a long test on hardware that can handle a lot of transactions, |
| 2480 |
the log files can become very large. The <option>--sampling-rate</option> option |
| 2481 |
can be used to log only a random sample of transactions. |
| 2482 |
</para> |
| 2483 |
</refsect2> |
| 2484 |
|
| 2485 |
<refsect2> |
| 2486 |
<title>Aggregated Logging</title> |
| 2487 |
|
| 2488 |
<para> |
| 2489 |
With the <option>--aggregate-interval</option> option, a different |
| 2490 |
format is used for the log files. Each log line describes one |
| 2491 |
aggregation interval. It contains the following space-separated |
| 2492 |
fields: |
| 2493 |
|
| 2494 |
<variablelist> |
| 2495 |
<varlistentry> |
| 2496 |
<term><replaceable>interval_start</replaceable></term> |
| 2497 |
<listitem> |
| 2498 |
<para> |
| 2499 |
start time of the interval, as a Unix-epoch time stamp |
| 2500 |
</para> |
| 2501 |
</listitem> |
| 2502 |
</varlistentry> |
| 2503 |
|
| 2504 |
<varlistentry> |
| 2505 |
<term><replaceable>num_transactions</replaceable></term> |
| 2506 |
<listitem> |
| 2507 |
<para> |
| 2508 |
number of transactions within the interval |
| 2509 |
</para> |
| 2510 |
</listitem> |
| 2511 |
</varlistentry> |
| 2512 |
|
| 2513 |
<varlistentry> |
| 2514 |
<term><replaceable>sum_latency</replaceable></term> |
| 2515 |
<listitem> |
| 2516 |
<para> |
| 2517 |
sum of transaction latencies |
| 2518 |
</para> |
| 2519 |
</listitem> |
| 2520 |
</varlistentry> |
| 2521 |
|
| 2522 |
<varlistentry> |
| 2523 |
<term><replaceable>sum_latency_2</replaceable></term> |
| 2524 |
<listitem> |
| 2525 |
<para> |
| 2526 |
sum of squares of transaction latencies |
| 2527 |
</para> |
| 2528 |
</listitem> |
| 2529 |
</varlistentry> |
| 2530 |
|
| 2531 |
<varlistentry> |
| 2532 |
<term><replaceable>min_latency</replaceable></term> |
| 2533 |
<listitem> |
| 2534 |
<para> |
| 2535 |
minimum transaction latency |
| 2536 |
</para> |
| 2537 |
</listitem> |
| 2538 |
</varlistentry> |
| 2539 |
|
| 2540 |
<varlistentry> |
| 2541 |
<term><replaceable>max_latency</replaceable></term> |
| 2542 |
<listitem> |
| 2543 |
<para> |
| 2544 |
maximum transaction latency |
| 2545 |
</para> |
| 2546 |
</listitem> |
| 2547 |
</varlistentry> |
| 2548 |
|
| 2549 |
<varlistentry> |
| 2550 |
<term><replaceable>sum_lag</replaceable></term> |
| 2551 |
<listitem> |
| 2552 |
<para> |
| 2553 |
sum of transaction start delays |
| 2554 |
(zero unless <option>--rate</option> is specified) |
| 2555 |
</para> |
| 2556 |
</listitem> |
| 2557 |
</varlistentry> |
| 2558 |
|
| 2559 |
<varlistentry> |
| 2560 |
<term><replaceable>sum_lag_2</replaceable></term> |
| 2561 |
<listitem> |
| 2562 |
<para> |
| 2563 |
sum of squares of transaction start delays |
| 2564 |
(zero unless <option>--rate</option> is specified) |
| 2565 |
</para> |
| 2566 |
</listitem> |
| 2567 |
</varlistentry> |
| 2568 |
|
| 2569 |
<varlistentry> |
| 2570 |
<term><replaceable>min_lag</replaceable></term> |
| 2571 |
<listitem> |
| 2572 |
<para> |
| 2573 |
minimum transaction start delay |
| 2574 |
(zero unless <option>--rate</option> is specified) |
| 2575 |
</para> |
| 2576 |
</listitem> |
| 2577 |
</varlistentry> |
| 2578 |
|
| 2579 |
<varlistentry> |
| 2580 |
<term><replaceable>max_lag</replaceable></term> |
| 2581 |
<listitem> |
| 2582 |
<para> |
| 2583 |
maximum transaction start delay |
| 2584 |
(zero unless <option>--rate</option> is specified) |
| 2585 |
</para> |
| 2586 |
</listitem> |
| 2587 |
</varlistentry> |
| 2588 |
|
| 2589 |
<varlistentry> |
| 2590 |
<term><replaceable>skipped</replaceable></term> |
| 2591 |
<listitem> |
| 2592 |
<para> |
| 2593 |
number of transactions skipped because they would have started too late |
| 2594 |
(zero unless <option>--rate</option> |
| 2595 |
and <option>--latency-limit</option> are specified) |
| 2596 |
</para> |
| 2597 |
</listitem> |
| 2598 |
</varlistentry> |
| 2599 |
|
| 2600 |
<varlistentry> |
| 2601 |
<term><replaceable>retried</replaceable></term> |
| 2602 |
<listitem> |
| 2603 |
<para> |
| 2604 |
number of retried transactions |
| 2605 |
(zero unless <option>--max-tries</option> is not equal to one) |
| 2606 |
</para> |
| 2607 |
</listitem> |
| 2608 |
</varlistentry> |
| 2609 |
|
| 2610 |
<varlistentry> |
| 2611 |
<term><replaceable>retries</replaceable></term> |
| 2612 |
<listitem> |
| 2613 |
<para> |
| 2614 |
number of retries after serialization or deadlock errors |
| 2615 |
(zero unless <option>--max-tries</option> is not equal to one) |
| 2616 |
</para> |
| 2617 |
</listitem> |
| 2618 |
</varlistentry> |
| 2619 |
|
| 2620 |
<varlistentry> |
| 2621 |
<term><replaceable>serialization_failures</replaceable></term> |
| 2622 |
<listitem> |
| 2623 |
<para> |
| 2624 |
number of transactions that got a serialization error and were not |
| 2625 |
retried afterwards |
| 2626 |
(zero unless <option>--failures-detailed</option> is specified) |
| 2627 |
</para> |
| 2628 |
</listitem> |
| 2629 |
</varlistentry> |
| 2630 |
|
| 2631 |
<varlistentry> |
| 2632 |
<term><replaceable>deadlock_failures</replaceable></term> |
| 2633 |
<listitem> |
| 2634 |
<para> |
| 2635 |
number of transactions that got a deadlock error and were not |
| 2636 |
retried afterwards |
| 2637 |
(zero unless <option>--failures-detailed</option> is specified) |
| 2638 |
</para> |
| 2639 |
</listitem> |
| 2640 |
</varlistentry> |
| 2641 |
</variablelist> |
| 2642 |
</para> |
| 2643 |
|
| 2644 |
<para> |
| 2645 |
Here is some example output generated with this option: |
| 2646 |
<screen> |
| 2647 |
<userinput>pgbench --aggregate-interval=10 --time=20 --client=10 --log --rate=1000 --latency-limit=10 --failures-detailed --max-tries=10 test</userinput> |
| 2648 |
|
| 2649 |
1650260552 5178 26171317 177284491527 1136 44462 2647617 7321113867 0 9866 64 7564 28340 4148 0 |
| 2650 |
1650260562 4808 25573984 220121792172 1171 62083 3037380 9666800914 0 9998 598 7392 26621 4527 0 |
| 2651 |
</screen> |
| 2652 |
</para> |
| 2653 |
|
| 2654 |
<para> |
| 2655 |
Notice that while the plain (unaggregated) log format shows which script |
| 2656 |
was used for each transaction, the aggregated format does not. Therefore if |
| 2657 |
you need per-script data, you need to aggregate the data on your own. |
| 2658 |
</para> |
| 2659 |
|
| 2660 |
</refsect2> |
| 2661 |
|
| 2662 |
<refsect2> |
| 2663 |
<title>Per-Statement Report</title> |
| 2664 |
|
| 2665 |
<para> |
| 2666 |
With the <option>-r</option> option, <application>pgbench</application> |
| 2667 |
collects the following statistics for each statement: |
| 2668 |
<itemizedlist> |
| 2669 |
<listitem> |
| 2670 |
<para> |
| 2671 |
<literal>latency</literal> — elapsed transaction time for each |
| 2672 |
statement. <application>pgbench</application> reports an average value |
| 2673 |
of all successful runs of the statement. |
| 2674 |
</para> |
| 2675 |
</listitem> |
| 2676 |
<listitem> |
| 2677 |
<para> |
| 2678 |
The number of failures in this statement. See |
| 2679 |
<xref linkend="failures-and-retries"/> for more information. |
| 2680 |
</para> |
| 2681 |
</listitem> |
| 2682 |
<listitem> |
| 2683 |
<para> |
| 2684 |
The number of retries after a serialization or a deadlock error in this |
| 2685 |
statement. See <xref linkend="failures-and-retries"/> for more information. |
| 2686 |
</para> |
| 2687 |
</listitem> |
| 2688 |
</itemizedlist> |
| 2689 |
</para> |
| 2690 |
|
| 2691 |
<para> |
| 2692 |
The report displays retry statistics only if the <option>--max-tries</option> |
| 2693 |
option is not equal to 1. |
| 2694 |
</para> |
| 2695 |
|
| 2696 |
<para> |
| 2697 |
All values are computed for each statement executed by every client and are |
| 2698 |
reported after the benchmark has finished. |
| 2699 |
</para> |
| 2700 |
|
| 2701 |
<para> |
| 2702 |
For the default script, the output will look similar to this: |
| 2703 |
<screen> |
| 2704 |
starting vacuum...end. |
| 2705 |
transaction type: <builtin: TPC-B (sort of)> |
| 2706 |
scaling factor: 1 |
| 2707 |
query mode: simple |
| 2708 |
number of clients: 10 |
| 2709 |
number of threads: 1 |
| 2710 |
maximum number of tries: 1 |
| 2711 |
number of transactions per client: 1000 |
| 2712 |
number of transactions actually processed: 10000/10000 |
| 2713 |
number of failed transactions: 0 (0.000%) |
| 2714 |
number of transactions above the 50.0 ms latency limit: 1311/10000 (13.110 %) |
| 2715 |
latency average = 28.488 ms |
| 2716 |
latency stddev = 21.009 ms |
| 2717 |
initial connection time = 69.068 ms |
| 2718 |
tps = 346.224794 (without initial connection time) |
| 2719 |
statement latencies in milliseconds and failures: |
| 2720 |
0.012 0 \set aid random(1, 100000 * :scale) |
| 2721 |
0.002 0 \set bid random(1, 1 * :scale) |
| 2722 |
0.002 0 \set tid random(1, 10 * :scale) |
| 2723 |
0.002 0 \set delta random(-5000, 5000) |
| 2724 |
0.319 0 BEGIN; |
| 2725 |
0.834 0 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; |
| 2726 |
0.641 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; |
| 2727 |
11.126 0 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; |
| 2728 |
12.961 0 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; |
| 2729 |
0.634 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); |
| 2730 |
1.957 0 END; |
| 2731 |
</screen> |
| 2732 |
|
| 2733 |
Another example of output for the default script using serializable default |
| 2734 |
transaction isolation level (<command>PGOPTIONS='-c |
| 2735 |
default_transaction_isolation=serializable' pgbench ...</command>): |
| 2736 |
<screen> |
| 2737 |
starting vacuum...end. |
| 2738 |
transaction type: <builtin: TPC-B (sort of)> |
| 2739 |
scaling factor: 1 |
| 2740 |
query mode: simple |
| 2741 |
number of clients: 10 |
| 2742 |
number of threads: 1 |
| 2743 |
maximum number of tries: 10 |
| 2744 |
number of transactions per client: 1000 |
| 2745 |
number of transactions actually processed: 6317/10000 |
| 2746 |
number of failed transactions: 3683 (36.830%) |
| 2747 |
number of transactions retried: 7667 (76.670%) |
| 2748 |
total number of retries: 45339 |
| 2749 |
number of transactions above the 50.0 ms latency limit: 106/6317 (1.678 %) |
| 2750 |
latency average = 17.016 ms |
| 2751 |
latency stddev = 13.283 ms |
| 2752 |
initial connection time = 45.017 ms |
| 2753 |
tps = 186.792667 (without initial connection time) |
| 2754 |
statement latencies in milliseconds, failures and retries: |
| 2755 |
0.006 0 0 \set aid random(1, 100000 * :scale) |
| 2756 |
0.001 0 0 \set bid random(1, 1 * :scale) |
| 2757 |
0.001 0 0 \set tid random(1, 10 * :scale) |
| 2758 |
0.001 0 0 \set delta random(-5000, 5000) |
| 2759 |
0.385 0 0 BEGIN; |
| 2760 |
0.773 0 1 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; |
| 2761 |
0.624 0 0 SELECT abalance FROM pgbench_accounts WHERE aid = :aid; |
| 2762 |
1.098 320 3762 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; |
| 2763 |
0.582 3363 41576 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; |
| 2764 |
0.465 0 0 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); |
| 2765 |
1.933 0 0 END; |
| 2766 |
</screen></para> |
| 2767 |
|
| 2768 |
<para> |
| 2769 |
If multiple script files are specified, all statistics are reported |
| 2770 |
separately for each script file. |
| 2771 |
</para> |
| 2772 |
|
| 2773 |
<para> |
| 2774 |
Note that collecting the additional timing information needed for |
| 2775 |
per-statement latency computation adds some overhead. This will slow |
| 2776 |
average execution speed and lower the computed TPS. The amount |
| 2777 |
of slowdown varies significantly depending on platform and hardware. |
| 2778 |
Comparing average TPS values with and without latency reporting enabled |
| 2779 |
is a good way to measure if the timing overhead is significant. |
| 2780 |
</para> |
| 2781 |
</refsect2> |
| 2782 |
|
| 2783 |
<refsect2 id="failures-and-retries" xreflabel="Failures and Serialization/Deadlock Retries"> |
| 2784 |
<title>Failures and Serialization/Deadlock Retries</title> |
| 2785 |
|
| 2786 |
<para> |
| 2787 |
When executing <application>pgbench</application>, there are three main types |
| 2788 |
of errors: |
| 2789 |
<itemizedlist> |
| 2790 |
<listitem> |
| 2791 |
<para> |
| 2792 |
Errors of the main program. They are the most serious and always result |
| 2793 |
in an immediate exit from <application>pgbench</application> with the |
| 2794 |
corresponding error message. They include: |
| 2795 |
<itemizedlist> |
| 2796 |
<listitem> |
| 2797 |
<para> |
| 2798 |
errors at the beginning of <application>pgbench</application> |
| 2799 |
(e.g. an invalid option value); |
| 2800 |
</para> |
| 2801 |
</listitem> |
| 2802 |
<listitem> |
| 2803 |
<para> |
| 2804 |
errors in the initialization mode (e.g. the query to create |
| 2805 |
tables for built-in scripts fails); |
| 2806 |
</para> |
| 2807 |
</listitem> |
| 2808 |
<listitem> |
| 2809 |
<para> |
| 2810 |
errors before starting threads (e.g. could not connect to the |
| 2811 |
database server, syntax error in the meta command, thread |
| 2812 |
creation failure); |
| 2813 |
</para> |
| 2814 |
</listitem> |
| 2815 |
<listitem> |
| 2816 |
<para> |
| 2817 |
internal <application>pgbench</application> errors (which are |
| 2818 |
supposed to never occur...). |
| 2819 |
</para> |
| 2820 |
</listitem> |
| 2821 |
</itemizedlist></para> |
| 2822 |
</listitem> |
| 2823 |
<listitem> |
| 2824 |
<para> |
| 2825 |
Errors when the thread manages its clients (e.g. the client could not |
| 2826 |
start a connection to the database server / the socket for connecting |
| 2827 |
the client to the database server has become invalid). In such cases |
| 2828 |
all clients of this thread stop while other threads continue to work. |
| 2829 |
However, <option>--exit-on-abort</option> is specified, all of the |
| 2830 |
threads stop immediately in this case. |
| 2831 |
</para> |
| 2832 |
</listitem> |
| 2833 |
<listitem> |
| 2834 |
<para> |
| 2835 |
Direct client errors. They lead to immediate exit from |
| 2836 |
<application>pgbench</application> with the corresponding error message |
| 2837 |
in the case of an internal <application>pgbench</application> |
| 2838 |
error (which are supposed to never occur...) or when |
| 2839 |
<option>--exit-on-abort</option> is specified. Otherwise in the worst |
| 2840 |
case they only lead to the abortion of the failed client while other |
| 2841 |
clients continue their run (but some client errors are handled without |
| 2842 |
an abortion of the client and reported separately, see below). Later in |
| 2843 |
this section it is assumed that the discussed errors are only the |
| 2844 |
direct client errors and they are not internal |
| 2845 |
<application>pgbench</application> errors. |
| 2846 |
</para> |
| 2847 |
</listitem> |
| 2848 |
</itemizedlist> |
| 2849 |
</para> |
| 2850 |
|
| 2851 |
<para> |
| 2852 |
A client's run is aborted in case of a serious error; for example, the |
| 2853 |
connection with the database server was lost or the end of script was reached |
| 2854 |
without completing the last transaction. In addition, if execution of an SQL |
| 2855 |
or meta command fails for reasons other than serialization or deadlock errors, |
| 2856 |
the client is aborted. Otherwise, if an SQL command fails with serialization or |
| 2857 |
deadlock errors, the client is not aborted. In such cases, the current |
| 2858 |
transaction is rolled back, which also includes setting the client variables |
| 2859 |
as they were before the run of this transaction (it is assumed that one |
| 2860 |
transaction script contains only one transaction; see |
| 2861 |
<xref linkend="transactions-and-scripts"/> for more information). |
| 2862 |
Transactions with serialization or deadlock errors are repeated after |
| 2863 |
rollbacks until they complete successfully or reach the maximum |
| 2864 |
number of tries (specified by the <option>--max-tries</option> option) / the maximum |
| 2865 |
time of retries (specified by the <option>--latency-limit</option> option) / the end |
| 2866 |
of benchmark (specified by the <option>--time</option> option). If |
| 2867 |
the last trial run fails, this transaction will be reported as failed but |
| 2868 |
the client is not aborted and continues to work. |
| 2869 |
</para> |
| 2870 |
|
| 2871 |
<note> |
| 2872 |
<para> |
| 2873 |
Without specifying the <option>--max-tries</option> option, a transaction will |
| 2874 |
never be retried after a serialization or deadlock error because its default |
| 2875 |
value is 1. Use an unlimited number of tries (<literal>--max-tries=0</literal>) |
| 2876 |
and the <option>--latency-limit</option> option to limit only the maximum time |
| 2877 |
of tries. You can also use the <option>--time</option> option to limit the |
| 2878 |
benchmark duration under an unlimited number of tries. |
| 2879 |
</para> |
| 2880 |
<para> |
| 2881 |
Be careful when repeating scripts that contain multiple transactions: the |
| 2882 |
script is always retried completely, so successful transactions can be |
| 2883 |
performed several times. |
| 2884 |
</para> |
| 2885 |
<para> |
| 2886 |
Be careful when repeating transactions with shell commands. Unlike the |
| 2887 |
results of SQL commands, the results of shell commands are not rolled back, |
| 2888 |
except for the variable value of the <command>\setshell</command> command. |
| 2889 |
</para> |
| 2890 |
</note> |
| 2891 |
|
| 2892 |
<para> |
| 2893 |
The latency of a successful transaction includes the entire time of |
| 2894 |
transaction execution with rollbacks and retries. The latency is measured |
| 2895 |
only for successful transactions and commands but not for failed transactions |
| 2896 |
or commands. |
| 2897 |
</para> |
| 2898 |
|
| 2899 |
<para> |
| 2900 |
The main report contains the number of failed transactions. If the |
| 2901 |
<option>--max-tries</option> option is not equal to 1, the main report also |
| 2902 |
contains statistics related to retries: the total number of retried |
| 2903 |
transactions and total number of retries. The per-script report inherits all |
| 2904 |
these fields from the main report. The per-statement report displays retry |
| 2905 |
statistics only if the <option>--max-tries</option> option is not equal to 1. |
| 2906 |
</para> |
| 2907 |
|
| 2908 |
<para> |
| 2909 |
If you want to group failures by basic types in per-transaction and |
| 2910 |
aggregation logs, as well as in the main and per-script reports, use the |
| 2911 |
<option>--failures-detailed</option> option. If you also want to distinguish |
| 2912 |
all errors and failures (errors without retrying) by type including which |
| 2913 |
limit for retries was exceeded and how much it was exceeded by for the |
| 2914 |
serialization/deadlock failures, use the <option>--verbose-errors</option> |
| 2915 |
option. |
| 2916 |
</para> |
| 2917 |
</refsect2> |
| 2918 |
|
| 2919 |
<refsect2> |
| 2920 |
<title>Table Access Methods</title> |
| 2921 |
|
| 2922 |
<para> |
| 2923 |
You may specify the <link linkend="tableam">Table Access Method</link> |
| 2924 |
for the pgbench tables. The environment variable <envar>PGOPTIONS</envar> |
| 2925 |
specifies database configuration options that are passed to PostgreSQL via |
| 2926 |
the command line (See <xref linkend="config-setting-shell"/>). |
| 2927 |
For example, a hypothetical default Table Access Method for the tables that |
| 2928 |
pgbench creates called <literal>wuzza</literal> can be specified with: |
| 2929 |
<programlisting> |
| 2930 |
PGOPTIONS='-c default_table_access_method=wuzza' |
| 2931 |
</programlisting> |
| 2932 |
</para> |
| 2933 |
</refsect2> |
| 2934 |
|
| 2935 |
<refsect2> |
| 2936 |
<title>Good Practices</title> |
| 2937 |
|
| 2938 |
<para> |
| 2939 |
It is very easy to use <application>pgbench</application> to produce completely |
| 2940 |
meaningless numbers. Here are some guidelines to help you get useful |
| 2941 |
results. |
| 2942 |
</para> |
| 2943 |
|
| 2944 |
<para> |
| 2945 |
In the first place, <emphasis>never</emphasis> believe any test that runs |
| 2946 |
for only a few seconds. Use the <option>-t</option> or <option>-T</option> option |
| 2947 |
to make the run last at least a few minutes, so as to average out noise. |
| 2948 |
In some cases you could need hours to get numbers that are reproducible. |
| 2949 |
It's a good idea to try the test run a few times, to find out if your |
| 2950 |
numbers are reproducible or not. |
| 2951 |
</para> |
| 2952 |
|
| 2953 |
<para> |
| 2954 |
For the default TPC-B-like test scenario, the initialization scale factor |
| 2955 |
(<option>-s</option>) should be at least as large as the largest number of |
| 2956 |
clients you intend to test (<option>-c</option>); else you'll mostly be |
| 2957 |
measuring update contention. There are only <option>-s</option> rows in |
| 2958 |
the <structname>pgbench_branches</structname> table, and every transaction wants to |
| 2959 |
update one of them, so <option>-c</option> values in excess of <option>-s</option> |
| 2960 |
will undoubtedly result in lots of transactions blocked waiting for |
| 2961 |
other transactions. |
| 2962 |
</para> |
| 2963 |
|
| 2964 |
<para> |
| 2965 |
The default test scenario is also quite sensitive to how long it's been |
| 2966 |
since the tables were initialized: accumulation of dead rows and dead space |
| 2967 |
in the tables changes the results. To understand the results you must keep |
| 2968 |
track of the total number of updates and when vacuuming happens. If |
| 2969 |
autovacuum is enabled it can result in unpredictable changes in measured |
| 2970 |
performance. |
| 2971 |
</para> |
| 2972 |
|
| 2973 |
<para> |
| 2974 |
A limitation of <application>pgbench</application> is that it can itself become |
| 2975 |
the bottleneck when trying to test a large number of client sessions. |
| 2976 |
This can be alleviated by running <application>pgbench</application> on a different |
| 2977 |
machine from the database server, although low network latency will be |
| 2978 |
essential. It might even be useful to run several <application>pgbench</application> |
| 2979 |
instances concurrently, on several client machines, against the same |
| 2980 |
database server. |
| 2981 |
</para> |
| 2982 |
</refsect2> |
| 2983 |
<refsect2> |
| 2984 |
<title>Security</title> |
| 2985 |
|
| 2986 |
<para> |
| 2987 |
If untrusted users have access to a database that has not adopted a |
| 2988 |
<link linkend="ddl-schemas-patterns">secure schema usage pattern</link>, |
| 2989 |
do not run <application>pgbench</application> in that |
| 2990 |
database. <application>pgbench</application> uses unqualified names and |
| 2991 |
does not manipulate the search path. |
| 2992 |
</para> |
| 2993 |
</refsect2> |
| 2994 |
</refsect1> |
| 2995 |
</refentry> |