understanding postgres backend process memory usage

Hello,


Understanding postgresql memory usage ?

While traffic loading our application that uses a postgres 9.3.11 database instance, we observe higher RAM usage then
expected. The system bogs down as free memory decreases to minimal values.  The most RAM usage seems to be with
postgresbackends.   I am looking for clarity on how postgress backend connections use RAM.  I suspect our tuning is bad
for2 GB RAM pool.  We likely need to add memory.  

Please look at the "top" output below and offer an opinion that the "top RES" values could be that high for the
configurationparameters postgres is running on. 

There are no error messages in postgres log of relevance.

Regards and thank you for consideration on the matter.


Dave Day

------


Details:


Postgresql.conf  ( relevant subset )

max_connections = 100
shared_buffers = 64MB
#temp_buffers = 8MB
work_mem = 1MB
maintenance_work_mem=16MB
max_stack_depth=2MB
#wal_level=minimal
#fsync=on
#synchronous_commit=on
#full_page_writes = on
#wal_buffers = -1
#autovacuum = on
log_destination='syslog'
update_process_title=off

------

Here are all the backend connections via "top" taken after offered load has been running a while.

I annotated some of the "top" data as to connection ownership sorted by RES and user.
Within the DBM connections I correlate the pid to the client session. Connections
0-3 are somewhat specialized towards read operations ( selects )  whiles connections 4-9 are
More write oriented. ( Insert/updates ).
----

top capture:

97 processes:  1 running, 91 sleeping, 5 zombie
CPU:  1.6% user,  0.0% nice,  9.4% system,  0.4% interrupt, 88.6% idle
Mem: 1474M Active, 79M Inact, 387M Wired, 19M Cache, 10M Free
ARC: 156M Total, 44M MFU, 38M MRU, 1885K Anon, 2619K Header, 70M Other
Swap: 2048M Total, 761M Used, 1287M Free, 37% Inuse, 144K In

  PID USERNAME       THR PRI NICE   SIZE    RES STATE   C   TIME    WCPU COMMAND   DBM connection
DBM
 9099 pgsql            1  20    0   373M   205M sbwait  1   4:02   0.00% postgres  6
 9094 pgsql            1  20    0   365M   204M sbwait  1   3:53   0.00% postgres  4
 9089 pgsql            1  20    0   325M   162M sbwait  1   8:14   0.00% postgres  9
 9097 pgsql            1  20    0   309M   145M sbwait  1   3:40   0.00% postgres  5
 9096 pgsql            1  20    0   301M   138M sbwait  0   3:24   0.00% postgres  8
 9095 pgsql            1  20    0   233M 72648K sbwait  1   1:03   0.00% postgres  0
 9092 pgsql            1  20    0   217M 53852K sbwait  1   0:44   0.00% postgres  2
 9100 pgsql            1  20    0   217M 53708K sbwait  0   0:44   0.00% postgres  3
 9098 pgsql            1  20    0   217M 53552K sbwait  0   0:43   0.00% postgres  1
 9093 pgsql            1  20    0   373M 32328K sbwait  1   4:02   0.00% postgres  7

 9084 pgsql            1  20    0   112M 21284K select  0   0:37   0.00% postgres

glassfish
72033 pgsql            1  20    0   112M 13624K sbwait  0   0:00   0.00% postgres
72034 pgsql            1  20    0   112M 13456K sbwait  1   0:00   0.00% postgres
70428 pgsql            1  20    0   112M 13244K sbwait  0   0:00   0.00% postgres
70430 pgsql            1  20    0   112M 13244K sbwait  1   0:00   0.00% postgres
70429 pgsql            1  20    0   112M 13244K sbwait  1   0:00   0.00% postgres
70431 pgsql            1  20    0   112M 13244K sbwait  0   0:00   0.00% postgres
70432 pgsql            1  20    0   112M 13240K sbwait  0   0:00   0.00% postgres
70427 pgsql            1  20    0   112M 13224K sbwait  1   0:00   0.00% postgres

 9087 pgsql            1  20    0   112M 10772K select  1   0:10   0.00% postgres
 9085 pgsql            1  20    0   112M 10736K select  0   0:04   0.00% postgres
 9082 pgsql            1  20    0   112M 10648K select  0   0:04   0.00% postgres
 9086 pgsql            1  20    0   112M 10496K select  0   0:24   0.00% postgres
 9088 pgsql            1  20    0 39808K  5384K select  1   0:24   0.00% postgres

collectd
 9140 pgsql            1  20    0   112M  4408K sbwait  0   0:13   0.00% postgres


Machine details:  Oracle Virtual Box VM  - guest on Windows.

Copyright (c) 1992-2014 The FreeBSD Project.
Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994
        The Regents of the University of California. All rights reserved.
FreeBSD is a registered trademark of The FreeBSD Foundation.
FreeBSD 10.1-RELEASE-p31 #6 r28476: Fri Apr  8 02:58:45 EDT 2016
    root@build-ace-1-2.redcom.com:/usr/obj/space/jenkins/customws/1.2.0/amd64/src/sys/GENERIC amd64
FreeBSD clang version 3.4.1 (tags/RELEASE_34/dot1-final 208032) 20140512
CPU: AMD Opteron(tm) Processor 4386                  (3100.04-MHz K8-class CPU)
  Origin = "AuthenticAMD"  Id = 0x600f20  Family = 0x15  Model = 0x2  Stepping = 0
  Features=0x1783fbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE,SSE2,HTT>
  Features2=0x9e983203<SSE3,PCLMULQDQ,SSSE3,FMA,CX16,SSE4.1,SSE4.2,POPCNT,AESNI,XSAVE,OSXSAVE,AVX,HV>
  AMD Features=0x2a500800<SYSCALL,NX,MMX+,FFXSR,RDTSCP,LM>
  AMD Features2=0x10be9<LAHF,ExtAPIC,ABM,SSE4A,MAS,Prefetch,OSVW,XOP,FMA4>
  TSC: P-state invariant
real memory  = 2147483648 (2048 MB)
avail memory = 2050375680 (1955 MB)
Event timer "LAPIC" quality 400

Postgres package details:

pkg info postgresql93-server-9.3.11
postgresql93-server-9.3.11
Name           : postgresql93-server
Version        : 9.3.11
Installed on   : Mon Apr 11 15:16:52 2016 EDT
Origin         : databases/postgresql93-server
Architecture   : freebsd:10:x86:64
Prefix         : /usr/local
Categories     : databases
Licenses       :
Maintainer     : pgsql@FreeBSD.org
WWW            : http://www.postgresql.org/
Comment        : The most advanced open-source database available anywhere
Options        :
        DEBUG          : off
        DTRACE         : off
        GSSAPI         : off
        HEIMDAL_KRB5   : off
        ICU            : off
        INTDATE        : on
        LDAP           : off
        MIT_KRB5       : off
        NLS            : on
        OPTIMIZED_CFLAGS: off
        PAM            : off
        SSL            : on
        TZDATA         : on
        XML            : on
Shared Libs required:
        libintl.so.8
        libxml2.so.2
        libpq.so.5
Annotations    :
        cpe            : cpe:2.3:a:postgresql:postgresql:9.3.11:::::freebsd10:x64
        repo_type      : binary
        repository     : redcom