From: "Carlo Stonebanks" <stonec.register@sympatico.ca>
Subject: Performance on 8CPU's  and 32GB of RAM
Date: Tue, 4 Sep 2007 18:45:24 -0400
Organization: Hub.Org Networking Services
Lines: 48
Message-ID: <fbkn5d$cfq$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

A client is moving their postgresql db to a brand new Windows 2003 x64 
server with 2 quad cores and 32GB of RAM. It is a dedicated server to run 
8.2.4.

The server typically will have less than 10 users. The primary use of this 
server is to host a database that is continuously being updated by data 
consolidation and matching software software that hits the server very hard. 
There are typically eight such processes running at any one time. The 
software extensively exploits postgresql native fuzzy string for data 
matching. The SQL is dynamically generated by the software and consists of 
large, complex joins. (the structure of the joins change as the software 
adapts its matching strategies).

I would like to favour the needs of the data matching software, and the 
server is almost exclusivly dedicated to PostgreSQL.

I have made some tentative modifications to the default postgres.config file 
(see below), but I don't think I've scratched the surface of what this new 
system is capable of. Can I ask - given my client's needs and this new, 
powerful server and the fact that the server typically has a small number of 
extremely busy processes, what numbers they would change, and what the 
recommendations would be?

Thanks!

Carlo

max_connections = 100
shared_buffers = 100000
work_mem = 1000000
max_fsm_pages = 204800
max_fsm_relations = 1500
vacuum_cost_delay = 40
bgwriter_lru_maxpages = 100
bgwriter_all_maxpages = 100
checkpoint_segments = 64
checkpoint_warning = 290
effective_cache_size = 375000
stats_command_string = on
stats_start_collector = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 250
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1