Postgres memory usage

[r***@superguai.com]

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.





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

[Jeff]

Post by r***@superguai.com
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 <***@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

[ruben]

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.

Post by Jeff

Post by r***@superguai.com
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)
.
--
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Jeff]

Post by ruben
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.

Post by ruben
-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.

Post by ruben
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 <***@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org