Thread: Postgres memory usage
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.
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/
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
>
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/