Re: [PERFORM] postgresql.conf suggestions?

[Robert Haas]

On Wed, May 20, 2009 at 12:22 PM, Kobby Dapaah kdap...@gmail.com wrote:
 I just upgraded from a

 2xIntel Xeon-Harpertown 5450-Quadcore,16 GB,Redhat EL 5.1-64
 To
 2xIntel Xeon-Nehalem 5570-Quadcore,36GB,Redhat EL 5.3-64

 Any advice on how I'll get the best of this server?

 This is what I currently have:
 max_connections = 100
 shared_buffers = 2048MB
 maintenance_work_mem = 128MB
 max_fsm_pages = 100
 bgwriter_delay = 10ms
 bgwriter_lru_maxpages = 0
 wal_sync_method = open_sync
 wal_buffers = 2MB
 checkpoint_segments = 64
 checkpoint_warning = 270s
 archive_mode = on
 effective_cache_size = 5400MB
 default_statistics_target = 500
 logging_collector = on
 log_rotation_size = 100MB
 log_min_duration_statement = 1000
 log_connections = off
 log_disconnections = off
 log_duration = off
 log_line_prefix = '%t'
 track_counts = on
 autovacuum = on
 statement_timeout = 5min

What are you doing with it?  You probably want to increase work_mem,
and very likely you want to decrease random_page_cost and
seq_page_cost.  I think there may be some more bgwriter settings that
you want to fiddle as well but that's not my area of expertise.

...Robert

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] postgresql.conf suggestions?

[Greg Smith]

On Wed, 20 May 2009, Kobby Dapaah wrote:


shared_buffers = 2048MB
effective_cache_size = 5400MB


You should consider seriously increasing effective_cache_size.  You might 
also double or quadruple shared_buffers from 2GB, but going much higher 
may not buy you much--most people seem to find diminishing returns 
increasing that beyond the 10GB range.



bgwriter_delay = 10ms
bgwriter_lru_maxpages = 0


I found that bg_writer_delay doesn't really work so well when set to this 
small.  It doesn't actually matter right now though, because you're 
turning the background writer off by setting brwriter_lru_maxpages=0. 
Just wanted to point this out because if you increase that later it may 
pop up as a concern, small values for the delay make it more likely you'll 
need to increase the multiplier to a higher value for background writing 
to work well.


Robert's message already mentions the other things you should consider, 
particularly work_mem.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance