Urgent -- High memory usage on PostgreSQL server

On Mon, Dec 13, 2010 at 8:49 AM, savio rodriges <sj_savio@yahoo.com> wrote:

Hello,

We are facing very HIGH memory utilization on postgreSQL server and need help.

Below are details of PostgreSQL server,

===========================================================================
MemTotal:      8165696 kB
CpuTotal:      8
===========================================================================
/etc/sysctl.conf
----------------
# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296
===========================================================================
top - 21:43:35 up 55 days,  8:07,  4 users,  load average: 0.05, 0.25, 0.17
Tasks: 257 total,   1 running, 256 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8165696k total,  7943160k used,   222536k free,   282044k buffers
Swap:  8385920k total,   112828k used,  8273092k free,  4793732k cached
===========================================================================
-bash-3.2$ free -m
            total       used       free     shared    buffers     cached
Mem:          7974       7756        217          0        275       4681
-/+ buffers/cache:       2799       5174
Swap:         8189        110       8079

===========================================================================
-bash-3.2$ cat /proc/meminfo
MemTotal:      8165696 kB
MemFree:        222576 kB
Buffers:        282136 kB
Cached:        4793748 kB
SwapCached:      21144 kB
Active:        3508524 kB
Inactive:      4004532 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:      8165696 kB
LowFree:        222576 kB
SwapTotal:     8385920 kB
SwapFree:      8273092 kB
Dirty:             548 kB
Writeback:         368 kB
AnonPages:     2415992 kB
Mapped:         730104 kB
Slab:           332440 kB
PageTables:      58632 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:  12468768 kB
Committed_AS:  4774104 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    267392 kB
VmallocChunk: 34359470967 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB
===========================================================================
processor       : Total 8
vendor_id       : GenuineIntel
cpu family      : 6
model           : 26
model name      : Intel(R) Xeon(R) CPU           E5504  @ 2.00GHz
stepping        : 5
cpu MHz         : 2000.118
cache size      : 4096 KB
physical id     : 1
siblings        : 4
core id         : 3
cpu cores       : 4
apicid          : 22
fpu             : yes
fpu_exception   : yes
cpuid level     : 11
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
bogomips        : 4000.10
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management: [8]
===========================================================================
-bash-3.2$ ipcs

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x0052e2c1 35454978   postgres  600        1142923264 28

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0x0052e2c1 37650432   postgres  600        17
0x0052e2c2 37683201   postgres  600        17
0x0052e2c3 37715970   postgres  600        17
0x0052e2c4 37748739   postgres  600        17
0x0052e2c5 37781508   postgres  600        17
0x0052e2c6 37814277   postgres  600        17
0x0052e2c7 37847046   postgres  600        17

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages
===========================================================================
-bash-3.2$ ipcs -l

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1

------ Semaphore Limits --------
max number of arrays = 128
max semaphores per array = 250
max semaphores system wide = 32000
max ops per semop call = 32
semaphore max value = 32767

------ Messages: Limits --------
max queues system wide = 16
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536
===========================================================================
/var/lib/pgsql/data/postgresql.conf
-----------------------------------
(Non-default values are as follows)

maintenance_work_mem = 480MB
checkpoint_completion_target = 0.7
work_mem = 36MB
wal_buffers = 4MB
checkpoint_segments = 8
shared_buffers = 1024MB
log_duration = on
log_min_duration_statement = 10000
effective_cache_size=2048MB
===========================================================================

Any idea where is the issue and what needs to be changed ?

Thanks,

Savio

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

What distribution and version of Linux are you running? I have seen
this kind of behaviour on Ubuntu but never on Fedora. A restart of
PostgreSQL server service will not free substantial memory indicating
that the probable memory leak is attributed to other application,
service or the OS itself.

Allan.

On Mon, Dec 13, 2010 at 4:49 PM, savio rodriges <sj_savio@yahoo.com> wrote:

Hello,

We are facing very HIGH memory utilization on postgreSQL server and need
help.

Mem: 8165696k total, 7943160k used, 222536k free, 282044k buffers
Swap: 8385920k total, 112828k used, 8273092k free, 4793732k cached

What makes you say memory usage is high?

You have ~8gb of memory.
Less than 3gb of that is being used by applications and the OS is using the
"spare" 5gb for caching and buffers.

Paul

Hello Savio,

top - 21:43:35 up 55 days, 8:07, 4 users, load average: 0.05,
0.25, 0.17 Tasks: 257 total, 1 running, 256 sleeping, 0 stopped,
0 zombie Cpu(s): 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa,
0.0%hi, 0.0%si, 0.0%st Mem: 8165696k total, 7943160k used,
222536k free, 282044k buffers Swap: 8385920k total, 112828k
used, 8273092k free, 4793732k cached

this looks kind of normal behaviour. Your system uses almost your entire
ram right, but if you look at buffers and cached in your top output
about 5 GB of your 8GB are used there. Only the remaining 3GB are used
by postgres or other applications.

Every "new" linux I know will use non-used ram for caching your
filesystem data for example to achieve 100% memory usage. This is "a
good thing" (TM) as postgres will run better if there is caching on os
level and the instance knows about this:

effective_cache_size = <num> � This value tells PostgreSQL's
optimizer how much memory PostgreSQL has available for caching data
and helps in determing whether or not it use an index or not. The
larger the value increases the likely hood of using an index. This
should be set to the amount of memory allocated to shared_buffers
plus the amount of OS cache available. Often this is more than 50% of
the total system memory.

So don't worry - everything is fine. You should pay attention that your
system doesn't start swapping.

Cheers,
Jan

On Sun, Dec 12, 2010 at 09:49:52PM -0800, savio rodriges wrote:

Hello,

We are facing very HIGH memory utilization on postgreSQL server and need help.

which number from all of what's below is making you worried?

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007