Re: performance config help

Here's a list of what I consider to be key changes we've made to the config file (from default)..

for comparison purposes, the diff command was "diff postgresql.conf.dist postgresql.conf.mod"
64c64
< max_connections = 100            # (change requires restart)
---
> max_connections = 300            # (change requires restart)
78c78
< ssl = true                # (change requires restart)
---
> #ssl = off                # (change requires restart)
106c106,107
< shared_buffers = 32MB            # min 128kB
---
> #shared_buffers = 32MB            # min 128kB
> shared_buffers = 8GB            # min 128kB (rdk)
115a117
> work_mem = 64MB                # min 64kB (vrk) (rdk)
117c119,121
< #max_stack_depth = 2MB            # min 100kB
---
> maintenance_work_mem = 2GB        # min 1MB (rdk)
> #max_stack_depth = 1MB            # min 100kB
> max_stack_depth = 9MB            # min 100kB (vrk)
127a132
> vacuum_cost_delay = 15ms        # 0-100 milliseconds (rdk)
150c155
< #fsync = on                # turns forced synchronization on or off
---
> fsync = off                # turns forced synchronization on or off (rdk)

Please note, I've been reading this list a bit lately, and I'm aware of the kind of advice that some offer with respect to fsync.  I understand that with 8.4 we can turn this on and shut off "synchronous_commit".  I would be interested in more information on that.  But the bottom line is that we've gotten in the habit of shutting this off (on production servers) using Postgres 7.4, as the performance gain is enormous, and with fsync=on, we couldn't get the performance we needed. 

151a157
> synchronous_commit = off        # immediate fsync at commit
152a159
> wal_sync_method = open_sync        # the default is the first option (vrk)
159c166
< #full_page_writes = on            # recover from partial page writes
---
> full_page_writes = off            # recover from partial page writes (rdk)
160a168
> wal_buffers = 8MB            # min 32kB (rdk)
164c172
< #commit_delay = 0            # range 0-100000, in microseconds
---
> commit_delay = 10            # range 0-100000, in microseconds (vrk)
169a178
> checkpoint_segments = 256        # in logfile segments, min 1, 16MB each (rdk)
170a180
> checkpoint_timeout = 15min        # range 30s-1h (rdk)
171a182
> checkpoint_completion_target = 0.7    # checkpoint target duration, 0.0 - 1.0 (rdk)
206a218
> effective_cache_size = 24GB # (rdk)

I would be willing to send our entire config file to someone if that would help... I didn't want to attach it to this email, because I'm not sure about the etiquette of attaching files to emails on this list.  

 

We will spend more time on this and see if we can learn more. 

But, we're hoping someone on this list can offer us some quick tips to help us use up more of the 16 cpus we have available. 

Thanks for pointing all of that out.