pgpro_tune

pgpro_tune
Prev	Up	Postgres Pro Server Applications	Home	Next

Description

The pgpro_tune utility is a command-line tool for automatic tuning. Optimal values for different configuration parameters of Postgres Pro depend on the hardware configuration. The pgpro_tune utility collects information about the system and transforms it into a set of parameters written into the configuration file.

Usage

The use of pgpro_tune is enabled in initdb by default. There are two initdb options for this tool: --no-tune disables its usage, --tune=OPTIONS allows passing additional options for the pgpro_tune run.

First pgpro_tune runs the default preset. Additional presets may be provided by using -P or --preset=NAME option, or specifying the preset name as the last command-line argument. To disable the default preset, use the --no-default option.

The following default presets are currently available:

default.tune: This is currently the main preset, which provides different configurations specific to the server's edition and version.
regress.tune: This preset is useful for debugging purposes. It reverts changes made by the default.tune configuration parameters to ensure that regression tests will complete successfully since their output may vary due to different hardware-specific configurations.
1c.tune: This preset contains 1C-specific options, some of which require the plantuner module to be installed. For more information about configuring Postgres Pro for 1C solutions, see Appendix K.
extensions.tune: This preset contains additional options for extensions. These options require other modules (such as pg_stat_statements) to be installed.

Additional options may be passed via -O or --options=OPTIONS parameter for any preset, but for the default preset --default-options=OPTIONS should be used.

Additional environment variables can be set for presets with the --set NAME=VALUE parameter. These values will be the same both for the default preset and all custom presets.

By default, the presets are placed in the share directory, but you can specify their location manually in the --preset-dir=NAME parameter.

By default, pgpro_tune works with the postgresql.conf configuration file located in the data directory. The data directory can be specified with -D or --pgdata=DATADIR, or provided with environment variable PGDATA. When runing pgpro_tune from initdb, the tool will use the data directory of initdb.

Configuration file can also be specified with --config-file=FILENAME.

The pgpro_tune tool writes its changes into a separate block at the end of the configuration file, which is commented as added by pgpro_tune. For details, see the example of the preset.

Options

--config-file=filename: Specifies the main configuration file name.
-D datadir --pgdata=datadir: Specifies the directory where the database cluster should be stored.
--default-options=options: Specifies parameters for the default preset.
--no-default: Disables the default preset. Note that in this case another preset must be specified anyway either with the -P option or as the last parameter with the preset name.
-O preset-options --options=preset-options: Specifies options to be passed directly to the preset that was previously specified in the -P parameter or to the preset indicated as the last command-line parameter, if -P was not used.
-P name --preset=name: Specifies the preset name. If several names are indicated, presets are executed in turn, taking into account the output of the previous preset.
Note
The preset name may be specified either with this option or as the last parameter. Using both will cause an error.
--preset-dir=name: Specifies the directory containing presets. By default, presets are located in the share directory.
--set name=value: Sets the environment variable for presets.
--show: Shows all available presets from the directory specified in the --preset-dir option, if it is set, or from the default directory otherwise.
-V --version: Print the pgpro_tune version and exit.
-? --help: Show help about pgpro_tune command line arguments, and exit.

Environment variables

pgpro_tune sets the following environment variables before running presets.

EDITION — the Postgres Pro edition.
ENABLE_CRASH_INFO — if set, Postgres Pro was built with the --enable-crash-info option.
ENABLE_NLS — if set, Postgres Pro was built with the --enable-nls option.
ENABLE_PGPRO_TUNE — if set, Postgres Pro was built with the --enable-pgpro-tune option.
MEMMB — the RAM size in megabytes.
MVER — Postgres Pro major version.
NCPU — the number of CPUs.
USE_BONJOUR — if set, Postgres Pro was built with the --with-bonjour option.
USE_BSD_AUTH — if set, Postgres Pro was built with the --with-bsd-auth option.
USE_ICU — if set, Postgres Pro was built the --with-icu option.
USE_LDAP — if set, Postgres Pro was built with the --with-ldap option.
USE_LIBUNWIND — if set, Postgres Pro was built with the --with-libunwind option.
USE_LIBXML — if set, Postgres Pro was built with the --with-libxml option.
USE_LIBXSLT — if set, Postgres Pro was built with the --with-libxslt option.
USE_LLVM — if set, Postgres Pro was built with the --with-llvm option.
USE_LZ4 — if set, Postgres Pro was built with the --with-lz4 option.
USE_OPENSSL — if set, Postgres Pro was built with the --with-openssl option.
USE_PAM — if set, Postgres Pro was built with the --with-pam option.
USE_SYSTEMD — if set, Postgres Pro was built with the --with-systemd option.
USE_ZSTD — if set, Postgres Pro was built with the --with-zstd option.

Writing presets

A preset is an executable, usually a shell script, that transforms the information passed through environment variables into the set of configuration parameters.

The expected output of the preset is the set of specifically formatted lines. The first non-whitespace character is the indicator of the line content interpretation for pgpro_tune. The indicator should be one of the following:

# for the comment action, the rest of the output line will be written into the configuration file after '#'.
= for the replace action, the rest of the output line should contain a pair of parameters NAME and VALUE (both NAME=VALUE and NAME VALUE are acceptable). Line NAME = VALUE will be written into the configuration file. The text after VALUE will be added as a comment.
=+ or += for the add action, the rest of the output line should contain a pair of parameters NAME and VALUE. VALUE will be added to the previous effective value of the parameter NAME at the back (=+) or at the front (+=). If there is no effective value, the ADD action works like the replace action. The text after VALUE will be added as a comment.
-= for the withdraw action, the rest of the output line should contain a pair of parameters NAME and VALUE. If the NAME parameter has the effective value containing VALUE, it will be removed and the rest of the effective value will remain the same. The text after VALUE will be added as a comment. If there is no effective value, nothing happens.

Example of the preset

Suppose you have the following configuration file called test.conf:

work_mem = 4MB # Default value
shared_preload_libraries = 'plantuner'
search_path = '"$user",wrong_schema,public'

And the following preset called test.tune:

echo "# Adding new configuration parameters."
#Replace configuration parameter value by a new one
echo "work_mem = 8MB"
#Append to the start of existing value
echo "shared_preload_libraries += pg_stat_statements"
#Append to the end of existing value
echo "shared_preload_libraries =+ pg_prewarm"
#Withdraw from existing value
echo "search_path -= 'wrong_schema'"

Run the following command to use this preset:

pgpro_tune --config-file=/path/to/test.conf -P/path/to/test.tune --no-default

This command will result in the following changes of the configuration file:

#------------------------------------------------------------------------------
# The following settings were added by pgpro_tune.
# pgpro_tune was run with the following options:
# --no-default --config-file=/path/to/test/conf -P/path/to/test/tune --no-default
# At YYYY-MM-DD HH:MM:SS
#------------------------------------------------------------------------------
# Adding new configuration parameters.
work_mem = 8MB
shared_preload_libraries = 'pg_stat_statements, plantuner'
shared_preload_libraries = 'pg_stat_statements, plantuner, pg_prewarm'
search_path = '"$user", public'
#------------------------------------------------------------------------------
# End of settings added by pgpro_tune at YYYY-MM-DD HH-MM-SS
#------------------------------------------------------------------------------