Quoting Stephane Tessier <stephane.tessier@abovesecurity.com>:
General parameter suggestions:
> shared_buffers = 128000 # min max_connections*2 or 16, 8KB each
> effective_cache_size = 196608 # typically 8KB each
Try reducing shared_buffers (say 30000). There has been much discussion regards
setting this parameter - most folks see minimal gains, or even performance
*loss* with settings higher than 30000.
> #sort_mem = 32168 # min 64, size in KB
Depending on what queries you are experiencing problems with, you might want to
set this parameter to something. However with 160 connections you need to be
careful, as each one uses this much memory (if it needs to sort).
> #wal_buffers = 8
Try this in the 100-1000 range
> #checkpoint_segments = 3
Try this quite a lot higher - say 10-50.
Pg_autovacuum:
I have not used this, so can't really give any help about setup. However you can
check how well it is working by selecting relname, reltuples, relpages out of
pg_class, and see if you have relations with stupidly low numbers of rows per
page (clearly you need to be ANALYZing reasonably frequently for this query to
give accurate information).
Other Comments:
To provide you with more help, we need to know what is slow. Particulary what
queries are the most troublesome (post EXPLAIN ANALYZE of them). You can
identify these by setting the log_statement and log_duration parameters.
Finally: (comment for your boss), it is *not* a given that ORACLE will perform
any better.....
regards
Mark
