Re: Postgres Performance Tuning

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

> 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

First of all, there's no reason to increase wal_buffers above 32MB. AFAIK
the largest sensible value is 16MB - I doubt increasing it further will
improve performance.

Second - effective_cache_size is just a hint how much memory is used by
the operating system for filesystem cache. So this does not influence
amount of allocated memory in any way.

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

Are you sure it's PostgreSQL. What else is running on the box? Have you
analyzed why the SQL queries are slow (using EXPLAIN)?

regards
Tomas

tv(at)fuzzy(dot)cz wrote:
>> 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
>
> First of all, there's no reason to increase wal_buffers above 32MB. AFAIK
> the largest sensible value is 16MB - I doubt increasing it further will
> improve performance.
>
> Second - effective_cache_size is just a hint how much memory is used by
> the operating system for filesystem cache. So this does not influence
> amount of allocated memory in any way.
>
>> but Still Postgres Server uses Swap Memory While SELECT & INSERT into
>> database tables.
>
> Are you sure it's PostgreSQL. What else is running on the box? Have you
> analyzed why the SQL queries are slow (using EXPLAIN)?

Thanks , Below is my action points :-

max_connections = 300 ( I don't think that application uses more than
300 connections )
shared_buffers = 4096MB
temp_buffers = 16MB
work_mem = 64MB
maintenance_work_mem = 128MB
wal_buffers = 16MB ( As per U'r suggestions )
checkpoint_segments = 32
random_page_cost = 2.0
effective_cache_size = 8192MB ( Recommended 50% of RAM )

My Shared Memory Variables are as:-

[root(at)s8-mysd-2 ~]# cat /proc/sys/kernel/shmmax

6442450944

[root(at)s8-mysd-2 ~]# cat /proc/sys/kernel/shmall

6442450944

[root(at)s8-mysd-2 ~]

Please let me know if any parameter need some change.

As now I am going change my parameters as per the below link :-

http://airumman.blogspot.com/2011/03/postgresql-parameters-for-new-dedicated.html

But one thing I am not able to understand is :-

Start the server and find out how much memory is still available for the
OS filesystem cache

U'r absolutely right I am also researching on the explain of all select
statements and i find one reason of poor indexing on TEXT columns.

Thanks & best Regards,
Adarsh Sharma

>
> regards
> Tomas
>
>

On Mon, Apr 4, 2011 at 3:40 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)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(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.

>
> 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.

Also you can try to take the help of pgtune before hand.

pgfoundry.org/projects/*pgtune*/

On Mon, Apr 4, 2011 at 12:43 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Mon, Apr 4, 2011 at 3:40 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)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(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.
>
> >
> > 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.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

On Mon, Apr 4, 2011 at 4:43 AM, Scott Marlowe <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.

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>wrote:

> On Mon, Apr 4, 2011 at 4:43 AM, Scott Marlowe <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)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/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(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
>
>

On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> wrote:
> 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
> PLease help me to understand how much memory does 1 Connection Uses and how
> to use Server parameters accordingly.

OK, first, see the 15585396k cached? That's how much memory your OS
is using to cache file systems etc. Basically that's memory not being
used by anything else right now, so the OS borrows it and uses it for
caching.

Next, VIRT is how much memory your process would need to load every
lib it might need but may not be using now, plus all the shared memory
it might need, plus it's own space etc. It's not memory in use, it's
memory that might under the worst circumstances, be used by that one
process. RES is the amount of memory the process IS actually
touching, including shared memory that other processes may be sharing.
Finally, SHR is the amount of shared memory the process is touching.
so, taking your biggest process, it is linked to enough libraries and
shared memory and it's own private memory to add up to 4288Meg. It is
currently actually touching 3.3G. Of that 3.3G it is touching 3.3G is
shared with other processes. So, the difference between RES and SHR
is 0, so the delta, or extra memory it's using besides shared memory
is ZERO (or very close to it, probably dozens or fewer of megabytes).

So, you're NOT running out of memory. Remember when I mentioned
iostat, vmstat, etc up above? Have you run any of those?

Thanks Scott :

My iostat package is not installed but have a look on below output:

[root(at)s8-mysd-2 8.4SS]# vmstat 10
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy
id wa st
1 0 147664 93920 72332 15580748 0 1 113 170 47 177 6
1 92 1 0
0 0 147664 94020 72348 15580748 0 0 0 4 993 565 0
0 100 0 0
0 0 147664 93896 72364 15580748 0 0 0 5 993 571 0
0 100 0 0
0 0 147664 93524 72416 15580860 0 0 0 160 1015 591 0
0 100 0 0
0 0 147664 93524 72448 15580860 0 0 0 8 1019 553 0
0 100 0 0
0 0 147664 93648 72448 15580860 0 0 0 0 1019 555 0
0 100 0 0
0 0 147664 93648 72448 15580860 0 0 0 3 1023 560 0
0 100 0 0

[root(at)s8-mysd-2 8.4SS]# iostat
-bash: iostat: command not found
[root(at)s8-mysd-2 8.4SS]#

Best regards,
Adarsh

Scott Marlowe wrote:
> On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> wrote:
>
>> 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
>> PLease help me to understand how much memory does 1 Connection Uses and how
>> to use Server parameters accordingly.
>>
>
> OK, first, see the 15585396k cached? That's how much memory your OS
> is using to cache file systems etc. Basically that's memory not being
> used by anything else right now, so the OS borrows it and uses it for
> caching.
>
> Next, VIRT is how much memory your process would need to load every
> lib it might need but may not be using now, plus all the shared memory
> it might need, plus it's own space etc. It's not memory in use, it's
> memory that might under the worst circumstances, be used by that one
> process. RES is the amount of memory the process IS actually
> touching, including shared memory that other processes may be sharing.
> Finally, SHR is the amount of shared memory the process is touching.
> so, taking your biggest process, it is linked to enough libraries and
> shared memory and it's own private memory to add up to 4288Meg. It is
> currently actually touching 3.3G. Of that 3.3G it is touching 3.3G is
> shared with other processes. So, the difference between RES and SHR
> is 0, so the delta, or extra memory it's using besides shared memory
> is ZERO (or very close to it, probably dozens or fewer of megabytes).
>
> So, you're NOT running out of memory. Remember when I mentioned
> iostat, vmstat, etc up above? Have you run any of those?
>

You got to have something to compare against.
I would say, try to run some benchmarks (pgbench from contrib) and compare them
against a known good instance of postgresql, if you have access in such a machine.

That said, and forgive me if i sound a little "explicit" but if you dont know how to install iostat
then there are few chances that you understand unix/linux/bsd concepts properly
and therefore any efforts to just speed up postgresql in such an environment , at this point,
will not have the desired effect, because even if you manage to solve smth now,
tommorow you will still be in confusion about smth else that might arise.
So, i suggest:
1) try to get an understanding on how your favorite distribution works (read any relevant info, net, books, etc..)
2) Go and get the book "PostgreSQL 9.0 High Performance" by Greg Smith. It is a very good book
not only about postgresql but about the current state of systems performance as well.

