Movatterモバイル変換

-i
--initialize

Required to invoke initialization mode.

-Ffillfactor
--fillfactor=fillfactor

Create thepgbench_accounts,pgbench_tellers andpgbench_branches tables with the given fillfactor. Default is 100.

-n
--no-vacuum

Perform no vacuuming after initialization.

-q
--quiet

Switch logging to quiet mode, producing only one progress message per 5 seconds. The default logging prints one message each 100000 rows, which often outputs many lines per second (especially on good hardware).

-sscale_factor
--scale=scale_factor

Multiply the number of rows generated by the scale factor. For example,-s 100 will create 10,000,000 rows in thepgbench_accounts table. Default is 1. When the scale is 20,000 or larger, the columns used to hold account identifiers (aid columns) will switch to using larger integers (bigint), in order to be big enough to hold the range of account identifiers.

--foreign-keys

Create foreign key constraints between the standard tables.

--index-tablespace=index_tablespace

Create indexes in the specified tablespace, rather than the default tablespace.

--tablespace=tablespace

Create tables in the specified tablespace, rather than the default tablespace.

--unlogged-tables

Create all tables as unlogged tables, rather than permanent tables.

-bscriptname[@weight]
--builtin=scriptname[@weight]

Add the specified built-in script to the list of scripts to be executed. Available built-in scripts are:tpcb-like,simple-update andselect-only. Unambiguous prefixes of built-in names are accepted. With the special namelist, show the list of built-in scripts and exit immediately.

Optionally, write an integer weight after@ to adjust the probability of selecting this script versus other ones. The default weight is 1. See below for details.

-cclients
--client=clients

Number of clients simulated, that is, number of concurrent database sessions. Default is 1.

-C
--connect

Establish a new connection for each transaction, rather than doing it just once per client session. This is useful to measure the connection overhead.

-d
--debug

Print debugging output.

-Dvarname=value
--define=varname=value

Define a variable for use by a custom script (see below). Multiple-D options are allowed.

-ffilename[@weight]
--file=filename[@weight]

Add a transaction script read fromfilename to the list of scripts to be executed.

Optionally, write an integer weight after@ to adjust the probability of selecting this script versus other ones. The default weight is 1. (To use a script file name that includes an@ character, append a weight so that there is no ambiguity, for examplefilen@me@1.) See below for details.

-jthreads
--jobs=threads

Number of worker threads withinpgbench. Using more than one thread can be helpful on multi-CPU machines. Clients are distributed as evenly as possible among available threads. Default is 1.

-l
--log

Write information about each transaction to a log file. See below for details.

-Llimit
--latency-limit=limit

Transactions that last more thanlimit milliseconds are counted and reported separately, aslate.

When throttling is used (--rate=...), transactions that lag behind schedule by more thanlimit ms, and thus have no hope of meeting the latency limit, are not sent to the server at all. They are counted and reported separately asskipped.

-Mquerymode
--protocol=querymode

Protocol to use for submitting queries to the server:

• simple: use simple query protocol.

• extended: use extended query protocol.

• prepared: use extended query protocol with prepared statements.

The default is simple query protocol. (SeeChapter 50 for more information.)

-n
--no-vacuum

Perform no vacuuming before running the test. This option isnecessary if you are running a custom test scenario that does not include the standard tablespgbench_accounts,pgbench_branches,pgbench_history, andpgbench_tellers.

-N
--skip-some-updates

Run built-in simple-update script. Shorthand for-b simple-update.

-Psec
--progress=sec

Show progress report everysec seconds. The report includes the time since the beginning of the run, the tps since the last report, and the transaction latency average and standard deviation since the last report. Under throttling (-R), the latency is computed with respect to the transaction scheduled start time, not the actual transaction beginning time, thus it also includes the average schedule lag time.

-r
--report-latencies

Report the average per-statement latency (execution time from the perspective of the client) of each command after the benchmark finishes. See below for details.

-Rrate
--rate=rate

Execute transactions targeting the specified rate instead of running as fast as possible (the default). The rate is given in transactions per second. If the targeted rate is above the maximum possible rate, the rate limit won't impact the results.

The rate is targeted by starting transactions along a Poisson-distributed schedule time line. The expected start time schedule moves forward based on when the client first started, not when the previous transaction ended. That approach means that when transactions go past their original scheduled end time, it is possible for later ones to catch up again.

When throttling is active, the transaction latency reported at the end of the run is calculated from the scheduled start times, so it includes the time each transaction had to wait for the previous transaction to finish. The wait time is called the schedule lag time, and its average and maximum are also reported separately. The transaction latency with respect to the actual transaction start time, i.e., the time spent executing the transaction in the database, can be computed by subtracting the schedule lag time from the reported latency.

If--latency-limit is used together with--rate, a transaction can lag behind so much that it is already over the latency limit when the previous transaction ends, because the latency is calculated from the scheduled start time. Such transactions are not sent to the server, but are skipped altogether and counted separately.

A high schedule lag time is an indication that the system cannot process transactions at the specified rate, with the chosen number of clients and threads. When the average transaction execution time is longer than the scheduled interval between each transaction, each successive transaction will fall further behind, and the schedule lag time will keep increasing the longer the test run is. When that happens, you will have to reduce the specified transaction rate.

-sscale_factor
--scale=scale_factor

Report the specified scale factor inpgbench's output. With the built-in tests, this is not necessary; the correct scale factor will be detected by counting the number of rows in thepgbench_branches table. However, when testing only custom benchmarks (-f option), the scale factor will be reported as 1 unless this option is used.

