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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