Στις Monday 04 April 2011 14:51:13 ο/η Adarsh Sharma έγραψε:
>
> Thanks Scott :
>
> My iostat package is not installed but have a look on below output:
>
> [root(at)s8-mysd-2 8.4SS]# vmstat 10
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
> r b swpd free buff cache si so bi bo in cs us sy
> id wa st
> 1 0 147664 93920 72332 15580748 0 1 113 170 47 177 6
> 1 92 1 0
> 0 0 147664 94020 72348 15580748 0 0 0 4 993 565 0
> 0 100 0 0
> 0 0 147664 93896 72364 15580748 0 0 0 5 993 571 0
> 0 100 0 0
> 0 0 147664 93524 72416 15580860 0 0 0 160 1015 591 0
> 0 100 0 0
> 0 0 147664 93524 72448 15580860 0 0 0 8 1019 553 0
> 0 100 0 0
> 0 0 147664 93648 72448 15580860 0 0 0 0 1019 555 0
> 0 100 0 0
> 0 0 147664 93648 72448 15580860 0 0 0 3 1023 560 0
> 0 100 0 0
>
> [root(at)s8-mysd-2 8.4SS]# iostat
> -bash: iostat: command not found
> [root(at)s8-mysd-2 8.4SS]#
>
> Best regards,
> Adarsh
>
> Scott Marlowe wrote:
> > On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> wrote:
> >
> >> 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
> >> PLease help me to understand how much memory does 1 Connection Uses and how
> >> to use Server parameters accordingly.
> >>
> >
> > OK, first, see the 15585396k cached? That's how much memory your OS
> > is using to cache file systems etc. Basically that's memory not being
> > used by anything else right now, so the OS borrows it and uses it for
> > caching.
> >
> > Next, VIRT is how much memory your process would need to load every
> > lib it might need but may not be using now, plus all the shared memory
> > it might need, plus it's own space etc. It's not memory in use, it's
> > memory that might under the worst circumstances, be used by that one
> > process. RES is the amount of memory the process IS actually
> > touching, including shared memory that other processes may be sharing.
> > Finally, SHR is the amount of shared memory the process is touching.
> > so, taking your biggest process, it is linked to enough libraries and
> > shared memory and it's own private memory to add up to 4288Meg. It is
> > currently actually touching 3.3G. Of that 3.3G it is touching 3.3G is
> > shared with other processes. So, the difference between RES and SHR
> > is 0, so the delta, or extra memory it's using besides shared memory
> > is ZERO (or very close to it, probably dozens or fewer of megabytes).
> >
> > So, you're NOT running out of memory. Remember when I mentioned
> > iostat, vmstat, etc up above? Have you run any of those?
> >
>
>

