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 1pwnAU-00011F-4s for pgsql-general@arkaria.postgresql.org; Wed, 10 May 2023 16:59:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pwnAR-0004Pb-5u for pgsql-general@arkaria.postgresql.org; Wed, 10 May 2023 16:59:55 +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 1pwnAQ-0004P6-Mr for pgsql-general@lists.postgresql.org; Wed, 10 May 2023 16:59:54 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pwnAJ-001o09-FA for pgsql-general@lists.postgresql.org; Wed, 10 May 2023 16:59:53 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-96652cb7673so682849866b.0 for ; Wed, 10 May 2023 09:59:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20221208.gappssmtp.com; s=20221208; t=1683737986; x=1686329986; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:cc:to:from:subject:message-id:from:to:cc:subject :date:message-id:reply-to; bh=oSqV50ucQASpT0pUfVpHjkuuQLXuJYzb/xgX6x+Htmc=; b=wFAneV9+YykQo9O+vvApwYMlSmT5oLBy0Ro6s+63FlX1DEeA67NNxyCkJczChBrlmP 5G8giyAIvyRieZNVmfZKSwbtShDCfCkRX66gxYeYPZ/3e2DzoEkhBH4t7NBkJZBO/fN+ 5ghnRRlT5uo9OA9drofJhxhNeocUmlVRYj9qwfBU1xq87OUJ5POyJ1ufgOY2cVUn74Vm /BOcPEqP0+xNa1NYeo+eO4SNnEuAofLuXkCagTDV9dWcll76nhTlBa4rPVpNp/OpmiKh dXFx7154fhh8j58Z/ThITrd6x5ARqiOLQ9H1tAyIsxfxHv4/RC6YvCplHVyE10bipcoH WjjA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1683737986; x=1686329986; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:cc:to:from:subject:message-id:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=oSqV50ucQASpT0pUfVpHjkuuQLXuJYzb/xgX6x+Htmc=; b=PDch+TU58fUdjKmyBYeAE564sXyazVkNyeOhGkI4Lbf020ruTEfSyRNJXPDfJECIEU Me0zB7QZ/mSltZOy6C9CTRNCf71LE/58rrEs0FqJ+XVWHJx/xzFvLmecGuNLkeF66yLN dCg+9n/gvVzMzjWWu3uXD9nsdIyCnnxCyNPO5FGwaiAH4CtI3IXuVNHSJ2u7GloYNOyA 49QVBMgFR9LxsMaDDVcVmcbbECCXS8KyxTfheWyxxOEDMfkVyNjMVIu9oSf7TO9vrXbE XGMt1+i6DXl2qGiG2t1JZVLWGVgWdLzGxYTP71M3Y4lgSkNZzkwjrnqqowcffUsg300F LX/w== X-Gm-Message-State: AC+VfDzkJl1OmphoJGMIqD012z4OPHc/M+8xRTdrpY6Q6OEpoNggwiWm cVXdChEN/XCT6Hn4dLQq0uTG2A== X-Google-Smtp-Source: ACHHUZ5r8vtkwVMMsTFkOetc9wDJtiyZhsZWsLmFytDeZEf8lMMxo14xawS/yP4A3Se/eOKSu8nKOA== X-Received: by 2002:a17:907:a49:b0:96a:30b5:cfac with SMTP id be9-20020a1709070a4900b0096a30b5cfacmr2099954ejc.28.1683737986408; Wed, 10 May 2023 09:59:46 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:e993:81f0:8bc3:96ab:de53]) by smtp.gmail.com with ESMTPSA id v20-20020a170906b01400b0096165b2703asm2891055ejy.110.2023.05.10.09.59.45 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 10 May 2023 09:59:46 -0700 (PDT) Message-ID: <09a91f1fc8a3f1237df5461f28b5f99cc0dda512.camel@cybertec.at> Subject: Re: Death postgres From: Laurenz Albe To: Marc Millas Cc: "pgsql-general@lists.postgresql.org" Date: Wed, 10 May 2023 18:59:44 +0200 In-Reply-To: References: <9061fac25866f02c539ae389ace7617d9cfdca74.camel@cybertec.at> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 2023-05-10 at 16:35 +0200, Marc Millas wrote: > > > postgres 14.2 on Linux redhat > > >=20 > > > temp_file_limit set around 210 GB. > > >=20 > > > a select request with 2 left join have crashed the server (oom killer= ) after the postgres > > > disk occupation did grow from 15TB to 16 TB. > > >=20 > > > What are the cases where postgres may grow without caring about temp_= file_limit ? > >=20 > > That's too little information for a decent answer. > > One obvious answer is: if it is not writing temporary files. >=20 > so here is a little more info: >=20 > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=20 > -------------------------------------------------------------------------= -------------------------------------------------- > =C2=A0Unique =C2=A0(cost=3D72377463163.02..201012533981.80 rows=3D1021522= 829864 width=3D97) > =C2=A0 =C2=A0-> =C2=A0Gather Merge =C2=A0(cost=3D72377463163.02..19590491= 9832.48 rows=3D1021522829864 width=3D97) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Workers Planned: 5 > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Sort =C2=A0(cost=3D72377462162= .94..72888223577.87 rows=3D204304565973 width=3D97) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Key: t1.col_i= ne, (CASE WHEN (t2.col_ibi IS NULL) THEN t3.col_ibi ELSE t2.col_ibi END) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel = Hash Left Join =C2=A0(cost=3D604502.76..1276224253.51 rows=3D204304565973 w= idth=3D97) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Hash Cond: ((t1.col_ano)::text =3D (t2.col_ano)::text) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0-> =C2=A0Parallel Hash Left Join =C2=A0(cost=3D300803.38..582295.38 rows= =3D4857277 width=3D52) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Hash Cond: ((t1.col_ne)::text =3D (t3.col_ne)::text= ) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel Seq Scan on table_a t1 =C2=A0(cos= t=3D0.00..184052.35 rows=3D2616335 width=3D53) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel Hash =C2=A0(cost=3D243466.06..243= 466.06 rows=3D2965306 width=3D31) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel Seq Scan on = table_b t3 =C2=A0(cost=3D0.00..243466.06 rows=3D2965306 width=3D31) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0-> =C2=A0Parallel Hash =C2=A0(cost=3D243466.06..243466.06 rows=3D2965306= width=3D34) > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Parallel Seq Scan on table_b t2 =C2=A0(cos= t=3D0.00..243466.06 rows=3D2965306 width=3D34) > =C2=A0JIT: > =C2=A0 =C2=A0Functions: 19 > =C2=A0 =C2=A0Options: Inlining true, Optimization true, Expressions true,= Deforming true > (17 rows) Perhaps parallel query drives you OOM. Does the problem also happen if "max_parallel_workers_per_gather" is set to 0? Yours, Laurenz Albe