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 1pxcL2-0000iu-3P for pgsql-admin@arkaria.postgresql.org; Fri, 12 May 2023 23:38:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pxcL0-0005qw-DT for pgsql-admin@arkaria.postgresql.org; Fri, 12 May 2023 23:38:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pxcL0-0005p6-2k for pgsql-admin@lists.postgresql.org; Fri, 12 May 2023 23:38:14 +0000 Received: from mout-u-204.mailbox.org ([2001:67c:2050:101:465::204]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pxcKv-002IkD-7G for pgsql-admin@lists.postgresql.org; Fri, 12 May 2023 23:38:13 +0000 Received: from smtp102.mailbox.org (smtp102.mailbox.org [IPv6:2001:67c:2050:b231:465::102]) (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 4QJ4vF2rJRz9sMF; Sat, 13 May 2023 01:38:01 +0200 (CEST) Date: Sat, 13 May 2023 01:38:00 +0200 (CEST) From: Erik Wienhold To: Alex Balashov , pgsql-admin Message-ID: <729198445.317848.1683934680067@office.mailbox.org> In-Reply-To: <2D17E15D-8D5B-4DF7-A238-12455C2731D8@evaristesys.com> References: <2D17E15D-8D5B-4DF7-A238-12455C2731D8@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: 4QJ4vF2rJRz9sMF List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 13/05/2023 00:12 CEST Alex Balashov wrote: > > We have an application that has a preforked worker pool architecture and > opens a relatively large (70+) number of persistent connections to PostgreSQL > 14, and have `max_connections` set to 200 accordingly. Which pg14 minor version exactly? > Occasionally, about once every 2 months, we'll get a slow-motion implosion > over a period of about 24 hours, where the resident memory size of some of > the backends shoots up from a reasonable few hundred MB to several gigs -- > and will keep growing, e.g. > > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND > 3932733 postgres 20 0 637740 327124 323840 R 10.7 0.5 0:00.11 postgres > 782930 postgres 20 0 3151360 2.8g 551704 S 9.7 4.5 221:43.17 postgres > 782839 postgres 20 0 2999896 2.7g 551832 S 8.7 4.3 220:54.55 postgres > 3932734 postgres 20 0 636128 338168 336096 R 8.7 0.5 0:00.09 postgres > 3932735 postgres 20 0 636128 319824 317768 R 8.7 0.5 0:00.09 postgres > 782851 postgres 20 0 3142152 2.8g 551872 S 7.8 4.5 221:22.62 postgres > 782855 postgres 20 0 3155144 2.8g 551828 S 7.8 4.5 221:38.59 postgres > 782897 postgres 20 0 3148808 2.8g 551724 S 7.8 4.5 220:05.94 postgres > 783106 postgres 20 0 3152416 2.8g 551912 S 7.8 4.5 221:24.40 postgres > 783168 postgres 20 0 2992592 2.7g 551968 S 7.8 4.2 220:36.06 postgres > 782843 postgres 20 0 3146772 2.8g 551944 R 4.9 4.5 221:38.46 postgres > 782778 postgres 20 0 3150956 2.8g 551852 R 3.9 4.5 220:49.88 postgres 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 -- Erik