--
Achilleas Mantzios

On Mon, Apr 4, 2011 at 5:51 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> wrote:
>
>
> Thanks Scott :
>
> My iostat package is not installed but have a look on below output:
>
> [root(at)s8-mysd-2 8.4SS]# vmstat 10
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id
> wa st
>  1  0 147664  93920  72332 15580748    0    1   113   170   47   177  6  1
> 92  1  0
>  0  0 147664  94020  72348 15580748    0    0     0     4  993   565  0  0
> 100  0  0
>  0  0 147664  93896  72364 15580748    0    0     0     5  993   571  0  0
> 100  0  0
>  0  0 147664  93524  72416 15580860    0    0     0   160 1015   591  0  0
> 100  0  0
>  0  0 147664  93524  72448 15580860    0    0     0     8 1019   553  0  0
> 100  0  0
>  0  0 147664  93648  72448 15580860    0    0     0     0 1019   555  0  0
> 100  0  0
>  0  0 147664  93648  72448 15580860    0    0     0     3 1023   560  0  0
> 100  0  0

OK, right now your machine is at idle. Run vmstat / iostat when it's
under load. If the wa column stays low, then you're not IO bound but
more than likely CPU bound.

>
>
> Thanks Scott :
>
> My iostat package is not installed but have a look on below output:
>
> [root(at)s8-mysd-2 8.4SS]# vmstat 10
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
> r b swpd free buff cache si so bi bo in cs us sy
> id wa st
> 1 0 147664 93920 72332 15580748 0 1 113 170 47 177 6
> 1 92 1 0
> 0 0 147664 94020 72348 15580748 0 0 0 4 993 565 0
> 0 100 0 0
> 0 0 147664 93896 72364 15580748 0 0 0 5 993 571 0
> 0 100 0 0
> 0 0 147664 93524 72416 15580860 0 0 0 160 1015 591 0
> 0 100 0 0
> 0 0 147664 93524 72448 15580860 0 0 0 8 1019 553 0
> 0 100 0 0
> 0 0 147664 93648 72448 15580860 0 0 0 0 1019 555 0
> 0 100 0 0
> 0 0 147664 93648 72448 15580860 0 0 0 3 1023 560 0
> 0 100 0 0

Is this from a busy or idle period? I guess it's from an idle one, because
the CPU is 100% idle and there's very little I/O activity. That's useless
- we need to see vmstat output from period when there's something wrong.

> [root(at)s8-mysd-2 8.4SS]# iostat
> -bash: iostat: command not found
> [root(at)s8-mysd-2 8.4SS]#

Then install it. Not sure what distro you use, but it's usually packed in
sysstat package.

Tomas

Adarsh,

> [root(at)s8-mysd-2 8.4SS]# iostat
> -bash: iostat: command not found
>
> /usr/bin/iostat

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.

When you are doing bulk inserts you need to tune AUTOVACUUM parameters or
Change the autovacuum settings for those tables doing bulk INSERTs. Insert's
need analyze.

