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:
1 2 3 4 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
# 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 |