Thread: Postgres memory usage

Postgres memory usage

From
"ruben20@superguai.com"
Date:
Hi:

Our postgres database has tables with several million rows in a server
running Red Hat 8.0 with 1GB of memory. Recently we are experiencing a
low performance in the access to the server via HTTP, after rebooting
the server the speed is the same.

I have noticed that available memory is aparently too low, according to top:

  12:58pm  up  1:28,  3 users,  load average: 0,00, 0,01, 0,09
94 processes: 91 sleeping, 3 running, 0 zombie, 0 stopped
CPU states:  0,0% user,  0,0% system,  0,0% nice, 100,0% idle
Mem:  1031012K av, 1021440K used,    9572K free,       0K shrd,   62864K
buff
Swap: 2040244K av,   14960K used, 2025284K free                  876808K
cached

Is it normal for Postgres to allocate almost all the memory in the computer?

Thanks in advance.
Ruben.





Re: Postgres memory usage

From
Jeff
Date:
On Sep 16, 2004, at 8:58 AM, ruben20@superguai.com wrote:

> I have noticed that available memory is aparently too low, according
> to top:
>
>  12:58pm  up  1:28,  3 users,  load average: 0,00, 0,01, 0,09
> 94 processes: 91 sleeping, 3 running, 0 zombie, 0 stopped
> CPU states:  0,0% user,  0,0% system,  0,0% nice, 100,0% idle
> Mem:  1031012K av, 1021440K used,    9572K free,       0K shrd,
> 62864K
> buff
> Swap: 2040244K av,   14960K used, 2025284K free
> 876808K
> cached
>

in the unix world, "free" memory is mostly useless because the OS will
give up various buffers it is using for caching if an app needs memory.
  It is usually best to look at the output of free which will show you
how much of the memory is used by buffers & caches.

If that number is also low you should look to see how much memory your
applications are using.

Also, what are your shared_buffer and sort_mem settings set to in
postgresql.conf?
Remember in Linux top will include how much shared memory an app has
touched in its SIZE. (But you can also look at the shared column to see
how much of hte size is shared)
.


--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/


Re: Postgres memory usage

From
ruben
Date:
Thanks Jeff:

This is the output of free:

           total      used     free shared buffers cached
Mem:     1031012  1018608    12404      0   64984 848160
-/+ buffers/cache: 105464   925548
Swap:    2040244        0  2040244

shared_buffers and sort_mem are both commented in postgresql.conf:

-bash-2.05b$ grep shared_buf data/postgresql.conf
#shared_buffers = 64        # 2*max_connections, min 16
-bash-2.05b$ grep sort_m data/postgresql.conf
#sort_mem = 512             # min 32

I have verified that postgres is not the responsible for the low
performance of the system, but I just wanted to be sure that memory
usage is reasonable in a postgres installation.

Ruben.



Jeff wrote:

>
> On Sep 16, 2004, at 8:58 AM, ruben20@superguai.com wrote:
>
>> I have noticed that available memory is aparently too low, according
>> to top:
>>
>>  12:58pm  up  1:28,  3 users,  load average: 0,00, 0,01, 0,09
>> 94 processes: 91 sleeping, 3 running, 0 zombie, 0 stopped
>> CPU states:  0,0% user,  0,0% system,  0,0% nice, 100,0% idle
>> Mem:  1031012K av, 1021440K used,    9572K free,       0K shrd,   62864K
>> buff
>> Swap: 2040244K av,   14960K used, 2025284K free                  876808K
>> cached
>>
>
> in the unix world, "free" memory is mostly useless because the OS will
> give up various buffers it is using for caching if an app needs memory.
>  It is usually best to look at the output of free which will show you
> how much of the memory is used by buffers & caches.
>
> If that number is also low you should look to see how much memory your
> applications are using.
>
> Also, what are your shared_buffer and sort_mem settings set to in
> postgresql.conf?
> Remember in Linux top will include how much shared memory an app has
> touched in its SIZE. (But you can also look at the shared column to see
> how much of hte size is shared)
> .
>
>
> --
> Jeff Trout <jeff@jefftrout.com>
> http://www.jefftrout.com/
> http://www.stuarthamm.net/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match
>



Re: Postgres memory usage

From
Jeff
Date:
On Sep 16, 2004, at 11:26 AM, ruben wrote:

> Thanks Jeff:
>
> This is the output of free:
>
>           total      used     free shared buffers cached
> Mem:     1031012  1018608    12404      0   64984 848160
> -/+ buffers/cache: 105464   925548
> Swap:    2040244        0  2040244
>
a vast majority of your ram is all in cache.
thats is good. It means the memory isn't being wasted.

> shared_buffers and sort_mem are both commented in postgresql.conf:
>
> -bash-2.05b$ grep shared_buf data/postgresql.conf
> #shared_buffers = 64        # 2*max_connections, min 16
> -bash-2.05b$ grep sort_m data/postgresql.conf
> #sort_mem = 512             # min 32
>

that is an extremely small value of shared buffers.
you should set it to at least 1000, maybe even 10000.
You'll likely get a nice performance boost by increasing it.

> I have verified that postgres is not the responsible for the low
> performance of the system, but I just wanted to be sure that memory
> usage is reasonable in a postgres installation.
>
Yes. It is fine, except I'd increase shared_buffers

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/