Quick note on Tuning Postrgres
A while ago I wrote this in the online doc for postgres after getting some help on IRC. I Had trouble finding my own comments again but then doing a vanity search research I found the comments – putting them here so it is easier for me to find.
I would love to see more hints or other peoples experience. Postgresql tuning is one of the least explained pieces of Postgresql.
So without further ado – here are some notes for tuning Postgres when your machine has more than 4 gigs of RAM (which is most servers now).
From AndrewSN on the IRC Channel here is the config setting for a Unix like machine with RAM >= 4g
shared_buffers = 5% to 10% of physical RAM
effective_cache_size = 50%-75% of physical RAM (unless you’re using 8.1 or older, in which case 75%-100% of physical RAM)
wal_buffers = 8M
maintenance_work_mem = 384M (or so, there’s not usually much to gain from going higher)
work_mem = this one is harder, since it depends a lot more on the query workload than the others; 16M to 64M might be a reasonable starting point
the more concurrent connections you have doing complex queries, the smaller that needs to be
max_fsm_pages is determined more by the on-disk size of your db than the size of the server. estimate or measure the on-disk db size, divide by 16k, and use that as a starting point
if you use temp tables a lot, a modest increase in temp_buffers may help if you have a lot of RAM.
checkpoint_segments = 32 ?
checkpoint_segments is worth increasing if you have a heavy write load and don’t mind a lot of disk space used for pg_xlog. It does slow down recovery after a crash or unclean shutdown.
I personally increase default_statistics_target to 100 by default regardless of the size of the server
