Re: Postgres Performance Tuning

My database size is :-
postgres=# select pg_size_pretty(pg_database_size('pdc_uima_dummy'));
pg_size_pretty
----------------
49 GB
(1 row)

I have a doubt regarding postgres Memory Usage :-

Say my Application makes Connection to Database Server ( *.*.*.106) from
(*.*.*.111, *.*.*.113, *.*.*.114) Servers and I check the top command as
:-- Say it makes 100 Connections

top - 17:01:02 up 5:39, 4 users, load average: 0.00, 0.00, 0.00
Tasks: 170 total, 1 running, 169 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 0.2% sy, 0.0% ni, 99.6% id, 0.1% wa, 0.0% hi,
0.0% si, 0.0% st
Mem: 16299476k total, 16198784k used, 100692k free, 73776k buffers
Swap: 16787884k total, 148176k used, 16639708k free, 15585396k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
COMMAND

3401 postgres 20 0 4288m 3.3g 3.3g S 0 21.1 0:24.73
postgres

3397 postgres 20 0 4286m 119m 119m S 0 0.8 0:00.36
postgres

4083 postgres 20 0 4303m 104m 101m S 0 0.7 0:07.68
postgres

3402 postgres 20 0 4288m 33m 32m S 0 0.2 0:03.67
postgres

4082 postgres 20 0 4301m 27m 25m S 0 0.2 0:00.85
postgres

4748 postgres 20 0 4290m 5160 3700 S 0 0.0 0:00.00
postgres

4173 root 20 0 12340 3028 1280 S 0 0.0 0:00.12
bash

4084 postgres 20 0 4290m 2952 1736 S 0 0.0 0:00.00
postgres

4612 root 20 0 12340 2920 1276 S 0 0.0 0:00.06
bash

4681 root 20 0 12340 2920 1276 S 0 0.0 0:00.05
bash

4550 root 20 0 12208 2884 1260 S 0 0.0 0:00.08
bash

4547 root 20 0 63580 2780 2204 S

and free command says :--
[root(at)s8-mysd-2 8.4SS]# free -g
total used free shared buffers cached
Mem: 15 15 0 0 0 14
-/+ buffers/cache: 0 15
Swap: 16 0 15
[root(at)s8-mysd-2 8.4SS]#

Now when my job finishes and I close the Connections from 2 Servers ,
the top & free output remains the same :-

I don't know What is the reason behind this as I have only 3 Connections
from the below command :

postgres=# select datname, client_addr,current_query from pg_stat_activity;
datname | client_addr |
current_query
----------------+---------------+------------------------------------------------------------------
postgres | | select datname,
client_addr,current_query from pg_stat_activity;
postgres | 192.168.0.208 | <IDLE>
pdc_uima_s9 | 192.168.0.208 | <IDLE>
pdc_uima_s9 | 192.168.0.208 | <IDLE>
pdc_uima_dummy | 192.168.0.208 | <IDLE>
pdc_uima_dummy | 192.168.1.102 | <IDLE>
pdc_uima_dummy | 192.168.1.102 | <IDLE>
pdc_uima_dummy | 192.168.1.102 | <IDLE>
(8 rows)

PLease help me to understand how much memory does 1 Connection Uses and
how to use Server parameters accordingly.

Thanks & best Regards,
Adarsh Sharma

Raghavendra wrote:
> Adarsh,
>
> What is the Size of Database?
>
> Best Regards,
> Raghavendra
> EnterpriseDB Corporation
>
> On Mon, Apr 4, 2011 at 4:24 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com
> <mailto:scott(dot)marlowe(at)gmail(dot)com>> wrote:
>
> On Mon, Apr 4, 2011 at 4:43 AM, Scott Marlowe
> <scott(dot)marlowe(at)gmail(dot)com <mailto:scott(dot)marlowe(at)gmail(dot)com>> wrote:
> >
> >> [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
> >
> > 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.
>
> Sorry that's 15.xG is system cache, not shared buffers. Anyway, still
> not a problem.
>
> --
> Sent via pgsql-performance mailing list
> (pgsql-performance(at)postgresql(dot)org
> <mailto:pgsql-performance(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>