> #autovacuum = on # Enable autovacuum subprocess?
> 'on'
> # requires track_counts to also be
> on.
> #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions
> and
> # their durations, > 0 logs only
> # actions running at least this
> number
> # of milliseconds.
> #autovacuum_max_workers = 3 # max number of autovacuum
> subprocesses
> #autovacuum_naptime = 1min # time between autovacuum runs
> #autovacuum_vacuum_threshold = 50 # min number of row updates before
> # vacuum
> #autovacuum_analyze_threshold = 50 # min number of row updates before
> # analyze
> #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before
> vacuum
> #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
> analyze
> #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced
> vacuum
> # (change requires restart)
> #autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
> # autovacuum, in milliseconds;
> # -1 means use vacuum_cost_delay
> #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
> # autovacuum, -1 means use
> # vacuum_cost_limit

These are all default AUTOVACUUM settings. If you are using PG 8.4 or above,
try AUTOVACUUM settings on bulk insert tables for better performance. Also
need to tune the 'autovacuum_naptime'

Eg:-
ALTER table <table name> SET (autovacuum_vacuum_threshold=xxxxx,
autovacuum_analyze_threshold=xxxx);

wal_buffers //max is 16MB
checkpoint_segment /// Its very less in your setting
checkpoint_timeout
temp_buffer // If application is using temp tables

These parameter will also boost the performance.

Best Regards
Raghavendra
EnterpriseDB Corporation.

> Scott Marlowe wrote:
>
> On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> <adarsh(dot)sharma(at)orkash(dot)com> wrote:
>
>
> 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
> PLease help me to understand how much memory does 1 Connection Uses and how
> to use Server parameters accordingly.
>
>
> OK, first, see the 15585396k cached? That's how much memory your OS
> is using to cache file systems etc. Basically that's memory not being
> used by anything else right now, so the OS borrows it and uses it for
> caching.
>
> Next, VIRT is how much memory your process would need to load every
> lib it might need but may not be using now, plus all the shared memory
> it might need, plus it's own space etc. It's not memory in use, it's
> memory that might under the worst circumstances, be used by that one
> process. RES is the amount of memory the process IS actually
> touching, including shared memory that other processes may be sharing.
> Finally, SHR is the amount of shared memory the process is touching.
> so, taking your biggest process, it is linked to enough libraries and
> shared memory and it's own private memory to add up to 4288Meg. It is
> currently actually touching 3.3G. Of that 3.3G it is touching 3.3G is
> shared with other processes. So, the difference between RES and SHR
> is 0, so the delta, or extra memory it's using besides shared memory
> is ZERO (or very close to it, probably dozens or fewer of megabytes).
>
> So, you're NOT running out of memory. Remember when I mentioned
> iostat, vmstat, etc up above? Have you run any of those?
>
>
>
>

Thank U all,

I know some things to work on & after some work & study on them , I will
continue this discussion tomorrow .

Best Regards,
Adarsh

