Table of Contents
- 1. max_connections
- 2. shared_buffers
- 3. effective_cache_size
- 4. work_mem
- 5. max_wal_size
- 6. checkpoint_completion_target
- 7. synchronous_commit
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.
1 max_connections
Aria Config requires approximately 50-60 connections for the Aria Config processes themselves and an additional ~10 connections per master. The default 100 connections is usually okay. But consider adding an additional 10 connections per salt-master. 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 Aria Config log (/var/log/raas/raas) indicating that the DB is out of connections can also be an indication that this value should be raised.
2 shared_buffers
Rule of thumb value is 25% of system memory. For a 16 GB machine this would be 4GB or
shared_buffers = 4096MB
3 effective_cache_size
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
4 work_mem
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 Aria Config.
work_mem = 16MB
5 max_wal_size
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
6 checkpoint_completion_target
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
7 synchronous_commit
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. VMware 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.
