From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Frits Jalvingh <jal(at)etc(dot)to> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: temp_file_limit? |
Date: | 2022-12-18 15:57:05 |
Message-ID: | 20221218155705.GT1153@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Sun, Dec 18, 2022 at 12:48:03PM +0100, Frits Jalvingh wrote:
> Hi list,
>
> I have a misbehaving query which uses all available disk space and then
> terminates with a "cannot write block" error. To prevent other processes
> from running into trouble I've set the following:
>
> temp_file_limit = 100GB
> The comment in the file states that this is a per-session parameter, so
> what is going wrong here?
Do you mean the comment in postgresql.conf ?
commit d1f822e58 changed to say that temp_file_limit is actually
per-process and not per-session.
Could you send the query plan, preferably "explain analyze" (if the
query finishes sometimes) ?
log_temp_files may be helpful here.
> The query does parallelize and uses one parallel worker while executing,
> but it does not abort when the temp file limit is reached:
>
> 345G pgsql_tmp
>
> It does abort way later, after using around 300+ GB:
> [53400] ERROR: temporary file size exceeds temp_file_limit (104857600kB)
> Where: parallel worker
Are you sure the 345G are from only one instance of the query ?
Or is it running multiple times, or along with other queries writing
100GB of tempfiles.
It seems possible that it sometimes runs with more than one parallel
worker. Also, are there old/stray tempfiles there which need to be
cleaned up?
--
Justin
From | Date | Subject | |
---|---|---|---|
Next Message | Frits Jalvingh | 2022-12-18 17:29:41 | Fwd: temp_file_limit? |
Previous Message | Frits Jalvingh | 2022-12-18 11:48:03 | temp_file_limit? |