Raghavendra wrote:
> Adarsh,
>
>
> [root(at)s8-mysd-2 8.4SS]# iostat
> -bash: iostat: command not found
>
> /usr/bin/iostat
>
> 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.
>
>
> When you are doing bulk inserts you need to tune AUTOVACUUM parameters
> or Change the autovacuum settings for those tables doing bulk INSERTs.
> Insert's need analyze.
>
>
>
> #autovacuum = on # Enable autovacuum
> subprocess? 'on'
> # requires track_counts to
> also be on.
> #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all
> actions and
> # their durations, > 0 logs
> only
> # actions running at least
> this number
> # of milliseconds.
> #autovacuum_max_workers = 3 # max number of autovacuum
> subprocesses
> #autovacuum_naptime = 1min # time between autovacuum runs
> #autovacuum_vacuum_threshold = 50 # min number of row
> updates before
> # vacuum
> #autovacuum_analyze_threshold = 50 # min number of row
> updates before
> # analyze
> #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size
> before vacuum
> #autovacuum_analyze_scale_factor = 0.1 # fraction of table size
> before analyze
> #autovacuum_freeze_max_age = 200000000 # maximum XID age before
> forced vacuum
> # (change requires restart)
> #autovacuum_vacuum_cost_delay = 20ms # default vacuum cost
> delay for
> # autovacuum, in milliseconds;
> # -1 means use
> vacuum_cost_delay
> #autovacuum_vacuum_cost_limit = -1 # default vacuum cost
> limit for
> # autovacuum, -1 means use
> # vacuum_cost_limit
>
>
> These are all default AUTOVACUUM settings. If you are using PG 8.4 or
> above, try AUTOVACUUM settings on bulk insert tables for better
> performance. Also need to tune the 'autovacuum_naptime'
>
> Eg:-
> ALTER table <table name> SET (autovacuum_vacuum_threshold=xxxxx,
> autovacuum_analyze_threshold=xxxx);
>
> wal_buffers //max is 16MB
> checkpoint_segment /// Its very less in your setting
> checkpoint_timeout
> temp_buffer // If application is using temp tables
>
>
> These parameter will also boost the performance.
>
> Best Regards
> Raghavendra
> EnterpriseDB Corporation.
>
>
>
>
>
>
>
> Scott Marlowe wrote:
>> On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> <mailto:adarsh(dot)sharma(at)orkash(dot)com> wrote:
>>
>>> 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
>>> PLease help me to understand how much memory does 1 Connection Uses and how
>>> to use Server parameters accordingly.
>>>
>> OK, first, see the 15585396k cached? That's how much memory your OS
>> is using to cache file systems etc. Basically that's memory not being
>> used by anything else right now, so the OS borrows it and uses it for
>> caching.
>>
>> Next, VIRT is how much memory your process would need to load every
>> lib it might need but may not be using now, plus all the shared memory
>> it might need, plus it's own space etc. It's not memory in use, it's
>> memory that might under the worst circumstances, be used by that one
>> process. RES is the amount of memory the process IS actually
>> touching, including shared memory that other processes may be sharing.
>> Finally, SHR is the amount of shared memory the process is touching.
>> so, taking your biggest process, it is linked to enough libraries and
>> shared memory and it's own private memory to add up to 4288Meg. It is
>> currently actually touching 3.3G. Of that 3.3G it is touching 3.3G is
>> shared with other processes. So, the difference between RES and SHR
>> is 0, so the delta, or extra memory it's using besides shared memory
>> is ZERO (or very close to it, probably dozens or fewer of megabytes).
>>
>> So, you're NOT running out of memory. Remember when I mentioned
>> iostat, vmstat, etc up above? Have you run any of those?
>>
>
>

Best of luck, the two standard links for this kind of problem are:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
http://wiki.postgresql.org/wiki/SlowQueryQuestions

Note that in creating the information needed to report a problem you
may well wind up troubleshooting it and fixing it. That's a good
thing :)

Hi, Good Morning To All of You.

Yesterday I had some research on my problems. As Scott rightly suggest
me to have pre information before posting in the list, I aggreed to him.

Here is my first doubt , that I explain as:

My application makes several connections to Database Server & done their
work :

During this process have a look on below output of free command :

[root(at)s8-mysd-2 ~]# free -m
total used free shared buffers cached
Mem: 15917 15826 90 0 101 15013
-/+ buffers/cache: 711 15205
Swap: 16394 143 16250

It means 15 GB memory is cached.

[root(at)s8-mysd-2 ~]# cat /proc/meminfo
MemTotal: 16299476 kB
MemFree: 96268 kB
Buffers: 104388 kB
Cached: 15370008 kB
SwapCached: 3892 kB
Active: 6574788 kB
Inactive: 8951884 kB
Active(anon): 3909024 kB
Inactive(anon): 459720 kB
Active(file): 2665764 kB
Inactive(file): 8492164 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 16787884 kB
SwapFree: 16640472 kB
Dirty: 1068 kB
Writeback: 0 kB
AnonPages: 48864 kB
Mapped: 4277000 kB
Slab: 481960 kB
SReclaimable: 466544 kB
SUnreclaim: 15416 kB
PageTables: 57860 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 24904852 kB
Committed_AS: 5022172 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 310088 kB
VmallocChunk: 34359422091 kB
HugePages_Total: 32
HugePages_Free: 32
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 3776 kB
DirectMap2M: 16773120 kB
[root(at)s8-mysd-2 ~]#

Now Can I know why the cached memory is not freed after the connections
done their work & their is no more connections :

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

Same output is when it has 100 connections.

Now I have to start more queries on Database Server and issue new
connections after some time. Why the cached memory is not freed.

Flushing the cache memory is needed & how it could use so much if I set

effective_cache_size = 4096 MB.

