Re: understanding postgres backend process memory usage - Mailing list pgsql-general

From Day, David
Subject Re: understanding postgres backend process memory usage
Date
Msg-id 401084E5E73F4241A44F3C9E6FD794280378ED075D@exch-01
Whole thread Raw
In response to Re: understanding postgres backend process memory usage  (Jony Cohen <jony.cohenjo@gmail.com>)
List pgsql-general

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.cohenjo@gmail.com]
Sent: Sunday, April 17, 2016 4:24 AM
To: Day, David
Cc: pgsql-general@postgresql.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@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
    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









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

 

pgsql-general by date:

Previous
From:
Date:
Subject: Re: How to detoast a column of type BYTEAOID
Next
From: Adrian Klaver
Date:
Subject: Re: Enhancement request for pg_dump