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 <d...@redcom.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 > > r...@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 : pg...@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 > > > > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
