Testing and tuning commands v5

EDB Postgres Distributed has tools which help with testing and tuning of your PGD clusters. For background, read the Testing and Tuning section.

pgd_bench

Synopsis

A benchmarking tool for PGD enhanced PostgreSQL.

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

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

Consult the Testing and Tuning - Pgd_bench section for examples of pgd_bench options and usage.

Options

pgd_bench specific options include:

Setting mode

-m or --mode

Which can be set to regular, camo, or failover. It defaults to 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 should 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 will instruct pgd_bench to retry transactions when there is a failover. The --retry option is automatically enabled with -m camo.

Setting GUC variables

-o or --set-option

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

The other options are identical to the Community PostgreSQL pgbench. For more details, consult the official documentation on pgbench.

We list all the options (pgd_bench and pgbench) below for completeness.

Initialization options:

• -i, --initialize — invokes 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 — do not run VACUUM during initialization
• -q, --quiet — quiet logging (one message each 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 (default: range)
• --partitions=NUM — partition pgbench_accounts into NUM parts (default: 0)
• --tablespace=TABLESPACE — create tables in the specified tablespace
• --unlogged-tables — create tables as unlogged tables (Note: unlogged tables are not replicated)

Options to select what to run:

• -b, --builtin=NAME[@W] — add builtin script NAME weighted at W (default: 1). Use -b list to list available scripts.
• -f, --file=FILENAME[@W] — add script FILENAME weighted at W (default: 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 (default: 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 (default: 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 pgbench should run (default: regular)
• -M, --protocol=simple|extended|prepared — protocol for submitting queries (default: simple)
• -n, --no-vacuum — do not 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 (default: 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 (default: pgbench_log)
• --max-tries=NUM — max number of tries to run transaction (default: 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 (e.g., 0.01 for 1%)
• --show-script=NAME — show builtin 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

• On this page
• pgd_bench