-S
--select-only

Run built-in select-only script. Shorthand for-b select-only.

-ttransactions
--transactions=transactions

Number of transactions each client runs. Default is 10.

-Tseconds
--time=seconds

Run the test for this many seconds, rather than a fixed number of transactions per client.-t and-T are mutually exclusive.

-v
--vacuum-all

Vacuum all four standard tables before running the test. With neither-n nor-v,pgbench will vacuum thepgbench_tellers andpgbench_branches tables, and will truncatepgbench_history.

--aggregate-interval=seconds

Length of aggregation interval (in seconds). May be used only with-l option. With this option, the log contains per-interval summary data, as described below.

--log-prefix=prefix

Set the filename prefix for the log files created by--log. The default ispgbench_log.

--progress-timestamp

When showing progress (option-P), use a timestamp (Unix epoch) instead of the number of seconds since the beginning of the run. The unit is in seconds, with millisecond precision after the dot. This helps compare logs generated by various tools.

--sampling-rate=rate

Sampling rate, used when writing data into the log, to reduce the amount of log generated. If this option is given, only the specified fraction of transactions are logged. 1.0 means all transactions will be logged, 0.05 means only 5% of the transactions will be logged.

Remember to take the sampling rate into account when processing the log file. For example, when computing tps values, you need to multiply the numbers accordingly (e.g., with 0.01 sample rate, you'll only get 1/100 of the actual tps).

-hhostname
--host=hostname

The database server's host name

-pport
--port=port

The database server's port number

-Ulogin
--username=login

The user name to connect as

-V
--version

Print thepgbench version and exit.

-?
--help

Show help aboutpgbench command line arguments, and exit.

1. BEGIN;

2. UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

3. SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

4. UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

5. UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

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

7. END;

Note

BeforePostgres Pro 9.6, SQL commands in script files were terminated by newlines, and so they could not be continued across lines. Now a semicolon isrequired to separate consecutive SQL commands (though a SQL command does not need one if it is followed by a meta command). If you need to create a script file that works with both old and new versions ofpgbench, be sure to write each SQL command on a single line ending with a semicolon.

Table 244. Automatic Variables

\setvarnameexpression

Sets variablevarname to a value calculated fromexpression. The expression may contain integer constants such as5432, double constants such as3.14159, references to variables:variablename, unary operators (+,-) and binary operators (+,-,*,/,%) with their usual precedence and associativity,function calls, and parentheses.

Examples:

\set ntellers 10 * :scale\set aid (1021 * random(1, 100000 * :scale)) % \           (100000 * :scale) + 1

\sleepnumber [ us | ms | s ]

Causes script execution to sleep for the specified duration in microseconds (us), milliseconds (ms) or seconds (s). If the unit is omitted then seconds are the default.number can be either an integer constant or a:variablename reference to a variable having an integer value.

Example:

\sleep 10 ms

\setshellvarnamecommand [argument ... ]

Sets variablevarname to the result of the shell commandcommand with the givenargument(s). The command must return an integer value through its standard output.

command and eachargument can be either a text constant or a:variablename reference to a variable. If you want to use anargument starting with a colon, write an additional colon at the beginning ofargument.

Example:

\setshell variable_to_be_assigned command literal_argument :variable ::literal_starting_with_colon

\shellcommand [argument ... ]

Same as\setshell, but the result of the command is discarded.

Example:

\shell command literal_argument :variable ::literal_starting_with_colon

Table 245. pgbench Functions

• For an exponential distribution,parameter controls the distribution by truncating a quickly-decreasing exponential distribution atparameter, and then projecting onto integers between the bounds. To be precise, with

  
  f(x) = exp(-parameter * (x - min) / (max - min + 1)) / (1 - exp(-parameter))
  

  Then valuei betweenmin andmax inclusive is drawn with probability:f(i) - f(i + 1).

  Intuitively, the larger theparameter, the more frequently values close tomin are accessed, and the less frequently values close tomax are accessed. The closer to 0parameter is, the flatter (more uniform) the access distribution. A crude approximation of the distribution is that the most frequent 1% values in the range, close tomin, are drawnparameter% of the time. Theparameter value must be strictly positive.

• For a Gaussian distribution, the interval is mapped onto a standard normal distribution (the classical bell-shaped Gaussian curve) truncated at-parameter on the left and+parameter on the right. Values in the middle of the interval are more likely to be drawn. To be precise, ifPHI(x) is the cumulative distribution function of the standard normal distribution, with meanmu defined as(max + min) / 2.0, with

  
  f(x) = PHI(2.0 * parameter * (x - mu) / (max - min + 1)) /
         (2.0 * PHI(parameter) - 1)
  

  then valuei betweenmin andmax inclusive is drawn with probability:f(i + 0.5) - f(i - 0.5). Intuitively, the larger theparameter, the more frequently values close to the middle of the interval are drawn, and the less frequently values close to themin andmax bounds. About 67% of values are drawn from the middle1.0 / parameter, that is a relative0.5 / parameter around the mean, and 95% in the middle2.0 / parameter, that is a relative1.0 / parameter around the mean; for instance, ifparameter is 4.0, 67% of values are drawn from the middle quarter (1.0 / 4.0) of the interval (i.e., from3.0 / 8.0 to5.0 / 8.0) and 95% from the middle half (2.0 / 4.0) of the interval (second and third quartiles). The minimumparameter is 2.0 for performance of the Box-Muller transform.