Testing and tuning commands v6.1.0

EDB Postgres Distributed has tools that help with testing and tuning your PGD clusters. For background, see Testing and tuning.

pgd_bench

Synopsis

A benchmarking tool for EDB Postgres Distributed deployments.

pgd_bench [OPTION]... [DBNAME] [DBNAME2]

DBNAME can be a conninfo string of the format: "host=10.1.1.2 user=postgres dbname=master"

See pgd_bench in Testing and tuning for examples of pgd_bench options and usage.

Options

The pgd_bench command is implemented as a wrapper around the pgbench command. This means that it shares many of the same options and created tables named pgbench as it performs its testing.

Options that are specific to pgd_bench include the following.

Setting mode

-m or --mode

The mode can be set to regular, camo, or failover. The default is regular.

• regular — Only a single node is needed to run pgd_bench.
• camo — A second node must be specified to act as the CAMO partner. (CAMO must be set up.)
• failover — A second node must be specified to act as the failover.

When using -m failover, an additional option --retry is available. This option instructs pgd_bench to retry transactions when there's a failover. The --retry option is automatically enabled when -m camo is used.

When using -m camo and providing a custom script, the SQL commands in the script must be wrapped in SQL transaction commands. That is, the first SQL command must be BEGIN, and the final SQL command must be COMMIT.

Setting GUC variables

-o or --set-option

This option is followed by NAME=VALUE entries, which are applied using the Postgres SET command on each server that pgd_bench connects to, and only those servers.

The other options are identical to the Postgres pgbench command. For details, see the PostgreSQL pgbench documentation.

The complete list of options (pgd_bench and pgbench) follow.

Initialization options

• -i, --initialize — Invoke initialization mode.
• -I, --init-steps=[dtgGvpf]+ (default "dtgvp") — Run selected initialization steps.
  • d — Drop any existing pgbench tables.
  • t — Create the tables used by the standard pgbench scenario.
  • g — Generate data client-side and load it into the standard tables, replacing any data already present.
  • G — Generate data server-side and load it into the standard tables, replacing any data already present.
  • v — Invoke VACUUM on the standard tables.
  • p — Create primary key indexes on the standard tables.
  • f — Create foreign key constraints between the standard tables.
• -F, --fillfactor=NUM — Set fill factor.
• -n, --no-vacuum — Don't run VACUUM during initialization.
• -q, --quiet — Quiet logging (one message every 5 seconds).
• -s, --scale=NUM — Scaling factor.
• --foreign-keys — Create foreign key constraints between tables.
• --index-tablespace=TABLESPACE — Create indexes in the specified tablespace.
• --partition-method=(range|hash) — Partition pgbench_accounts with this method. The default is range.
• --partitions=NUM — Partition pgbench_accounts into NUM parts. The default is 0.
• --tablespace=TABLESPACE — Create tables in the specified tablespace.
• --unlogged-tables — Create tables as unlogged tables. (Note: Unlogged tables aren't replicated.)

Options to select what to run

• -b, --builtin=NAME[@W] — Add built-in script NAME weighted at W. The default is 1. Use -b list to list available scripts.
• -f, --file=FILENAME[@W] — Add script FILENAME weighted at W. The default is 1.
• -N, --skip-some-updates — Updates of pgbench_tellers and pgbench_branches. Same as -b simple-update.
• -S, --select-only — Perform SELECT-only transactions. Same as -b select-only.

Benchmarking options

• -c, --client=NUM — Number of concurrent database clients. The default is 1.
• -C, --connect — Establish new connection for each transaction.
• -D, --define=VARNAME=VALUE — Define variable for use by custom script.
• -j, --jobs=NUM — Number of threads. The default is 1.
• -l, --log — Write transaction times to log file.
• -L, --latency-limit=NUM — Count transactions lasting more than NUM ms as late.
• -m, --mode=regular|camo|failover — Mode in which to run pgbench. The default is regular.
• -M, --protocol=simple|extended|prepared — Protocol for submitting queries. The default is simple.
• -n, --no-vacuum — Don't run VACUUM before tests.
• -o, --set-option=NAME=VALUE — Specify runtime SET option.
• -P, --progress=NUM — Show thread progress report every NUM seconds.
• -r, --report-per-command — Latencies, failures, and retries per command.
• -R, --rate=NUM — Target rate in transactions per second.
• -s, --scale=NUM — Report this scale factor in output.
• -t, --transactions=NUM — Number of transactions each client runs. The default is 10.
• -T, --time=NUM — Duration of benchmark test, in seconds.
• -v, --vacuum-all — Vacuum all four standard tables before tests.
• --aggregate-interval=NUM — Data over NUM seconds.
• --failures-detailed — Report the failures grouped by basic types.
• --log-prefix=PREFIX — Prefix for transaction time log file. The default is pgbench_log.
• --max-tries=NUM — Max number of tries to run transaction. The default is 1.
• --progress-timestamp — Use Unix epoch timestamps for progress.
• --random-seed=SEED — Set random seed (time, rand, integer).
• --retry — Retry transactions on failover. Used with -m.
• --sampling-rate=NUM — Fraction of transactions to log, for example, 0.01 for 1%.
• --show-script=NAME — Show built-in script code, then exit.
• --verbose-errors — Print messages of all errors.

Common options:

• -d, --debug — Print debugging output.
• -h, --host=HOSTNAME — Database server host or socket directory.
• -p, --port=PORT — Database server port number.
• -U, --username=USERNAME — Connect as specified database user.
• -V, --version — Output version information, then exit.
• -?, --help — Show help, then exit.