Re: understanding postgres backend process memory usage

Hi David,
How many different table/objects do you read from and do you use bind
variables?
Each postmaster process keeps internal dictionary on objects and queries
ran - If you have too many objects or queries planned you might take up too
much ram.

If this is indeed your situation, you might benefit from shorter lived
connections.

Regards,
- Jony

On Fri, Apr 15, 2016 at 12:14 AM, Day, David <dday(at)redcom(dot)com> wrote:

> 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 postgres backends. I am looking for clarity on how postgress backend
> connections use RAM. I suspect our tuning is bad for 2 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 configuration parameters 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(at)build-ace-1-2(dot)redcom(dot)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(at)FreeBSD(dot)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
>
>
>
>
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>