Date: Sun, 18 Dec 2022 14:10:52 -0600
From: Justin Pryzby <pryzby@telsasoft.com>
To: Frits Jalvingh <jal@etc.to>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Fwd: temp_file_limit?
Message-ID: <20221218201052.GV1153@telsasoft.com>
References: 
 <CAKhTGFXmSBSXYjBXVGiQ_VO7Wz14SrNy2sQOU8KKH3priWKWUQ@mail.gmail.com>
 <20221218155705.GT1153@telsasoft.com>
 <CAKhTGFVwMLCL04hU6Q9rvwg0q2C9aCrj3KKOcgPjL-JRQwTDUg@mail.gmail.com>
 <CAKhTGFX7uH3bK-NTcOB06+2TA0j5Czu3YWrepD7v8VNT45d99Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAKhTGFX7uH3bK-NTcOB06+2TA0j5Czu3YWrepD7v8VNT45d99Q@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20221218201052.GV1153%40telsasoft.com>
Precedence: bulk

On Sun, Dec 18, 2022 at 06:29:41PM +0100, Frits Jalvingh wrote:
> Just to be clear: my real question is: why is temp_file_limit not
> working at the specified size? Because this is my real problem: when a
> query is dying like this it will also kill other queries because these
> are also running out of space. Even when the limit is per-process it
> should not have exceeded 200GB imo.

What OS and filesystem are in use ?

Could you list the tmpdir when it's getting huge?  The filenames include
the PID, which would indicate if there's another procecss involved, or a
bug allowed it to get huge.
sudo du --max=2 -mx ./pgsql_tmp |sort -nr

BTW, pg_ls_tmpdir() hides directories, so you shouldn't rely on it for
listing temporary directories...

One possibility is that there are files in the tmpdir, which have been
unlinked, but are still opened, so their space hasn't been reclaimed.
You could check for that by running lsof -nn |grep pgsql_tmp Any deleted
files would say things like 'DEL|deleted|inode|no such'

> BTW, if that limit is really per process instead of per
> session/query then that is a Very Bad Thing(tm), because this makes the
> limit effectively worthless - if a query can spawn 8 parallel processes
> then you can suddenly, without any form of control, again fill up that disk.

8 is the default value of max_worker_processes and max_parallel_workers,
but 2 is the default value of max_parallel_workers_per_gather.  You're
free the change the default value to balance it with the temp_file_limit
(as suggested by the earlier-mentioned commit).

-- 
Justin