Date: Wed, 23 Mar 2022 10:20:22 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Shai Shapira <Shai.Shapira@Amdocs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: High process memory consumption when running sort
Message-ID: <20220323152022.GT28503@telsasoft.com>
References: 
 <VI1PR06MB5567FA134E185D5243CA726CEF189@VI1PR06MB5567.eurprd06.prod.outlook.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <VI1PR06MB5567FA134E185D5243CA726CEF189@VI1PR06MB5567.eurprd06.prod.outlook.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20220323152022.GT28503%40telsasoft.com>
Precedence: bulk

On Wed, Mar 23, 2022 at 02:42:06PM +0000, Shai Shapira wrote:
> Hi,
> 
> When running our application, we noticed that some processes are taking a lot of memory ( 10, 15, 20GB or so, of RSS ).
> It is also reproduced when running in psql.

Note that RSS can include shared_buffers read by that backend.
That's a linux behavior, not specific to postgres.  It's what Andres was
describing here:
https://www.postgresql.org/message-id/flat/20201003230149.mtd7fjsjwgii3jv7@alap3.anarazel.de

You have effective_cache_size = 48GB, so this seems to be working as intended.
(ecc is expected to include data cached not only by postgres but by the OS page
cache, too).

> Memory consumption: ( of case 2, application table, using system_stats )

I'm not sure, but I guess this is just a postgres view of whatever the OS
shows.

> Using top:
>   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
> 15298 postgres  20   0   16.8g   1.1g   1.1g S   0.0  1.7   0:02.63 postgres

> PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
> Linux illin7504 3.10.0-1127.19.1.el7.x86_64 #1 SMP Tue Aug 11 19:12:04 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux

> shared_buffers                      | configuration file   | postmaster        | 2097152                                            | 8kB  | 1024
> effective_cache_size                | configuration file   | user              | 6291456                                            | 8kB  | 524288
> work_mem                            | configuration file   | user              | 20480                                              | kB   | 4096