Table of Contents
DISCLAIMER: The recommendations below are suggestions for your environment. Every infrastructure is different; we've seen wide variations in PostgreSQL performance even among seemingly similar infrastructures that are hosting different types of workloads or using Salt in different ways.
The following settings should be applied to the
postgresql.conf file for the active cluster. The database will need to be restarted for the parameters to take effect.
This document was based on PostgreSQL 9.6 but should be valid for 10.x series as well.
They are also based on an example 4-8 core server with 16 GB of RAM and assume that Postgres is the only service consuming the bulk of resources on the machine.
SaltStack Enterprise with DirectDB turned on for salt-masters requires approximately 50-60 connections for the SSE processes themselves and an additional ~10 connections per master. If you are not using DirectDB you can most likely stick to the Pg default of 100 maximum connections. Otherwise add an additional 10 connections per salt-master with DirectDB turned on. Note that Pg allocates some resources on startup based on this value in conjunction with
work_mem (see below) so beware of turning these parameters up without watching their impact on server resources.
Error messages in the SSE log (
/var/log/raas/raas) indicating that the DB is out of connections can also be an indication that this value should be raised.
Rule of thumb value is 25% of system memory. For a 16 GB machine this would be 4GB or
shared_buffers = 4096MB
This is an estimate used by the Pg query planner, it does not result in more or less memory allocation. A reasonable suggestion is 75% of system RAM.
effective_cache_size = 12288MB
Work_mem tunes the amount of memory that Pg can use for in-memory sorts. It defaults to 4 MB, but it is suggested to increase this to 16 MB to accommodate some of the more complex sorts and joins needed by SSE.
work_mem = 16MB
Maximum size to let the WAL grow between checkpoints. This is a soft limit. The default is 1 GB, it can safely be increased at the expense of potentially longer recovery time in case of a dirty database shutdown.
max_wal_size = 4GB
Tunes the WAL segment writing algorithm to spread out the checkpoint writing. The default value of .5 (which aims to have the checkpoint completed before 50% of the next checkpoint is written) is too low for modern fast disks and disk arrays. This setting can be safely turned up to its maximum of 0.9.
checkpoint_completion_target = 0.9
DANGER…turning this off can greatly increase the risk of data loss in case of power failure or server crash, especially on systems that have caching disk controllers without internal battery backup. SaltStack will not be liable for data loss or corrupt databases if you turn this off based on the recommendations in this document.
synchronous_commit specifies whether transaction commit will wait for WAL records to be written to disk before the command returns a “success” indication to the client. When off, there can be a delay between when success is reported to the client and when the transaction is really guaranteed to be safe against a server crash. Turning it off can seriously increase the performance of inserts into the database.
synchronous_commit = off
Portions of this document were taken from the PostgreSQL official documentation as well as the Tuning article in the PostgreSQL Wiki. Further guidance is available from Josh Berkus's Annotated.conf GitHub Repository.