Re: good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9

On Thu, Nov 11, 2010 at 2:59 AM, AI Rumman <rummandba(at)gmail(dot)com> wrote:
> Server Specification:
>   dual-core 4 cpu
>   RAM: 32 GB
>   OS: Centos
> What will be good settings for DB parameters such as shared_buffers,
> checkpoint_segment and etc.

I'll take this one ... :) On my 24GB quad-core Opteron servers
running FreeBSD 8.1, with big external fibre connected RAID array, I
use the following changes relative to the default 9.0.1
postgresql.conf. You probably don't need to adjust the prepared
transactions setting, unless you use them :-)

The default config is pretty darned good, compared to what used to
ship with older releases like 8.1 :)

listen_addresses = '*'
max_connections = 200
shared_buffers = 4200MB
max_prepared_transactions = 100 # guideline: same number as max_connections
work_mem = 512MB
maintenance_work_mem = 1024MB
vacuum_cost_delay = 15
checkpoint_segments = 64
checkpoint_timeout = 15min
checkpoint_completion_target = 0.8
random_page_cost = 1.3 # fast disk with big buffer
effective_cache_size = 6400MB # shared_buffers + `sysctl -n
vfs.hibufspace` / 8192 (BLKSZ)
log_checkpoints = on
update_process_title = on
log_autovacuum_min_duration = 0