shared_buffers
Akin to Oracle's db_buffer_cache.
Common rule of thumb for
shared_buffers is to set it to 25% of system memory. Larger values may require a corresponding increase in
checkpoint_segments and/or
max_wal_size to spread writing larger quantities of changed data over a longer period of time.
If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system. There are some workloads where even large settings for shared_buffers are effective, but because PostgreSQL also relies on the operating system cache, it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount. Larger settings for shared_buffers usually require a corresponding increase in max_wal_size, in order to spread out the process of writing large quantities of new or changed data over a longer period of time.
Increasing this parameter might cause Advanced Server to request more System V shared memory than your operating system's default configuration allows. See “
Shared Memory and Semaphores” in the PostgreSQL Core Documentation for information on how to adjust those parameters, if necessary.
huge_pages
Once on PostgreSQL >= 9.4, we should be using linux HugePages to lock shared memory and save memory on PageTable overhead.
- set
huge_pages to try (default) or on (to force it)
work_mem
Akin to Oracle's PGA.
This is the amount of memory allocated to each session for sort, hash and join operations. Increasing this value can dramatically improve the performance of certain queries.
If this is set to a high level and many sessions connect, it could cause the system to begin swapping, leading to a significant performance drop-off. Over the past week, the number of simultaneous sessions is usually between 7 and 12, but will occasionally spike up to around 20 for short periods. Most of the sessions have a short (< 1s) duration, but non-trivial amount can also go to 30s. We have very few that extend beyond that.
maintenance_work_mem
This is the memory reserved for certain maintenance operations, most notably VACUUM. Increasing this allows
these operations to complete more work in one pass, rather than having the pass over tables multiple times to
process rows. This memory is not reserved, so it is only taken up as needed by the session running the work. It
just defines an upper bound.
One rule of thumb that I've seen in a few places is to use 5% of system memory, but not more than 512MB.
checkpoint_timeout
Quoth Robert Haas:
"Increasing checkpoint_timeout from 5 minutes (the default) to a larger value, such as 15 minutes, can reduce the I/O load on your system, especially when using large values for shared_buffers. The downside of making these adjustments is that your system will use a modest amount of additional disk space, and will take longer to recover in the event of a crash. However, for most users, this is a small price to pay for a significant performance improvement."
effective_cache_size
Parameter to hint the query planner for estimating caching effects. No actual memory change are done.
Setting effective_cache_size to 1/2 of total memory would be a normal conservative setting, and 3/4 of memory is a more aggressive but still reasonable amount. You might find a better estimate by looking at your operating system's statistics. On UNIX-like systems, add the free+cached numbers from free or top to get an estimate.Again, Robert Haas:
"Despite being measured in megabytes, this parameter does not allocate any memory. Instead, it is used by the query planner to estimate certain caching effects. When this parameter is set too low, the planner may decide not to use an index even when it would be beneficial to do so. An appropriate value is approximately 75% of physical memory."
Quoting EDB:
"If this parameter is set too low, the planner may decide not to use an index even when it would be beneficial to do so. Setting effective_cache_size to 50% of physical memory is a normal, conservative setting. A more aggressive setting would be approximately 75% of physical memory."
wal_buffers
This value defaults to 1/32 (roughly 3%) of shared_buffers. One recommendation is to go with at least 16MB so you get at least one full WAL file in there and possibly reduce fsync calls. Or set it back to -1 and let it auto-tune.
Increasing this parameter might cause Advanced Server to request more System V shared memory than your operating system's default configuration allows.