Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qrHyy-003XZr-By for pgsql-admin@arkaria.postgresql.org; Fri, 13 Oct 2023 13:13:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qrHyw-000YuY-36 for pgsql-admin@arkaria.postgresql.org; Fri, 13 Oct 2023 13:13:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qrHyv-000YuI-Ko for pgsql-admin@lists.postgresql.org; Fri, 13 Oct 2023 13:13:34 +0000 Received: from truschnigg.info ([89.163.150.210]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qrHys-000Lij-0W for pgsql-admin@lists.postgresql.org; Fri, 13 Oct 2023 13:13:33 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=truschnigg.info; s=m22; t=1697202809; bh=ijudmBaE2qhrexwSgcEzEgJmFe/o4F4iYRIOBRYrY74=; h=Date:From:To:Cc:Subject:References:In-Reply-To:From; b=ArDxG4BTpvmgxfFRw1Q6XqTdCC1yu8wcpqabBz/lKWynJ3FYHkRhN2s4v2kAKxA/j FP71ouD5ilXTHRBIUbA3yjcCHxd0shTkrVaDyahHQbZ6z2S157hwvWAmp5BTiN5gUA 0eHPeMUiHnDWFTIZ3uyw+6m2TAHfQPzS+cTz62V8nLCTjzsIRW3h2idSazMXlVQZvk 0MBRWcgTO8q945BwfOM1AAx62xJEoiCumX2sTUT5NUNFdgYNyzyvENdIHJUNp4KsnJ kWQDWiWedq6lzR//X5xnr+LWdrd1jRWCAAfWLcXLLBtUJfa1Ev2y0iE/9+50E03sAV OBxS/YhNCiEmw== Received: from vault.lan (unknown [IPv6:2a02:1748:fafe:cf3f:1eb7:2cff:fe02:8261]) by truschnigg.info (Postfix) with ESMTPSA id 93B9F2027E; Fri, 13 Oct 2023 13:13:29 +0000 (UTC) Date: Fri, 13 Oct 2023 15:13:28 +0200 From: Johannes Truschnigg To: Jean-Christophe Boggio Cc: pgsql-admin@lists.postgresql.org Subject: Re: One PG process eating more than 40GB of RAM and getting killed by OOM Message-ID: References: <1018f9c0-9d14-4146-b7e9-24c48ea29e1d@thefreecat.org> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="yGby4iuSNFOAwbq0" Content-Disposition: inline In-Reply-To: <1018f9c0-9d14-4146-b7e9-24c48ea29e1d@thefreecat.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --yGby4iuSNFOAwbq0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable You will want to try decreasing work_mem to a sane number first, without looking at anything else really. Check out the official docs: https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WO= RK-MEM The gist is that work_mem is not a limit that's effective per session/connection/query, but per sort- or hash-node, of which there can be many in complex queries. Which is why 1GB of work_mem can end up consuming several multiples of that, if you are (un)lucky enough. --=20 with best regards: - Johannes Truschnigg ( johannes@truschnigg.info ) www: https://johannes.truschnigg.info/ phone: +436502133337 xmpp: johannes@truschnigg.info --yGby4iuSNFOAwbq0 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAEBCgAdFiEEGu9IhkI+7/aKLUWF95W3jMsYfLUFAmUpQncACgkQ95W3jMsY fLVOKBAAjWZ/6Xy8nVoBiUeWVTIBQbaK5T0vWx83ea6d3WTMjp2kSd+4L96YvTpG dF3vk0XK40RtTaQ7NWQ2CtxXAdIkB+6FkwVxHBNkeIeea/lmd/YkATbHwgK7w8F4 KOftDe9Rzuj3KUn5gKguLXnRTUCYaxjmLmaQXq225RPXn6KhrDyqH60MLESm2Enn ubYYAkKe6ydF19Podzt+tAHOsxmLZaH/pcIVYziXx5dJQnW7p1Q2OF0l0FgQp6/L KnzvVwcCotcCjxhkI0zcHqYsDJ+8cSMi3PI/vfOzRx1KlZQkgx2fUbue91KppLI2 rM9BWmW4uX0kRRR0CQhaxM1mB/n/CgOsRwTsMK5Kafe1xSx+mA53EiEC8IbS5WEx mcY4CSG8TetgMOTGh1fPEP089qEsp1MdMsyP00mVYzo+lBXq9lEetEVVxL+WSms4 5kk5iOVZOgH+yovEUHXG8HlcRMMV7cxOP5DA2gftfM94YeiLzxePRiPwgmMs+WYw IV6CC2PHQLhgr4Zg1lFE+Yp8ANJWJqMB4mldw8+JE1Vy/8FLvr824ExGYKzT6vBO sAbEi2U1HEtqTaDYAUE3qVadD5YABLtiHVmc+fYG1jqpYdD4gZtx2Zj8JzWWxD6n AdfGyd1e3gLRM0KTtjwlhDCUzrEiFP1dEXIWV4Kugw6S2F2NeSg= =Tskr -----END PGP SIGNATURE----- --yGby4iuSNFOAwbq0--