How-To:
Tune postgreSQL
Important note before you begin
Please be aware that current UCS versions (up to 4.3-2 e264) have not yet implemented a mechanism for editing PostgreSQL configuration for own need. The following steps might get lost when updating UCS and you might need to re-apply your changes.
In case your postgreSQL installation on UCS turns out to be too slow you might consider to adjust the default values configured. The default values might not be appropriate for all workloads.
Step 1 Current settings
Identify current relevant settings:
root@member:/etc/postgresql/9.6/main# grep -E "max_connections|shared_buffers|effective_cache_size|maintenance_work_mem|checkpoint_completion_target|wal_buffers|default_statistics_target|random_page_cost|effective_io_concurrency|^work_mem|^#work_mem|checkpoint_segments" postgresql.conf
max_connections = 100 # (change requires restart)
shared_buffers = 128MB # min 128kB
#work_mem = 4MB # min 64kB
#maintenance_work_mem = 64MB # min 1MB
#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#random_page_cost = 4.0 # same scale as above
#effective_cache_size = 4GB
#default_statistics_target = 100 # range 1-10000
Step 2 Backup current settings
root@member:/etc/postgresql/9.6/main# cp postgresql.conf postgresql.conf.bak
Step 3 Decide how much memory to assign to postgreSQL
Main decision is how much memory of your server postgreSQL should be allowed to use as it’s max. As a rough value you should calculate 2GB for basic use for the UCS operating system plus additional memory for any application running. Do not forget to take needed file system cache into account.
Note: In case you consider to have a more or less dedicated database server you should not install any other services. Other services need file system cache (buffers etc.) while for postgreSQL it is usually preferred to assign the memory to postgreSQL instead of using it as cache.
Example:
Basic UCS, no additional services. Server has 8GB of memory (RAM). Here you might assign 6GB max to postgreSQL.
Step 4 Calculate parameters
Use recommended values from postgreSQL documentation according to your hardware or use an online calculator likePGTune.
See screenshot for example of 6GB with 4CPUs:
Step 5 Implement new parameters
Edit your configuration file /etc/postgresql/9.6/main/postgresql.conf accordingly to the new settings and restart your postgreSQL server:
systemctl start postgresql
Step 6 Monitor your system
Monitor your system to see its behaviour regarding CPU and RAM usage. If you encounter out-of-memory (oom) messages in /var/log//syslog reduce your settings.
Otherwise you might use Nagios to monitor.