pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Marc Millas <marc.millas@mokadb.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Death postgres
Date: Wed, 10 May 2023 18:59:44 +0200
Message-ID: <09a91f1fc8a3f1237df5461f28b5f99cc0dda512.camel@cybertec.at> (raw)
In-Reply-To: <CADX_1aZn2NAxKW6mdmRqP8ciyPhztQ2bRZv6UYYYVJt+jDceeQ@mail.gmail.com>
References: <CADX_1aa=7vXKhsiHQA97yT1+WBcKM4PSfzvxrszC7xqnujZVVQ@mail.gmail.com>
	<9061fac25866f02c539ae389ace7617d9cfdca74.camel@cybertec.at>
	<CADX_1aZn2NAxKW6mdmRqP8ciyPhztQ2bRZv6UYYYVJt+jDceeQ@mail.gmail.com>

On Wed, 2023-05-10 at 16:35 +0200, Marc Millas wrote:
> > > postgres 14.2 on Linux redhat
> > > 
> > > temp_file_limit set around 210 GB.
> > > 
> > > 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.
> > > 
> > > What are the cases where postgres may grow without caring about temp_file_limit ?
> > 
> > That's too little information for a decent answer.
> > One obvious answer is: if it is not writing temporary files.
> 
> so here is a little more info:
> 
>                                                         QUERY PLAN                                                         
> ---------------------------------------------------------------------------------------------------------------------------
>  Unique  (cost=72377463163.02..201012533981.80 rows=1021522829864 width=97)
>    ->  Gather Merge  (cost=72377463163.02..195904919832.48 rows=1021522829864 width=97)
>          Workers Planned: 5
>          ->  Sort  (cost=72377462162.94..72888223577.87 rows=204304565973 width=97)
>                Sort Key: t1.col_ine, (CASE WHEN (t2.col_ibi IS NULL) THEN t3.col_ibi ELSE t2.col_ibi END)
>                ->  Parallel Hash Left Join  (cost=604502.76..1276224253.51 rows=204304565973 width=97)
>                      Hash Cond: ((t1.col_ano)::text = (t2.col_ano)::text)
>                      ->  Parallel Hash Left Join  (cost=300803.38..582295.38 rows=4857277 width=52)
>                            Hash Cond: ((t1.col_ne)::text = (t3.col_ne)::text)
>                            ->  Parallel Seq Scan on table_a t1  (cost=0.00..184052.35 rows=2616335 width=53)
>                            ->  Parallel Hash  (cost=243466.06..243466.06 rows=2965306 width=31)
>                                  ->  Parallel Seq Scan on table_b t3  (cost=0.00..243466.06 rows=2965306 width=31)
>                      ->  Parallel Hash  (cost=243466.06..243466.06 rows=2965306 width=34)
>                            ->  Parallel Seq Scan on table_b t2  (cost=0.00..243466.06 rows=2965306 width=34)
>  JIT:
>    Functions: 19
>    Options: 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





view thread (26+ messages)  latest in thread

Message-ID: <09a91f1fc8a3f1237df5461f28b5f99cc0dda512.camel@cybertec.at>
Permalink:  ../09a91f1fc8a3f1237df5461f28b5f99cc0dda512.camel@cybertec.at/
Also on:    postgresql.org/message-id/09a91f1fc8a3f1237df5461f28b5f99cc0dda512.camel@cybertec.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: laurenz.albe@cybertec.at, marc.millas@mokadb.com
  Subject: Re: Death postgres
  In-Reply-To: <09a91f1fc8a3f1237df5461f28b5f99cc0dda512.camel@cybertec.at>
  References: <CADX_1aa=7vXKhsiHQA97yT1+WBcKM4PSfzvxrszC7xqnujZVVQ@mail.gmail.com> <9061fac25866f02c539ae389ace7617d9cfdca74.camel@cybertec.at> <CADX_1aZn2NAxKW6mdmRqP8ciyPhztQ2bRZv6UYYYVJt+jDceeQ@mail.gmail.com> <09a91f1fc8a3f1237df5461f28b5f99cc0dda512.camel@cybertec.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox