From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Marc Millas <marc(dot)millas(at)mokadb(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Death postgres |
Date: | 2023-05-10 16:59:44 |
Message-ID: | 09a91f1fc8a3f1237df5461f28b5f99cc0dda512.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter J. Holzer | 2023-05-10 17:23:59 | Re: Death postgres |
Previous Message | Marc Millas | 2023-05-10 14:35:04 | Re: Death postgres |