On Mon, Apr 4, 2011 at 3:40 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote: > 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
That's a little high.
> shared_buffers = 4096MB > work_mem = 64MB
That's way high. Work mem is PER SORT as well as PER CONNECTION. 1000 connections with 2 sorts each = 128,000MB.
There is nothing wrong here. You're using 153M out of 16G swap. 15.x Gig is shared buffers. If your system is slow, it's not because it's running out of memory or using too much swap.
> > I think there may be some problem in my Configuration parameters and change > it as :
Don't just guess and hope for the best. Examine your system to determine where it's having issues. Use vmstat 10 iostat -xd 10 top htop
and so on to see where your bottleneck is. CPU? Kernel wait? IO wait? etc.
log long running queries. Use pgfouine to examine your queries.
