Testing and tuning commands =========================== EDB Postgres Distributed has tools that help with testing and tuning your PGD clusters. For background, see :ref:`Postgres Distributed terminology ` . pgd_bench --------- Synopsis ^^^^^^^^ A benchmarking tool for EDB Postgres Distributed deployments. .. code:: shell pgd_bench [OPTION]... [DBNAME] [DBNAME2] ``DBNAME`` can be a conninfo string of the format: ``"host=10.1.1.2 user=postgres dbname=master"`` See :ref:`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.