I think if i issue some new select queries on large set of data, it will
use Swap Memory & degrades Performance.

Please correct if I'm wrong.

Thanks & best Regards,
Adarsh Sharma

Raghavendra wrote:
> Adarsh,
>
>
> [root(at)s8-mysd-2 8.4SS]# iostat
> -bash: iostat: command not found
>
> /usr/bin/iostat
>
> 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.
>
>
> When you are doing bulk inserts you need to tune AUTOVACUUM parameters
> or Change the autovacuum settings for those tables doing bulk INSERTs.
> Insert's need analyze.
>
>
>
> #autovacuum = on # Enable autovacuum
> subprocess? 'on'
> # requires track_counts to
> also be on.
> #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all
> actions and
> # their durations, > 0 logs
> only
> # actions running at least
> this number
> # of milliseconds.
> #autovacuum_max_workers = 3 # max number of autovacuum
> subprocesses
> #autovacuum_naptime = 1min # time between autovacuum runs
> #autovacuum_vacuum_threshold = 50 # min number of row
> updates before
> # vacuum
> #autovacuum_analyze_threshold = 50 # min number of row
> updates before
> # analyze
> #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size
> before vacuum
> #autovacuum_analyze_scale_factor = 0.1 # fraction of table size
> before analyze
> #autovacuum_freeze_max_age = 200000000 # maximum XID age before
> forced vacuum
> # (change requires restart)
> #autovacuum_vacuum_cost_delay = 20ms # default vacuum cost
> delay for
> # autovacuum, in milliseconds;
> # -1 means use
> vacuum_cost_delay
> #autovacuum_vacuum_cost_limit = -1 # default vacuum cost
> limit for
> # autovacuum, -1 means use
> # vacuum_cost_limit
>
>
> These are all default AUTOVACUUM settings. If you are using PG 8.4 or
> above, try AUTOVACUUM settings on bulk insert tables for better
> performance. Also need to tune the 'autovacuum_naptime'
>
> Eg:-
> ALTER table <table name> SET (autovacuum_vacuum_threshold=xxxxx,
> autovacuum_analyze_threshold=xxxx);
>
> wal_buffers //max is 16MB
> checkpoint_segment /// Its very less in your setting
> checkpoint_timeout
> temp_buffer // If application is using temp tables
>
>
> These parameter will also boost the performance.
>
> Best Regards
> Raghavendra
> EnterpriseDB Corporation.
>
>
>
>
>
>
>
> Scott Marlowe wrote:
>> On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> <mailto:adarsh(dot)sharma(at)orkash(dot)com> wrote:
>>
>>> 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
>>> PLease help me to understand how much memory does 1 Connection Uses and how
>>> to use Server parameters accordingly.
>>>
>> OK, first, see the 15585396k cached? That's how much memory your OS
>> is using to cache file systems etc. Basically that's memory not being
>> used by anything else right now, so the OS borrows it and uses it for
>> caching.
>>
>> Next, VIRT is how much memory your process would need to load every
>> lib it might need but may not be using now, plus all the shared memory
>> it might need, plus it's own space etc. It's not memory in use, it's
>> memory that might under the worst circumstances, be used by that one
>> process. RES is the amount of memory the process IS actually
>> touching, including shared memory that other processes may be sharing.
>> Finally, SHR is the amount of shared memory the process is touching.
>> so, taking your biggest process, it is linked to enough libraries and
>> shared memory and it's own private memory to add up to 4288Meg. It is
>> currently actually touching 3.3G. Of that 3.3G it is touching 3.3G is
>> shared with other processes. So, the difference between RES and SHR
>> is 0, so the delta, or extra memory it's using besides shared memory
>> is ZERO (or very close to it, probably dozens or fewer of megabytes).
>>
>> So, you're NOT running out of memory. Remember when I mentioned
>> iostat, vmstat, etc up above? Have you run any of those?
>>
>
>

On Apr 5, 2011, at 9:33 AM, Adarsh Sharma wrote:

> Now I have to start more queries on Database Server and issue new connections after some time. Why the cached memory is not freed.

It's freed on-demand.

> Flushing the cache memory is needed & how it could use so much if I set

Why would forced flushing be needed? And why would it be useful? It is not.

> effective_cache_size = 4096 MB.

Watch the "cached" field of free's output and set effective_cache_size to that amount (given that your server is running postgres only, has no major other tasks)

