Configuring PostgreSQL to perform well on big machines with gobs of resources is a bit of a challenge. Here are the relevant parts of our own postgresql.conf hoping someone may find it useful.
Relevant hardware specs: 32-core machine, Ubuntu 10.10 64-bit server, 64 GB of RAM, two drive arrays (one with eight and one with 15 HDDs). PostgreSQL 8.4.8. The actual data lives on the second (larger but slower) array, the WAL lives on the first (smaller but faster) array.
Whatever is not listed here sticks to the PostgreSQL defaults.
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 16GB # min 128kB
# (change requires restart)
temp_buffers = 512MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
work_mem = 256MB # min 64kB
maintenance_work_mem = 1GB # min 1MB
max_stack_depth = 7680 # min 100kB
# - Asynchronous Behavior -
effective_io_concurrency = 16 # 1-1000. 0 disables prefetching
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
#fsync = on # turns forced synchronization on or off
#synchronous_commit = on # immediate fsync at commit
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
wal_buffers = 256MB # min 32kB
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
checkpoint_segments = 64 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
These settings leverage our machine much better than the defaults, which are ~ 10 years old and meant for small, slow machines. YMMV.
Advertisement
