Postgres Performance Tuning

From: Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Postgres Performance Tuning
Date: 2011-04-04 09:40:33
Message-ID: 4D999211.9080507@orkash.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Dear all,

I have a Postgres database server with 16GB RAM.
Our application runs by making connections to Postgres Server from
different servers and selecting data from one table & insert into
remaining tables in a database.

Below is the no. of connections output :-

postgres=# select datname,numbackends from pg_stat_database;
datname | numbackends
-------------------+-------------
template1 | 0
template0 | 0
postgres | 3
template_postgis | 0
pdc_uima_dummy | 107
pdc_uima_version3 | 1
pdc_uima_olap | 0
pdc_uima_s9 | 3
pdc_uima | 1
(9 rows)

I am totally confused for setting configuration parameters in Postgres
Parameters :-

First of all, I research on some tuning parameters and set mu
postgresql.conf as:-

max_connections = 1000
shared_buffers = 4096MB
temp_buffers = 16MB
work_mem = 64MB
maintenance_work_mem = 128MB
wal_buffers = 32MB
checkpoint_segments = 3
random_page_cost = 2.0
effective_cache_size = 8192MB

Then I got some problems from Application Users that the Postgres Slows
down and free commands output is :-

[root(at)s8-mysd-2 ~]# free -g
total used free shared buffers cached
Mem: 15 15 0 0 0 14
-/+ buffers/cache: 0 14
Swap: 16 0 15
[root(at)s8-mysd-2 ~]# free
total used free shared buffers cached
Mem: 16299476 16202264 97212 0 58924 15231852
-/+ buffers/cache: 911488 15387988
Swap: 16787884 153136 16634748

I think there may be some problem in my Configuration parameters and
change it as :

max_connections = 700
shared_buffers = 4096MB
temp_buffers = 16MB
work_mem = 64MB
maintenance_work_mem = 128MB
wal_buffers = 32MB
checkpoint_segments = 32
random_page_cost = 2.0
effective_cache_size = 4096MB

but Still Postgres Server uses Swap Memory While SELECT & INSERT into
database tables.

Please check the attached postgresql.conf .

And also have some views on how to tune this server.

DO I need to Increase my RAM s.t I hit H/W limitation.

Thanks & best Regards,
Adarsh Sharma

Attachment Content-Type Size
postgresql.conf text/plain 16.5 KB

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message tv 2011-04-04 10:28:06 Re: Postgres Performance Tuning
Previous Message Lars Feistner 2011-04-04 08:11:41 Re: very long updates very small tables