Re: performance config help

On Mon, Jan 11, 2010 at 9:07 AM, A. Kretschmer <
andreas(dot)kretschmer(at)schollglas(dot)com> wrote:

> In response to Bob Dusek :
> > Hello,
> >
> > We're running Postgres 8.4.2 on Red Hat 5, on pretty hefty hardware...
> >
> > 4X E7420 Xeon, Four cores (for a total of 16 cores)
> > 2.13 GHz, 8M Cache, 1066 Mhz FSB
> > 32 Gigs of RAM
> > 15 K RPM drives in striped raid
> >
> > Things run fine, but when we get a lot of concurrent queries running, we
> see a
> > pretty good slow down.
> >
> > We don't have much experience with this sort of hardware. Does anyone
> have an
> > example config file we could use as a good starting point for this sort
> of
> > hardware?
>
> Have you tuned your postgresql.conf? (memory-parameter)
>
>
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.

> Here are some links for you:
>
> 15:07 < akretschmer> ??performance
> 15:07 < rtfm_please> For information about performance
> 15:07 < rtfm_please> see
> http://revsys.com/writings/postgresql-performance.html
> 15:07 < rtfm_please> or
> http://wiki.postgresql.org/wiki/Performance_Optimization
> 15:07 < rtfm_please> or
> http://www.depesz.com/index.php/2007/07/05/how-to-insert-data-to-database-as-fast-as-possible/
>

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.

>
>
> HTH, Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>