Re: Postgres Performance Tuning - Mailing list pgsql-performance

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@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@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.marlowe@gmail.com> wrote:

On Mon, Apr 4, 2011 at 4:43 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
>> [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.

Sorry that's 15.xG is system cache, not shared buffers.  Anyway, still
not a problem.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance