On Mon, Jan 11, 2010 at 9:07 AM, A. Kretschmer <andreas.kretschmer@xxxxxxxxxxxxxx> wrote:
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.
In response to Bob Dusek :
> Hello,Have you tuned your postgresql.conf? (memory-parameter)
>
> 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?
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
