PostgreSQL + Chef – Automatically configure based on server RAM

I was sick of changing shared_buffers, effective_cache_size, maintenance_work_mem, work_mem, SHMMAX, SHMALL each time we’d spin up a new server. So I decided instead to have chef calculate it for me! It takes the available RAM of the server and then calculates each value based on:

shared_buffers = 25% of maximum memory
maintenance_work_mem = 10% of maximum memory
work_mem = 75% of maximum memory/max # of connections
effective_cache_size = 75% of maximum memory

The recipe:

# SHMMAX in bytes
def shmmax
  (node['memory']['total'][0..-3].to_i * 1024)
end

# SHMMAX in pages
def shmall
  pages = (node['memory']['total'][0..-3].to_i * 1024) / 4096
  if pages < 2097152
    2097152
  else
    pages
  end
end

node.override["postgresql"]["max_connections"]                 = 250
node.override["postgresql"]["shared_buffers"]                  = ((node['memory']['total'][0..-3].to_i / 1024).to_f * 0.25).to_i.to_s + "MB"
node.override["postgresql"]["maintenance_work_mem"]            = ((node['memory']['total'][0..-3].to_i / 1024).to_f * 0.10).to_i.to_s + "MB"
node.override["postgresql"]["work_mem"]                        = (((node['memory']['total'][0..-3].to_i / 1024).to_f * 0.75)/node["postgresql"]["max_connections"].to_f).to_i.to_s + "MB"
node.override["postgresql"]["effective_cache_size"]            = ((node['memory']['total'][0..-3].to_i / 1024).to_f * 0.75).to_i.to_s + "MB"

node.override["postgresql"]["shmall"] = shmall
node.override["postgresql"]["shmmax"] = shmmax

bash "add shm settings" do
  user "root"
  code <<-EOF
    echo 'kernel.shmmax = #{node['postgresql']['shmmax']}' >> /etc/sysctl.conf
    echo 'kernel.shmall = #{node['postgresql']['shmall']}' >> /etc/sysctl.conf
    /sbin/sysctl -p /etc/sysctl.conf
  EOF
  not_if "egrep '^kernel.shmmax = ' /etc/sysctl.conf"
end