From: "Carlo Stonebanks" <stonec.register@sympatico.ca>
Subject: Got that new server, now it's time for config!
Date: Mon, 22 Mar 2010 18:36:03 -0400
Organization: Hub.Org Networking Services
Lines: 70
Message-ID: <ho8r9s$2i4p$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
To: pgsql-performance@postgresql.org

Here we go again!

Based on recommendations made here, I got my client to migrate off of our 
Windows 2003 Server x64 box to a new Linux box.

# CENTOS 5.4
# Linux mdx_octo 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 
x86_64 x86_64 GNU/Linux
# pgsql 8.3.10, 8 CPUs, 48GB RAM
# RAID 10, 4 Disks

Below are the config values of this production server (those not listed are 
those stubbed out) . Sadly, in an attempt to improve the server's 
performance, someone wiped out all of the changes I had made to date, along 
with comments indicating previous values, reason for the change, etc.

This is a data warehouse production server, used for ETL. 500 GB database, 
approx 8000 tables and growing, although the vast majority of them are the 
original import resource tables and are rarely accessed. The actual core 
data is about 200 tables, consisting of millions of rows. Data importing and 
content management is done via a 15,000 line TCL import scripts and 
application base (as this is ETL with fuzzy logic, not just COPY... FROM...) 
.

So, we have the hardware, we have the O/S - but I think our config leaves 
much to be desired. Typically, our planner makes nad decisions, picking seq 
scan over index scan, where index scan has a better result.

Can anyone see any obvious faults?

Carlo

autovacuum = on
autovacuum_analyze_scale_factor = 0.05
autovacuum_analyze_threshold = 1000
autovacuum_naptime = 1min
autovacuum_vacuum_cost_delay =  50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 1000
bgwriter_lru_maxpages = 100
checkpoint_segments = 128
checkpoint_warning = 290s
client_min_messages =  debug1
datestyle = 'iso, mdy'
default_statistics_target = 250
default_text_search_config = 'pg_catalog.english'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
listen_addresses = '*'
log_destination = 'stderr'
log_error_verbosity =  verbose
log_line_prefix = '%t '
log_min_error_statement =  debug1
log_min_messages = debug1
logging_collector = on
maintenance_work_mem = 256MB
max_connections = 100
max_fsm_relations = 1000
max_locks_per_transaction = 128
port = 5432
shared_buffers = 4096MB
shared_preload_libraries = '$libdir/plugins/plugin_debugger.so'
track_counts = on
vacuum_cost_delay = 5
wal_buffers = 4MB
wal_sync_method = open_sync
work_mem = 64MB