> I think if i issue some new select queries on large set of data, it will use Swap Memory & degrades Performance.

Have you ever tried that? Will not.

> Please correct if I'm wrong.

You seem to know very little about Unix/Linux memory usage and how to interpret the tools' output.
Please read some (very basic) documentation for sysadmins regarding these subjects.
It will help you a lot to understand how things work.

--
Akos Gabriel
General Manager
Liferay Hungary Ltd.
Liferay Hungary Symposium, May 26, 2011 | Register today: http://www.liferay.com/hungary2011

On Apr 5, 2011, at 9:33 AM, Adarsh Sharma wrote:

> Now I have to start more queries on Database Server and issue new connections after some time. Why the cached memory is not freed.

It's freed on-demand.

> Flushing the cache memory is needed & how it could use so much if I set

Why would forced flushing be needed? And why would it be useful? It is not.

> effective_cache_size = 4096 MB.

Watch the "cached" field of free's output and set effective_cache_size to that amount (given that your server is running postgres only, has no major other tasks)

> I think if i issue some new select queries on large set of data, it will use Swap Memory & degrades Performance.

Have you ever tried that? Will not.

> Please correct if I'm wrong.

You seem to know very little about Unix/Linux memory usage and how to interpret the tools' output.
Please read some (very basic) documentation for sysadmins regarding these subjects.
It will help you a lot to understand how things work.

--
Akos Gabriel
General Manager
Liferay Hungary Ltd.
Liferay Hungary Symposium, May 26, 2011 | Register today: http://www.liferay.com/hungary2011

--
Üdvözlettel,
Gábriel Ákos

On Tue, Apr 5, 2011 at 1:33 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> wrote:
>
> [root(at)s8-mysd-2 ~]# free -m
>            total       used       free     shared    buffers     cached
> Mem:         15917      15826         90          0        101      15013
> -/+ buffers/cache:        711      15205
> Swap:        16394        143      16250
>
> It means 15 GB memory is cached.

Note that the kernel takes all otherwise unused memory and uses it for
cache. If, at any time a process needs more memory, the kernel just
dumps some cached data and frees up the memory and hands it over, it's
all automatic. As long as cache is large, things are OK. You need to
be looking to see if you're IO bound or CPU bound first. so, vmstat
(install the sysstat package) is the first thing to use.

Scott Marlowe wrote:
> On Tue, Apr 5, 2011 at 1:33 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> wrote:
>
>> [root(at)s8-mysd-2 ~]# free -m
>> total used free shared buffers cached
>> Mem: 15917 15826 90 0 101 15013
>> -/+ buffers/cache: 711 15205
>> Swap: 16394 143 16250
>>
>> It means 15 GB memory is cached.
>>
>
> Note that the kernel takes all otherwise unused memory and uses it for
> cache. If, at any time a process needs more memory, the kernel just
> dumps some cached data and frees up the memory and hands it over, it's
> all automatic. As long as cache is large, things are OK. You need to
> be looking to see if you're IO bound or CPU bound first. so, vmstat
> (install the sysstat package) is the first thing to use.
>
Thanks a lot , Scott. :-)

Best Regards , Adarsh

On Tue, Apr 5, 2011 at 7:20 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com> wrote:
> Scott Marlowe wrote:
>
> On Tue, Apr 5, 2011 at 1:33 AM, Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>
> wrote:
>
>
> [root(at)s8-mysd-2 ~]# free -m
>            total       used       free     shared    buffers     cached
> Mem:         15917      15826         90          0        101      15013
> -/+ buffers/cache:        711      15205
> Swap:        16394        143      16250
>
> It means 15 GB memory is cached.
>
>
> Note that the kernel takes all otherwise unused memory and uses it for
> cache. If, at any time a process needs more memory, the kernel just
> dumps some cached data and frees up the memory and hands it over, it's
> all automatic. As long as cache is large, things are OK. You need to
> be looking to see if you're IO bound or CPU bound first. so, vmstat
> (install the sysstat package) is the first thing to use.

BTW, just remembered that vmstat is it's own package, it's iostat and
sar that are in sysstat.

If you install sysstat, enable stats collecting by editing the
/etc/default/sysstat file and changing the ENABLED="false" to
ENABLED="true" and restarting the service with sudo
/etc/init.d/sysstat restart