Обсуждение: postgresql.conf suggestions?
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 = 1000000 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
On Wed, May 20, 2009 at 12:22 PM, Kobby Dapaah <kdapaah@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 = 1000000 > 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
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 gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD