Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pxdtS-0001bG-Ow for pgsql-admin@arkaria.postgresql.org; Sat, 13 May 2023 01:17:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pxdtQ-0002nG-UW for pgsql-admin@arkaria.postgresql.org; Sat, 13 May 2023 01:17:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pxdtQ-0002n6-JZ for pgsql-admin@lists.postgresql.org; Sat, 13 May 2023 01:17:52 +0000 Received: from mout-u-204.mailbox.org ([2001:67c:2050:101:465::204]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pxdtJ-0021dP-KJ for pgsql-admin@lists.postgresql.org; Sat, 13 May 2023 01:17:51 +0000 Received: from smtp202.mailbox.org (smtp202.mailbox.org [IPv6:2001:67c:2050:b231:465::202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4QJ76B5hDwz9sNp; Sat, 13 May 2023 03:17:38 +0200 (CEST) Date: Sat, 13 May 2023 03:17:37 +0200 (CEST) From: Erik Wienhold To: Alex Balashov , pgsql-admin Message-ID: <346274191.318070.1683940657936@office.mailbox.org> In-Reply-To: <7402BB66-E774-405D-8F75-86142239194F@evaristesys.com> References: <2D17E15D-8D5B-4DF7-A238-12455C2731D8@evaristesys.com> <729198445.317848.1683934680067@office.mailbox.org> <7402BB66-E774-405D-8F75-86142239194F@evaristesys.com> Subject: Re: Unexplained rapid growth in memory usage of idle backends MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4QJ76B5hDwz9sNp List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 13/05/2023 01:56 CEST Alex Balashov wrote: > > > On May 12, 2023, at 7:38 PM, Erik Wienhold wrote: > > > > Function pg_log_backend_memory_contexts(pid int) may give you some insights on > > on the memory allocation for specific backends. > > > > https://www.postgresql.org/docs/14/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE > > Ah, thanks! > > 2023-05-12 23:53:23.293 UTC [3934125] eti_adm@eti LOG: level: 3; CachedPlanQuery: 2048 total in 2 blocks; 552 free (0 chunks); 1496 used > 2023-05-12 23:53:23.293 UTC [3934125] eti_adm@eti LOG: level: 2; SPI Plan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used > 2023-05-12 23:53:23.293 UTC [3934125] eti_adm@eti LOG: level: 1; 828 more child contexts containing 2714624 total in 2097 blocks; 899280 free (329 chunks); 1815344 used > 2023-05-12 23:53:23.293 UTC [3934125] eti_adm@eti LOG: level: 1; WAL record construction: 49768 total in 2 blocks; 6360 free (0 chunks); 43408 used > 2023-05-12 23:53:23.293 UTC [3934125] eti_adm@eti LOG: level: 1; PrivateRefCount: 8192 total in 1 blocks; 2616 free (0 chunks); 5576 used > 2023-05-12 23:53:23.293 UTC [3934125] eti_adm@eti LOG: level: 1; MdSmgr: 8192 total in 1 blocks; 5640 free (1 chunks); 2552 used > 2023-05-12 23:53:23.293 UTC [3934125] eti_adm@eti LOG: level: 1; LOCALLOCK hash: 16384 total in 2 blocks; 4592 free (2 chunks); 11792 used > 2023-05-12 23:53:23.293 UTC [3934125] eti_adm@eti LOG: level: 1; Timezones: 104120 total in 2 blocks; 2616 free (0 chunks); 101504 used > 2023-05-12 23:53:23.293 UTC [3934125] eti_adm@eti LOG: level: 1; ErrorContext: 8192 total in 1 blocks; 7928 free (5 chunks); 264 used > 2023-05-12 23:53:23.293 UTC [3934125] eti_adm@eti LOG: Grand total: 6417232 bytes in 2461 blocks; 2409920 free (468 chunks); 4007312 used > > But what can I learn from this that might be of applied value? Narrowing down the memory contexts with high allocation. But I haven't used this function before and would have to look up the memory context names. I bet there are plenty of people on this list who can interpret the output. But the log messages that you posted are not the entire output. The first line should read "logging memory context if PID". -- Erik