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.
> [root@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
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.