Well, it's not quite that simple
the rule of thumb is 6-10% of available memory before postgres loads is allocated to shared_buffers.
then effective cache is set to the SUM of shared_buffers + kernel buffers
Then you have to look at individual slow queries to determine why they are slow, fortunately you are running 7.4 so you can set log_min_duration to some number like 1000ms and then
try to analyze why those queries are slow.
Also hyperthreading may not be helping you..
Dave
Pallav Kalva wrote:
Hi ,
I am experiencing a very bad performance on my production database lately , all my queries are slowing down. Our application is a webbased system with lot of selects and updates. I am running "vacuumdb" daily on all the databases, are the below postgres configuration parameters are set properly ? can anyone take a look. Let me know if you need anymore information.
Postgres Version: 7.4 Operating System: Linux Red Hat 9 Cpus: 2 Hyperthreaded RAM: 4 gb Postgres Settings: max_fsm_pages | 20000 max_fsm_relations | 1000 shared_buffers | 65536 sort_mem | 16384 vacuum_mem | 32768 wal_buffers | 64 effective_cache_size | 393216
Thanks! Pallav
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
-- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings