Adjust Postgres memory settings to current best practice
After reviewing some advanced memory usage with a customer it was discovered that some of the default settings we have for Postgres memory are out of date.
Specifically the shared_buffers
setting, currently pinned to both the shmmax
value as well as having an upper limit of 14gb, is no longer required as of Postgres 9.3:
default['postgresql']['shmmax'] = /x86_64/.match?(node['kernel']['machine']) ? 17179869184 : 4294967295
default['postgresql']['shmall'] = /x86_64/.match?(node['kernel']['machine']) ? 4194304 : 1048575
default['postgresql']['shared_buffers'] = if (node['memory']['total'].to_i / 4) > ((node['postgresql']['shmmax'].to_i / 1024) - 2097152)
# guard against setting shared_buffers > shmmax on hosts with installed RAM > 64GB
# use 2GB less than shmmax as the default for these large memory machines
"14336MB"
else
"#{(node['memory']['total'].to_i / 4) / 1024}MB"
end
Today's best practice is to just set it to 1/4 of total available ram, which is import for larger systems.
There's also a suggestion we may not need to adjust shmmax
, shmall
and possibly other shm
postgres settings although this should be reviewed.