Re: understanding postgres backend process memory usage

Hi Jony,

First of all thank for considering the matter.

Although the overall database has many tables, the relations/objects accessed by high RAM usage connections is rather limited. ( < 10 )
There are essentially a couple of “selects” (2-4) dependent on options that are enabled and then the bulk of the subsequent activity are
Inserts/updates on call state information about a phone call.

Usage of Bind Variables:

If this is what Bind Variables means then yes.
Each session/connection sets ups 19 prepared statements with variables when a connection is established.
Some of these prepared statements are used at a later time, dependent on call scenario, if needed, at which time the variables provided by the requestor are “bound” to the prepared statement variables. The prepared statements are generally server side functions with arguments being provided by these bind variables.

Periodically resetting the connection is something we could do but it seems a work-around to really understanding what is going on with the RAM usage. I was hoping there might be a simple formula, that given the postgres config file settings a connection should never exceed a certain amount of RAM as observed by the “top” utility. I am not confident, in googling about, that I have found an authoritative answer as to what is that formula.

Best Regards and thanks again.

Dave

From: Jony Cohen [mailto:jony(dot)cohenjo(at)gmail(dot)com]
Sent: Sunday, April 17, 2016 4:24 AM
To: Day, David
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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<mailto: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<mailto: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<mailto: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<mailto:pgsql-general(at)postgresql(dot)org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general