Tuning the PostgreSQL database

This topic was edited by a BMC Contributor and has not been approved.  More information.

The basic PostgreSQL configuration shipped with the product is tuned for a wide compatibility rather than performance, and some default parameters are undersized.

We recommend that you use an online version of the pgtune (  https://pgtune.leopard.in.ua/#/ Open link  ) tool to optimize the configuration. The tool generates a set of configuration items that can directly be replaced in the postgresql.conf file. You must restart the server after the changes are applied.

Note

Previously, the standard recommendation was to use the third-party tool pgtune ( https://github.com/gregs1104/pgtune Open link ) to optimize the configuration. However, the Python script tool was discontinued with PostgresSQL version 9.1. 

Example

Example of the configuration items modified for a system with 16 GB of memory for PostgreSQL version 9.5:

#------------------------------------------------------------------------------

# pgtune config Based on 16433176 KB RAM, platform Linux

#------------------------------------------------------------------------------

default_statistics_target = 100

maintenance_work_mem = 960MB

checkpoint_completion_target = 0.9

effective_cache_size = 11GB

work_mem = 80MB

wal_buffers = 16MB

checkpoint_segments = 32

shared_buffers = 3840MB


Example of the configuration items modified for a system with 32 GB of memory for PostgreSQL version 9.6:


#------------------------------------------------------------------------------

# pgtune config Based on 32947648 KB RAM, platform Linux

#------------------------------------------------------------------------------

max_connections = 1300

shared_buffers = 8GB

effective_cache_size = 24GB

maintenance_work_mem = 2GB

checkpoint_completion_target = 0.7

wal_buffers = 16MB

default_statistics_target = 100

random_page_cost = 1.1

effective_io_concurrency = 300

work_mem = 10485kB

min_wal_size = 1GB

max_wal_size = 4GB

max_worker_processes = 8

max_parallel_workers_per_gather = 4

After the installation or upgrade or before taking a backup of BMC Helix Business Workflows, we also recommend that you run the VACUUM FULL ANALYZE command from the pgAdmin for every AR-related database. This reclaims and optimizes the space used by the database server and rebuilds the statistics. For more information about the VACUUM FULL ANALYZE command, see   Routine Vacuuming Open link  in the PostgreSQL documentation. 

Was this page helpful? Yes No Submitting... Thank you

Comments